How can I CONTINUE a LOOP from a exception?

In my previous article, I mentioned about the easy way to make LIST-LIST partition.

And then, I am trying to make the procedure. To make Making the procedure isn’t difficult. But, I have spent lot of time to find the finding a way to EXCEPTION handling in LOOP.

The solution is very simple. Just Put the EXCEPTION clause into the BEGIN.. END block.

Bellow is the sample code.

(1) Create table and insert meta data for LIST-LIST partition.

ceate table weekly_partition_list
 ( 
   table_name varchar2(30),
   category   varchar2(100)
 );
 
 create unique index weekly_partition_list_uk on weekly_partition_list (table_name, category); 
 
 insert all
 into   weekly_partition_list values('PART1','CATEGORY1')
 into   weekly_partition_list values('PART1','CATEGORY2')
 into   weekly_partition_list values('PART1','CATEGORY3')
 into   weekly_partition_list values('PART1','CATEGORY4')
 into   weekly_partition_list values('PART1','DEFAULT')    
 into   weekly_partition_list values('PART2','CATEGORY1')
 into   weekly_partition_list values('PART2','CATEGORY2')
 into   weekly_partition_list values('PART2','CATEGORY3')
 into   weekly_partition_list values('PART2','DEFAULT')
 into   weekly_partition_list values('PART3','CATEGORY1')
 into   weekly_partition_list values('PART3','CATEGORY2')
 into   weekly_partition_list values('PART3','CATEGORY3')
 into   weekly_partition_list values('PART3','CATEGORY4')
 into   weekly_partition_list values('PART3','DEFAULT')  
 select * from dual;

(2) Create log table for job log.

 create table sp_job_result
 ( 
   table_name      varchar2(30),
   partition_name  varchar2(30),
   job_result      varchar2(10),
   job_date        date,
   errorcode       varchar2(500),
   command         varchar2(4000)
 ); 

(3) Make procedure.

 CREATE OR REPLACE PROCEDURE sp_add_weekly_partition
 IS
  v_pos               number;
  v_table_name        varchar2(30);
  v_next_planid       varchar2(30);
  v_command           varchar2(10000);
  v_work              varchar2(10000);
  v_errorcode         varchar2(100);
 
  cursor C1 is
  select table_name from weekly_partition_list group by table_name; 
 
  cursor C2 is
  select category from weekly_partition_list where table_name=v_table_name;
 
 begin
 
   v_command     := NULL;
   v_work        := NULL;   
 
   for pKey1 in C1
   loop 
     v_table_name := pKey1.table_name;  

     for i in 1..4
     loop
       case
         when i=1 then select to_char(sysdate+7, 'YYYYIW')       into v_next_planid from dual;
         when i=2 then select to_char(sysdate+7, 'YYYYIW')||'_A' into v_next_planid from dual;
         when i=3 then select to_char(sysdate+7, 'YYYYIW')||'_B' into v_next_planid from dual;
         when i=4 then select to_char(sysdate+7, 'YYYYIW')||'_C' into v_next_planid from dual;
       end case;
 
       v_command := 'ALTER TABLE '|| pKey1.table_name || ' ADD PARTITION p_' || v_next_planid || ' VALUES  ('||''''||v_next_planid||''''||')' ||chr(10) ;
 
       for pKey2 in C2
       loop
         if C2%ROWCOUNT=1 THEN
           v_command :=  v_command || ' ( ';
         else
           v_command :=  v_command || ' , ';
         end if;
 
        v_command :=  v_command ||' SUBPARTITION p_' || v_next_planid ||'_';
         v_work    :=  replace(replace(lower(pKey2.category),' ','_'),'-','_');
         v_pos     :=  instr(v_work, '(');   
 
         if (v_pos > 0) THEN
           v_command := v_command || substr(v_work,1,v_pos-1);
         else
           v_command := v_command || v_work;
         end if;    
 
         v_command :=  v_command ||replace(' VALUES ('||''''|| pKey2.category ||''''||')','''DEFAULT''','DEFAULT')||chr(10);
       end loop;
 
       v_command := v_command || ')' ||chr(10);
    
       BEGIN -- added for exception handling in LOOP
         execute immediate v_command;
         insert into sp_job_result(table_name,   partition_name, job_result, job_date, errorcode, command) 
                            values(v_table_name, v_next_planid, 'SUCC',      sysdate,  null,      null);     
         commit;
      
         EXCEPTION
           WHEN OTHERS THEN
             dbms_output.put_line(SQLCODE||'-ERROR-'||SQLERRM||chr(10)||'----COMMAND----'||chr(10)||v_command);
             v_errorcode := substr(SQLCODE||'-ERROR-'||SQLERRM,1,100);       
             insert into sp_job_result(table_name,   partition_name, job_result, job_date, errorcode,   command) 
                                values(v_table_name, v_next_planid, 'FAILED',    sysdate,  v_errorcode, v_command);     
             commit;
       END;-- added for exception handling in LOOP    
       v_command     := NULL;
       v_work        := NULL;
       
     end loop;
   end loop;
 end;
/  

(4) Test.

 SQL> set serveroutput on 
 
 -- 1st time, the procedure runs witout error. 

 SQL> exec sp_add_weekly_partition;
 PL/SQL procedure successfully completed. 
 
 -- 2th time, the procedure occurs error every execute. 
 
 SQL> set serveroutput on
 SQL> exec sp_add_weekly_partition;
 -14312-ERROR-ORA-14312: Value '201326' already exists in partition2
 -14312-ERROR-ORA-14312: Value '201326_A' already exists in partition3
 -14312-ERROR-ORA-14312: Value '201326_B' already exists in partition4
 -14312-ERROR-ORA-14312: Value '201326_C' already exists in partition5
 -14312-ERROR-ORA-14312: Value '201326' already exists in partition2
 -14312-ERROR-ORA-14312: Value '201326_A' already exists in partition3
 -14312-ERROR-ORA-14312: Value '201326_B' already exists in partition4
 -14312-ERROR-ORA-14312: Value '201326_C' already exists in partition5
 -14312-ERROR-ORA-14312: Value '201326' already exists in partition2
 -14312-ERROR-ORA-14312: Value '201326_A' already exists in partition3
 -14312-ERROR-ORA-14312: Value '201326_B' already exists in partition4
 -14312-ERROR-ORA-14312: Value '201326_C' already exists in partition5
 
 PL/SQL procedure successfully completed.