tag:blogger.com,1999:blog-5215551487816981140.post7624733979245651619..comments2024-01-23T06:10:31.184-08:00Comments on ORA-00001: Unique constraint violated: REF Cursor to JSONMorten Bratenhttp://www.blogger.com/profile/12300886042835631690noreply@blogger.comBlogger37125tag:blogger.com,1999:blog-5215551487816981140.post-54356730984126406152017-09-01T07:35:57.291-07:002017-09-01T07:35:57.291-07:00you are right, I think I have a previous version m...you are right, I think I have a previous version mine says <br /><br />function sql_to_json (p_sql in varchar2,<br /> p_max_rows in number := null,<br /> p_skip_rows in number := null) return xxtt_json_list<br />as<br /> v_cur sys_refcursor;<br />begin<br /> open v_cur for p_sql;<br /> return ref_cursor_to_json(v_cur, p_max_rows, p_skip_rows);<br /><br />end sql_to_json;<br /><br /><br />thanks a lot =)<br />DanSiriushttps://www.blogger.com/profile/01506984831095955882noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-49607722637888051162017-09-01T07:16:00.387-07:002017-09-01T07:16:00.387-07:00@Daniel: json_util_pkg.sql_to_json is indeed retur...@Daniel: json_util_pkg.sql_to_json is indeed returning a clob, as you can see here: https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/json_util_pkg.pkb#L282<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-72267505559847244352017-08-29T12:38:57.047-07:002017-08-29T12:38:57.047-07:00Hi guys,
I'm trying to use the code but I ha...Hi guys, <br /><br />I'm trying to use the code but I have a question, I did this simple test<br /><br />declare<br />l_json clob;<br />begin<br />l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,<br />cursor (select e.*<br />from emp e<br />where e.deptno = d.deptno) as the_emps<br />from dept d');<br />dbms_output.put_line (substr(l_json, 1, 10000));<br />end;<br /><br />using my own tables but I see that l_json is declared as a clob but the function sql_to_json is returning a json_list, =| am I wrong?<br /><br />Thanks for your response<br />Daniel Siriushttps://www.blogger.com/profile/01506984831095955882noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-47947242280781410612016-09-23T03:03:55.302-07:002016-09-23T03:03:55.302-07:00Is it works for Orcle 9i?Is it works for Orcle 9i?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-43686463135092434462016-03-30T03:59:07.560-07:002016-03-30T03:59:07.560-07:00error occured also about g_json_null_object had to...error occured also about g_json_null_object had to add it in package spcs instead of bodyAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-65861649381741161812015-06-06T01:01:55.233-07:002015-06-06T01:01:55.233-07:00@Tony: If I understand your question correctly, yo...@Tony: If I understand your question correctly, you would first call JSON_UTIL_PKG to generate a JSON clob. Then you use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the web service, passing the JSON clob as the p_body parameter. You get a CLOB back (note: in Apex 5 there is actually a function called MAKE_REST_REQUEST_B that returns a BLOB instead of a CLOB). Then you convert the CLOB to a BLOB using SQL_UTIL_PKG.CLOB_TO_BLOB. And finally you pass the BLOB to the OWA_UTIL_PKG.DOWNLOAD_FILE procedure to send it to the user's browser.<br /><br />https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_web_service.htm#AEAPI1955<br /><br />https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/sql_util_pkg.pkb<br /><br />https://github.com/mortenbra/alexandria-plsql-utils/blob/master/ora/owa_util_pkg.pkb<br /><br />Hope this helps,<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-39849260795881552002015-05-28T13:12:58.391-07:002015-05-28T13:12:58.391-07:00Morten,
Quick question.. I have your package and...Morten,<br /> Quick question.. I have your package and am using it to build a json object that I need to pass to a restful web service (using a POST vs a GET request..). Can you show how I would pass this object via pl/sql and receive back a blob type file for download by the user of the procedure??<br /><br />Thank you,<br /><br />Tony Miller<br />LuvMuffin Software<br />Ruckersville, VAAPEX Developer In the USAhttps://www.blogger.com/profile/06746962787261032815noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-25755529297378155912015-02-25T12:27:42.543-08:002015-02-25T12:27:42.543-08:00Never mind. I finally found the answer. There are ...Never mind. I finally found the answer. There are &s in the get_xml_to_json_stylesheet and that can cause problems, since that starts a substitution in sqlplus. So I did a SET SCAN OFF and recreated json_util_pkg. It no longer throws an error for me.<br /><br />Found the answer here:<br />http://code.google.com/p/jqgrid-for-plsql/issues/detail?id=5Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-19042165843397757122015-02-25T11:26:18.325-08:002015-02-25T11:26:18.325-08:00I know this post was from forever ago, but I am tr...I know this post was from forever ago, but I am trying to use this Alexandria library to feed some Handlebars JS functions and I keep running into this error no matter what I pass into your functions and I am not sure what could be going on. Please help. Here's my last attempt:<br /><br />Error starting at line 1 in command:<br />DECLARE <br /> l_json CLOB; <br /> l_cursor SYS_REFCURSOR;<br />BEGIN <br /> OPEN l_cursor FOR <br /> SELECT <br /> DECODE( COUNT(1), 1, MAX( cc.code ), 'MULT' ) || ' - ' || <br /> DECODE( COUNT(1), 1, MAX( cc.name ), 'Multiple Charge Codes' ) AS code_heading, <br /> CURSOR( SELECT <br /> pjs.projectnumber || ' - ' || pjs.title AS project_heading, <br /> pjs.projectnumber <br /> FROM project.project pjs <br /> WHERE pjs.projectnumber = pj.projectnumber ) AS projects<br /> FROM project.project pj <br /> INNER JOIN project.proposal p <br /> ON p.proposalnumber = pj.proposalnumber <br /> INNER JOIN project.vw_project_status_chg_period vpscp <br /> ON vpscp.projectnumber = pj.projectnumber <br /> AND vpscp.periodid = 244 <br /> AND vpscp.statusid = 'W' <br /> INNER JOIN project.vw_proposal_alloc_period_RT vpap <br /> ON vpap.proposalnumber = p.proposalnumber <br /> AND vpap.periodid = 244 <br /> INNER JOIN project.chargecode cc <br /> ON cc.code = vpap.code <br /> INNER JOIN project.vw_proposal_unit_root vpur <br /> ON vpur.proposalnumber = p.proposalnumber <br /> AND vpur.branch_root = ( SELECT <br /> TO_NUMBER( gp.value ) <br /> FROM basic.global_parameters gp <br /> WHERE gp.name = 'EU_ROOT_UNIT' ) <br /> WHERE pj.typeid <> 'C' <br /> GROUP BY pj.projectnumber, pj.title <br /> ORDER BY pj.projectnumber;<br /> <br /> l_json := json_util_pkg.ref_cursor_to_json( l_cursor );<br /><br /> DBMS_OUTPUT.PUT_LINE( l_json );<br />END;<br />Error report:<br />ORA-31011: XML parsing failed<br />ORA-19202: Error occurred in XML processing<br />LPX-00601: Invalid token in: '*[count(../*[name(../*)=name(.)])=count(../*) and count(../*);1]'<br />ORA-06512: at "SYS.XMLTYPE", line 187<br />ORA-06512: at "ALEXANDRIA.JSON_UTIL_PKG", line 269<br />ORA-06512: at line 36<br />31011. 00000 - "XML parsing failed"<br />*Cause: XML parser returned an error while trying to parse the document.<br />*Action: Check if the document to be parsed is valid.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-60486435933042094102014-11-19T09:26:10.097-08:002014-11-19T09:26:10.097-08:00Wonder if it is possible to get JSON to ref cursor...Wonder if it is possible to get JSON to ref cursor function ?Annanoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-68548009403265467962014-09-18T04:55:01.342-07:002014-09-18T04:55:01.342-07:00@Stefan Knecht
might be due to the JSON db implem...@Stefan Knecht<br /><br />might be due to the JSON db implementation in 12.1.0.2 (aka not visible but imlemented in earlier versions of 12.1).<br /><br />SQL> select keyword<br /> 2 from v$reserved_words<br /> 3* where keyword like '%JSON%'<br /><br />KEYWORD<br />------------------------------<br />JSONGET<br />JSON_EXISTS<br />JSON_OBJECT<br />JSON_TEXTCONTAINS2<br />JSON_QUERY<br />JSON_VALUE<br />JSONPARSE<br />JSON_SERIALIZE<br />JSON_ARRAYAGG<br />JSON_OBJECTAGG<br />JSON_EQUAL<br />JSON_EXISTS2<br />JSON_TABLE<br />JSON_ARRAY<br />JSON<br />JSON_TEXTCONTAINS<br /><br />16 rows selected.Marco Gralikehttps://www.blogger.com/profile/12810709424310804316noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-57702872813758953192014-05-12T10:45:00.700-07:002014-05-12T10:45:00.700-07:00Has anyone used this on 12cR1 ?
I get odd results...Has anyone used this on 12cR1 ?<br /><br />I get odd results with even the basic:<br /><br />select json_util_pkg.ref_cursor_to_json(cursor(select sysdate from dual)) from dual;<br /><br />JSON_UTIL_PKG.REF_CURSOR_TO_JSON(CURSOR(SELECTSYSDATEFROMDUAL))(LIST_DATA(TYPEVAL, STR, NUM, OBJECT_OR_ARRAY(), EXTENDED_STR, MAPNAME, MAPINDX))<br />--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />JSON_LIST(JSON_VALUE_ARRAY(JSON_VALUE(1, NULL, NULL, ANYDATA(), NULL, 'ROW', 1)))<br /><br />Stefan<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-81337956001633461382014-03-31T05:32:56.495-07:002014-03-31T05:32:56.495-07:00dear morten
first thank you..nice job
second..
w...dear morten<br /><br />first thank you..nice job<br /><br />second..<br />when using <br />select SQL_TO_JSON('select code from table')from dual;<br /><br />if code have null value come first record return element whit out { .<br /><br />i cahange your code <br /> <br /> <br /> {<br /> <br /> <br /> <br /> :null<br /> ,<br /> } <br /> <br /><br />new code commeted by name.<br /><br />Regards<br />Mohammad TalatAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-58479401153016046702014-01-16T11:06:13.692-08:002014-01-16T11:06:13.692-08:00@Bharat: As stated near the end of the blog post, ...@Bharat: As stated near the end of the blog post, the full source code is part of the Alexandria Library for PL/SQL and can be downloaded here: http://code.google.com/p/plsql-utils/<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-49448961771411594662014-01-09T23:05:21.531-08:002014-01-09T23:05:21.531-08:00Hi Morten,
I am new to JSON, can you please tell m...Hi Morten,<br />I am new to JSON, can you please tell me from where I can get the "get_xml_to_json_stylesheet" used in function "ref_cursor_to_json"..<br /><br />Where can I download this?<br /><br />Thanks in advance!<br /><br />Regards,<br />BharathAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-71705466928473450562013-12-18T06:22:40.585-08:002013-12-18T06:22:40.585-08:00Morten, this was a very well done piece of code. ...Morten, this was a very well done piece of code. I seldom compile donated code flawlessly. Great job.<br /><br />I'm trying to use your code as a bridge to Google Charts (Visualization) and the JSON format required is organized differently. Here's a short example:<br><br />{<br /> "cols": [<br /> {"id":"","label":"Topping","pattern":"","type":"string"},<br /> {"id":"","label":"Slices","pattern":"","type":"number"}<br /> ],<br /> "rows": [<br /> {"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},<br /> {"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},<br /> {"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},<br /> {"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},<br /> {"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}<br /> ]<br />}<br /><br />I suspect the XSLT transform might make quick work of this, but I'm a total novice at that. Do you know of anyone else who has used your tool successfully in this way?<br />Thanks.<br />Stan<br />STANhttps://www.blogger.com/profile/07184985540080770276noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-6547072394815990942013-08-13T13:18:35.096-07:002013-08-13T13:18:35.096-07:00@Manny: You could use the SYS.HTP package to print...@Manny: You could use the SYS.HTP package to print your CLOB in small chunks. You need to construct the JSON "markup" yourself in this case.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-54777568085668654772013-07-25T03:28:00.922-07:002013-07-25T03:28:00.922-07:00Dear Mortan,
My requirement has the data which is ...Dear Mortan,<br />My requirement has the data which is more than 4000+ length. I hv tired using XMLTYPE , but that doesn't help for JSON data to send. Please suggest how to send the CLOB of JSON or JSON data which can have more than 4000+.Mannynoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-11847097214728785572012-01-13T06:23:09.529-08:002012-01-13T06:23:09.529-08:00Issue of a different JSON result for one row and m...Issue of a different JSON result for one row and multiple row, can by solved by modification of ref_cursor_to_json function. Like this:<br /><br /> if(l_num_rows = 1) then<br /> declare <br /> ret json_list := json_list();<br /> js json := json();<br /> begin<br /> ret.append(json(json(l_returnvalue).get('ROWSET')).get('ROW'));<br /> js.put('ROWSET', ret);<br /> dbms_lob.trim(l_returnvalue, 0);<br /> js.to_clob(l_returnvalue);<br /> return l_returnvalue;<br /> end;<br /> else <br /> return l_returnvalue;<br /> end if;Kiasnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-40251543115514209712011-12-17T13:10:09.049-08:002011-12-17T13:10:09.049-08:00Excellent library. Thx for sharing that.
Have ...Excellent library. Thx for sharing that. <br /><br />Have you had any luck getting this to work on queries that return text columns with embedded special chars? <br /><br />For example:<br /><br /> select 'Johnson & Johnson' as<br /> company_name from dual;<br /><br />generates an LPX-00242 parsing error.<br /><br />Other than wrapping every column in the query with an encode stmt before submitting for conversion to JSON is there a way to handle this in the package?<br /><br />ThxAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-84460033866951577952011-11-11T10:05:59.369-08:002011-11-11T10:05:59.369-08:00json is a very interesting language to be used. ve...json is a very interesting language to be used. very good tutorial and can hopefully help me in building json in the application that I created for this lecture. thank youandryhttp://library.gunadarma.ac.id/noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-61085847829756219462011-01-19T15:41:28.439-08:002011-01-19T15:41:28.439-08:00Excellent code. Thank you very much.
I also ran ...Excellent code. Thank you very much.<br /><br />I also ran across the issue of a different JSON result for one row and multiple rows. I changed the ret.add_elem() line in the ref_cursor_to_json function to return ROWSET/ROW and it seemed to work.<br /><br /> if(l_num_rows = 1) then<br /> declare ret json_list := json_list();<br /> begin<br /> --ret.add_elem(json(l_returnvalue).get('ROWSET'));<br /> ret.add_elem(json(json(l_returnvalue).get('ROWSET')).get('ROW'));<br /> return ret;<br /> end;<br /> elseUnknownhttps://www.blogger.com/profile/05930731455980948925noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-45320056608500782052010-07-29T23:30:37.420-07:002010-07-29T23:30:37.420-07:00@Matt:
Unfortunately, I'm no XLST expert eith...@Matt:<br /><br />Unfortunately, I'm no XLST expert either, and I didn't write the XSLT piece (see the source code for credits/license).<br /><br />You might find it helpful to start with a Google search on "xml to json xslt". I suppose there are specialized forums out there somewhere which can help with this (or perhaps try Stackoverflow.com ?).<br /><br />Of course, let me know if you find a solution, so I can incorporate the fix into the codebase.<br /><br />- MortenMorten Bratenhttps://www.blogger.com/profile/12300886042835631690noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-38496551510263504032010-07-28T06:35:21.772-07:002010-07-28T06:35:21.772-07:00Thanks mate for the javascript workaround, unfortu...Thanks mate for the javascript workaround, unfortunately I won't be able to use it....<br /><br />Do you have any intentions of modifying the XSLT stylesheet in the near future? <br /><br />I had a look at modifying it but it's out of my comfort zone as I'm new to using XLST...<br /><br />That said I need this for a project I'm working on so I just need to know if I can "cheekily" rely on you for a fix or whether I'll have to roll up my sleeves and get my hands dirty :)<br /><br />Thanks <br />Mattmnolanhttps://www.blogger.com/profile/03589368399513500774noreply@blogger.comtag:blogger.com,1999:blog-5215551487816981140.post-47421723073283153062010-07-28T06:25:46.689-07:002010-07-28T06:25:46.689-07:00This comment has been removed by the author.mnolanhttps://www.blogger.com/profile/03589368399513500774noreply@blogger.com