Oracle: Pragma Exception_Init

Oracle: Pragma Exception_Init

I need to catch the following error ORA-01442

In my script below, does it need the leading zero? -01442 or -1442 which of the two values and why?

----------------------------------
    -- Alter column null --
    ----------------------------------
    DECLARE
       allready_null EXCEPTION;
       PRAGMA EXCEPTION_INIT(allready_null, -01442);
    BEGIN
       execute immediate 'ALTER TABLE NMSACTIVECONTACT MODIFY ("ISOURCEID" NULL)';
    EXCEPTION
       WHEN allready_null THEN
          dbms_output.put_line('ISOURCEID is already null, skipping...');
    END;
    /

It came to my attention when it fail to catch the exeption because the error code was different.

1 Answer

It doesn't matter. Error code is a number, so leading zero doesn't affect it.

As documentation says about error number in EXCEPTION_INIT pragma:

Any valid Oracle error number. These are the same error numbers (always negative) returned by the function SQLCODE.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

James H. Sterling
Author

James H. Sterling

James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.