Hello Tom,
The odata-JSON string created by f/e
https://www.odata.org/getting-started/ what is provided through a web-service needs to be inserted into a table.
The json looks like:
{"@odata.context": "serviceRoot/$metadata#People"}
How can i extract the context out of the JSON string.
See also live link, but here is the pl/sql block
declare
a_str varchar2(4000);
cursor c_cur (p_str IN VARCHAR2) is
select JS.* from
json_table(p_str FORMAT JSON , '$'
COLUMNS ( c_context varchar2(100) PATH '$.@odata.context',
c_nextDelta varchar2(100) PATH '$.@odata.nextDelta'
) ) AS "JS";
begin
a_str:= '{"@odata.context": "odatacontext", '
||' "@odata.nextDelta": "odatanextDelta"'
||'} ' ;
for r_cur in c_cur(a_str) loop
dbms_output.put_line (r_cur.c_context||'; '||
r_cur.c_nextDelta||';'
);
end loop;
end;
/
The JSON parser interprets dots in the path as nested attributes. So you need to wrap the whole attribute name in double quotes in your path expression.
But there's a bug when querying JSON attributes including special characters in 12.1. So it still fails:
select js.*
from json_table('{
"@odata.context": "odatacontext",
"@odata.nextDelta": "odatanextDelta"
}' format json , '$'
columns (
c_context varchar2(100) path '$."@odata.context"',
c_nextdelta varchar2(100) path '$."@odata.nextDelta"'
)
) as js;
ORA-40442: JSON path expression syntax errorLuckily this is fixed in 12.2:
select * from v$version;
BANNER CON_ID
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
select js.*
from json_table('{
"@odata.context": "odatacontext",
"@odata.nextDelta": "odatanextDelta"
}' format json , '$'
columns (
c_context varchar2(100) path '$."@odata.context"',
c_nextdelta varchar2(100) path '$."@odata.nextDelta"'
)
) as js;
C_CONTEXT C_NEXTDELTA
odatacontext odatanextDelta
I
believe this is caused by bug 18999568. See MOS notes 2123923.1 & 1992767.1 for patch details.
PS - thanks for using LiveSQL. But you need to save your script and share the link this provides!