PL/SQL combines the power and flexibility of SQL (4GL) with the procedural constructs of a 3GL. This results in a robust, powerful language suited for designing complex applications. It is able to use control structure(IF-THEN,FOR,etc) like any other 3GL in combination with SQL.
PL/SQL uses early binding to better performance when the block is called, however early binding has the side effect that all objects must exist (in order to be bined) at compile time , this restriction prohibits that PL/SQL use DDL (Data Definition Language ) since this type of manipulation would create new objects, which in turn would not be availabale when the compilation started.
DML and transaction control statements are the only SQL statements that dont have the potential to modify schema objects or permissions on schema objects, thus they are the only legal SQL statements in PL/SQL. (The package DBMS_SQL allows some flexibilty to this restriction).
There are several types of PL/SQL blocks:
The following is a typical structure used to delimit the areas of the block :
DECLARE /* Declaritive section is here */ BEGIN /* Executables section is here */ EXCEPTION /* Exception section is here */ END;
In general, there are two forms of variables: scalar and composite .Scalar variables permit only a single value for the variable.Composite variables (Records or Tables) can contain multiple values of either identical or single data types.
DECLARE nombre_articulo varchar2(42); precio number(5,2);
A variable can also be of a type referenced from antoher table or variable, this is done with the argument %TYPE.
DECLARE
precio number(8,2);
costo PRECIO%TYPE; -- References the datatype for precio
descripcion articulos.tipo%TYPE; -- References the datatype for column
tipo in table articulos
BEGIN
-------
END;
See the following examples
It contains all the DML language of the PL/SQL block
See the following examples
Each block declares its variables in the DECLARATIVE SECTION and they are only valid for that Block,the same applies for EXCEPTION HANDLERS, they are valid only in the block. The use of packages makes the block rules more flexible.
See the following examples
Normally reserved words are all capitalized. There are no strict rules to these conventions, however there is a big benefit in establishing a system wide coding convention, as this a basic step in tuning applications, this allows for statments to be parsed and be reused, in case a query is syntatically the same as the other but an upper case letter is used instead of a lower case, then the statement will have to be reparsed causing a slight delay in the application.
SELECT ---select_list_item--- INTO ---PL/SQL_record,variable--- FROM ---table_reference--- WHERE --- where_clause---
DECLARE v_id nombres.id%TYPE; estudios escuela.nivel%TYPE;
See following examples
The commands COMMIT,ROLLBACK and SAVEPOINT can also be used inside the PL/SQL statements.
See following examples
They are normally used to update,delete or update batches of data. The structures are common to any 3GL : IF=THEN-ELSE, FOR,WHILE.
See the following examples
DECLARE
v_number_seats rooms.number_seats%TYPE;
v_comment VARCHAR2(35);
BEGIN
SELECT number_seats
FROM rooms
WHERE room_id = 9000;
IF v_number_seats < 50 THEN
v_comment := 'Small';
ELSIF v_number_seats < 100 THEN
v_comment := 'Big';
ELSE
v_comment := 'Very Big';
END IF;
END;
DECLARE
v_number1 NUMBER;
v_number2 NUMBER;
v_Result VARCHAR2(25);
BEGIN
............
IF v_number1 IS NULL OR
v_number2 IS NULL THEN
v_Result := 'Unknown';
ELSIF v_number1 < v_number2 THEN
v_Result := 'Yes';
ELSE
v_Result := 'No';
END IF;
END;
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table
VALUES (v_Counter,'Loop Index');
v_Counter := v_Counter + 1;
EXIT WHEN v_Counter > 50;
END LOOP;
END;
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
WHILE v_Counter <= 50 LOOP
INSERT INTO temp_table
VALUES (v_Counter,'Loop Index');
v_Counter := v_Counter + 1;
END LOOP;
END;
BEGIN
FOR v_Counter IN 1..50 LOOP
INSERT INTO temp_table
values (v_Counter, 'Loop Index');
END LOOP;
END;
BEGIN
FOR v_Counter IN REVERSE 10..50 LOOP
INSERT INTO temp_table
values (v_Counter,'Loop Index');
END LOOP;
END;
Labels are used primarly in conjunction with GOTOs to send the execution of a program to a specific line, the labels are identified by <<name of label>>, however labels can also be used with other control structures.
DECLARE
v_Counter BINARY_INTEGER := 1;
BEGIN
LOOP
INSERT INTO temp_table
VALUES (v_Counter,'Loop Index');
v_Counter := v_Counter + 1;
IF v_Counter > 50 THEN
GOTO 1_endofloop;
END IF;
END LOOP;
<<1_endofloop>>
INSERT INTO temp_table (char_col)
VALUES ('Done!')
END;
A GOTO is illegal when the loop tries to branch into an inner loop ( that is,go into the loop, before checking the constraints on that loop).
It is also illegal to branch from an IF clause to another (that is,being inside an IF a label cannot move the execution inside the (IFs) ELSE's clause.
And finally it is illegal to branch from an exception handler back into the current block.
A label inside other Control statements can also be used :
BEGIN
<<L_Outer>>
FOR v_outerindex IN 1..50 LOOP
.....
<<L_Inner>>
FOR v_innerindex IN 1..50 LOOP
.....
IF v_outerindex > 40 THEN
EXIT L_outer; -- Exits both loops
END IF;
END LOOP L_inner;
END LOOP L_outer;
SIDENOTE: NULL statement.In some cases within a control structure there may be a need to explicitly state that no action be taken, this is what the statement NULL does:
DECLARE
v_TempVar NUMBER:= 7;
BEGIN
IF v_number < 5 THEN
INSERT INTO temp_table (char_col)
VALUES ( 'Very Small');
ELSIF v_Tempvar < 10 THEN
INSERT INTO temp_table (char_col)
VALUES ( ' OK ');
ELSE
NULL;
END IF;
END;
PL/SQL records are similar to C structures. A record provides a way to deal with separate but related variables. The benefit of records is that all the variables can be manipulated as a unit.
DECLARE TYPE t_StudentRecord IS RECORD ( StudentID NUMBER(5), First_name VARCHAR2(40), Last_name VARCHAR2(40)); /* Now you can declare a variable of this type */ v_Studentinfo t_StudentRecord; ............
A record can also have default values assigned to it.
DECLARE TYPE t_automoviles IS RECORD ( Transmision varchar2(15) :='Automatica', Descripcion varchar2(100), Modelo date ); v_propietario t_automoviles;
In order to assign values to a record (based on the declaration above):
BEGIN v_propietario.Modelo := sysdate; v_propietario.Descrpcion := 'Un volkswagen';
Similar to the %TYPE attribute a Record can be created with a reference to another table, thus the variables of the record will be composed of the columns of the refered table. This is done directly in the final declaration.
v_relatives family_members%ROWTYPE
The variable v_relatives will be a RECORD with the variables of the columns in family_members.
PL/SQL Tables are similar to arrays in C. A PL/SQL is similar to a database table that contains a KEY and VALUE column. The type of key in a PL/SQL table is a BINARY INTEGER, and the type of value is whatever it was defined to be at the initial declaration.
DECLARE TYPE t_charactername IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; v_character t_charactername;
The %TYPE attribute can also be used:
DECLARE TYPE t_tablename IS TABLE OF students.last_name%TYPE INDEX BY BINARY_INTEGER; v_table t_tablename
In order to assign values to Table it can be done as (based on the previous example):
BEGIN v_table(12) := 'Rubio'; v_table(-31) := 'Veytia'; END;
The things to notice on PL/SQL tables are:
BEGIN
TYPE t_StudentTable IS TABLE OF students%ROWTYPE
INDEX BY BINARY INTEGER;
v_Students t_StudentTable;
BEGIN
SELECT *
INTO v_Students(10001)
FROM students
WHERE id = 10001;
END;
In order to refer to the fields with the table of records use:
table(index).field v_students(10001).first_name := 'Daniel'
See previous example
A Table also has various attributes that can be used in the PL/SQL block:
(The following examples are based on the existence of:
TYPE t_NumberTable IS TABLE OF NUMBER
INDEX BY BINARY INTEGER;
v_Numbers t_NumberTable
)
v_total:v_Numbers.COUNT
v_Numbers.DELETE(-5); -- Deletes rows with index 5
v_Number.DELETE(7-12); -- Deletes rows with index 7 through 12
v_Number.DELETE; --Deletes all rows in the PL/SQL table.
In order to process an SQL statement,Oracle allocates an area of memory known as the context area. This area contains information necessary to complete the processing, including the number of rows processed by a statement, a pointer to the parsed representation of the statement, and in the case of query an active set , which is the set of rows returned by the query. A cursor is a handle,or pointer to the context area.Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed.
An explicit cursor is when a cursor name is explictitly assigned to a select statement via the CURSOR..IS. An implicit cursor is used for all other SQL statements.
Declaration of an explcit cursor:
DECLARE
v_StudentID students.id%TYPE;
v_FirtsName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
v_Major students.major%TYPE := 'Computer Science';
CURSOR c_Students IS
SELECT id,first_name, last_name
FROM students
WHERE major = v_Major;
BEGIN
OPEN c_Students
LOOP
FETCH c_Students INTO v_StudentID, v_FirstName,v_LastName;
EXIT WHEN c_Students%NOTFOUND;
END LOOP;
CLOSE c_Students;
END;
Just like PL/SQL table attributes , CURSORS also posses the following :
NOTE: IMPLICIT cursors are known as SQL cursor , thus if you wish to use the attributes from an implict cursor (which in other words is every SELECT statement that retruns more than one row) you use SQL%NOTFOUND, SQL%ROWCOUNT...etc.
DECLARE
CURSOR c_AllStudents IS
SELECT * from students;
v_StudentInfo c_AllStudents%ROWTYPE;
BEGIN
....
END;
DECLARE
CURSOR c_HistoryStudents IS
SELECT id, first_name, last_name
FROM students
WHERE major='History';
BEGIN
--Begin Loop the cursor is implicitly OPEN
FOR v_StudentData in c_HistoryStudents LOOP
--An implicit fecth
INSERT INTO registered_students (student_id, department,course)
VALUES ( v_StudentData.ID,'HIS',301);
INSERT INTO temp_table (num_col,char_col)
VALUES (v_StudentData.ID,
v_StudentData.first_name || ' '|| v_StudentData.last_name);
END LOOP;
COMMIT;
END;
CURSOR c_students (p_Department classes.department%TYPE
p_Course classes.department%TYPE ) IS
SELECT * FROM classes
WHERE department = p_Department
AND course = p_Course;
To call the cursor:
OPEN c_students('CS',101);
The syntax for this parameter in the SELECT statement is:
SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT]
where column_reference is a column in the table against which the query is performed. A list of columns can also be used.
DECLARE
CURSOR c_AllStudents IS
SELECT *
FROM students
FOR UPDATE OF first_name, last_name;
Or the cursor can select every column by not specifing a range
DECLARE
CURSOR c_AllStudents IS
SELECT *
FROM students
FOR UPDATE;
Normally a SELECT statement will not take any locks on the rows being accessed.This allows other sessions connected to the database to change the data being selected. At OPEN time, when the active set is determined, Oracle takes a snapshot of the table. Any changes that have been commited prior to this point are reflected in the acitve set. Any changes made after this point, even if they are commited, are not reflected unless the cursor is reopened, which will evaluate the active set again. However,if the FOR UPDATE clause is present, exclusive row locks are taken on the rows in the active set before the OPEN returns ( the cursor is CLOSED ). These locks prevent other sessions from changing the rows in the active set until the transaction is commited.
If another session already has locks on the rows in the active set, then the SELECT FOR UPDATE will hang until the other session releases the lock. To handle this situation the parameter NOWAIT is available, which in case the rows are locked,OPEN will return the error ORA-54 resource busy and acquire with NOWAIT specified.
When a cursor is declared with a FOR UPDATE, the WHERE CURRENT OF clause can be used in an UPDATE or DELETE statement. The syntax is :
WHERE CURRENT OF cursor
where cursor is the name of a cursor that has been declared with a FOR UPDATE clause. The WHERE CURRENT OF evaluates to the row that was just retrieved by the cursor.
DECLARE
v_NumCredits classes.num_credits%TYPE;
CURSOR c_RegisteredStudents IS
SELECT *
FROM students
WHERE ID in ( SELECT student_id
FROM registered_students
WHERE department = 'HIS'
AND course=101)
FOR UPDATE OF current_credits;
BEGIN
FOR v_StudentInfo IN c_RegisteredStudents LOOP
SELECT num_credits
INTO v_NumCredits
FROM classes
WHERE department='HIS'
AND COURSE= 101;
UPDATE students
SET current_credits = current_credits + v_NumCredits
WHERE CURRENT OF c_RegisteredStudents;
END LOOP;
COMMIT;
END;
Fetching Across COMMITS: Note that the COMMIT statement is done after the fetch loop is complete. This is done because COMMIT will release any locks held by the session. Since the FOR UPDATE clause acquires locks, these will be released by the COMMIT. When this happens, the cursor is invalidated. Any subsequent fetches will return the Oracle error: ORA-1002 : fetch out of sequenece . Thus if there is a COMMIT indside a SELECT FOR UPDATE fetch loop, any fetches done after the COMMIT will fail. So it is not advisable to use a COMMIT inside the LOOP. If the cursor is not defined with a SELECT FOR UPDATE then there is no problem.
The other option available for updating a table without the use of FOR UPDATE and the WHERE CURRENT OF, is to use the primary key of the table :
DECLARE
v_NumCredits classes.num_credits%TYPE;
CURSOR c_RegisteredStudents IS
SELECT *
FROM students
WHERE id IN ( SELECT student_id
FROM registered_students
WHERE department = 'HIS'
AND course= 101 );
BEGIN
FOR v_StudentInfo IN c_RegisteredStudents LOOP
SELECT num_credits
INTO v_NumCredits
FROM classes
WHERE department = 'HIS'
AND course = 101 ;
UPDATE students
SET current_credits = current_credits + v_NumCredits
WHERE id = v_StudentInfo.id;
COMMIT;
END LOOP;
END;
The drawback of using this method is that it DOES NOT acquire locks on the rows in the active set. As a result, it may not perform as expected if the other sessions are accessing the data concurrently.
See previous example
By using exceptions and exception handlers, you can make your PL/SQL programs robust and able to deal with both unexpected and expected errors during execution. The two type of errors that occur are : compile time which are reported by the PL/SQL compiler, and at run time which are handled by the PL/SQL run-time engine.
Exceptions are designed for run-time error handling, rather than compile time error handling. When an error occurs an exception is raised. When this happens control is passed to the exception handler, which is a separate section of the program. This separates the error handling from the rest of the program, which makes the logic of the program easier to understand. PL/SQL offers this capability in contrast to other programming languages which need the exception declaration EXACTLY after the statement that is being revised. The advantage to the exception handling being done in a separate block are:
e_TooManyStudents EXCPETION;
The previous declaration is an identifier that will be visible until the end of the block. Note that the scope of an exception is the same as the scope of any other variable or cursor in the same decalrative section.
User defined exceptions are raised explicitly via the RAISE statement, while predefined excpetions are raised implicitly when their associated Oracle error occurs. Predefined excpetions have the option of also being raised with the RAISE statement if necessary.
DECLARE
e_TooManyStudents EXCEPTION;
v_CurrentStudents NUMBER(3);
v_MaxStudents NUMBER(3);
BEGIN
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM CLASSES
WHERE department ='HIS' AND course=101;
IF v_CurrentStudents > v_MaxStudents THEN
RAISE e_TooManyStudents;
END IF;
END;
When an exception is raised, control immediatly passes to the exception section of the block. If there is no exception section, the exception is propagated to the enclosing block. Once control passes to the exception handler , there is NO WAY to return to the executable section of the block.
It is good programming practice to avoid unhandled exceptions. This can be done via an OTHERS handler at the TOPMOST level of your program. This handler may simply log the error and where it ocurred. This way, you ensure that no error will go undetected. Example:
DECLARE
v_ErrorNumber NUMBER -- Variable to hold the error number
v_ErrorText VARCHAR2(200) -- Variable to hold the error message text
BEGIN
/* Normal PL/SQL Processing */
---
EXCEPTION
WHEN OTHERS THEN
v_ErrorNumber := SQLCODE;
v_ErrorText := SUBSTR(SQLERRM, 1, 200);
INSERT INTO log_table (code, message, info) VALUES
(v_ErrorNumber,v_ErrorText, 'Oracle error occurred at ' ||
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
The exception section consists of handlers for all the exceptions. An exception handler contains the code that is executed when the error is associated with the excpetion occurs, and the exception is raised.
The syntax for the exception section is as follows:
EXCEPTION WHEN exception_name THEN sequence_of_statements; WHEN exception_name THEN sequence_of_statements_2; WHEN OTHERS THEN sequence_of_statements_3; END;
An example would be:
DECLARE
e_TooManyStudents EXCEPTION;
v_CurrentStudents NUMBER(3);
v_MaxStudents NUMBER(3);
BEGIN
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = 'HIS' AND course = 101;
IF v_CurrentStudents = v_MaxStudents THEN
RAISE e_TooManyStudents;
END IF;
EXCEPTION
WHEN e_TooManyStudents THEN
INSERT INTO log_table (info) values
('History 101 has' || v_CurrentStudents || 'students: max allowed is'
|| v_MaxStudents);
WHEN OTHERS THEN
INSERT INTO log_table (info) values ('Another error occurred');
END;
PL/SQL provides two built-in functions to identify which type of error is the exception that occrued , this is specially useful in the WHEN OTHERS clause in the exception block. The funcitions are SQLCODE and SQLERRM.
It is necessary to assign the SQLCODE / SQLERRM to local variables prior to any other use of them because these functions are procedural and they cannot be used directly inside the SQL statement.
SQLERRM can also be called directly, the argument provided to SQLERRM must be a negative number. If SQLERRM is called with any positive number other than +100, the message User-defined Exceptionis returned SQLERRM(100) returns ORA-1403: no data found.
This is done via the WHEN OTHERS or WHEN NO_DATA_FOUND clause in the exception declarations of a PL/SQL block. In order to recognize in which part of the blocks or block the unanticipated error ocurred in , 2 methods are recommended for this problem.
Increment a Counter identifying the SQL statement
DECLARE
v_SelectCounter := 1; -- Variable to hold the select statement number
BEGIN
SELECT ...
v_SelectCounter :=2;
SELECT ...
v_SelectCounter :=3;
SELECT ...
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO log_table (info) VALUES ('No data found in select' || v_SelectCounter);
END;
The second method is to put each statement into its own sub-block:
BEGIN BEGIN SELECT ... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 1'); END; BEGIN SELECT... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 2'); END; BEGIN SELECT... EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO log_table(info) VALUE ('No data found in select 3'); END; END;Exceptions are scoped just like variables. If a user-defined is propagated out of scope, it can no longer be referenced by name.
In general, if a user-defined error is to be propageted out of a block, it is best to define the exception in a package so that it will still be available outside the block, or to use RAISE_APPLICATION_ERROR instead. An example without using a package:
DECLARE
...
...
BEGIN
...
DECLARE
e_UserDefinedException EXCEPTION;
BEGIN
RAISE e_UserDefinedException;
END;
EXCEPTION
/* e_UserDefined_Exception is out of scope here -can only be handled
by
an OTHERS handler */
WHEN OTHERS THEN
/* Handle Error */
END;
Using a package :
CREATE OR REPLACE PACKAGE GLOBALS
/* This package contains global declarations. Objects declared here
will be visible via qualafied references for any other blocks or
procedures. Note that this package does not have a package body */
/* A user defined exception */
e_UserDefinedException EXCEPTION;
END Globals;
Given the package Globals the preceding example can be written as:
DECLARE
...
BEGIN
...
BEGIN
/* Note that we must qualify e_UserDefinedPackages with the package
name */
RAISE Globals.e_UserDefinedException;
END;
EXCEPTION
/* Since e_UserDefinedException is still visible, we can handle it
explicitly */
WHEN Globals.e_UserDefinedException THEN
/* Handle Error */
END;
EXCEPTION PROPAGATION
Exceptions Raised in the Executable Section
DECLARE
A EXCEPTION;
BEGIN
BEGIN
RAISE A ; --Exception A is raised in the sub-block
EXCEPTION
WHEN A THEN -- A is also handled in the sub-block
----
END;
-- Control Resumes HERE
END;
DECLARE
A EXCEPTION;
B EXCEPTION;
BEGIN
BEGIN
RAISE B ; --Exception B is raised in the sub-block
EXCEPTION
WHEN A THEN -- No handler for B in the sub-block
----
END;
EXECPTION
WHEN B THEN -- Exception B is propagated to enclosing block and handled there
----
END;
--Control then passes out the enclosing block, which completes succesfully
DECLARE
A EXCEPTION;
B EXCEPTION;
C EXCEPTION;
BEGIN
BEGIN
RAISE C ; --Exception C is raised in the sub-block
EXCEPTION
WHEN A THEN -- No handler for C in the sub-block
----
END;
EXECPTION
WHEN B THEN -- Exception C is propagated to enclosing block, but there is no handler for it there either
----
END;
--The exception is then propagted out the calling environment. The enclosing block completes with an unhandled exception
DECLARE
v_Number NUMBER(3) := 'ABC'; -- Illegal assignment raises VALUE_ERROR
BEGIN
----
EXCEPTION
WHEN OTHERS THEN -- Even though there is a WHEN OTHERS handler, it is not executed
----
END;
--The Block completes unsuccesfully with the VALUE_ERROR exception
BEGIN
DECLARE
v_Number NUMBER(3) := 'ABC'; -- Illegal assignment raises
VALUE_ERROR
BEGIN
----
EXCEPTION
WHEN OTHERS THEN -- Even though there is a WHEN OTHERS handler, it is not executed
----
END;
EXCEPTION
WHEN OTHERS THEN --The exception handler in the outer block handles the exceptio
END;
-- Control passes out the enclosing block, which completes successfully.
DECLARE
A EXCEPTION;
B EXCEPTION;
BEGIN
RAISE A; -- Exception A is raised
EXCEPTION
WHEN A THEN -- Exception is handled, and B is raised in A's handler
RAISE B;
WHEN B THEN -- Even though there is a handler for B here, it is not executed.
---- The exception is propagated out of the block
END;
-- The block completes unsuccessfully with unhandled exception B.
BEGIN
DECLARE
A EXCEPTION;
B EXCEPTION;
BEGIN
RAISE A; -- Exception A is raised
EXCPETION
WHEN A THEN -- Exception is handled, and B is raised in A's handler
RAISE B;
WHEN B THEN -- Even though there is a handler for B here, it is not executed.
---- The exception is propagated out of the block
EXCEPTION
WHEN B THEN -- Exception B is handled in the outer block
----
END;
-- The block completes successfully and control passes to the calling environment.
DECLARE
A EXCEPTION;
BEGIN
RAISE A; -- Exception A is raised
EXCEPTION
WHEN A THEN -- Exception is handled, and the error is logged
INSERT INTO ------;
RAISE;-- The same excpetion is raised again
END;
-- The exception is propagated out of the block, which completes unsuccesfully with unhandled exception A.
You can associate a named exception with a Particular Oracle error. This gives you the ability to trap this error specifically, rather than via an OTHER handler. This is done via the EXCEPTION_INIT pragma. They are declared as:
PRAGMA EXCEPTION_INIT (exception_name, Oracle_error_number);
where exception_name is the name of an exception declared prior to the pragma, and Oracle_error_number is the desired error code to be associated with this named exception. This pragma must be in the declaritive section.
The following example will raise the e_MissingNull user-defined exception if the >"ORA-1400:mandatory NOT NULL column missing or NULL during insert" error is encountered at run time.
DECLARE
e_MissingNull EXCEPTION;
PRAGMA EXCPETION_INIT(e_MisingNull, -1400);
BEGIN
INSERT INTO students(id) VALUES (NULL);
EXCEPTION
WHEN e_MissingNull THEN
INSERT INTO log_table (info) VALUES ('ORA-1400 occurred');
END;
Only one user-defined exception can be associated with an Oracle error with each occurence of PRAGMA EXCEPTION_INIT. Inside the exception handler, SQLCODE and SQLERRM will return the code and message for the Oracle error which occurred, rather than the user define message.
Using RAISE_APPLICATION_ERRORYou can use the built-in function RAISE_APPLICATION_ERROR to create your own error messages, which can be more descriptive that named exceptions. User-defined errors are passed out of the block the same way as Oracle errors to the calling environment. The syntax is
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
where error_number is a parameter between -20,000 and -20,999 , error_message is the text associated with this error, and keep_errors is a boolean value. The boolean parameter, keep_errors is optional. If keep_errors is TRUE, the new error is added to the list of errors already raised (if one exists). If keep_errors FALSE, which is the default, the new error will replace the current list of errors. This declaration is done directly within the block like :
BEGIN
----
IF V_estudiantes + 1 > V_maximo_estudiantes THEN
RAISE_APPLICATION_ERROR (-20000, 'No es posible realizar la transaccion');
END IF;
---
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, 'No existen los datos');
END;