title: Finding length of JSON array in PL/SQL Trigger
body: <p>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:</p>
  
  <pre><code>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=&gt;'order_sku');
      FOR i IN 1..json_member.count
      LOOP
      ...
      END LOOP;
  end;
  </code></pre>
  
  <p>I am getting the following error when running this trigger:</p>
  
  <blockquote>
    <p>SQL Error Message: ORA-06502: PL/SQL: numeric or value error<br>
    ORA-06512: at "APEX_050100.WWV_FLOW_JSON", line 1597<br>
    ORA-06512: at "TriggerName", line 9<br>
    ORA-04088: error during execution of trigger 'TriggerName'<br>
    ORA-06512: at line 6`</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>Also if it is of help the JSON is stored in the following form:</p>
  
  <pre><code>{
  "order_sku":[{"sku1":"details"},{"sku2":"details"}]
  }
  </code></pre>