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
