Raising an Exception in PL/SQL or SQL Oracle Developer within a Stored Procedure-CodePudding

im trying to handle an custom EXCEPTION in SQLPLUS, i have two stored Procedures, and I have the second stored procedure calling the first one.

In the first Stored procedure, i have a CASE thats checking the data_types of the rows in a table, and if the Type IS NOT a CHAR, VARCHAR2, DATE, or NUMBER, then its supposed to display the Data_Type and RAISE_APPLICATION_ERROR of the customer EXCEPTION that i declared in the 2nd Procedure that calls the first one.

Here is my code from the 2nd stored proc:

 CASE CurrentRow.Data_Type 
      WHEN 'CHAR' THEN
      wDataType := (CurrentRow.Data_Type || '(' || CurrentRow.CHAR_LENGTH || ')');      
      WHEN 'VARCHAR2' THEN
      wDataType := (CurrentRow.Data_Type || '(' || CurrentRow.CHAR_LENGTH || ')');
      WHEN 'DATE' THEN
      wDataType := (CurrentRow.Data_Type);
      WHEN 'NUMBER' THEN
      wDataType := (CurrentRow.Data_Type || '(' || CurrentRow.Data_Precision || ',' || CurrentRow.Data_Scale || ')');
    ELSE
      wDataType := (CurrentRow.Data_Type);
      RAISE_APPLICATION_ERROR(-20100, '*** Unknown Data Type ' || CurrentRow.Data_Type || ' ***');      
    END CASE;

Now i have a nested block within my second stored proc, but no matter where i put it or try and place it around, as soon as the error is raised? it stops executing of the rest of my code, and thats my issue. I have a feeling im not doing my nested block correctly perhaps in my second stored proc...

below is the code to my second stored proc that calls my first stored proc:

CREATE OR REPLACE PROCEDURE Get_Tables
/*
* Sept 26th, 2021
* To process each table in the schema, and output table-related lines.
*/
AS 
  CURSOR TablesSelected IS
    SELECT Table_Name
    FROM User_Tables
    ORDER BY Table_Name ASC;
  CurrentRow User_Tables%ROWTYPE;
  dt DATE := sysdate;
  wVersionNumber VARCHAR(4) := 'V3.0';
  wLeftPadTableName VARCHAR(100);
  wLengthOfStatement VARCHAR(100);

BEGIN
  DBMS_OUTPUT.PUT_LINE( '---- Oracle Catalog Extract Utility ' || wVersionNumber || ' ----' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- Run on '|| TO_CHAR(dt,'Mon DD,YYYY','NLS_DATE_LANGUAGE=English')||' at '||TO_CHAR(dt,'HH24:MI') );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- S T A R T I N G  T A B L E  D R O P S' );
  DBMS_OUTPUT.PUT_LINE( '----' );

  FOR CurrentRow IN TablesSelected LOOP
    DBMS_OUTPUT.PUT_LINE( 'DROP TABLE ' || CurrentRow.Table_Name);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- T A B L E  D R O P S  C O M P L E T E D' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- S T A R T I N G  T A B L E  C R E A T E' );
  DBMS_OUTPUT.PUT_LINE( '----' );

/* 
* Nested block starts
* LOOP gets all the tables in the database and aligns them up. 
*/
    DECLARE
      INVALID_COLUMN_EXCEPTION EXCEPTION;
      PRAGMA EXCEPTION_INIT(INVALID_COLUMN_EXCEPTION, -20100);
    BEGIN

  FOR CurrentRow IN TablesSelected LOOP
    wLengthOfStatement := LENGTH('CREATE TABLE' || CurrentRow.Table_Name || ' ' || '); -- ');
    wLeftPadTableName := LPAD('); -- ', wLengthOfStatement, ' ');
    DBMS_OUTPUT.PUT_LINE( '--   Start extracting table ' || CurrentRow.Table_Name);
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE ' || CurrentRow.Table_Name || '( ');
    Extract_Columns(CurrentRow.Table_Name);
    DBMS_OUTPUT.PUT_LINE(wLeftPadTableName || 'END of table ' || CurrentRow.Table_Name || ' creation');
    DBMS_OUTPUT.PUT_LINE( '--' );
    DBMS_OUTPUT.PUT_LINE( '--' );
  END LOOP;

    EXCEPTION
      WHEN INVALID_COLUMN_EXCEPTION THEN
    DBMS_OUTPUT.PUT_LINE('================================================================================');   
        DBMS_OUTPUT.PUT_LINE('== EXCEPTION ' || SQLCODE || ' Raised - ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('== Unable to complete table generation for ' || CurrentRow.Table_Name);
        DBMS_OUTPUT.PUT_LINE('================================================================================');
    END;

/* 
* Nested block ends 
*/

  DBMS_OUTPUT.PUT_LINE( '---- T A B L E  C R E A T E  C O M P L E T E D' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- Oracle Catalog Extract Utility ' || wVersionNumber || ' ----' );
  DBMS_OUTPUT.PUT_LINE( '---- Run Completed on '||TO_CHAR(dt,'Mon DD,YYYY','NLS_DATE_LANGUAGE=English')||' at '||TO_CHAR(dt,'HH24:MI') );

END;
/

When i print out my report...my raised ERROR kills the execution of the rest of my loop, so it doesn't grab the other tables and their rows.

If any SQL wizards can help me isolate my issue i would greatly appreciate that. I need to know what I can do to make my LOOP and stored procedure CONTINUE executing even after the EXCEPTION was raised.

CodePudding user response：

If you want the FOR loop to continue after an EXCEPTION occurs, put the nested block inside the FOR loop.
(You also don't need a separate DECLARE for the custom EXCEPTION.)

CREATE OR REPLACE PROCEDURE Get_Tables
AS 
  CURSOR TablesSelected IS
    SELECT Table_Name
    FROM User_Tables
    ORDER BY Table_Name ASC;
  CurrentRow User_Tables%ROWTYPE;
  dt DATE := sysdate;
  wVersionNumber VARCHAR(4) := 'V3.0';
  wLeftPadTableName VARCHAR(100);
  wLengthOfStatement VARCHAR(100);
  INVALID_COLUMN_EXCEPTION EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVALID_COLUMN_EXCEPTION, -20100);
BEGIN
  DBMS_OUTPUT.PUT_LINE( '---- Oracle Catalog Extract Utility ' || wVersionNumber || ' ----' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- Run on '|| TO_CHAR(dt,'Mon DD,YYYY','NLS_DATE_LANGUAGE=English')||' at '||TO_CHAR(dt,'HH24:MI') );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- S T A R T I N G  T A B L E  D R O P S' );
  DBMS_OUTPUT.PUT_LINE( '----' );

  FOR CurrentRow IN TablesSelected LOOP
    DBMS_OUTPUT.PUT_LINE( 'DROP TABLE ' || CurrentRow.Table_Name);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- T A B L E  D R O P S  C O M P L E T E D' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- S T A R T I N G  T A B L E  C R E A T E' );
  DBMS_OUTPUT.PUT_LINE( '----' );

  FOR CurrentRow IN TablesSelected LOOP
    wLengthOfStatement := LENGTH('CREATE TABLE' || CurrentRow.Table_Name || ' ' || '); -- ');
    wLeftPadTableName := LPAD('); -- ', wLengthOfStatement, ' ');
    DBMS_OUTPUT.PUT_LINE( '--   Start extracting table ' || CurrentRow.Table_Name);
    DBMS_OUTPUT.PUT_LINE('CREATE TABLE ' || CurrentRow.Table_Name || '( ');
  /* 
   * Nested block starts
   */
    BEGIN
      Extract_Columns(CurrentRow.Table_Name);
    EXCEPTION
      WHEN INVALID_COLUMN_EXCEPTION THEN
        DBMS_OUTPUT.PUT_LINE('================================================================================');
        DBMS_OUTPUT.PUT_LINE('== EXCEPTION ' || SQLCODE || ' Raised - ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('== Unable to complete table generation for ' || CurrentRow.Table_Name);
        DBMS_OUTPUT.PUT_LINE('================================================================================');
    END;
  /*
   * Nested block ends 
   */
    DBMS_OUTPUT.PUT_LINE(wLeftPadTableName || 'END of table ' || CurrentRow.Table_Name || ' creation');
    DBMS_OUTPUT.PUT_LINE( '--' );
    DBMS_OUTPUT.PUT_LINE( '--' );
  END LOOP;

  DBMS_OUTPUT.PUT_LINE( '---- T A B L E  C R E A T E  C O M P L E T E D' );
  DBMS_OUTPUT.PUT_LINE( '----' );
  DBMS_OUTPUT.PUT_LINE( '---- Oracle Catalog Extract Utility ' || wVersionNumber || ' ----' );
  DBMS_OUTPUT.PUT_LINE( '---- Run Completed on '||TO_CHAR(dt,'Mon DD,YYYY','NLS_DATE_LANGUAGE=English')||' at '||TO_CHAR(dt,'HH24:MI') );
END;
/