bulk update in oracle

chalie001
chalie001 used Ask the Experts™
on
how can i update the records fast within hour i have got 7 million records,am in oracle 12c
i try this
DECLARE
       c_limit PLS_INTEGER := 3000000;

       CURSOR cur1
       IS
          SELECT item_no
            FROM noc_item_encoded_reply
           WHERE enc_encoded_reply  is not null
		   and enc_encoded_reply like '%*';

       TYPE curr_ids_t IS TABLE OF  noc_item_encoded_reply.item_no%TYPE;
	  

       l_curr_ids   curr_ids_t;
	   
    BEGIN
       OPEN cur1 ;

       LOOP
          FETCH cur1 
          BULK COLLECT INTO l_curr_ids
          LIMIT c_limit;      -- This will make sure that every iteration has 100 records selected

          EXIT WHEN l_curr_ids.COUNT = 0;           

        FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS   
		   update noc_item_encoded_reply re
         set   enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
         where enc_encoded_reply like '%*'          
		  and enc_encoded_reply  is not null
		  and re.item_no = l_curr_ids(indx);
      commit;  
        
	  FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
	  update noc_item_encoded_reply_jn re
         set   enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
         where enc_encoded_reply like '%*'          
		  and enc_encoded_reply  is not null
		  and re.item_no = l_curr_ids(indx);
		  commit;
	 
	  FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
	  update  noc_t_mrc re
         set   enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
         where enc_encoded_reply like '%*'          
		  and enc_encoded_reply  is not null
		  and re.item_no = l_curr_ids(indx);
		  commit;
		  
      
      
      FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
      update  noc_fc_seg_v_jn re
         set   segv_encoded_reply = rpad(nvl(rtrim(segv_encoded_reply, '*'), '#'), length(segv_encoded_reply), '#')
         where segv_encoded_reply like '%*'          
      and segv_encoded_reply  is not null
      and re.segv_item_no = l_curr_ids(indx);
      commit;
      
      FORALL indx IN 1 .. l_curr_ids.COUNT SAVE EXCEPTIONS 
      update  noc_fc_seg_v re
         set   segv_encoded_reply = rpad(nvl(rtrim(segv_encoded_reply, '*'), '#'), length(segv_encoded_reply), '#')
         where segv_encoded_reply like '%*'          
      and segv_encoded_reply  is not null
      and re.segv_item_no = l_curr_ids(indx);
      commit;
      END LOOP;

    EXCEPTION
       WHEN OTHERS
       THEN
          IF SQLCODE = -24381
          THEN
             FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
             LOOP
                 -- Caputring errors occured during update
                DBMS_OUTPUT.put_line (
                      SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
                   || ': '
                   || SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);

                 --<You can inset the error records to a table here>


             END LOOP;
          ELSE
             RAISE;
          END IF;
    END;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Have you tried single statements without the PL/SQL code and the in memory pl/sql table?

Looks like one update and two MERGE statements should work.

Author

Commented:
if i do like that am geting

ORA-20220: ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
Remove the dbms_output calls ;-)
Or trim them!
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>if i do like that am getting

Just to confirm:  You get a ORU-10027 when doing a straight update without any PL/SQL code?
Just to confirm:  You get a ORU-10027 when doing a straight update without any PL/SQL code?
I doubt that ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial