DBMS_SQL.TO_CURSOR_NUMBER

One of our programmers asked me the other day if it was possible to retrieve the meta-deta (columns, column types, etc) from a ref cursor. I told him that it's easy to do in Java (which I believe is one of his strong suits), but that's not what he wanted.

He wanted to do it in SQL or PL/SQL. Essentially, he wanted to be able to generate type-safe classes (I could look it up but it's Friday and I'm lazy). Also as a beginning to generate code so it can be moved out of the database into the web service layer. It hasn't been completely decided whether that will happen or not...once it is, I will go with the flow (or find a new job if I can't handle it right?).

So, how to do it in PL/SQL? I wasn't sure, but I immediately thought that if it could be done, it would be by using the DBMS_SQL package. So I opened the docs to see what was available to me.

Reading through, I found a procedure, TO_CURSOR_NUMBER. Basically it allows you to accept a ref cursor as an input parameter and convert it to a DBMS_SQL cursor handle. Then you can use all the goodness of DBMS_SQL as you normally would.

I created a short working example (which doesn't differ a whole lot from the example provided). Here's the function returning a ref cursor:

CREATE OR REPLACE
FUNCTION get_cursor RETURN SYS_REFCURSOR
IS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR
    SELECT
      owner,
      table_name,
      tablespace_name,
      status,
      logging,
      last_analyzed
    FROM all_tables
    WHERE rownum < 51;
  RETURN c;
END get_cursor;
/
show errors

And here's the anonymous block I used to transform the ref cursor into a DBMS_SQL cursor:

DECLARE
  c SYS_REFCURSOR;
  l_cursorid NUMBER;
  l_column_count INTEGER;
  l_describe_table DBMS_SQL.DESC_TAB;
  l_numvar NUMBER;
  PROCEDURE p( i_text IN VARCHAR2 )
  IS
  BEGIN
    dbms_output.put_line( i_text );
  END p;
BEGIN
  c := get_cursor;

  l_cursorid := dbms_sql.to_cursor_number( c );
  p( 'Cursor ID:  ' || l_cursorid );

  dbms_sql.describe_columns( l_cursorid, l_column_count, l_describe_table );
  p( 'Column Count:  ' || l_column_count );
  p( 'DESC_TAB Count:  ' || l_describe_table.COUNT );


  FOR i IN 1..l_describe_table.COUNT LOOP
    p( 'Column:  ' || l_describe_table(i).col_name ); 
  END LOOP;

  dbms_sql.close_cursor( l_cursorid );
END;
/

I get tired of typing out DBMS_OUTPUT.PUT_LINE every 2 lines, so if I am going to use it more than once or twice, I use a procedure named "p" to do so.

Here's the output when I run it:

Cursor ID:  1438299795
Column Count:  6
DESC_TAB Count:  6
Column:  OWNER
Column:  TABLE_NAME
Column:  TABLESPACE_NAME
Column:  STATUS
Column:  LOGGING
Column:  LAST_ANALYZED

Fairly easy to use. It took about 20 minutes to read the docs and write it up. As you can see from the definition of the DBMS_SQL.DESC_TAB (below), you have access to quite a number of attributes of the cursor.

TYPE desc_rec IS RECORD (
      col_type            BINARY_INTEGER := 0,
      col_max_len         BINARY_INTEGER := 0,
      col_name            VARCHAR2(32)   := '',
      col_name_len        BINARY_INTEGER := 0,
      col_schema_name     VARCHAR2(32)   := '',
      col_schema_name_len BINARY_INTEGER := 0,
      col_precision       BINARY_INTEGER := 0,
      col_scale           BINARY_INTEGER := 0,
      col_charsetid       BINARY_INTEGER := 0,
      col_charsetform     BINARY_INTEGER := 0,
      col_null_ok         BOOLEAN        := TRUE );

I think I found (with the help of my DBA) the conversion of the col_type (which is an integer) to it's text equivalent, but I can't say for certain yet as I haven't tried it.