tag:blogger.com,1999:blog-21122514.post5771123331066159448..comments2024-03-23T11:05:13.046+01:00Comments on Dimitri Gielis Blog (Oracle Application Express - APEX): Generate nested JSON from SQL with ORDS and APEX 5Dimitri Gielishttp://www.blogger.com/profile/16295721159626839167noreply@blogger.comBlogger30125tag:blogger.com,1999:blog-21122514.post-71645363151823215352021-07-11T10:40:39.465+02:002021-07-11T10:40:39.465+02:00Nice work but I am searching for nested json from ...Nice work but I am searching for nested json from tables query..<br />Can u suggest me that ?<br /><br /><br />Thanksps3rhttps://ps3r.com/noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-74600039548553284582018-04-06T10:13:15.785+02:002018-04-06T10:13:15.785+02:00Hi Pierre,
You mean do pagination for just a subs...Hi Pierre,<br /><br />You mean do pagination for just a subset?<br />I never tried that, but my first thought that is not possible.<br />The pagination size you specify is for the entire result set not just for a specific cursor as far as I know, but you might want to check on the ORDS forum.<br /><br />As a workaround you could write your own where clause and define some parameters in the sql of the cursor and paginate like that.<br />Is that an option?<br /><br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-12823412886697676142018-04-05T21:46:38.505+02:002018-04-05T21:46:38.505+02:00Hi dimitri,
Is there a way to paginate nested jso...Hi dimitri,<br /><br />Is there a way to paginate nested json?<br /><br />Regards<br /><br />PierrePierre Yottihttps://www.blogger.com/profile/12222001293669284610noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-21255880536871110852016-10-15T18:09:45.967+02:002016-10-15T18:09:45.967+02:00Hi Antony,
I'm not aware you can do that, or ...Hi Antony,<br /><br />I'm not aware you can do that, or it must be a new feature in a newer release of ORDS.<br />I see you already submitted on OTN https://community.oracle.com/thread/3981666 so the dev team will probably answer there.<br /><br />Thanks,<br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-56317389811410775852016-10-15T14:31:15.209+02:002016-10-15T14:31:15.209+02:00Hi Dimitri,
Is there a way to change the json out...Hi Dimitri,<br /><br />Is there a way to change the json output tag "{"items":" to something customized?<br /><br />Thanks<br />AntonyAntony Tnoreply@blogger.comtag:blogger.com,1999:blog-21122514.post-34482938963255446192016-06-08T15:28:31.074+02:002016-06-08T15:28:31.074+02:00Thanks for the suggestion with the pagionation set...Thanks for the suggestion with the pagionation set to 0. Dont get ORA-22902 anymore but <br /><br />500 Internal Server Error<br /><br />JDBCException [kind=SQL]Anonymoushttps://www.blogger.com/profile/17554343432466773792noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-67838512691260708212016-05-27T17:54:09.302+02:002016-05-27T17:54:09.302+02:00To the folks getting "ORA-22902: CURSOR expre...To the folks getting "ORA-22902: CURSOR expression not allowed" with nested cursor expressions, please check the following:<br />1. The pagination size must be set to 0 for the module AND the handler<br />2. Cursor expressions can only be in the top level<br />3. Always use the latest versions of ORDS and APEXDan McGhanhttps://www.blogger.com/profile/13971112000913341546noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-72132141009670013452016-04-27T11:50:24.266+02:002016-04-27T11:50:24.266+02:00I am facing the same issue, in toad working good, ...I am facing the same issue, in toad working good, but in apex not working.<br /><br />ThanksOmar Aljaberhttps://www.blogger.com/profile/03939647980907335667noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-69164768342195681632016-04-27T11:47:42.226+02:002016-04-27T11:47:42.226+02:00Thank you for you post, but I am facing this issue...Thank you for you post, but I am facing this issue ORA-00932: inconsistent datatypes: expected NUMBER got CURSER, and this Error during evaluation of resource template: GET getsub, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed<br />Omar Aljaberhttps://www.blogger.com/profile/03939647980907335667noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-78093490095680451372016-04-21T18:24:33.427+02:002016-04-21T18:24:33.427+02:00Hi Dimitri,
Thank you for the great example. I a...Hi Dimitri,<br /><br />Thank you for the great example. I am using APEX 5.0.3 with ORDS 3.0.4 on 11gXE (11.2.0.2) on a Win 10 laptop. I'm fairly new to APEX but have been using Oracle for 30 years.<br /><br />I tried using just a single level cursor in a query to create a nested json response, but<br />received the error:<br />500 Internal Server Error<br /><br />Error during evaluation of resource template: GET po, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed. <br /><br />This appears to point to a restriction about using cursors below the top level.<br /><br />I tried the same thing in SQL Plus and it works perfectly. In SQL Workshop inside APEX<br />it also fails...<br /><br />This was my sql statement:<br />select o.po_no, o.po_dt, o.vend_id, o.shipvia_cd, o.drop_ship, o.notes,<br />cursor ( select i.coin_id, i.line_desc, i.cert_no, i.unit_price, i.price_um_cf, i.um_id, i.nonstk, i.ord_qty<br /> from po_line i<br /> where o.po_no = i.po_no ) items<br /> from po o<br />where o.po_no = 6<br /><br />Any ideas what I might be doing wrong? Or configured incorrectly?<br /><br />Thank you<br /><br />BryanAnonymoushttps://www.blogger.com/profile/11970652045260084021noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-85064724431375394782016-04-20T17:33:22.565+02:002016-04-20T17:33:22.565+02:00Dimitri, that pagination tip was awesome! The curs...Dimitri, that pagination tip was awesome! The cursor error message is very cryptic, how did you figure that out?<br /><br />Btw- we were working in the same group under Marcie recently while you were contracting here. (Mark Lindros)John Adamshttps://www.blogger.com/profile/15455180048133554171noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-16137809037436851542016-03-10T00:50:17.726+01:002016-03-10T00:50:17.726+01:00is there a way to get rid of the items parent elem...is there a way to get rid of the items parent element from the json generated by ORDS ? I use<br /><br><br />jsonData = JSON.parse(response);<br><br />jsonData.items <br><br /><br />But in some places I need to set the source as URL directly. So without writing a custom application process using apex_json.write can I do it in ORDS restful query ?<br /><br /><br><br />thanks<br><br />-AAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-21122514.post-10918019111218004262016-02-14T00:58:30.409+01:002016-02-14T00:58:30.409+01:00Hi Dietmar,
My guess is that your pagination size...Hi Dietmar,<br /><br />My guess is that your pagination size is set to a number, which I saw causes this issue.<br />I always put my pagination size to 0. (both at module and get level)<br /><br />Hope that helps,<br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-63439075727315378582016-02-09T00:22:55.095+01:002016-02-09T00:22:55.095+01:00Hi Dimitri, is this still working with ords3? I ge...Hi Dimitri, is this still working with ords3? I get a ORA-22902: CURSOR expression not allowed when trying to use the following statement: <br /><br />select dept.*,<br /> cursor(select emp.* from emp where emp.deptno=dept.deptno)<br /> from dept<br /> <br />Everything is soo buggy, drives me crazy. <br /><br />Thanks, <br />~Dietmar. <br /><br />Anonymoushttps://www.blogger.com/profile/04283721022948206416noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-89599142611652174792015-10-30T17:22:41.205+01:002015-10-30T17:22:41.205+01:00This would help reader of this blog : JSON Format...This would help reader of this blog : <a href="http://jsonformatter.org" rel="nofollow"> JSON Formatter</a> and <a href="http://codebeautify.org/jsonviewer" rel="nofollow"> JSON Viewer</a> for JSON LoverAwesomehttps://www.blogger.com/profile/15037598698637581441noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-18927381699280849272015-10-16T13:14:52.092+02:002015-10-16T13:14:52.092+02:00As per ORA-22902 error mentioned above, I too was ...As per ORA-22902 error mentioned above, I too was getting this error and overcome this by setting Pagination Size to 0 (zero). This is set in RESTful Service Module page.Sunny Patelhttps://www.blogger.com/profile/05927043025790402983noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-75758157813240722422015-07-18T01:32:49.608+02:002015-07-18T01:32:49.608+02:00Very informative, as Always, thank you Dimitri.
Ju...Very informative, as Always, thank you Dimitri.<br />Just wanted to say that for some reason, the simple output procedure apex_json.write(sys_refcursor) works as expected ONLY if you don't have nested Cursors.<br /><br />I couldn't get a comparable query with nested cursors to the example shown above in the screenshot to work. No errors, just no output.<br />with some digging around I ended up following what the documentation "seems to recommend" as:<br /><br /> apex_json.open_object;<br /> apex_json. write('json object name', c);<br /> apex_json.close_object;<br /><br />And it works as expectedziZovhttps://www.blogger.com/profile/03705113989127599525noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-50580300834720514742015-06-10T19:44:09.143+02:002015-06-10T19:44:09.143+02:00Hello dimitri,
Thank you for response, we will tr...Hello dimitri,<br /><br />Thank you for response, we will try another database (oracle 11.2.0.4 database standard edition one ), and shar the result with you,<br /><br />Simo, Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-21122514.post-24911273463585259492015-06-02T21:40:04.499+02:002015-06-02T21:40:04.499+02:00Hi Simo,
As far as I know it's supported yes,...Hi Simo,<br /><br />As far as I know it's supported yes, but some people get that error, but I've no idea way.<br />I'm on 12.1.0.2.0 (linux) and it still works for me.<br /><br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-23800739337942476072015-06-02T20:32:08.999+02:002015-06-02T20:32:08.999+02:00Hello dimitri,
I tried to use the apex_json packa...Hello dimitri,<br /><br />I tried to use the apex_json package in oracle 12c but i have an ORA-22902 error when i try to introduce a cursor in my query. do you know if it's still supported?<br />thanks in advance.<br />SimoAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-21122514.post-27133915480091878772015-04-24T18:59:18.196+02:002015-04-24T18:59:18.196+02:00Hi Jerry,
I haven't done real performance/str...Hi Jerry,<br /><br />I haven't done real performance/stress testing on json services yet. I propose you try both in your environment and see which ones work best for your environment.<br /><br />Hope that helps,<br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-1109421612559295672015-04-24T17:57:35.513+02:002015-04-24T17:57:35.513+02:00Thanks Dimitri, so, pljson and other methods used ...Thanks Dimitri, so, pljson and other methods used string concatenation, one of the slowest operations in plsql.<br /><br />With ORDS, we observed that the pure SQL web services were significantly faster, leveraging java to perform the json conversion.<br /><br />Have you observed any significant performance improvements with plsql web services using the native json capability?<br /><br />Thanks!Anonymoushttps://www.blogger.com/profile/06987867595262565977noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-21536489975559853922015-01-23T09:26:45.513+01:002015-01-23T09:26:45.513+01:00Hi Andre,
I did some new blog posts about JSON.
-...Hi Andre,<br /><br />I did some new blog posts about JSON.<br />- http://dgielis.blogspot.be/2015/01/json-for-apex-developers-part-1.html<br />- http://dgielis.blogspot.be/2015/01/json-for-apex-developers-part-2.html<br /><br />Hope that helps,<br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-76620326840690918442015-01-21T14:25:59.255+01:002015-01-21T14:25:59.255+01:00Hi Dimitri,
I would like to see a little example ...Hi Dimitri,<br /><br />I would like to see a little example from real world, so I can consume data from other rest service that is already created and one I can create a REST to do the usual like select, insert and update my data. All using Oracle Apex and XE 11g. <br /><br />The way you explain how things works I find it very easy to understand and a very simple and functional example would do the trick.<br /><br />REST and JSON are kind of knew for me and I believe the same for a lot of other APEX developers, cant wait for your new post about consuming json and its many possibilities.<br /><br />Thank you again for your time, <br />AndréAndrehttps://www.blogger.com/profile/04157190234956911286noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-4357738558307304522015-01-20T02:28:45.416+01:002015-01-20T02:28:45.416+01:00I'm with Andre. You mention how easy it is to ...I'm with Andre. You mention how easy it is to generate JSON with ORDS supplying a SQL statement, but there are plenty of mechanics around it that have been skipped over in this post.Scott Wesleyhttps://www.blogger.com/profile/18106937181788036683noreply@blogger.com