getting MY_SCHEMA_NAME.JSON_LIST when i execute json_util_pkg.sql_to_json

[Nagesh Patil]

Hi,

when I execute following code:
set serveroutput on;
declare
ret json_list;
begin
ret := json_util_pkg.sql_to_json('select sysdate from dual');
ret.print;
end;
/

It works fine and gives accurate results, but when I tried it as single query it does not show results.

query: select json_util_pkg.sql_to_json('select sysdate from dual') from dual;

Did i miss anything in code or i m using any wrong path.
please help urgently, I m stuck with it.

Thanks in advance...

regards,
Nagesh Patil

[Herb Swift]

json_util_pkg.sql_to_json() returns a json_list, which isn't a native data
type. It needs to be converted to a clob before a sql query can return it
inline. Your anonymous block, which is pl/sql, rather than sql, invokes
the print method of the json_list type, which converts the json_list into a
clob and sends it to output, where it can be viewed, but not much else.
And you can't call it from a sql query. I think you might be forced to
stick with using pl/sql. If I'm wrong though, I'd very much like to learn
how to do it!

On Tue, Aug 19, 2014 at 2:17 AM, Nagesh Patil nageshradha@users.sf.net
wrote:

declare
ret json_list;
begin
ret := json_util_pkg.sql_to_json('select sysdate from dual');
ret.print;
end;
/

--
Herb Swift™ (sæ•só•nus ra•pí•dus™)

"Keep your stick on the ice and keep reaching for the stars."
-- Red Green, d. April 7, 2006 and Casey Kasem, d. June 15, 2014

[Nagesh Patil]

Thanks Herb Swift.

you clear my points.

yesterday, I did some modification and got my work done to print JSON on browser.

code:
declare
ret json_list;
j_obj json;
begin
ret := json_dyn.executeList('select emp_name,emp_id from emp where emp_id =1001 ');
j_obj := json(ret);
json_ext.pp_htp(j_obj, '');
end;

Hope this can help to anyone..

regards,
Nagesh

[Neelakanta]

Hi NAgesh,

Can you send us the sample code how to extract data from Json from SQL.
I've a requirement is Input data coming as CLOB in to Oracle Standalon Function

We have written PAcakge.Function to accept input (JSON) as CLOB.
I want to read data from CLOB which is JSON file in to custom table.

Can you please help me .

Thanks,
Neel