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:
And here's the anonymous block I used to transform the ref cursor into a DBMS_SQL cursor:
CREATE OR REPLACE
FUNCTION get_cursor RETURN SYS_REFCURSOR
OPEN c FOR
WHERE rownum < 51;
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.
PROCEDURE p( i_text IN VARCHAR2 )
dbms_output.put_line( i_text );
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 );
dbms_sql.close_cursor( l_cursorid );
Here's the output when I run it:
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.
Cursor ID: 1438299795
Column Count: 6
DESC_TAB Count: 6
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.
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 );