Oracle pragma example: prediction_init ()

PRAGMA EXCEPTION_INIT (DEADLOCK_DETECTED,-60 );

EXCEPTION_INIT refers to the command. We can understand it literally, that is, EXCEPTION initialization.

The syntax is also simple. pragma exception (exception_name, error_number );

Everyone has experience. EXCEPTION Processing is that when no defined exception name is available, we use OTHERS to handle all exceptions that have not been captured, PL/SQL designers recommend that you try to use the known EXCEPTION NAME to capture the EXCEPTION. If you do not need this option, try not to use it. Although you do not know why, it is estimated that it is related to performance, the compiled code should be long.

The introduction of this compilation instruction also brings about a solution. For example, what should we do if we want to capture the deadlock EXCEPTION? No predefined exception name is available. Then we can use this compilation command.

We know that ora-60 errors mean deadlocks, www.bkjia.com, then we can set an exception name for this error number, such as DEADLOCK_DETECTED, the syntax is as follows:

DECLARE
DEADLOCK_DETECTED EXCEPTION;
PRAGMA EXCEPTION_INIT (DEADLOCK_DETECTED,-60 );

Well, we can catch the EXCEPTION that handles the deadlock as below.

EXCEPTION
WHEN DEADLOCK_DETECTED THEN
......

In this way, we can avoid using when others then to capture and handle this exception.

This compilation command can be used in almost all program declaration items, but pay attention to the scope of the action. In addition, remember to only give an exception name for an error code.

The following is an example of how to use the SQLERRM function to find the Oracle predefined error number and corresponding error information. This is passed in the 10G version. In the previous version, the buffer overflow error may be reported because the default value of set serveroutput on is 2000 bytes, but the default value of set serveroutput on is unlimited in the 10G version.

SET SERVEROUTPUT ON
Spool errormsg. LOG
DECLARE
VMSG VARCHAR2 (500 );
BEGIN
For I IN 0 .. 20000 LOOP
VMSG: = SQLERRM (-I );
If instr (VMSG, 'not found ') = 0 THEN
DBMS_OUTPUT.PUT_LINE (VMSG );
End if;
End loop;
END;
/

SPOOL OFF

---------------Www.bkjia.com add another example ---------------

The following is an example.

Create or replace procedure SP_Del_Test
(P_ItemAdmin in mfitem. itemadmin % type, -- ItemAdmin
P_ItemCd in mfitem. itemcd % type, -- ItemCode
P_Return out number -- output parameter
) Is
Exp exception;
PRAGMA Exception_Init (exp,-2292 );
Begin
Delete from mfitem t
Where t. itemcd = P_ItemCd
And t. itemadmin = P_ItemAdmin; -- this sentence will cause a-2292 exception, and a level-1 connection deletion exception.
EXCEPTION
WHEN EXP THEN
P_Return: = 9;
ROLLBACK;
WHEN OTHERS THEN
ROLLBACK;
End SP_Del_Test;