Finding length of JSON array in PL/SQL Trigger

Question

I am trying to find the length of a JSON array in a PL/SQL trigger programmatically in Oracle 12c using Application Express. This will allow me to loop through each element and insert the data into another table. Right now the JSON is stored in a column as a CLOB and I am converting it to VarCHAR2. Code Below:

declare
    json CLOB;
    json_varchar VARCHAR2(32767);
    json_member WWV_FLOW_T_VARCHAR2;
begin
    json := :new.ORDER_SKU;
    json_varchar := CAST(json AS VARCHAR2);
    apex_json.parse (json);
    json_member := APEX_JSON.get_members(p_path=>'order_sku');
    FOR i IN 1..json_member.count
    LOOP
    ...
    END LOOP;
end;

I am getting the following error when running this trigger:

SQL Error Message: ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "APEX_050100.WWV_FLOW_JSON", line 1597
ORA-06512: at "TriggerName", line 9
ORA-04088: error during execution of trigger 'TriggerName'
ORA-06512: at line 6`

I am thinking I should just be using a different method to find the length of the JSON array, but I haven't been able to find any through the documentation or stack overflow.

Also if it is of help the JSON is stored in the following form:

{
"order_sku":[{"sku1":"details"},{"sku2":"details"}]
}

Thanks N Cheadle. It looks like you are on 12c. I wondered if you've looked into json support brought in 12cR1? It has functions such as json_table that might be of interest. docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6246 — alexgibbs
JSON_Table did what I was intending to do with the trigger thanks for the comment. — N Cheadle

hmarques hmarques · Accepted Answer · 2017-05-23T13:29:05

Since you are in 12c version I agree with @alexgibbs as you should take a look at json_table if you really want to use apex_json you don't need to convert your clob to varchar and you could do something like

DECLARE
 l_json CLOB := '{"order_sku":[{"sku1":"details"},{"sku2":"details"}] }';
 j apex_json.t_values;
 l_path  VARCHAR2(100);

BEGIN
 apex_json.parse(j, l_json);

 FOR i IN 1..apex_json.get_count(p_values => j, p_path   => 'order_sku')
 LOOP
   -- if your sku tag could be always the same "sku" you would not need the following line
   l_path := 'order_sku[%d].' || apex_json.get_members(p_values => j, p_path => 'order_sku[%d]', p0 => i)(1);

   -- replace with the insert you want to do
   dbms_output.put_line(apex_json.get_varchar2(p_values => j, p_path => l_path, p0 => i));

 END LOOP;

END;

Finding length of JSON array in PL/SQL Trigger

1 Answers