Friday, September 5, 2008

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.

3 comments:

Anonymous said...

I keep getting PLS-00302: component 'TO_CURSOR_NUMBER' must be declared when I run this.. Do you know why??

Anonymous said...

PLS-00302: component 'TO_CURSOR_NUMBER' must be declared

Do you know why I get this error when I execute this..

You can reach me @ sunilvarma2008@gmail.com...

Thanks,
Sunil

Anonymous said...

Sunil, you get this error because you're running an older database version. This function is implemented in Oracle 11g database onwards.
Check this:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sql.htm