How do I get the columns from a REF CURSOR?
When working with dynamic sql you sometimes have to get the columns (names, types etc.) at runtime because the result isn't always the same. This can easily be achieved when working with a cursor by using dbms_sql.describe_columns, but when you have a SYS_REFCURSOR things get a bit tricky.
Recipe #1 - Oracle 11g only
Since Oracle 11g there's a method called DBMS_SQL.TO_CURSOR_NUMBER(l_rcursor) which you can use to cast a ref cursor into a cursor and use dbms_sql.describe_columns. The following example shows you how to get a list from an SQL SELECT query which is located in a string and dynamically openend into a REF CURSOR.
DECLARE TYPE ref_cursor IS REF CURSOR; rc_ ref_cursor; c_ NUMBER; i_ NUMBER; col_count_ NUMBER; desc_tab_ DBMS_SQL.DESC_TAB; BEGIN OPEN rc_ FOR 'SELECT year, revenue FROM revenues'; c_ := DBMS_SQL.to_cursor_number(rc_); DBMS_SQL.DESCRIBE_COLUMNS(c_, col_count_, desc_tab_); FOR i_ IN 1..col_count_ LOOP DBMS_OUTPUT.PUT_LINE(desc_tab_(i_).col_name); END LOOP; DBMS_SQL.CLOSE_CURSOR(c_); END;
Recipe #2 - Before Oracle 11g
If you're working with an Oracle version older than 11g, the possibilities are a bit limited. You could use a Java procedure to get access to more features when working with ref cursors but that's mostly a bit beyond for such a simple problem.
However, if you just want to get the column names, there's one way to achieve that, look at the following example:
DECLARE cur_ SYS_REFCURSOR; CURSOR get_columns IS SELECT t2.column_value.getrootelement() name, EXTRACTVALUE(t2.column_value, 'node()') VALUE FROM (SELECT * FROM TABLE (XMLSEQUENCE(cur_))) t1, TABLE (XMLSEQUENCE(EXTRACT(t1.column_value, '/ROW/node()'))) t2; BEGIN OPEN cur_ FOR 'SELECT 42 the_answer FROM DUAL'; FOR rec_ IN get_columns LOOP DBMS_OUTPUT.put_line(rec_.name || ': ' || rec_.VALUE); END LOOP; END;
What does this little script do? It basically casts a ref cursor into an XMLSequence from which you can get the column names as well. This can be quite handy if you'd like to export a table with an unknown structure into a text file.