title: How to convert a json array of clob into a object type in Oracle (12.1) stored procedure
body: <p>I am trying to convert a json array to json clob and the convert it object type in an Oracle stored procedure.</p>
  <p>Below is the object type I have in Oracle.</p>
  <pre><code>create or replace TYPE REPORT_OBJ FORCE as OBJECT (
    id NUMBER,
    name NUMBER,
    createDt Date,
    value NUMBER(10,2)
  );
  
  create or replace TYPE REPORT_OBJ_LIST as TABLE OF REPORT_OBJ;
  </code></pre>
  <p>This is my json array:</p>
  <pre><code>[{&quot;id&quot;:1,&quot;name&quot;:&quot;john&quot;,:&quot;createDt&quot;:&quot;01-jan-2020&quot;,&quot;value&quot;:10},
  {&quot;id&quot;:2,&quot;name&quot;:&quot;crystal&quot;,&quot;createDt&quot;:&quot;01-feb-2020&quot;,&quot;value&quot;:20},
  {&quot;id&quot;:3,&quot;name&quot;:&quot;bob&quot;,&quot;createDt&quot;:&quot;01-mar-2020&quot;,&quot;value&quot;:30}]
  </code></pre>
  <p>This is my stored procedure which takes report_obj_list as input parameter</p>
  <pre><code>create or replace PROCEDURE SaveUpdate_ReportsData(reportList IN REPORT_OBJ_LIST)
  AS v_count number;
  v_column REPORTS_DATA.id%TYPE;
  updatedRecs Number;
  recsCount Number;
  dbid REPORTS_DATA.Id%TYPE;
  dbname REPORTS_DATA.name%TYPE;
  dbcreateDt REPORTS_DATA.createDt%TYPE;
  dbvalue REPORTS_DATA.value%TYPE;
  BEGIN
  recsCount := 0;
  updatedRecs := 0;
  for i in reportList.first..reportList.last loop
  v_column := 0;
  dbid := 0;
  dbname := 0;
  dbcreateDt := sysdate;
  dbvalue := 0;
  BEGIN
      SELECT DISTINCT NVL(b.repId,0) into v_column  from (
      (SELECT 'TEMP' as temp from REPORTS_DATA) a left join (
      SELECT DISTINCT 'TEMP' AS temp, NVL(id,0) as repId FROM REPORTS_DATA
      where createDt = reportList(i).createDt ) b on a.temp = b.temp);
          if(v_column &lt;= 0 ) then 
              INSERT INTO REPORTS_DATA (Id,name,createDt,value)        
              VALUES (reportList(i).Id,reportList(i).name, reportList(i).createDt,
                  reportList(i).value);
              updatedRecs := updatedRecs+1;
          else 
              updatedRecs := updatedRecs+1;
  
              SELECT id,name,createDt,value INTO 
                  dbid,dbname,dbcreateDt,dbvalue
                  FROM REPORTS_DATA
               where createDt = reportList(i).createDt;
  
              update REPORTS_DATA set id = NVL(reportList(i).id,dbid), 
                  name = NVL(reportList(i).name,dbname) ,
                  createDt = NVL(reportList(i).createDt,dbcreateDt),
                  value = NVL(reportList(i).value, dbvalue);
       end if;
       EXCEPTION
  WHEN NO_DATA_FOUND THEN
  v_column := null;
  DBMS_OUTPUT.PUT_LINE('hello' || v_column);
      END;
      end loop;
        Commit;
       recsCount:= updatedRecs ;
          DBMS_OUTPUT.PUT_LINE('HELOOwq ' || recsCount);
  end SaveUpdate_ReportsData ;
  below is the oracle table
  
  create table REPORTS_DATA(
  id number,
  name varchar(200),
  createdt date,
  value number(10,2)
  );
  </code></pre>
  <p>From java, I have to convert jsonarray as clob (so that it can accept large amount of data as input to stored procedure), and the stored procedure should accept json array of clob and convert it to 'Report_obj_list', and from there the existing stored procedure will work fine. I have written the stored procedure which accepts object but i need to make changes so that it accepts clob json array and converts that to object inside the stored procedure.</p>
  <p>Updated stored procedure</p>
  <pre><code>create or replace PROCEDURE SaveUpdate_ReportsData(intnum in Number)
  AS v_count number;
  jstr clob;
  reportList report_obj_list;
  v_column REPORTS_DATA.id%TYPE;
  dbid REPORTS_DATA.Id%TYPE;
  dbname REPORTS_DATA.name%TYPE;
  dbcreateDt REPORTS_DATA.createDt%TYPE;
  dbvalue REPORTS_DATA.value%TYPE;
  BEGIN
  jstr := to_clob('[{&quot;id&quot;:1,&quot;name&quot;:&quot;john&quot;,&quot;createDt&quot;:&quot;01-jan-2020&quot;,&quot;value&quot;:10},
  {&quot;id&quot;:2,&quot;name&quot;:&quot;crystal&quot;,&quot;createDt&quot;:&quot;01-feb-2020&quot;,&quot;value&quot;:20},
  {&quot;id&quot;:3,&quot;name&quot;:&quot;bob&quot;,&quot;createDt&quot;:&quot;01-mar-2020&quot;,&quot;value&quot;:30}]');
  
  select report_obj(id, name, to_date(createdt, 'dd-mon-yyyy'), value) 
    bulk collect into reportList
    from json_table(jstr, '$[*]'
                          columns( id       number        path '$.id',
                                   name    varchar2(20)  path '$.name',
                                   createdt varchar2(11)  path '$.createDt',
                                   value   number(10, 2) path '$.value'
                                 )
                   );
  for i in reportList.first..reportList.last loop
          DBMS_OUTPUT.PUT_LINE('name_ ' || reportList(i).name);
  v_column := 0;
  dbid := 0;
  dbname := 0;
  dbcreateDt := sysdate;
  dbvalue := 0;
  BEGIN
      SELECT DISTINCT NVL(b.repId,0) into v_column  from (
      (SELECT 'TEMP' as temp from REPORTS_DATA) a left join (
      SELECT DISTINCT 'TEMP' AS temp, NVL(id,0) as repId FROM REPORTS_DATA
      where createDt = reportList(i).createDt ) b on a.temp = b.temp);
          if(v_column &lt;= 0 ) then 
              INSERT INTO REPORTS_DATA (Id,name,createDt,value)        
              VALUES (reportList(i).Id,reportList(i).name, reportList(i).createDt,
                  reportList(i).value);
          else 
              SELECT id,name,createDt,value INTO 
                  dbid,dbname,dbcreateDt,dbvalue
                  FROM REPORTS_DATA
               where createDt = reportList(i).createDt;
  
              update REPORTS_DATA set id = NVL(reportList(i).id,dbid), 
                  name = NVL(reportList(i).name,dbname) ,
                  createDt = NVL(reportList(i).createDt,dbcreateDt),
                  value = NVL(reportList(i).value, dbvalue);
       end if;
       EXCEPTION
  WHEN NO_DATA_FOUND THEN
  v_column := null;
      END;
      end loop;
        Commit;
  end SaveUpdate_ReportsData ;
  </code></pre>
  <p>and i am calling the stored procedure as below:</p>
  <pre><code>DECLARE
  BEGIN
    SaveUpdate_ReportsData(
     12
    );
  END;
  /
  </code></pre>
  <p>It's not throwing any type of error, but at the same time its not inserting the data into the REPORTS_DATA table not even printing the name.</p>
  <p>Help me solve the problem.</p>
  <p>Thanks in advance.</p>