What is pragma exception_init in oracle sql plsql interview question and answer?

pragma exception_init :

It associates an exception name with oracle error number.ORA_error is handled by writing specific handler. Word pragma signifies or mean that it is compiler directive. Function SQLCODE is used to return the valid oracle error number.EXCEPTION_INT is used in the declarative part (before BEGIN keyword) of any PLSQL block, subprogram or package. Pragma must be appeared in the declaration section as it is associative exception which is used somewhere after the exception declaration. One exception name is assigned to the particular error number.

It is allowed to handle the specific errors using the custom exception names. It is directive used to handle the user defined exception with a specific error number. It is useful for mapping oracle error codes like ORA-00942, ORA-01400 to user defined exceptions which enables more readable and structured error which is going to handle in the code.

Error number in the future may arise conflict among the unrelated application using that number. Error code which is associated with exception error code can be either 100 which is the numeric code for no data found which SQL CODE function returns or any negative integer greater than -1000000 except -1403 which is another numeric code for no data found. No data found is the predefined exception.

If two exception EXCEPTION_INIT pragmas is to assign different error codes to the same user defined exception, then the later pragma overrides the earlier pragma. Negative integers greater than -65536 are only partially converted. Application declared exception which are raised and caught locally within the layer of entry don't need EXCEPTION_INIT pragmas. If it is unhandled beyond the layer of entry, then it automatically converts into ORA-6515.

An error is not having the predefined name, then this exception PRAGMA EXCEPTION_INIT is used to give the name.

SYNTAX:

PRAGMA EXCEPTION_INIT(exception_name,error_number);

Here,exception_name is the user defined exception name which you want to associate with an oracle error number & error_number which you want to associate with an exception.

create table emp3(empno int not null,sal int not null);

DECLARE

  --User-defined exception

  custom_exception EXCEPTION;

  

  --  I am associating the exception with Oracle error ORA-01400 in which we cannot insert NULL into a column

  PRAGMA EXCEPTION_INIT(custom_exception, -1400);

  

BEGIN

  -- I am attempting to insert a NULL value into a column sal that does not allow NULL

  INSERT INTO emp3 (empno,sal) VALUES (1,NULL);

  

EXCEPTION

  WHEN custom_exception THEN

    DBMS_OUTPUT.PUT_LINE('Custom error occurred: cannot insert NULL into a non-nullable column.');

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Other error occurred: ' || SQLERRM);

END;

/