How to dynamically create a ref cursor

by Granville Bonyata on March 25, 2012

Ref cursors are the primary method of returning result sets in ODP.NET, and .NET is becoming more and more commonly used with Oracle, so ref cursors have taken on a much greater importance for Oracle developers.

There are a number of posts on how to dynamically read a ref cursor using the new Oracle 11g feature DBMS_SQL.TO_CURSOR_NUMBER but I haven’t seen much mention of dynamically creating a ref cursor.

A reasonably common requirement when using .NET for the user interface is for a procedure to return the contents of a table, where the table is dynamically selected. So, if you need a procedure that accepts a table name and returns the contents, it’s simple. You’ll need to return two ref cursors:
1) The data from the table
2) The list of columns/data types so .NET can grab the data.

CREATE OR REPLACE PROCEDURE return_table (p_table_name IN VARCHAR2,
p_ref_cursor OUT SYS_REFCURSOR,
p_column_list OUT SYS_REFCURSOR) IS

BEGIN
OPEN p_column_list FOR ‘select column_name, data_type, data_length from all_tab_columns where table_name = :tab_name’ USING p_table_name;

OPEN p_ref_cursor FOR ‘SELECT * FROM ‘||p_table_name;

END;

Previous post:

Next post: