Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Geetika .

Asked: March 07, 2017 - 12:17 pm UTC

Last updated: January 25, 2018 - 11:49 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hello,

I have a procedure with below structure:
procedure p_json (i_json clob)
is 
 l_firstname vacrchar2(10);
begin
--some code here
end;


Input parameter i_json will receive Json_object. Below is structure of json object :
{
          "FirstName"      : "John"
}


Below query is used for reading json_object from a table in sql: 
select json_value(j.doc,'$.FirstName')
from table j; //assuming doc is the column name in table


could you please suggest how to access the value of json object inside pl/sql procedure without inserting the input clob(json object) into a table.

Thanx!!

and Chris said...

In 12.1.0.2 json_value is a SQL function. But you can use in PL/SQL by placing it in a "select ... from dual" query!

declare
  json_doc varchar2(1000);
  fname    varchar2(10);
begin
  json_doc := '{
          "FirstName"      : "John"
}';
  
  select json_value(json_doc, '$.FirstName')
  into   fname
  from   dual;
  
  dbms_output.put_line(fname);
  
end;
/

PL/SQL procedure successfully completed.
John


Or use 12.2 where JSON in PL/SQL got a whole lot better. You can use json_value as a native function:

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 

declare
  json_doc varchar2(1000);
  fname    varchar2(10);
begin
  json_doc := '{
          "FirstName"      : "John"
}';
  
  fname := json_value(json_doc, '$.FirstName');
  
  dbms_output.put_line(fname);
  
end;
/

PL/SQL procedure successfully completed.
John


Or the new JSON object types to get parts of a document:

declare
  json_doc json_object_t;
  fname    varchar2(10);
begin
  json_doc := json_object_t.parse('{
          "FirstName"      : "John"
}');
  
  fname := json_doc.get_String('FirstName');
  
  dbms_output.put_line('Name: ' || fname);
  
end;
/

PL/SQL procedure successfully completed.
Name: John


For more on JSON in PL/SQL in 12.2, see:

https://blogs.oracle.com/sql/entry/12_things_developers_will_love#json-plsql
http://docs.oracle.com/database/122/ADJSN/overview-of-pl-sql-object-types-for-json.htm#ADJSN-GUID-A22995AD-A144-4A5C-AB80-A17225A56E41
http://docs.oracle.com/database/122/ADJSN/using-PLSQL-object-types-for-JSON.htm#ADJSN-GUID-F0561593-D0B9-44EA-9C8C-ACB6AA9474EE

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

retrieve multi-level data

Mark, January 24, 2018 - 1:47 pm UTC

I have a json object with many levels but don't know how to extract data from multiple levels. Here's your example but with my data. Can you help? thanks.

declare
json_doc json_object_t;
-- fname varchar2(10);
payment_method_type varchar2(10);
begin
json_doc := json_object_t.parse('{
"data": {
"id": "PefqUBaJ2FJYLHmfoeCjkDn8",
"type": "payment_method",
"attributes": {
"payment_method_type": "CNP",
"details": {
"card_brand": "Visa",
"name_on_card": "Mark Swarts",
"expiration_month": "10",
"expiration_year": "2020",
"billing_zip_code": "14225",
"last_4_digits": "1111"
},
"affiliate_reference_id": null,
"expiration": null,
"auto_update": false
},
"relationships": {
"transactions": {
"data": []
}
}
}
}');

--fname := json_doc.get_String('FirstName');
--dbms_output.put_line('Name: ' || fname);

payment_method_type := json_doc.get_String('data.attributes.payment_method_type');
dbms_output.put_line('payment_method_type: ' || payment_method_type);

end;
/
Chris Saxon
January 25, 2018 - 11:49 am UTC

Resolve the nested objects with get_object calls:

declare 
  json_doc json_object_t; 
  payment_method_type varchar2(10); 
begin 
json_doc := json_object_t.parse('{
  "test": "test",
  "data": {
    "id": "PefqUBaJ2FJYLHmfoeCjkDn8",
    "type": "payment_method",
    "attributes": {
      "payment_method_type": "CNP",
      "details": {
        "card_brand": "Visa",
        "name_on_card": "Mark Swarts",
        "expiration_month": "10",
        "expiration_year": "2020",
        "billing_zip_code": "14225",
        "last_4_digits": "1111"
      },
      "affiliate_reference_id": null,
      "expiration": null,
      "auto_update": false
    },
    "relationships": {
      "transactions": {
        "data": []
      }
    }
  }
}'); 

payment_method_type := json_doc.get_object('data').get_object('attributes').get_String('payment_method_type'); 
dbms_output.put_line('payment_method_type: ' || payment_method_type); 

end; 
/ 

payment_method_type: CNP

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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here