Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error in putting a SELECT json_object into a json object

User_RFKSXMay 25 2021

Hi, I'm new in JSON, because I usually read the nodes of a JSON object in PLSQL but now I was asked to generate a JSON object by selecting from a table
If I run this SQL:
SELECT json_object('name' VALUE t.name,
'sirname' VALUE t.sirname,
'detail' VALUE json_object('email' VALUE t.mail,
'genre' VALUE t.genre))
FROM mytable;
I get this JSON string:
{"name":"JOHN","sirname":"BROWN","detail":{"email":"john@test.com","genre":"masculine"}}
But if I try to run this block:
declare
v_json json_object_t;
begin
v_json := JSON_OBJECT_T();
SELECT json_object('name' VALUE t.name,
'sirname' VALUE t.sirname,
'detail' VALUE json_object('email' VALUE t.mail,
'genre' VALUE t.genre))
INTO v_json
FROM mytable';

end;
/
I get:
ORA-06550: line 7, column 19: PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR ORA-06550: line 7, column 1: PL/SQL: SQL Statement ignored
Is it not possible to put the result of a SELECT json_object ... into a JSON object defined?
Thanks!
Oracle version 12c
Mark
This post has been answered by Solomon Yakobson on May 25 2021
Jump to Answer

Comments

Solomon Yakobson
Answer

SQL function JSON_OBJECT returns VARCHAR2 while you need PL/SQL type JSON_OBJECT_T:

SQL> declare
  2      v_json    json_object_t;
  3  begin
  4      select  json_object(
  5                          'first_name' value first_name,
  6                          'last_name'  value last_name,
  7                          'detail'     value json_object(
  8                                                         'email' value email,
  9                                                         'phone' value phone_number
 10                                                        )
 11                         )
 12        into  v_json
 13        from  hr.employees
 14        where rownum = 1;
 15  end;
 16  /
    select  json_object(
                       *
ERROR at line 4:
ORA-06550: line 4, column 24:
PL/SQL: ORA-00932: inconsistent datatypes: expected UDT got CHAR
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

SQL> declare
  2      v_json json_object_t;
  3      v_json_text varchar2(4000);
  4  begin
  5      select  json_object(
  6                          'first_name' value first_name,
  7                          'last_name'  value last_name,
  8                          'detail'     value json_object(
  9                                                         'email' value email,
 10                                                         'phone' value phone_number
 11                                                        )
 12                         )
 13        into  v_json_text
 14        from  hr.employees
 15        where rownum = 1;
 16        v_json := json_object_t(v_json_text);
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL>

In addition your code will error out with ORA-01422: exact fetch returns more than requested number of rows if table mytable has more than one row.
SY.

Marked as Answer by User_RFKSX · May 25 2021
1 - 1

Post Details

Added on May 25 2021
1 comment
1,685 views