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.
2
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.
Realistically, no.
ORA-00947 is a PL/SQL compilation error. The code you posted doesn’t compile. Since it doesn’t compile, it cannot be run so it can never reach the exception handler. You can’t catch compilation errors with a runtime exception handler.
Technically, yes.
That being said, you can use dynamic SQL to defer the compilation until runtime which will change your compilation error into a runtime error that you could catch. Practically, you would not want to do this because it makes it vastly more time consuming to write, debug, and maintain your code.
So you could do something like
DECLARE
BEGIN
BEGIN
EXECUTE IMMEDIATE
'INSERT INTO RFTB_DAILY_GL_BAL ' ||
' 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;
/
But practically, it is very unlikely that moving everything that you would want to catch at runtime to dynamic SQL is going to be a good choice.
It is far more likely that you would be better served writing more modular code. If you have one package that handles all DML on RFTB_DAILY_GL_BAL, for example, then you know that if you are going to modify the structure of that table, you would need to modify the implementation of that package. You could make those implementation backwards compatible if you wanted to so that the calling code would at least continue to compile without any changes.
1