Hello Everyone, I am new to the site and wasn't sure which community to post this question since it involves PL/SQL and JSON so I thought I would start here.
I am executing a SQL Plus script that calls a PL/SQL Procedure that returns a Ref Cursor. I am trying to put the ref cursor results into JSON format but am receiving an error that the table or view does not exist (ORA-00942).
Here is the script that I am executing (with actual schema/package/procedure names changed but formats the same):
var rc refcursor;
begin
schema_name.package_name.procedure_name (in_param, :rc);
open :rc for ‘ with data as ( select * from rc)
select json_serialize(json_arrayagg(json_object(*)) pretty) as jason_result from data’;
end;
/
When I execute the above script, I get the error messages:
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 3
I know that the Package/Procedure call is working because I can run only the first three lines and print the Ref Cursor. So the error lies in the line(s) following the procedure call.
One other note: This example came from an internet search for working with JSON and Ref Cursors and in the example that they gave, a PL/SQL Function instead of a Procedure was being called to return the Ref Cursor and the code worked without any problems. I don’t see where that would make a difference but just wanting to make all information available.
Any ideas on what may be causing this error would be greatly appreciated.