Need help to construct query/pl-sql block to get DB table column name and query result as key-value pairs 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);

------------------------------------------------------------------------
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