Oracle 12c does not recognize JSON type

Question

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

Where are you getting that syntax from? As far as I can see from the docs the JSON value is a string, so v_parent_json should be a varchar2 or a CLOB. Or have you defined an object type with that name? — Alex Poole, Mar 03 '16 at 23:44
Or are you using (or trying to use) [PL/JSON](https://github.com/pljson/pljson/)? — Alex Poole, Mar 03 '16 at 23:54
My understanding is that PL/JSON is an Oracle standard package but it doesn't look like one. Is there a standard Oracle function to parse JSON or are my options limited regex string and pull elemtns for each JSON element — Abhi, Mar 04 '16 at 00:40
PL/JSON and 12c's native JSON are two completely different things. PL/JSON does not currently support 12c (officially). Some users have had success with it, though -- https://github.com/pljson/pljson/issues/28#issuecomment-187628569 — James Sumners, Mar 04 '16 at 18:04

score 1 · Answer 1 · edited Mar 04 '16 at 11:09

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;

Oracle 12c does not recognize JSON type

1 Answers1