Skip to Main Content
  • Questions
  • Extracting data elements from Json within PL/SQL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Don.

Asked: November 11, 2020 - 10:06 pm UTC

Last updated: November 13, 2020 - 9:17 am UTC

Version: 19

Viewed 100+ times

You Asked

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?

with LiveSQL Test Case:

and we said...

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 objects

declare 
  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; 
/
12345


Using JSON_table

declare 
    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

Rating

  (2 ratings)

Comments

A reader, November 12, 2020 - 2:56 pm UTC


Don Simpson, November 12, 2020 - 3:50 pm UTC

As I was aimlessly futzing around, I stumbled upon this that works as well:
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
    nested jrow columns(practitionerId);
    
    DBMS_OUTPUT.put_line (p_practitioner); 
end;
/

Chris Saxon
November 13, 2020 - 9:17 am UTC

Thanks for sharing

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.