SQL & PL/SQL

 

I have a procedure below. If the exception is CHART_RECORD_CURSOR%NOTFOUND as seen below, I want to bypass the exception and obtain the next record. 


i.e. from below 

  IF CHART_RECORD_CURSOR%NOTFOUND 
            THEN 
               NULL; -- continue 
            END IF; 

After returning no records found, how do I go to the begining of the loop without continuing with the rest of the code? Basically if the account number from the chart table is not found on the gtas_accounts table. I want to skip that record and  retreive the next chart table record. 

Thanks. 

 

CREATE OR REPLACE PACKAGE BODY CAMSADM.debbie_gtas_acct_vs_charts 
IS                                        
                                        
PROCEDURE check_gtas_acct_vs_charts  IS                                        

      nbr_of_records             NUMBER := 0; 
      deleted_nbr_of_records     NUMBER := 0; 
     FOUND_FLAG                BOOLEAN := FALSE;    
     P_ERROR VARCHAR2(10); 
                
                 v_apport_category_domain VARCHAR2(60); 
                 v_apport_b_cat_no_domain VARCHAR2(60); 

      CURSOR CHART_RECORD_CURSOR IS 
         SELECT CH.ACCOUNT_NO,                   
                CH.apport_category_flag, 
                CH.apport_b_cat_no_flag, 
         FROM   chart CH 
         ORDER  BY CH.account_no; 
    
      R_FILEREC CHART_RECORD_CURSOR%ROWTYPE; 
    
   BEGIN 
     dbms_output.enable( 500000 ); 
      DBMS_OUTPUT.PUT_LINE('Begin Program '); 
    
      OPEN CHART_RECORD_CURSOR; 
    
      LOOP 
       
         DECLARE 
            E_INVALID EXCEPTION; 
            PRAGMA EXCEPTION_INIT(E_INVALID, -22908); 
         BEGIN 
            FOUND_FLAG := TRUE; 
          
            FETCH CHART_RECORD_CURSOR 
               INTO R_FILEREC; 
            EXIT WHEN CHART_RECORD_CURSOR%NOTFOUND; 
          
            IF CHART_RECORD_CURSOR%NOTFOUND 
            THEN 
               NULL; -- continue 
            END IF; 
          
            nbr_of_records := nbr_of_records + 1; 
BEGIN            --                               
            IF R_FILEREC.apport_category_flag= 'Y' 
            THEN                              
                select  gta2.domain , gta1.ussgl_account_no 
                    INTO  v_APPORT_CATEGORY_DOMAIN ,  v_acct_no 
                FROM gtas_accounts gta1, TABLE( gta1.attribute_list ) gta2   
                    where GTA1.USSGL_ACCOUNT_NO =   R_FILEREC.ACCOUNT_NO ;  
                    and gta2.attribute ='apportion_category_code'          
                    and gta2.domain =  R_FILEREC.apport_category_domain;        
            END IF; 

          EXCEPTION                     
                        WHEN NO_DATA_FOUND THEN 
                                DBMS_OUTPUT.PUT_LINE('no data found  - apportion_category_code   '); 
                                DBMS_OUTPUT.PUT_LINE('CHART TABLE DOES NOT MATCH GTAS ACCOUNT TABLE - ACCOUNT NUMBER: ' || r_filerec.account_no); 
                                found_flag := FALSE; 
  end;    
            -- 
BEGIN                                              
            IF R_FILEREC.APPORT_B_CAT_NO_FLAG = 'Y' 
             THEN       
                  select  gta2.domain , gta1.ussgl_account_no ;   
                    into  v_APPORT_B_CAT_NO_DOMAIN  ,  v_acct_no - 
                FROM gtas_accounts gta1, TABLE( gta1.attribute_list ) gta2   
                    where GTA1.USSGL_ACCOUNT_NO =   R_FILEREC.ACCOUNT_NO ;  
                    and  gta2.attribute ='apportion_cat_b_program_code'          
                    and gta2.domain =  R_FILEREC.apport_b_cat_no_flag; 

            END IF;        

          EXCEPTION                     
                        WHEN NO_DATA_FOUND THEN 
                                DBMS_OUTPUT.PUT_LINE('no data found  - apportion_cat_b_program_code  '); 
                                DBMS_OUTPUT.PUT_LINE('CHART TABLE DOES NOT MATCH GTAS ACCOUNT TABLE - ACCOUNT NUMBER: ' || r_filerec.account_no); 
                                found_flag := FALSE; 
END;         
                                                           
        IF found_flag = FALSE 
        THEN 
                 DBMS_OUTPUT.PUT_LINE('no data found  '); 
               DBMS_OUTPUT.PUT_LINE('CHART TABLE DOES NOT MATCH GTAS ACCOUNT TABLE - ACCOUNT NUMBER: ' || r_filerec.account_no); 
        END IF; 
         
         EXCEPTION 
            WHEN E_INVALID THEN 
               DBMS_OUTPUT.PUT_LINE('Error ORA-22908: reference to NULL table value.- account no: ' || r_filerec.account_no); 
            WHEN NO_DATA_FOUND THEN 
               DBMS_OUTPUT.PUT_LINE('no data found  '); 
               DBMS_OUTPUT.PUT_LINE('CHART TABLE DOES NOT MATCH GTAS ACCOUNT TABLE - ACCOUNT NUMBER: ' || r_filerec.account_no); 
            WHEN OTHERS THEN 
               DBMS_OUTPUT.PUT_LINE('The program received an error. Error message returned was: ' || SQLCODE || ',' || SQLERRM); 
               IF SQLCODE = 0 
                 THEN 
                   p_error := SQLCODE; 
               END IF; 
         END; 
       
      END LOOP; 
    
      CLOSE CHART_RECORD_CURSOR; 
    
      DBMS_OUTPUT.PUT_LINE('     ');      
      DBMS_OUTPUT.PUT_LINE('Number of records  - Record count: ' || nbr_of_records); 
    

END check_gtas_acct_vs_charts; 

END debbie_gtas_acct_vs_charts; 
/