Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs

oracleoracle-sql-developerplsql

I have a DB table has more than 50 columns.

I am querying this table, it should return only one row any time. as below image from sqldeveloper.

here I need to construct query/pl-sql block, to find out table column as a key and query result as values.

Eg: Key – Value

      TASK_EVENT_ID   - 1765

      EVENT_TYPE        - ASR_UPDATE

       ...... etc upto all columns in my table.

In my procedure I am constructing as like below.

 Message_handle RAW(64); 
Enqueue_options DBMS_AQ.enqueue_options_t;
Message_properties DBMS_AQ.message_properties_t;  msg
SYS.AQ$_JMS_BYTES_MESSAGE;
msg.set_string_property('TASK_EVENT_ID', v_TASK_EVENT_ID);
msg.set_string_property('EVENT_TYPE', v_EVENT_TYPE);

------------------------------------------------------------------------

Best Answer

According to your requirement you might be looking for UNPIVOT operator(If you are using 11g or later version). It converts column-based data into separate rows.
For example:

SQL> desc tbl1
 Name                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                         NUMBER
 NAME                           VARCHAR2(20)

SQL> SELECT * FROM tbl1;

    ID NAME
---------- --------------------
     1 test name

WITH all_objects_data AS(
SELECT TO_CHAR(id) id, name FROM tbl1)
SELECT column_name, column_value FROM all_objects_data UNPIVOT(column_value FOR column_name IN (id,name));

COLU COLUMN_VALUE
---- ----------------------------------------
ID   1
NAME test name

For details: Pivot and Unpivot

Related Solutions

Oracle instead of trigger update performance issue

I'm not sure you need instead of triggers here.

If you are on Oracle 11g release 1 or later, create a virtual column for your concatenated compound key in the original table. Then create a unique index on the virtual column. You ought to be able to use this virtual column as a unique key.

Duplicate data in a table given table name

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data.

Example below.

Test table:

create table yourperftest
( pk number,
  col1 number,
  col2 number,
  col3 number
);

Test data:

insert into yourperftest values (1,1,1,1);
insert into yourperftest values (2,2,2,2);
insert into yourperftest values (3,3,3,3);
insert into yourperftest values (4,4,4,4);
insert into yourperftest values (5,5,5,5);

Query to "double up" the table:

insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
)
select rownum+maxvalue, col1, col2, col3
from yourperftest,maxvalue;

I'd probably use an /*+APPEND*/ hint too, plus make sure you gather stats on the table afterwards.

Having said that, stats play a big part in testing - you want to make sure that your data/performance test environment is as close to production as possible. Going forward, the stats are the key to this. Stats that are different between test and production will cause different query plans to be picked by the optimiser, which may make your performance testing invalid when production is going tits-up.

EDIT:

Since you ask about doing this for more than 1 column, here's another example:

 insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
), col1tab as (
   select max(col1) as col1maxvalue
   from yourperftest
)
select rownum+maxvalue, col1maxvalue+rownum, col2, col3
from yourperftest,maxvalue,col1tab ;

CTEs are brilliant.

Best Answer

Related Solutions

Oracle instead of trigger update performance issue

Duplicate data in a table given table name

Related Question