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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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!
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
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:
--
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
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
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