I needed to debug and test a lot of PL/SQL procedures that return SYS_REFCURSORs in output parameters. I wanted to print contents of this SYS_REFCURSORs. I checked https://oracle-base.com/articles/misc/check-the-contents-of-ref-cursors. Because we have APEX installed the winner was obvious: "JSON Using APEX_JSON".
Script:
declare
l_cursor1 SYS_REFCURSOR;
l_cursor2 SYS_REFCURSOR;
l_cursor1 SYS_REFCURSOR;
l_cursor2 SYS_REFCURSOR;
procedure returnTwoCursors (
po_cursor1 OUT SYS_REFCURSOR,
po_cursor2 OUT SYS_REFCURSOR
)
as
begin
open po_cursor1 for
SELECT level AS id,
'Description for ' || level AS description
FROM dual
CONNECT BY level <= 5;
open po_cursor2 for
SELECT level AS id,
'Random value for level' || level ||' is '|| dbms_random.random AS description
FROM dual
CONNECT BY level <= 3;
end;
begin
returnTwoCursors (l_cursor1, l_cursor2);
APEX_JSON.initialize_clob_output;
APEX_JSON.open_object;
APEX_JSON.write('l_cursor1', l_cursor1);
APEX_JSON.close_object;
APEX_JSON.open_object;
APEX_JSON.write('l_cursor2', l_cursor2);
APEX_JSON.close_object;
DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
APEX_JSON.free_output;
end;
DBMS Output:
{
"l_cursor1":[
{
"ID":1
,"DESCRIPTION":"Description for 1"
}
,{
"ID":2
,"DESCRIPTION":"Description for 2"
}
,{
"ID":3
,"DESCRIPTION":"Description for 3"
}
,{
"ID":4
,"DESCRIPTION":"Description for 4"
}
,{
"ID":5
,"DESCRIPTION":"Description for 5"
}
]
}
{
"l_cursor2":[
{
"ID":1
,"DESCRIPTION":"Random value for level1 is 238819274"
}
,{
"ID":2
,"DESCRIPTION":"Random value for level2 is -900822882"
}
,{
"ID":3
,"DESCRIPTION":"Random value for level3 is -929089605"
}
]
}
