Oracle Pragma EXCEPTION_INIT

Page 3 of 4

Author: Saurabh Gupta Published on: 6th Jul 2011

Oracle Pragma

Oracle Pragma EXCEPTION_INIT

The pragma EXCEPTION INIT allows the association of a self chosen error number with a user defined exception. The error number can be any of the system recognized error number.

Pragma EXCEPTION_INIT Syntax

Sample Code

PRAGMA EXCEPTION_INIT([EX NAME],[ERROR NUMBER], [TRUE | FALSE])

In the PL/SQL block below, the LOCAL_EXCEPTION exception is linked with the error number -100 through the PRAGMA EXCEPTION_INIT. Note the usage of SQLCODE, which returns the same associated error number.

Pragma EXCEPTION_INIT Example Code

The procedure P_GET_ORDERS has been created to get the order code and order quantity for a given date. If no order booking has been made, procedure must display appropriate message.

Sample Code

CREATE OR REPLACE PROCEDURE P_GET_ORDERS (P_ORD_DATE DATE)
IS
L_ORD_CODE VARCHAR2(100);
L_ORD_QTY NUMBER;
ORD_EXCEPTION EXCEPTION;
PRAGMA EXCEPTION_INIT(ORD_EXCEPTION, -100);
CURSOR C IS
SELECT ORD_CODE, ORD_QTY
FROM ORDERS
WHERE ORD_DATE= P_ORD_DATE;
BEGIN
OPEN C;
FETCH C INTO L_ORD_CODE, L_ORD_QTY;
IF C%ROWCOUNT = 0 THEN
RAISE ORD_EXCEPTION;
END IF;
CLOSE C;
EXCEPTION
WHEN ORD_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE ('No Orders booked on:'||TO_CHAR(P_ORD_DATE,'DD-MM-YYYY');
END;
/
SQL> EXEC P_GET_ORDERS(TRUNC(SYSDATE,'YYYY'));
No Orders booked ON 01-01-2011
PL/SQL procedure successfully completed.

Oracle Pragma SERIALLY_REUSABLE

The pragma SERIALLY_REUSABLE enables the package state initialization for each and every server call. As per the conventional behavior of the packages, their state is initialized only during the first time they are referenced in a session. This state remains persistent for the complete session and is flushed off only at the termination of the session.

With the use of the pragma SERIALLY_REUSABLE, the package state is persistent only for a sever call. It gets initialized with every call made to the server while invoking any of its member construct.

Ads

Pragma SERIALLY_REUSABLE Syntax

Sample Code

CREATE [OR REPLACE] PACKAGE [NAME] IS
PRAGMA SREIALLY_REUSABLE
[MEMBER CONSTRUCT PROTOTYPES]
END;
CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
PRAGMA SREIALLY_REUSABLE
[MEMBER CONSTRUCT DEFINTIONS]
END;

Pragma SERIALLY_REUSABLE Example Code

The package PKG_TEST_PRAGMA demonstrates non persistent package state at every server call.

Sample Code

CREATE OR REPLACE PACKAGE PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
L_PKG_STATE NUMBER := 1;
PROCEDURE ASSIGN_SUM_VALUE(P_FACTOR NUMBER);
FUNCTION DISPLAY_VALUE RETURN NUMBER;
END;
Package created.
CREATE OR REPLACE PACKAGE BODY PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE ASSIGN_SUM_VALUE (P_FACTOR NUMBER)
IS
BEGIN
L_PKG_STATE:= L_PKG_STATE + P_FACTOR;
DBMS_OUTPUT.PUT_LINE(CHANGE IN package state|| L_PKG_STATE);
END;
FUNCTION DISPLAY_VALUE RETURN NUMBER
IS
BEGIN
RETURN L_PKG_STATE;
END;
END;
Package body created.
SET SERVEROUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE(Package state before call: ||PKG_TEST_PRAGMA.DISPLAY_VALUE);
PKG_TEST_PRAGMA.ASIGN_SUM_VALUE(5);
DBMS_OUTPUT.PUT_LINE(Package state after call: ||PKG_TEST_PRAGMA.DISPLAY_VALUE);
END;
/
Package state before call: 1
CHANGE IN package state: 6
Package state after call: 1
PL/SQL procedure successfully completed.

1 2 3 4

Read Next: UTL_FILE

This tutorial is part of a Oracle 11g Tutorial tutorial series. Read it from the beginning and learn yourself.

Oracle 11g Tutorial

Comments

Exforsys Connect

Exforsys e-Newsletter