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;
/