Skip to Main Content
  • Questions

  • Extracting data elements from Json within PL/SQL

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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 10K+ times! This question is

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:
https://livesql.oracle.com/apex/livesql/s/kxmgyp4p2xcp4xxt8tktlee6n

and Chris 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)

We're not taking comments currently, so please try again later if you want to add a comment.

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.