SQL & PL/SQL

Hi Team,

Good Day !.

I have a case where I suppose to run multiple SELECT queries to get  some column value and use them into subsequent update queries.

But due to implementation restrictions, I'm unable to availing the benefits from FORALL for select queries.

ex:

set serveroutpu on;   

declare

cursor elm1 is

    SELECT DISTINCT

    pi.ROOTCORR_OBJECT_ID

    FROM ETL_INFO pi

    INNER JOIN ETL_PROCESS p on p.PROJECT_OBJECT_ID=pi.PROJECT_OBJECT_ID

    AND pi.PROCESS_NAME=p.PROCESS_NAME

    AND mp.STATUS='Closed-Out' ;

--type t_var_record_elm1 is record  ( v_vce_close date  );

--type t_elm1_calc_proc is table of elm1%rowtype;

--type t_close_count is table of t_var_record_elm1; -- index by pls_integer;

   vsql clob;

--v_elm1_calc t_elm1_calc_proc;

--var_record_elm1 t_close_count;

var_record_elm1 t_var_record_elm1;

begin

dbms_output.put_line('Before Bulk Collect: ' || systimestamp);

    open elm1;

    loop

        fetch elm1 bulk collect into v_elm1_calc limit 10000;

        dbms_output.put_line('After Bulk Collect: ' || systimestamp);

          vsql := ' SELECT MAX(cl.EVENT_TIME)  ';

           vsql := vsql ||'FROM W_ACX_CORREVENTLOG_D cl ';

           vsql := vsql ||'INNER JOIN W_CORRESPONDENCE_D c on c.CORRESPONDENCE_OBJECT_ID=cl.CORRESPONDENCE_OBJECT_ID AND                          c.DATASOURCE_ID=cl.DATASOURCE_ID ';

           vsql := vsql ||'WHERE c.ROOT_CORRESPONDENCE_ID=:ROOTCORR_OBJECT_ID ';

           vsql := vsql ||'AND c.DATASOURCE_ID=:vsrc_id ';

    begin

          forall rec in 1..v_elm1_calc.count SAVE EXCEPTIONS

Note:       1.want to use this space for SELECT and read the data in bulk. Not allowing to use BULK collect under FORALL.

                 2.tried below piece , was able to return data into record type, but not able to access it like nested/index by table type.

--           select MAX(c1.EVENT_TIME) into var_record_elm1

--           from W_ACX_CORREVENTLOG_D c1 inner join  W_CORRESPONDENCE_D c

--          on c.CORRESPONDENCE_OBJECT_ID=c1.CORRESPONDENCE_OBJECT_ID AND c.DATASOURCE_ID=c1.DATASOURCE_ID where c.ROOT_CORRESPONDENCE_ID=v_elm1_calc(rec).ROOTCORR_OBJECT_ID and c.DATASOURCE_ID=1;

           vsql:=''; 

           vsql := 'UPDATE ETL_ACO_PROCESS_MAP SET ';

            vsql := vsql ||'CONFIG_END = :1 ';

            vsql := vsql ||'WHERE ROOTCORR_OBJECT_ID = :2';

            vsql := vsql ||'AND DATASOURCE_ID=:3';

--        forall rec in 1..v_elm1_calc.count

--            EXECUTE IMMEDIATE vsql using var_record_elm1(rec).v_vce_close, v_elm1_calc(rec).ROOTCORR_OBJECT_ID,1;

          dbms_output.put_line('After FORALL: ' || systimestamp);            

    commit;

    EXIT WHEN elm1%notfound;

  END LOOP;

  CLOSE elm1;

end;

/

Please help me to handle this scenario, would like to apply the FORALL for both the kind of queries(SELECT & UPDATE).

Thanks,

Apparao Akki