Test suite for refcursors

One thing I’ve been waiting for is the cursor conversion function that converts a ref cursor to a DBMS_SQL cursor number. This is finally available in Oracle 11. The reason this is so valuable is because I often pass result sets as sys_refcursor output parameters from stored procedures, and up until recently there wasn’t any way to inspect the cursor without knowing explicitly what the structure of the cursor is. You have to fetch the cursor into explicitly defined variables in pl/sql, and there is no way to fetch the column names. In php or perl, the oci layer exposes column names and it always made me wonder why this flexibility is not available in the source language. In sqlplus, you can always use a refcursor bind variable and use the sqlplus ‘print’ command to print the cursor out, but it can look pretty ugly if the result set is large. What do you do if you just want to know the column names or the row count? This is where DBMS_SQL.TO_CURSOR_NUMBER is useful. You can convert the cursor to a dbms_sql cursor number and use the DBMS_SQL package to inspect column names and data types.

Here is an example:

 SQL> set serveroutput on
 SQL> variable x refcursor
 SQL> exec open :x for select * from v$session;

 PL/SQL procedure successfully completed.

 SQL> exec cursor_util.print_cursor_headings( :x );
SADDR SID   SERIAL#  AUDSID   PADDR USER# USERNAME COMMAND  OWNERID  TADDR LOCKWAIT
STATUS   SERVER SCHEMA#   SCHEMANAME  OSUSER   PROCESS  MACHINE  TERMINAL PROGRAM
TYPE  SQL_ADDRESS SQL_HASH_VALUE SQL_ID     SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID
PREV_SQL_ADDR  PREV_HASH_VALUE   PREV_SQL_ID   PREV_CHILD_NUMBER
PREV_EXEC_START   PREV_EXEC_ID   PLSQL_ENTRY_OBJECT_ID   PLSQL_ENTRY_SUBPROGRAM_ID
PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID  MODULE   MODULE_HASH ACTION   ACTION_HASH
CLIENT_INFO FIXED_TABLE_SEQUENCE   ROW_WAIT_OBJ#  ROW_WAIT_FILE#
ROW_WAIT_BLOCK#   ROW_WAIT_ROW#  LOGON_TIME  LAST_CALL_ET PDML_ENABLED
FAILOVER_TYPE  FAILOVER_METHOD   FAILED_OVER RESOURCE_CONSUMER_GROUP  PDML_STATUS
PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION  CLIENT_IDENTIFIER
BLOCKING_SESSION_STATUS BLOCKING_INSTANCE   BLOCKING_SESSION  SEQ#  EVENT#   EVENT
P1TEXT   P1 P1RAW P2TEXT   P2 P2RAW P3TEXT   P3     P3RAW WAIT_CLASS_ID  WAIT_CLASS#
WAIT_CLASS  WAIT_TIME   SECONDS_IN_WAIT   STATE WAIT_TIME_MICRO
TIME_REMAINING_MICRO  TIME_SINCE_LAST_WAIT_MICRO SERVICE_NAME   SQL_TRACE
SQL_TRACE_WAITS SQL_TRACE_BINDS  SQL_TRACE_PLAN_STATS SESSION_EDITION_ID
CREATOR_ADDR   CREATOR_SERIAL#

 PL/SQL procedure successfully completed.

 SQL> exec open :x for select * from v$session;

 PL/SQL procedure successfully completed.

 SQL>
 SQL> begin
   2     cursor_util.print_cursor( p_cursor=>:x,
   3                           p_column_delimiter=> ',',
   4                           p_max_rows =>20,
   5                           p_select_list => cursor_util.tarray('SID','SERIAL#','SQL_ID')
   6     );
   7  end;
   8  /
 SID,SERIAL#,SQL_ID
 265,11423
 268,5960,dcq6vvpwn1dph
 273,64357,dcq6vvpwn1dph
 274,46000
 276,32034,6hfd8m7xafq1k
 279,160
 280,11
 282,2,54w21muak2nq2
 283,3
 287,3
 289,1
 291,190
 292,1,c5brdpybgqss6
 294,38879,dcq6vvpwn1dph
 297,1
 299,1
 303,1
 304,1,c5brdpybgqss6
 305,1,c5brdpybgqss6
 308,36793

 PL/SQL procedure successfully completed.

Here’s the code that does the work:
Continue reading

Posted in pl/sql | Tagged , , | 9 Comments