Forum Stats

  • 3,740,533 Users
  • 2,248,268 Discussions
  • 7,861,320 Comments

Discussions

cannot execute cursor with nested cursor through ords

Emu
Emu Member Posts: 270 Bronze Badge

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)

Emu

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,766 Employee
    edited Jul 31, 2020 2:20PM

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

    pastedImage_0.png

  • Emu
    Emu Member Posts: 270 Bronze Badge
    edited Aug 1, 2020 3:18AM

    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
    Emu Member Posts: 270 Bronze Badge
    edited Aug 1, 2020 10:20AM

    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
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,766 Employee
    edited Aug 1, 2020 1:37PM
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 7,766 Employee
    edited Aug 1, 2020 1:39PM

    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

    EmuEmu
  • Emu
    Emu Member Posts: 270 Bronze Badge
    edited Aug 2, 2020 11:38AM

    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
    Emu Member Posts: 270 Bronze Badge
    edited Aug 2, 2020 12:50PM

    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...

Sign In or Register to comment.