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
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
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 will be a RECORD with the variables of the columns in
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_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:
The syntax for this parameter in the SELECT statement is:
SELECT ... FROM ... FOR UPDATE [OF column_reference] [NOWAIT]
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
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:
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
It is necessary to assign the
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;
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);
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_ERROR
You 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
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;