How to resume a failed statement in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to resume a failed statement in procedure

  1. #1
    Join Date
    Feb 2007
    Posts
    5

    Lightbulb How to resume a failed statement in procedure

    I have a procedure as below:

    CREATE OR REPLACE procedure SP_TRUNC
    AS
    sql_stmt varchar2(1024);
    v_ErrorSeq NUMBER;

    CURSOR cur01 is
    select table_name from trunctab;
    fch_sql cur01%ROWTYPE;
    BEGIN
    OPEN cur01;
    EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''START'',''SP_TRUNCTAB''); END;';
    LOOP
    FETCH cur01
    INTO fch_sql;
    EXIT WHEN cur01%NOTFOUND;
    sql_stmt := 'truncate table '||fch_sql.table_name;
    EXECUTE IMMEDIATE sql_stmt;
    END LOOP;
    EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''END'',''SP_TRUNCTAB''); END;';
    CLOSE cur01;

    EXCEPTION
    WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
    RAISE;

    END;
    /

    This procedure will truncate 10 tables, when the truncate process reach to table 7th and the error happend due to table not exists, table 8th, 9th 10th will not be truncated, how do I resume truncating the rest of the tables if one of the table failed to truncated.

    Thanks for any advise give,

    best regards,

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    You just need to catch the exception and ignore it:
    Code:
    CREATE OR REPLACE procedure SP_TRUNC 
    AS 
    
    -- an exception to catch not-existent table
    table_does_not_exist	exception;
    PRAGMA EXCEPTION_INIT (table_does_not_exist, -942);
    
    sql_stmt varchar2(1024); 
    v_ErrorSeq NUMBER; 
    
    CURSOR cur01 is 
    	select table_name from trunctab; 
    	fch_sql cur01%ROWTYPE; 
    BEGIN 
    	OPEN cur01; 
    	-- you do not need to use execute immediate to call a procedure
    	SP_PROCDTL('START','SP_TRUNCTAB');
    	LOOP 
    		FETCH cur01 INTO fch_sql; 
    		EXIT WHEN cur01%NOTFOUND; 
    		sql_stmt := 'truncate table '||fch_sql.table_name; 
    		begin
    			EXECUTE IMMEDIATE sql_stmt; 
    		exception
    			-- when table does not exist, just ignore it and continue
    			when table_does_not_exist then null;
    		end;
    	END LOOP; 
    	-- you do not need to use execute immediate to call a procedure
    	SP_PROCDTL('END','SP_TRUNCTAB');
    	CLOSE cur01; 
    
    	EXCEPTION 
    	WHEN OTHERS THEN 
    		ErrorPkg.HandleAll(FALSE); 
    		RAISE; 
    
    END; 
    /
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Feb 2007
    Posts
    5

    Smile

    Great Input Ales! Thanks for your information.
    Is there a way for me to resume the process for all the errors? (not only table not exists).

    Best regards

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Sure, just use "when others then null;".
    But this is a very bad practice. The procedure won't bother you with errors, but you might miss something important :-).
    HTH
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  5. #5
    Join Date
    Feb 2007
    Posts
    5
    Actually I have a exception at the bottom of my procedure:


    CREATE OR REPLACE procedure SP_TRUNC
    AS
    sql_stmt varchar2(1024);
    v_ErrorSeq NUMBER;

    CURSOR cur01 is
    select table_name from trunctab;
    fch_sql cur01%ROWTYPE;
    BEGIN
    OPEN cur01;
    EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''START'',''SP_TRUNCTAB''); END;';
    LOOP
    FETCH cur01
    INTO fch_sql;
    EXIT WHEN cur01%NOTFOUND;
    sql_stmt := 'truncate table '||fch_sql.table_name;
    EXECUTE IMMEDIATE sql_stmt;
    END LOOP;
    EXECUTE IMMEDIATE 'BEGIN SP_PROCDTL(''END'',''SP_TRUNCTAB''); END;';
    CLOSE cur01;

    EXCEPTION
    WHEN OTHERS THEN
    ErrorPkg.HandleAll(FALSE);
    RAISE;

    END;
    /

    This will raised an exception, and the error messages will be capture in an exception tables, but it will not resume the process once the exception been raise. If I include the exception code 'table_does_not_exist ' into the same block of the execute immediate, the error will not capture in my exception tables (because it skip the bottom block of my exception), how do I resume the process and keep my exception handling package in use state in my procedure?

    thanks

    best regards,

  6. #6
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Nothing difficult, I think. Just call the exception handler instead of "null". If you do not use the RAISE statement the procedure continues executing on the next line.

    Code:
    ...
    begin
        EXECUTE IMMEDIATE sql_stmt; 
    exception
        -- when table does not exist, just ignore it and continue
       when table_does_not_exist then
            ErrorPkg.HandleAll(FALSE);         
    end;
    ...
    I believe you should read something about Exception handling in PL/SQL.
    HTH,
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width