Oracle pragma example: prediction_init

PRAGMA is a Compilation instruction (or command ). Compilation instructions are processed during compilation. They are not executed at runtime. Compilation instructions are special commands issued to the Compilation Program. It is also called a pseudo-instruction and does not change the meaning of the program. It only transmits information to the compilation program. In fact, it is very similar to the performance adjustment prompt embedded in the comments of SQL statements.

EXCEPTION_INIT: associate a specific error code with the exception identifier declared in the program.

Example: PRAGMA EXCEPTION_INIT (DEADLOCK_DETECTED,-60 );

EXCEPTION_INIT compile command, that is, EXCEPTION initialization. The syntax is also simple. pragma exception (exception_name, error_number); when processing EXCEPTION is, when no defined exception name is available, we will use OTHERS to handle all the exceptions that have not been captured. PL/SQL designers recommend that you use the known EXCEPTION NAME to catch them. In the end, www.bkjia.com does not use this option as much as possible, although I don't know why, it is probably related to performance, and the compiled code should be very 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.

Ora-60 errors mean deadlocks, so 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. When www.bkjia.com is passed in 10 Gb, the buffer overflow error may be reported in earlier versions. This is because set serveroutput on is 2000 bytes by default, but it is unlimited by default in 10 Gb.

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

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;