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

Answered by: Chris Saxon - Last updated: August 06, 2020 - 9:42 am UTC

Category: SQL - Version: 12.2.0.1

Viewed 100+ 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 we 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.

and you rated our response

  (1 rating)

Reviews

August 05, 2020 - 9:51 am UTC

Reviewer: A reader

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

Followup  

August 06, 2020 - 9:42 am UTC

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.