CODEWITHSUNDEEP
pljson
NicoDP
NicoDP

Reputation: 11

Example for SQL_TO_JSON

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

Upvotes: 0

Views: 2201

Answers (2)

Ramiro Juarez
Ramiro Juarez

Reputation: 235

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

Upvotes: 0

James Sumners
James Sumners

Reputation: 14775

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.

Upvotes: 0

Related Questions