Type of PL/SQL Programs -
+ Anonymous block - not stored in the db. starts with DECLARE..BEGIN..END. You run it on demand.
+ Procedure - Stored in db. Can pass IN, OUT, INOUT variables.
+ Function - stored in db. Should have RETURN. Can accept variables . Can return only 1 output.
Can be used in SQL Statments but the function should not have any dml.
+ Pacakge - 2 parts. Package Body and Package Header
The advantage of having header is that we can change the body without invalidating other objects which uses the package.
Encapsulation, Overloading,
Cursors - Oracle uses work areas to execute SQL statements and store processing information.
A PL/SQL construct called a cursor lets you name a work area and access its stored information.
There are two kinds of cursors: implicit and explicit.
PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
Exceptions -
Predefined Exceptions
NO_DATA_FOUND -1403 +100
INVALID_CURSOR -1001 -1001
INVALID_NUMBER
ZERO_DIVIDE
Userdefined Exception
DECLARE
user_excp EXCEPTION;
To handle error conditions that have no predefined name, use WHEN OTHERS THEN or you can associate a name using
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
RAISE_APPLICATION_ERROR - lets you issue user-defined ORA- error messages from stored subprograms.
raise_application_error(error_number, message[, {TRUE | FALSE}]); -20000 .. -20999
If there is no handler for a user-defined exception, the calling application gets the following error: ORA-06510: PL/SQL: unhandled user-defined exception
When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
SQLCODE & SQLERRM :-
For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message: User-Defined Exception
unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message.
If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message: ORA-0000: normal, successful completion.
+ Anonymous block - not stored in the db. starts with DECLARE..BEGIN..END. You run it on demand.
+ Procedure - Stored in db. Can pass IN, OUT, INOUT variables.
+ Function - stored in db. Should have RETURN. Can accept variables . Can return only 1 output.
Can be used in SQL Statments but the function should not have any dml.
+ Pacakge - 2 parts. Package Body and Package Header
The advantage of having header is that we can change the body without invalidating other objects which uses the package.
Encapsulation, Overloading,
Cursors - Oracle uses work areas to execute SQL statements and store processing information.
A PL/SQL construct called a cursor lets you name a work area and access its stored information.
There are two kinds of cursors: implicit and explicit.
PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row.
For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:
Exceptions -
Predefined Exceptions
NO_DATA_FOUND -1403 +100
INVALID_CURSOR -1001 -1001
INVALID_NUMBER
ZERO_DIVIDE
Userdefined Exception
DECLARE
user_excp EXCEPTION;
To handle error conditions that have no predefined name, use WHEN OTHERS THEN or you can associate a name using
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
RAISE_APPLICATION_ERROR - lets you issue user-defined ORA- error messages from stored subprograms.
raise_application_error(error_number, message[, {TRUE | FALSE}]); -20000 .. -20999
If there is no handler for a user-defined exception, the calling application gets the following error: ORA-06510: PL/SQL: unhandled user-defined exception
When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
SQLCODE & SQLERRM :-
For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message: User-Defined Exception
unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message.
If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message: ORA-0000: normal, successful completion.
Comments