CODEWITHSUNDEEP
jsonplsqloracle12cpljson
Abhi
Abhi

Reputation: 1255

Oracle 12c does not recognize JSON type

I am working on parsing JSON CLOB in oracle. I am trying to retrieve and parse individual JSON elements.

Main issue I isolated is that compiler is unable recognize the JSON type here. However, I am able to insert and access individual JSON elements in the po_document field(declared as CLOB). This is the JSON I am trying to access

 '{"PONumber"             : 1600,
            "Reference"            : "ABULL-20140421",
            "Requestor"            : "Alexis Bull",
            "User"                 : "ABULL",
            "CostCenter"           : "A50",
            "ShippingInstructions" : "no such",
            "Special Instructions" : null,
            "AllowPartialShipment" : true,
            "LineItems"            : "no line"}'

I just took a standard Pl/SQL block to parse the JSON object:

    DECLARE

vCONTENT            CLOB;
v_parent_json           json;
v_json_message_list         json_list;
v_json_message_list_value   json_value;
v_parent_json_value         json_value;


BEGIN

SELECT po_document INTO vCONTENT FROM j_purchaseorder;

v_parent_json := json(vCONTENT);
v_parent_json := json(v_parent_json.get(1));

  v_json_message_list := json_list(v_parent_json.get('LineItems'));

DBMS_OUTPUT.PUT_LINE(v_json_message_list.count);

for message_loop_counter in 1 ..v_json_message_list.count loop
    v_parent_json_value := json(v_json_message_list.get(message_loop_counter)).get(1);

    DBMS_OUTPUT.PUT_LINE(v_parent_json_value.mapname);
END LOOP;

END;

The compiler log generates the error message: Error(3,8): PLS-00201: identifier 'JSON' must be declared

Output from v$version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production

Upvotes: 1

Views: 5898

Answers (1)

Abhi
Abhi

Reputation: 1255

I was trying different things initially. I was using PL/JSON functions in my questions but if I want to use Oracle functions, this could be a small demo to read JSON and print values:

declare
    l_has_data_level_co BOOLEAN  := FALSE;    -- TRUE is for R12C
    jdemo CLOB;
    l_name varchar2(2000):='';
    l_location varchar2(2000):='';
begin
    jdemo := '{"PONumber"             : 1600,
            "Reference"            : "ABULL-20140421",
            "Requestor"            : "Alexis Bull",
            "User"                 : "ABULL",
            "CostCenter"           : "A50",
            "ShippingInstructions" : "no such",
            "Special Instructions" : null,
            "AllowPartialShipment" : true,
            "LineItems"            : "no line"}';
    SELECT 
      json_value(jdemo, '$.PONumber'),
      json_value(jdemo, '$.Reference')
    into 
      l_name,      
      l_location
    FROM dual;
    --DBMS_OUTPUT.PUT_LINE (SYSDATE||' '||jdemo);
    DBMS_OUTPUT.PUT_LINE ('Name :'||l_name||'  Location :'||l_location);
end;

Upvotes: 1

Related Questions