Problem
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.
Solution
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.
Comments