tag:blogger.com,1999:blog-21122514.post5543726227662146430..comments2024-03-23T11:05:13.046+01:00Comments on Dimitri Gielis Blog (Oracle Application Express - APEX): SQLcl to the rescue when the Database and APEX fail (with JSON limitations)Dimitri Gielishttp://www.blogger.com/profile/16295721159626839167noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-21122514.post-53367791206206483502016-08-08T18:01:34.868+02:002016-08-08T18:01:34.868+02:00Great, thanks Anton!Great, thanks Anton!Dimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-67411777163094403852016-08-08T15:40:01.008+02:002016-08-08T15:40:01.008+02:00Or you can use as_json: https://technology.amis.nl...Or you can use as_json: https://technology.amis.nl/2015/12/22/as_json-relational-to-json-in-oracle-database/<br /><br />With a SQL-query:<br />select as_json.qget_string( j.column_value, 'question' )<br /> , as_json.qget_string_clob( j.column_value, 'description' )<br />from tbl_with_json a<br /> , table( as_json.qget_array(as_json.qjson( a.json_clob ), 'items' ) ) j<br /><br /><br />Or in PLSQL:<br />declare<br /> t_tmp clob;<br /> t_json as_json.tp_json_value;<br />begin<br /> select json_clob into t_tmp from tbl_with_json where rownum <= 1;<br /> t_json := as_json.json( t_tmp );<br /> t_tmp := as_json.get_string_clob( t_json, 'items[1].description' );<br /> dbms_output.put_line( length( t_tmp ) || ' ' || substr( t_tmp, 1, 50 ) );<br /> as_json.free; <br />end;Antonnoreply@blogger.comtag:blogger.com,1999:blog-21122514.post-2233728698100931752016-08-04T19:56:42.076+02:002016-08-04T19:56:42.076+02:00Dimitri,
thanks for blogging about the JSON suppor...Dimitri,<br />thanks for blogging about the JSON support in Oracle.<br />A few comments:<br />1) Currently, JSON_VALUE, JSON_QUERY and JSON_TABLE return string values as VARCHAR2 only. CLOB is not yet supported because of performance reasons (it would require creation of temp lobs).<br />2) If you just specify VARCHAR2 uses the default of VARCHAR2(4000). 4000 is always the default irregardless of 'extended varchar' setting.<br />If you don't like the default of 4000 you can specify the desired length. Example VARCHAR(20) or VARCHAR2(32767)<br />3) If the selected JSON value cannot be returned as the selected SQL return type then we have an error. The cause could be like your where the length does not fit. But it could also be that a number conversion failed if you specified RETURNING NUMBER and the selected value is not a number.<br />4) NULL ON ERROR is the default error handler. If you want the query execution to be aborted with an error messages then you should specify ERROR ON ERROR. This behavior is by design an defined in the SQL standard - so there is no issue in the product. Maybe you can clarify this concept in your blog so that readers are not mislead.<br /><br />Thanks<br /><br />Mr Bhttps://www.blogger.com/profile/11460130436721718130noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-12301970866274248742016-08-04T11:10:18.516+02:002016-08-04T11:10:18.516+02:00Hi Mark,
Thanks for the suggestion! Since the dat...Hi Mark,<br /><br />Thanks for the suggestion! Since the database and APEX support JSON I've not used PL/JSON anymore, but because of your comment I gave that a shot again.<br /><br />At first sight PL/JSON doesn't have a get_clob function, only get_string, get_number, get_boolean and get_date: https://github.com/pljson/pljson/blob/master/src/json_ext.sql<br /><br />But in APEX you have to do it a bit differently when there're clobs, so thought to search for that in PL/JSON and actually found a way to get it working by using the get_json_value!<br /><br />Here's a pure PL/SQL way with PL/JSON getting the large clob out of a single node:<br />--<br />declare<br /> l_obj json;<br /> l_json_value json_value;<br /> l_clob clob;<br /> l_result clob;<br />begin<br /> select json_clob<br /> into l_clob<br /> from tbl_with_json;<br /> <br /> l_obj := json(l_clob);<br /> -- print JSON<br /> -- l_obj.print;<br /><br /> l_json_value := json_ext.get_json_value(l_obj, 'items[1].description');<br /><br /> l_result := empty_clob();<br /> dbms_lob.createtemporary(l_result, true);<br /> l_json_value.to_clob(l_result, true);<br /><br /> dbms_output.put_line(substr(l_result,1,50));<br /> <br /> -- as json_value returns " and a carriage return we have to get rid of those<br /> l_result := trim(trailing '"' from trim(trailing CHR(13) from trim(trailing CHR(10) from trim(leading '"' from l_result))));<br /><br /> insert into tbl_with_description (description) values (l_result);<br /><br /> dbms_lob.freetemporary(l_result);<br />end;<br />/<br />--<br /><br />So thanks again for the suggestion!<br /><br />DimitriDimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-11174647882844663992016-08-04T08:58:24.270+02:002016-08-04T08:58:24.270+02:00Thanks for that comment Alex. I learn something ne...Thanks for that comment Alex. I learn something new every day :)Dimitri Gielishttps://www.blogger.com/profile/16295721159626839167noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-36990670025460727172016-08-04T08:31:43.483+02:002016-08-04T08:31:43.483+02:00Good stuff, didn't know about getting Nashhorn...Good stuff, didn't know about getting Nashhorn inside the database.<br />The reason you got NULL when using the JSON_TABLE in your first query is because of the default settings. If you add ERROR ON ERROR you will get ORA-40478:output value too large<br /><br /> select jt.question, jt.description<br /> from tbl_with_json, <br /> json_table(json_clob, '$.items[*]'<br /> columns (<br /> question varchar2 path '$.question',<br /> description varchar2 path '$.description'<br /> error on error) --<------- Add this :)<br /> ) as jt;<br />Alex Nuijtenhttps://www.blogger.com/profile/06345615264010120428noreply@blogger.comtag:blogger.com,1999:blog-21122514.post-45942259990325708592016-08-04T01:26:45.539+02:002016-08-04T01:26:45.539+02:00Great post Dimitri.
I'm seeing more opportuni...Great post Dimitri.<br /><br />I'm seeing more opportunities for JSON every day, both in the front end and also in the database.<br /><br />Thanks for highlighting the problem, and some of the options available.<br />Don't forget you could also try PL/JSON as a fallback, possibly after some customization. Mark Lancasterhttps://www.blogger.com/profile/06316329564169609358noreply@blogger.com