Example for SQL_TO_JSON

Question

How to use the function JSON_UTIL_PKG.SQL_TO_JSON? Please give an example.

I tried this:

---

select JSON_UTIL_PKG.SQL_TO_JSON('select column from table where rownum = 1',100) from dual;

but the result is not ok

Answer 1

The declaration of that function is:

function sql_to_json (p_sql in varchar2, p_max_rows in number := null, p_skip_rows in number := null) return json_list

Thus, the result should be a pljson.json_list object. Since the only information you have given is "the result is not okay" I can only assume you are expecting the result to be a JSON string. If that is the case, and your result is stored in a variable named foo, then you can use foo.to_char to generate the string. Or foo.to_clob to return the JSON string as a CLOB.

Answer 2

I tried to execute as:

declare jlist json_list;
Begin
  jlist := json_utl_pkg.sql_to_json('select col1, col2 from mytable', 10, 0);
end;

This shows an error like "jlist invalid left assignement"...

Then I tried this one, and it worked:

declare jobj json;
begin
   jobj := json_dyn.executeObject('select * from myTable');
   jobj.print;
end;

It worked correctly!

Based on:https://github.com/oberstet/pljson/blob/master/examples/ex16.sql

Best regards