EXCEPTIONS and PRAGMA Interview Questions

What is an Exception ? What are types of Exception ?

A warning or error condition in a PL/SQL program is called an exception. Types of exception are :

• Internally defined : Oracle Database Error Messages that does not have a name. The runtime system raises them implicitly.
• Predefined : internally defined exceptions that have predefined names, which PL/SQL declares globally in the package STANDARD. The runtime system raises them implicitly. E.g.: Zero_Divide, No_Data_Found, DUP_VAL_ON_INDEX, TOO_MANY_ROWS etc.
• User-Defined : declare your own exceptions in the declarative part of any PL/SQL anonymous block, subprogram, or package.

Syntax is : exception_name EXCEPTION;

E.g.: DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER(4);

BEGIN

...

IF number_on_hand < 1 THEN

RAISE out_of_stock;

END IF;

EXCEPTION

WHEN out_of_stock THEN

-- handle the error

END;

What is RAISE_APPLICATION_ERROR ?

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

To call RAISE_APPLICATION_ERROR, use the syntax :

raise_application_error(error_number, message[, {TRUE | FALSE}]);

where error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048 bytes long. If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors. RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.

In the following example, you call raise_application_error if an employee's salary is missing:

CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS

curr_sal NUMBER;

BEGIN

SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;

IF curr_sal IS NULL THEN

/* Issue user-defined error message. */

raise_application_error(-20101, 'Salary is missing');

ELSE

UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;

END IF;

END raise_salary;

What is the range of the Error Codes for User-Defined Exceptions ?

The range of error code is between -20000 and -20999.

What is the starting oracle error number ?

ORA-00000 normal, successful completion

Explain about SQLERRM and SQLCODE and their importance.

SQLERRM returns the error message for the last error that came across. SQLERRM is useful for WHEN OTHERS Exception. They can be used for Reporting purpose in Error Handling. They can also store the Error in the Code and store it in the Error Log Table. SQLCODE Returns the Value of the Error Number for the previous error.

What is the Difference between Runtime errors and Syntax errors ?

A Runtime Error is handled with the help of Exception Handling Mechanism in a PL/SQL Block whereas a Syntax Error such as a spelling mistake is efficiently detected by the SQL Compiler.

How can you assign a Name to an Un-Named PL/SQL Exception Block ?

You can assign a name to an Un-Named Exception using Pragma, also known as Exception_init.

Explain about Pragma Exception_Init.

To handle error conditions (typically ORA- messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number.

Syntax : PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);

where exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

DECLARE

deadlock_detected EXCEPTION;

PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

BEGIN

... -- Some operation that causes an ORA-00060 error

EXCEPTION

WHEN deadlock_detected THEN

-- handle the error

END;

What is PRAGMA ? What are the different types of PRAGMA ?

PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. Pragma tells Oracle to use specific rules other than the default rules for a particular object. They are processed at compile time, not at run time.

Differnt types of PRAGMA are:

RESTRICTED_REFERENCES : To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES. This pragma asserts that a function does not read and/or write database tables and/or package variables. Functions that do any of these read or write operations are difficult to optimize, because any call might produce different results or encounter errors.

EXCEPTION_INIT : This pragma is used to associates an exception name with an Oracle error number.

Declare MyExcep exception;

PRAGMA EXCEPTION_INIT(MyExcep,10001);

begin

..…

exception

when I then

dbms_output.put_line( 'Duplicate value');

end;

AUTONOMOUS_TRANSACTION : this pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

Eg : When you declare a trigger as AUTONOMOUS_TRANSACTION, then you can commit inside the trigger, irrespective of the calling procedure.

CREATE OR REPLACE TRIGGER audit_sal

AFTER UPDATE OF salary ON employees FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

-- bind variables are used here for values

INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,

:new.salary, :old.salary );

COMMIT;

END;

SERIALLY_REUSABLE : indicates that the package state is needed only for the duration of one call to the server. This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session. You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both.

INLINE : (introduced in 11g) specifys that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.

E.g.: PROCEDURE p1 (x PLS_INTEGER) IS ...

...

PRAGMA INLINE (p1, 'YES');

x:= p1(1) + p1(2) + 17; -- These 2 calls to p1 will be inlined

...

x:= p1(3) + p1(4) + 17; -- These 2 calls to p1 will not be inlined