CODEWITHSUNDEEP
sqloracle-databaseplsqlbulkinsert
Jay
Jay

Reputation: 65

Fetch and bulk collect from sys_refcursor from another procedure and insert into another table

create or replace procedure ins_act
as 
 l_result sys_refcursor; 
 l_id1                   varchar2(32);
 l_id2                   varchar2(32);
 l_id3                   varchar2(32);
 l_pid                   varchar2(16);
 l_ac                    varchar2(32);
 l_activity_date         varchar2(32);
 l_file_id               varchar2(64):='FILE_' || 
 to_char(sysdate,'MM/DD/YYYY');
      begin
          -- Procedure to pull the get records 
          get_act(l_result);
          loop 
             -- Bulk fetch 
              fetch l_result bulk collect into 
              l_id1,l_id2,l_id3,l_platform_id,l_ac,l_activity_date,l_file_id 
              limit 100;

       -- I need to insert into a temp table here. 
       -- Using bulk collect to increase the process speed and there are no 
       -- tables matching the l_result output type.

    -- temp table
         insert into temp( 
         id1,id2,id3,platform_id,activity_code,update_timestamp,file_id)

         values(to_char(to_date(l_id1,'mm/dd/yyyy'),'mm/dd/yyyy'),
         l_id2,l_id3,0,l_activity_code,sysdate,l_file_id);

         exit when l_result%notfound; 

      end loop;

  close l_result;*/

end;
/

I need to insert the sys_refcursor l_result records into the temp table. Please suggest the best method to achieve this. There will be around 250 k - 1 million records inserted

Upvotes: 0

Views: 2227

Answers (1)

XING
XING

Reputation: 9886

I understand you need a BULK operation to complete your requirement. See below how you can do it. Also read the comments inline.

create or replace procedure ins_act
as 

 l_result                sys_refcursor;

 --Created a RECORD type to hold the result of the SYS_REFCURSOR
 TYPE RSLT IS RECORD
 (

 l_id1                   varchar2(32),
 l_id2                   varchar2(32),
 l_id3                   varchar2(32),
 l_pid                   varchar2(16),
 l_ac                    varchar2(32),
 l_activity_date         varchar2(32),
 l_file_id               varchar2(64)
 ) ;

 --Created associative array to hold the result
 Type v_reslt is table of RSLT index by PLS_INETEGER;

 --Variable to Record type
 var_reslt   v_reslt;

begin
          -- Procedure to pull the get records 
          get_act(l_result);

          -- Bulk fetch
          Loop

          fetch l_result bulk collect into  var_reslt limit 100;         

          --Bulk Insert     
          FORALL I INTO 1..var_reslt.count SAVE EXCEPTIONS
          insert into temp(id1,
                           id2,
                           id3,
                           platform_id,
                           activity_code,
                           update_timestamp,
                           file_id)          
          values 
             (to_char(to_date(var_reslt(i).l_id1,'mm/dd/yyyy'),'mm/dd/yyyy'),
              var_reslt(i).l_id2,
              var_reslt(i).l_id3,
              0,
              var_reslt(i).l_ac,
              var_reslt(i).l_activity_date,
              var_reslt(i).l_file_id);

         exit when l_result%notfound; 

      end loop;

   Commit;

  close l_result;

Exception
 WHEN OTHERS
   THEN    
         --Bulk Exception handling
         FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
         LOOP
            DBMS_OUTPUT.put_line (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
               || ‘: ‘
               || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
         RAISE;          
end;
/

PS: Not tested.

Upvotes: 1

Related Questions