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