Skip to Main Content
  • Questions

  • JSON_OBJECT throws error in Stored Procedure

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Iysh.

Asked: August 05, 2020 - 6:51 am UTC

Last updated: August 06, 2020 - 9:42 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Dear Team,

I am trying to use the JSON functions in a stored procedure and TOAD throws the syntax error.
Found 'value', an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)

Below is the query that I use. However when I try to run outside the stored procedure, it works fine. 
SELECT JSON_OBJECT ( 'empid' value  '1' , 'name' value  'Tom' , 'address'  value  '23333' ) into emp_json  FROM dual ;


Could you please help me to identify the cause of the error.
Thank You.

and Chris said...

Sorry, I'm not able to reproduce:

create or replace procedure p as
  emp_json varchar2(100);
begin
  select json_object (
           'empid' value '1',
           'name' value 'tom',
           'address' value '23333'
         )
  into   emp_json
  from   dual;
  
  dbms_output.put_line ( emp_json );
end;
/

exec p();

{"empid":"1","name":"tom","address":"23333"}


Please show a complete example of your stored procedure - all the code needed for us to copy-paste and see the same issue you do.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 05, 2020 - 9:51 am UTC

Thanks for your quick response,Chris!

I am executing the same stored procedure that you had provided. Still receiving the error in Toad.
ERROR line 5, col 20, ending_line 5, ending_col 24, Found 'value', an alias is not allowed here (reserved for XMLCOLATTVAL, XMLFOREST and XMLATTRIBUTES only)
ERROR line 5, col 26, ending_line 5, ending_col 28, Found ''1'', Expecting: ) ,
Chris Saxon
August 06, 2020 - 9:42 am UTC

So what happens if you execute my example in SQL*Plus or SQL Developer?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.