I am building a package procedure that will accept a string of json as the input. In SQL, I can select a single value from the json object, but, using select x into var from ... in pl/sql throws an error:
ORA-06550: line 10, column 12:
PL/SQL: ORA-19200: Invalid column specification
with jdata as (
select treat('{"practitionerId":12345,"gender":"F"}' as json) as jrow
from dual
)
select j.jrow.practitionerId
from jdata j;
declare
p_input_json varchar2(32767) := '{"practitionerId":12345,"gender":"F"}';
p_practitioner int;
begin
with jdata as (
select treat(p_input_json as json) as jrow
from dual
)
select j.jrow.practitionerId
into p_practitioner
from jdata j;
end;
/
Several hours of searching docs and ye olde internet, I cannot find a method of extracting scalars from json in pl/sql. Can you point me in the right direction?
That looks like a bug, I'll follow up on this internally.
In the meantime, there are a couple of other ways you can extract the value:
With JSON objectsdeclare
p_input_json varchar2(32767) := '{"practitionerId":12345,"gender":"F"}';
json_obj json_object_t ;
begin
json_obj := json_object_t.parse ( p_input_json );
dbms_output.put_line ( json_obj.get ( 'practitionerId' ).to_String );
end;
/
12345Using JSON_tabledeclare
p_input_json varchar2(32767) := '{"practitionerId":12345,"gender":"F"}';
p_practitioner varchar2(400);
begin
select *
into p_practitioner
from json_table (
p_input_json, '$'
columns (
practitionerId
)
);
dbms_output.put_line ( p_practitioner );
end;
/
12345