The following PL/SQL can be used to print out the resulting row fetch from a ref cursor that is returned from a stored procedure (OUT variable).
declare
result_cv sys_refcursor;
procedure print_ref_cursor
( p_query in out sys_refcursor,
p_date_fmt in varchar2 default 'yyyy/mm/dd hh24:mi:ss' )
is
l_theCursor integer;
l_columnValue varchar2(4000);
l_descTbl dbms_sql.desc_tab2;
l_colCnt number;
l_date date;
begin
l_theCursor := dbms_sql.to_cursor_number( p_query );
dbms_sql.describe_columns2
( l_theCursor, l_colCnt, l_descTbl );
-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_date );
else
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type in ( 12, 178, 179, 180, 181, 231 ) )
then
dbms_sql.column_value( l_theCursor, i, l_date );
l_columnValue := to_char( l_date, p_date_fmt );
else
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
end if;
dbms_output.put_line
( rpad( l_descTbl(i).col_schema_name || '.' ||
l_descTbl(i).col_name, 30 ) || ': ' || nvl(l_columnValue, 'NULL') );
end loop;
dbms_output.put_line( '=============================================' );
end loop;
dbms_sql.close_cursor( l_theCursor );
end;
begin
dbms_output.enable(null);
MySchema.MyPackage.MyStoredProcudure(in_parm_list, refCursor);
print_ref_cursor( result_cv );
end;