Handle ORA-00947 and Continue Execution in PL/SQL Package

Question

Problem:

In a large PL/SQL package, I have multiple INSERT statements, and sometimes I encounter the ORA-00947: not enough values error when the number of values doesn't match the columns. I want to catch this error, log it, and allow the package to continue with subsequent operations (like inserting into other tables), without invalidating the entire package.

Code:

CREATE TABLE RFTB_DAILY_GL_BAL (
    col1 NUMBER,
    col2 VARCHAR2(20),
    col3 DATE
);

DECLARE
BEGIN
    BEGIN
        INSERT INTO RFTB_DAILY_GL_BAL-- (col1, col2, col3)
        SELECT 1 FROM dual; -- Causes ORA-00947
    EXCEPTION
        WHEN OTHERS THEN
                dbms_output.put_line('error found: ' || SQLERRM);
          
    END;

    -- Proceed to next insertion
    dbms_output.put_line('Continuing to next step...');
    -- Simulate another operation
    INSERT INTO another_table (col1, col2) VALUES ('test', SYSDATE);

END;
/

Question:

Is this an efficient way to catch and handle ORA-00947 without stopping the execution of the entire PL/SQL package? Any suggestions to improve this approach?

Reason:

I'm in the development phase, and I often create or drop columns based on evolving requirements (yes, I know it's not ideal, but short deadlines make it necessary). Because of this, even if I specify columns in the INSERT, I can't rely on the column structure remaining constant. I need to catch ORA-00947: not enough values and continue execution without invalidating the package.

Answer 1

In a package this will never work using regular sql. The "ORA-00947: not enough values" is not raised when running the package, it is raised when the package tries to recompile. A package with an invalid sql statement is invalid and cannot be executed. An alternative is to use dynamic sql with EXECUTE IMMEDIATE and define an exception for the ORA-00947. Then trap the exception.

Example:

CREATE OR REPLACE PROCEDURE ins AS

    e_too_many_values EXCEPTION;
    PRAGMA exception_init ( e_too_many_values,-00947 );
    c_too_many_values CONSTANT VARCHAR2(512) := 'INFO: ORA-00947: not enough values';
BEGIN
    dbms_output.put_line('start');
    BEGIN
        EXECUTE IMMEDIATE q'!insert into emp(empno,ename) values (1)!';
    EXCEPTION
        WHEN e_too_many_values THEN
            dbms_output.put_line(c_too_many_values);
    END;
    dbms_output.put_line('end');
END;
/

Procedure INS compiled

set serveroutput on 
exec ins;

start

INFO: ORA-00947: not enough values

end

PL/SQL procedure successfully completed.