cannot execute cursor with nested cursor through ords

Emu

I have a cursor that contains a cursor as in the example below:

DECLARE  c sys_refcursor;BEGINopen c for select deptno,                                              dname,                                              cursor(select empno,                                                                                     ename                                                                                 from emp e                                                 where e.deptno=d.deptno) emps                              from dept d;  apex_json.open_object;   apex_json. write('departments', c);   apex_json.close_object;

If I write this output to a clob it works fine.  If I remove the inner cursor it works fine over ORDS.

However with the inner cursor if I try to write it over ORDS...I get an indecipherable:

555 User Defined Resource Error
Does anyone have any ideas what might be happening here?

(Using autonomous database : Version 19c, ORDS: 19)

thatJeffSmith-Oracle

It works as 'plain ole SQL', be sure to disable paging though, won't work with a cursor

Emu

Yes you are right.  Unfortunately I really need paging, but i should be able to do the pagination myself.  I really wanted to use a procedure to make this.    I'll have to see if there is a way I can remove the other information that ORDs seems to add when you make json from SQL.

There are so many quirks to using ORDS.  I didn't think I was trying to do something very hard but a few attempts have failed now!

It maybe helpful if someone updated  the documentation  to make it clear that apex_json.write won't always work with ORDS. : https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_json.htm#CJHBGDIB

Emu

Is there a way to remove the stuff oracle adds to a 'plain ole SQL' ords setup like "hasMore":true,"limit":2,"offset":0,"count":2,"links":[{"rel":"self","href":?

thatJeffSmith-Oracle

See the different source types

https://www.thatjeffsmith.com/archive/2019/02/ords-and-source-types/

thatJeffSmith-Oracle

Apex_json will surely work with ords..if you're generating the json yourself,  you need to use the media resource source type

Then ords won't touch your output when passing it back with your mime type

Emu

the media resource source type: Executes a SQL Query conforming to a specific format "Executes a SQL Query conforming to a specific format" so it won't work with my proc. 

I was surprised my proc couldn't use htp.p to print out the document...its weird if i save it in a table then it works.  but if I try to just load it in a variable and then spit it out through htp.p then it does't work unless I remove the nested cursor. Maybe I'm making an error somewhere but I don't see where.

Emu

The solution that works for me is to use JSON_OBJECT to create the whole document, and serve it up with hpt.p.

I was hoping not to have to get so manual with the json creation - and to be able to follow more closely with what I've done elsewhere but it wasn't too bad and I really have spent way to long on this already.

Maybe Oracle will make this easier for us some year...