SQL & PL/SQL :: Changing Row Value As Column Head?

Jul 22, 2012

change my row value as column head. Here are my scripts:

Create table temp (code varchar2(3), head varchar2(4), value number(5));

insert into temp values('101','1101',500);
insert into temp values('101','1102',700);
insert into temp values('101','1103',600);
insert into temp values('102','1101',1000);
insert into temp values('102','1102',800);
insert into temp values('102','1103',900);
insert into temp values('103','1101',600);
insert into temp values('103','1102',400);
insert into temp values('103','1103',500);

select * from temp;

CODEHEADVALUE
1011101500
1011102700
1011103600
1021101100
1021102800
1021103900
1031101600
1031102400
1031103500

Now I like to get the above oputput as under:

Code 1101 1102 1103
101 500 700 600
102 100 800 900
103 600 400 500

View 4 Replies


ADVERTISEMENT

Application Express :: Pivot - 2 Head Columns Possible?

Apr 23, 2013

is it possible to create two head columns sql-pivot.

something like that...

SELECT * from ( Select position,cat_id, att_name as ATTRIBUTE, pro_name, value,competitor
     FROM   cat_attributes)
     PIVOT (
                    max (value)
                    FOR (pro_name) IN ('MS 3','MS 4'))
for (competitor) in ('1','1');

View 0 Replies View Related

Changing Sql Plus Column Width / Format?

Sep 20, 2004

I am using SQL Plus v9.2.0.1.0 and am having trouble with the column widths. By way of example:

Let's say the column is a varchar(2) and the column name is called V9ABC12345. When I see the result of my query I only get the heading name as V9 ie the maximum width of 2 characters. The table has over 100 columns and I know I can specify the column width using the format command but I am using the select * from table_name command

How do I change my formatting to include the column heading by default?

View 6 Replies View Related

SQL & PL/SQL :: Updating Table With Sequentially Changing Column Name?

Aug 9, 2012

I am trying to update all rows of 100 column of a table with '0'. The column name is sequentially increasing one like EMP_1,EMP_2,EMP_3, etc. I tried using the below code but I am getting ora-06550 and ora-00927 error's.

begin
FOR i in 1..100 loop
UPDATE EMP_DETAILS SET EMP_'||i||' =0
END LOOP;
COMMIT;
END;

View 6 Replies View Related

Reports & Discoverer :: How To Join Head / Main And Trailer Section

Feb 10, 2012

i have generate a report using report wizard but i want to know how join main section and head section , what ever you do in report wizard impact in main section ,i have done some head section ,when i run report, head section run first and main section in next page .. how to join both sections in single page output..

View 2 Replies View Related

Application Express :: 4.2 - Changing Link Column From ROWID To ID?

May 3, 2013

I have a report linked to a form. The link column is currently ROWID and I want to change it to the Primary Key of the DB table, which is called ID.

I have done this before on a previous report/form where that ID was a display column and it works fine. Now I'm trying to copy what I did, which is this:

On the report page, go to Interactive Report attributes, scroll down to Link Column, and for Item 1, change the Name and Value.

The problem is that Name does not list P52_ID. If I type it in, it throws an error when I run the page.

I think I added ID, which is also a Display column, later on, so I wonder whether Apex has a refresh issue and can't "see" P52_ID.

View 1 Replies View Related

Application Express :: Changing Sequence In Content Frame Not Changing Ordering Of Regions

Apr 24, 2013

Using Application Express 4.2.1.00.08

Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.

The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.

View 3 Replies View Related

Changing A Lot Of Information In Database

Feb 18, 2011

I have been implementing a script to change a lot of data in a database production.Because of this the database will be 100% dedicated to the execution of that script, in the sense that nothing else will be running in this period (the application will be stopped).

what can i do to improve performance of that execution? is there any oracle manual online for this type of problem? I do not know if it's possible, but I'm thinking of things like disabling locking mechanism (if possible I could run instead of a process many processes in parallel), disabling index growing (during the process), disabling constraints.

View 1 Replies View Related

Changing Year Format

Nov 23, 2009

I have a table in which years are stored in the form '2008/2009'. This is making it very difficult for me to do any calculations on that field and so I was wondering if there was a way to change the years (in a query and not in the actual table) so that if the year was '2008/2009' I would have just '2009'.

View 4 Replies View Related

Get Exact Value Without Changing Datatype?

Nov 23, 2011

I created a table of Number(20,4) column. I inserted an amount value 999999999999999.5555 but this value is rounded off to 1000000000000000.0000 automatically in Oracle. How to avoid this? I tried for less number of digits and I am getting the exact value. Is there any way to get the exact value without changing the datatype?

View 1 Replies View Related

ORA-ROWSCN Changing Unexpectedly?

Oct 27, 2012

I am struggling with the ORA_ROWSCN pseudocolumn. (Oracle version is 11g). I am aware that without ROWDEPENDANCIES set on the tables, the SCN of a row is in reality that of the block in which the row is resides. The problem I am seeing goes something like this:

1) A datapump import creates a large table (400M+ rows) at 11 am in the morning
2) No subsequent changes are made to this table (at least that I know of)
3) Despite no changes being made the ORA_ROWSCN of almost all the rows changes every few minutes. (a few rows remain with SCN's indicating the original time of import, but the rest change)

I detect this behavior by running this query:

SELECT ora_rowscn, SCN_TO_TIMESTAMP(ora_rowscn),COUNT(*)
FROM test_table GROUP BY ora_rowscn

This ran around 5.15 am on 10/27 and resulted in:

72353518 27-OCT-12 05.17.21.000000000 AM 492009391
67515248 26-OCT-2 11.01.45.000000000 AM 370

Note that almost all rows have a very recent SCN. Only a tiny portion of the rows (370) have an SCN consistent with when the import completed. If I rerun the query a bit later, the SCN of the large rowcount will have advanced again.Block size on the database is 8192.

View 3 Replies View Related

SQL & PL/SQL :: Changing Order Of Columns?

Apr 30, 2012

i have a table test on 4 different-different database, at the starting structure is same in all the database. and now i want to change the datatype of the primary key column named "testid" , then i add a temp column in test table (and it is added at the last in table).

i have copied the data of testid in temp column and renamed it as testid and i dropped the testid column , then the problem is that primary key column will comes at last and i want to make it like previous position so that there will not be any difference in all 4 database as in structure.

is it possible to changing column order as our desire without dropping the table?and i made all the script to changing the datatype of primary key column.

View 3 Replies View Related

RAC & Failsafe :: Changing VIP In RAC Node?

Nov 16, 2011

I need to change Virtual IP for one of my RAC node as it is conflicting with another server's IP.

View 1 Replies View Related

SQL & PL/SQL :: Changing DATE Type?

Oct 25, 2011

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

In the database, NLS_DATE_FORMAT = DD-MON-RR.I have a date field called LOAD_DATE in one of the tables. I want to store the value as '25-OCT-11 01:16:25 PM' in LOAD_DATE field. Is there a way to do it without changing the NLS parameter (ALTER SESSION...) ?

View 8 Replies View Related

Changing Tablespace Of Schema

Oct 26, 2012

I have created my schema in SYSTEM schema way back accidentally by forgetting to specify the database tablespace. There is about 60 objects (tables, indexes, functions etc) and 6MB Of data.

What would be the best way migrate the schema to the tablespace I planned to put it into? .

View 7 Replies View Related

Changing The Drives Of Datafiles

Jul 9, 2012

How to move the datafile from e-drive to d-drive in oracle 11g?

View 7 Replies View Related

Database Idle But SCN Is Changing Continue

Jul 22, 2013

Current database scn is changing faster even though no application,no transaction is running. Database is idle but scn is changing continue, why?

17:25:00 SQL> select current_scn from v$database;

CURRENT_SCN
-----------
2477896
1 row selected.
[code]....

View 2 Replies View Related

Forms :: Changing Message Type

Nov 14, 2011

Some time we use message function in oracle forms.

message('...');

can we change the type of this message? Icon should be caution instead of Stop.

View 1 Replies View Related

Changing Values Of Sysdate In Session Only?

Feb 19, 2007

Is there a way of changing the values of sysdate in a session only?

not the formate the actual values

is if the date was 31/12/2003 and i wanted to change it to 31/05/2003

could this be done

View 4 Replies View Related

Can Execute Procedure On Some Other Day Without Changing Actual

Aug 21, 2012

I have a procedure which will execute on every Monday. Same is not executed last Monday. Can I execute the Procedure on some other day with out changing the actual procedure?

View 1 Replies View Related

Windows :: Changing JDK From Production Client?

Oct 20, 2011

I have been asked to create a new instance of an application server. As part of this, my management has asked me to replace any old JREs or JDKs.

We run 10g, so I installed the 10203_vista_w2k8_x86_production_client on my new server. I would like to replace the 1.4.2 JDK that is bundled with the Oracle Production Client with 1.6.0.

View 1 Replies View Related

RAC & Failsafe :: Changing Disk Paths In ASM

Jul 13, 2012

We are about to undertake a storage migration of our RAC environment from EMC to XIV storage.

The migration method we are using means that we will be presented with identical disks post the migration, however the UNIX paths to the disk devices will be changing. Using our migration method it is not possible to present the old and new devices at the same time.

Therefore once the migration is complete i need to update the device configuration to reflect the new paths. I believe this is done using the following techniques but I am unsure as some of the documentation is ambiguous and I have not done this before

Changing path to Voting Disks

crsctl add css votedisk <new_path_to_disk> -force
crsctl delete css votedisk <old_path_to_disk> -force

Changing path to OCR Disks

ocrconfig repair ocr <new_path_to_ocr>
ocrconfig repair ocrmirror <new_path_to_ocrmirror>

Changing paths to ASM disks

alter system set asm_diskstring='<new_device_path>' scope=both;

View 3 Replies View Related

SQL & PL/SQL :: RESTRICT_REFERENCE Changing A Database State

Mar 24, 2010

When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?

as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.

RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state

what is there to state as i know what my code is doing.

without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).

View 7 Replies View Related

Server Administration :: Changing Web IP Address

Jun 22, 2011

trying to get oracle-xe-universal_10.2.0.1-1.0_i386.deb rolling on Debian/6.0.1a which coincidentally is on another server, not on my desktop, so I obviously can't reach the web frontend at 127.0.0.1:8080. And I couldn't trick it with a simple TCP proxy on the server:

nc -l -p 80 127.0.0.1 8080
and then on the client:
telnet 192.168.0.252 80
bailed out with an error
invalid connection to [192.168.0.252] from (UNKNOWN) [192.168.0.1] 35798

So, is there a way to change the listening IP address, or to allow IPs other than the local host to connect? I've tried some googling around,.

View 13 Replies View Related

Forms :: Changing The Colour Of Item?

Aug 5, 2010

For eg. If I want to change the background colour of Item it can be done in 6i using set_item_property builtin and can be written in mouse enter trigger. In Forms 10g when_mouse_enter trigger not be used.

How to implement the same funtionality or FOrms 10g.

View 3 Replies View Related

PL/SQL :: Changing Format From Numeric To Char

Jul 26, 2012

I want to get the last month of my table “a” (Which is in number format) and after that I want to change it to a date format and insert into my table “b”.

I have already done the max month query:

SELECT MAX(MONTH_ID) FROM Table_a;

SELECT DISTINCT MONTH_ID ---This data is the one that I would like to change format "mon-yyyy"
FROM Table_a
WHERE MONTH_ID = (SELECT MAX(MONTH_ID) FROM Table_a);

I am getting trouble with the to_char function, I have been reviewing how it works and how to handle it.

View 3 Replies View Related

Raw Datatype Changing The Case Of The Data?

Jul 31, 2013

I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the  uppercase .Is it the expected behavior of this datatype.   I am using Oracle 11g  and attaching the ddl and dml pertaining to the column 

CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) );  insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf'); 

But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF

View 4 Replies View Related

Forms :: Before / While Changing - Display All Records?

Jun 22, 2011

I have created two tabs tab1 & tab2. after changed(not while changing/before changing)) into tab2 all records should be display.

I have written execute_query in when-tab-page-changed.but that is before/while changing.i want after entered in tab2 it should be display.

View 1 Replies View Related

Finding Client Who Is Changing Schema Password

Mar 2, 2011

I want to send mail whenever the schema password is changed by the developers (clients) . I created this trigger to send mail. I am able to know which schema's password is changed but i want know how to get the client details who is changing the password.

Create Trigger Pwd_chg_trigger
before alter
on database
declare
begin
if (ora_dict_obj_type = 'USER') then
DBMS_OUTPUT.PUT_LINE(' ORA_DICT_OBJ_OWNER = ' || ORA_DICT_OBJ_NAME); -- to know which schema
-- using the above value and additional formatting i am sending the mail using my SMTP
end if;
end;
/

View 3 Replies View Related

Grid Control Could Not Started Up After Changing IP Address

Sep 13, 2012

I've changed the ip address of the enterprise manager grid control server. But oms could not be started. I got the error below:

[oracle@YDFULGRID bin]$ ./emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright © 1996, 2010 Oracle Corporation. All rights reserved.
Starting WebTier...
WebTier Successfully Started
Starting Oracle Management Server...
Oracle Management Server Could Not Be Started
Oracle Management Server is not functioning because of the following reason:
Connection to the repository failed. Verify that the repository connection information provided is correct.
Please check /oracle/gc_inst/em/EMGC_OMS1/sysman/log/emctl.log for error details

When i checked the log:

I am getting this error:

2012-09-13 16:23:56,602 [main] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.
2012-09-13 16:23:56,602 [main] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to servername
2012-09-13 16:24:17,638 [main] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503
2012-09-13 16:24:17,638 [main] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3
2012-09-13 16:24:17,639 [main] INFO wls.OMSController statusOMS.950 - statusOMS finished with result: 3

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved