title: Raising an Exception in PL/SQL or SQL Oracle Developer within a Stored Procedure
body: <p>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.</p>
  <p>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.</p>
  <p>Here is my code from the 2nd stored proc:</p>
  <pre><code> 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;
  </code></pre>
  <p>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...</p>
  <p>below is the code to my second stored proc that calls my first stored proc:</p>
  <pre><code>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;
  /
  </code></pre>
  <p>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.</p>
  <p><a href="https://i.stack.imgur.com/cUdUu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cUdUu.png" alt="pic1" /></a></p>
  <p>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.</p>