1.DEFINE EXCEPTION,ADVANTAGES OF EXCEPTION.
In PL/SQL, an error condition is called an exception. Exceptions can be internally defined (by the runtime system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.
When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.
To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.
2.TYPE OF EXCEPTION.
1.Pre - Defined Exception
An internal exception is raised automatically if your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
You can use the pragma EXCEPTION_INIT to associate exception names with other Oracle error codes that you can anticipate. To handle unexpected Oracle errors, you can use the OTHERS handler. Within this handler, you can call the functions SQLCODE and SQLERRM to return the Oracle error code and message text. Once you know the error code, you can use it with pragma EXCEPTION_INIT and write a handler specifically for that error.
PL/SQL declares predefined exceptions globally in package STANDARD. You need not declare them yourself. You can write handlers for predefined exceptions using the names in the following table:
Exception ORA Error SQLCODE Raise When ...
ACCESS_INTO_NULL 06530 -6530 A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND 06592 -6592 None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN 06511 -6511 A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX 00001 -1 A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR 01001 -1001 A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER 01722 -1722 n a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED 01017 -1017 A program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUND 01403 +100 A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.
Because this exception is used internally by some SQL functions to signal completion, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON 01012 -1012 A program issues a database call without being connected to Oracle.
PROGRAM_ERROR 06501 -6501 PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL 30625 -30625 A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
STORAGE_ERROR 06500 -6500 PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT 06533 -6533 A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT 06532 -6532 A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID 01410 -1410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE 00051 -51 A time out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS 01422 -1422 A SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE 01476 -1476 A program attempts to divide a number by zero.
b.User - Defined Exception
PL/SQL lets you define exceptions of your own. Unlike predefined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements.
Declaring PL/SQL Exceptions
Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the following example, you declare an exception named past_due:
DECLARE
past_due EXCEPTION;
Exception and variable declarations are similar. But remember, an exception is an error condition, not a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL statements. However, the same scope rules apply to variables and exceptions.
3.EXPLAIN FOLLOWING TERMS
a. Init_Pragma.
Associating a PL/SQL Exception with a Number: 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. A pragma is a compiler directive that is processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the 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;
B.When Others.
This keyword stands for all the exceptions not explicitly named in the exception-handling part of the block. The use of OTHERS is optional and is allowed only as the last exception handler. You cannot include OTHERS in a list of exceptions following the keyword WHEN.
C.Raise Application Error.
Defining Your Own Error Messages: Procedure 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.
An application can call raise_application_error only from an executing stored subprogram (or method). When called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. The error number and message can be trapped like any Oracle error.
D.SQL Code & SQL Errm.
Retrieving the Error Code and Error Message: SQLCODE and SQLERRM
In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to get the associated error message. For internal exceptions, SQLCODE returns the number of the Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. SQLERRM returns the corresponding error message. The message begins with the Oracle error code.
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. The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message: ORA-0000: normal, successful completion.
You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. Make sure you pass negative error numbers to SQLERRM. In the following example, you pass positive numbers and so get unwanted results:
DECLARE
err_msg VARCHAR2(100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(err_num); -- wrong; should be -err_num
INSERT INTO errors VALUES (err_msg);
END LOOP;
END;
Passing a positive number to SQLERRM always returns the message user-defined exception unless you pass +100, in which case SQLERRM returns the message no data found. Passing a zero to SQLERRM always returns the message normal, successful completion.
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;
The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. The functions SQLCODE and SQLERRM are especially useful in the OTHERS exception handler because they tell you which internal exception was raised.
Note: When using pragma RESTRICT_REFERENCES to assert the purity of a stored function, you cannot specify the constraints WNPS and RNPS if the function calls SQLCODE or SQLERRM.
E.Dbms_Output Vs Raise Application Error.
The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers.
The PUT and PUT_LINE procedures in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE procedure.
If you do not call GET_LINE, or if you do not display the messages on your screen in SQL*Plus or Enterprise Manager, then the buffered messages are ignored. The DBMS_OUTPUT package is especially useful for displaying PL/SQL debugging information.
Note:
Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.
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}]);
CURSORS
1.DEFINE CURSORS,ADVANTAGES OF CURSORS.
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.
A cursor is a handle to a specific private SQL area. In other words, a cursor can be thought of as a name for a specific private SQL area.
PL/SQL uses two types of cursors: implicit and explicit. PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including queries that return only one row. However, for queries that return more than one row, you must declare an explicit cursor, use a cursor FOR loop, or use the BULK COLLECT clause.
Explicit
The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover, you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved, or you can use the BULK COLLECT clause to fetch all rows at once. When the last row has been processed, you release the cursor with the CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors.
Implicit
Oracle implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. You can refer to the most recent implicit cursor as the SQL cursor. Although you cannot use the OPEN, FETCH, and CLOSE statements to control the SQL cursor, you can use cursor attributes to get information about the most recently executed SQL statement.
2.TYPE OF ATTRIBUTES.
Explicit Cursor Attribute
Using Cursor Attributes
Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement. You can use cursor attributes in procedural statements but not in SQL statements.
Overview of Explicit Cursor Attributes
Explicit cursor attributes return information about the execution of a multi-row query. When an explicit cursor or a cursor variable is opened, the rows that satisfy the associated query are identified and form the result set. Rows are fetched from the result set.
%FOUND Attribute: Has a Row Been Fetched?
After a cursor or cursor variable is opened but before the first fetch, %FOUND yields NULL. Thereafter, it yields TRUE if the last fetch returned a row, or FALSE if the last fetch failed to return a row. In the following example, you use %FOUND to select an action:
LOOP
FETCH c1 INTO my_ename, my_sal, my_hiredate;
IF c1%FOUND THEN -- fetch succeeded
...
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
END LOOP;
If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.
%ISOPEN Attribute: Is the Cursor Open?
%ISOPEN yields TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN yields FALSE. In the following example, you use %ISOPEN to select an action:
IF c1%ISOPEN THEN -- cursor is open
...
ELSE -- cursor is closed, so open it
OPEN c1;
END IF;
%NOTFOUND Attribute: Has a Fetch Failed?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOTFOUND to exit a loop when FETCH fails to return a row:
LOOP
FETCH c1 INTO my_ename, my_sal, my_hiredate;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
Before the first fetch, %NOTFOUND evaluates to NULL. So, if FETCH never executes successfully, the loop is never exited. That is because the EXIT WHEN statement executes only if its WHEN condition is true. To be safe, you might want to use the following EXIT statement instead:
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
If a cursor or cursor variable is not open, referencing it with %NOTFOUND raises INVALID_CURSOR.
%ROWCOUNT Attribute: How Many Rows Fetched So Far?
When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row. In the next example, you use %ROWCOUNT to take action if more than ten rows have been fetched:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
If a cursor or cursor variable is not open, referencing it with %ROWCOUNT raises INVALID_CURSOR.
Table 6-1 shows what each cursor attribute yields before and after you execute an OPEN, FETCH, or CLOSE statement.
Table 6-1 Cursor Attribute Values
%FOUND %ISOPEN %NOTFOUND %ROWCOUNT
OPEN before exception FALSE exception exception
after NULL TRUE NULL 0
First FETCH before NULL TRUE NULL 0
after TRUE TRUE FALSE 1
Next FETCH(es) before TRUE TRUE FALSE 1
after TRUE TRUE FALSE data dependent
Last FETCH before TRUE TRUE FALSE data dependent
after FALSE TRUE TRUE data dependent
CLOSE before FALSE TRUE TRUE data dependent
after exception FALSE exception exception
Notes:
1. Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR.
2. After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.
Some Examples of Cursor Attributes
Suppose you have a table named data_table that holds data collected from laboratory experiments, and you want to analyze the data from experiment 1. In the following example, you compute the results and store them in a database table named temp:
DECLARE
num1 data_table.n1%TYPE; -- Declare variables
num2 data_table.n2%TYPE; -- having same types as
num3 data_table.n3%TYPE; -- database columns
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO num1, num2, num3;
EXIT WHEN c1%NOTFOUND; -- TRUE when FETCH finds no more rows
result := num2/(num1 + num3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
CLOSE c1;
COMMIT;
END;
In the next example, you check all storage bins that contain part number 5469, withdrawing their contents until you accumulate 1000 units:
-- available online in file 'examp6'
DECLARE
CURSOR bin_cur(part_number NUMBER) IS
SELECT amt_in_bin FROM bins WHERE part_num = part_number AND amt_in_bin > 0
ORDER BY bin_num FOR UPDATE OF amt_in_bin;
bin_amt bins.amt_in_bin%TYPE;
total_so_far NUMBER(5) := 0;
amount_needed CONSTANT NUMBER(5) := 1000;
bins_looked_at NUMBER(3) := 0;
BEGIN
OPEN bin_cur(5469);
WHILE total_so_far < amount_needed LOOP
FETCH bin_cur INTO bin_amt;
EXIT WHEN bin_cur%NOTFOUND;
-- if we exit, there's not enough to fill the order
bins_looked_at := bins_looked_at + 1;
IF total_so_far + bin_amt < amount_needed THEN
UPDATE bins SET amt_in_bin = 0
WHERE CURRENT OF bin_cur;
-- take everything in the bin
total_so_far := total_so_far + bin_amt;
ELSE -- we finally have enough
UPDATE bins SET amt_in_bin = amt_in_bin
- (amount_needed - total_so_far)
WHERE CURRENT OF bin_cur;
total_so_far := amount_needed;
END IF;
END LOOP;
CLOSE bin_cur;
INSERT INTO temp
VALUES (NULL, bins_looked_at, '<- bins looked at');
COMMIT;
END;
Implicit Cursor Attributes
%FOUND Attribute: Has a DML Statement Changed Rows?
Until a SQL data manipulation statement is executed, %FOUND yields NULL. Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows, or a SELECT INTO statement returned one or more rows. Otherwise, %FOUND yields FALSE. In the following example, you use %FOUND to insert a row if a delete succeeds:
DELETE FROM emp WHERE empno = my_empno;
IF SQL%FOUND THEN -- delete succeeded
INSERT INTO new_emp VALUES (my_empno, my_ename, ...);
%ISOPEN Attribute: Always FALSE for Implicit Cursors
Oracle closes the SQL cursor automatically after executing its associated SQL statement. As a result, %ISOPEN always yields FALSE.
%NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, %NOTFOUND yields FALSE.
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In the following example, you use %ROWCOUNT to take action if more than ten rows have been deleted:
DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN -- more than 10 rows were deleted
...
END IF;
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
Guidelines for Using Implicit Cursor Attributes
The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope (for example, in a sub-block). So, if you want to save an attribute value for later use, assign it to a Boolean variable immediately. In the following example, relying on the IF condition is dangerous because the procedure check_status might have changed the value of %NOTFOUND:
BEGIN
...
UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
check_status(part_id); -- procedure call
IF SQL%NOTFOUND THEN -- dangerous!
...
END;
END;
You can improve the code as follows:
BEGIN
...
UPDATE parts SET quantity = quantity - 1 WHERE partno = part_id;
sql_notfound := SQL%NOTFOUND; -- assign value to Boolean variable
check_status(part_id);
IF sql_notfound THEN ...
END;
If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND whether you check %NOTFOUND on the next line or not. Consider the following example:
BEGIN
...
SELECT sal INTO my_sal FROM emp WHERE empno = my_empno;
-- might raise NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- condition tested only when false
... -- this action is never taken
END IF;
The check is useless because the IF condition is tested only when %NOTFOUND is false. When PL/SQL raises NO_DATA_FOUND, normal execution stops and control transfers to the exception-handling part of the block.
However, a SELECT INTO statement that calls a SQL aggregate function never raises NO_DATA_FOUND because aggregate functions always return a value or a null. In such cases, %NOTFOUND yields FALSE, as the following example shows:
BEGIN
...
SELECT MAX(sal) INTO my_sal FROM emp WHERE deptno = my_deptno;
-- never raises NO_DATA_FOUND
IF SQL%NOTFOUND THEN -- always tested but never true
... -- this action is never taken
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN ... -- never invoked
3.NO_DATA_FOUND VS %NOTFOUND.
A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row. In the following example, you use %NOTFOUND to exit a loop when FETCH fails to return a row:
4.CURSOR VARIABLE AND IT TYPES.
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. This gives you more flexibility.
Also, you can assign new values to a cursor variable and pass it as a parameter to local and stored subprograms. This gives you an easy way to centralize data retrieval.
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.
The Oracle server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server through remote procedure calls (RPCs).
What Are Cursor Variables?
Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.
Why Use Cursor Variables?
Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle server can all refer to the same work area.
A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.
If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side. Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.
Defining REF CURSOR Types
To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax
TYPE ref_type_name IS REF CURSOR [RETURN return_type];
where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:
DECLARE
TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; -- strong
TYPE GenericCurTyp IS REF CURSOR; -- weak
Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.
Cursor Variables As Parameters
You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:
DECLARE
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...
6.CURSOR WITH FOR LOOP
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.
-- available online in file 'examp7'
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END;
When the cursor FOR loop is entered, the cursor name cannot belong to a cursor already opened by an OPEN statement or enclosing cursor FOR loop. Before each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The record is defined only inside the loop. You cannot refer to its fields outside the loop.
The sequence of statements inside the loop is executed once for each row that satisfies the query associated with the cursor. When you leave the loop, the cursor is closed automatically--even if you use an EXIT or GOTO statement to leave the loop prematurely or an exception is raised inside the loop.
7.USING SUBQUERIES INSTEAD OF EXPLICIT CURSORS
You need not declare a cursor because PL/SQL lets you substitute a subquery. The following cursor FOR loop calculates a bonus, then inserts the result into a database table:
DECLARE
bonus REAL;
BEGIN
FOR emp_rec IN (SELECT empno, sal, comm FROM emp) LOOP
bonus := (emp_rec.sal * 0.05) + (emp_rec.comm * 0.25);
INSERT INTO bonuses VALUES (emp_rec.empno, bonus);
END LOOP;
COMMIT;
END;
8.USING CURSOR SUBQUERIES
You can use cursor subqueries, also know as cursor expressions, to pass sets of rows as parameters to functions. For example, this statement passes a parameter to the StockPivot function consisting of a REF CURSOR that represents the rows returned by the cursor subquery:
SELECT * FROM TABLE(StockPivot(CURSOR(SELECT * FROM StockTable)));
9. FOR UPDATE , CURRENT OF
for_update_clause
The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement (not in subqueries).
Restrictions on the FOR UPDATE Clause
• You cannot specify this clause with the following other constructs: the DISTINCT operator, CURSOR expression, set operators, group_by_clause, or aggregate functions.
• The tables locked by this clause must all be located on the same database, and on the same database as any LONG columns and sequences referenced in the same statement.
OF ... column
Use the OF ... column clause to lock the select rows only for a particular table or view in a join. The columns in the OF clause only indicate which table or view rows are locked. The specific columns that you specify are not significant. However, you must specify an actual column name, not a column alias. If you omit this clause, then Oracle locks the selected rows from all the tables in the query.
NOWAIT | WAIT
The NOWAIT and WAIT clauses let you tell Oracle how to proceed if the SELECT statement attempts to lock a row that is locked by another user.
NOWAIT
Specify NOWAIT to return control to you immediately if a lock exists.
WAIT
Specify WAIT to instruct Oracle to wait integer seconds for the row to become available, and then return control to you.
If you specify neither WAIT nor NOWAIT, then Oracle waits until the row is available and then returns the results of the SELECT statement.
Using the FOR UPDATE Clause: Examples
The following statement locks rows in the employees table with purchasing clerks located in Oxford (location_id 2500) and locks rows in the departments table with departments in Oxford that have purchasing clerks:
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e, departments d
WHERE job_id = 'SA_REP'
AND e.department_id = d.department_id
AND location_id = 2500
FOR UPDATE;
The following statement locks only those rows in the employees table with purchasing clerks located in Oxford (location_id 2500). No rows are locked in the departments table:
SELECT e.employee_id, e.salary, e.commission_pct
FROM employees e, departments d
WHERE job_id = 'SA_REP'
AND e.department_id = d.department_id
AND location_id = 2500
FOR UPDATE OF e.salary;
Using FOR UPDATE
When you declare a cursor that will be referenced in the CURRENT OF clause of an UPDATE or DELETE statement, you must use the FOR UPDATE clause to acquire exclusive row locks. An example follows:
DECLARE
CURSOR c1 IS SELECT empno, sal FROM emp
WHERE job = 'SALESMAN' AND comm > sal
FOR UPDATE NOWAIT;
The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.
The optional keyword NOWAIT tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the keyword NOWAIT, Oracle waits until the rows are available.
All rows are locked when you open the cursor, not as they are fetched. The rows are unlocked when you commit or roll back the transaction. So, you cannot fetch from a FOR UPDATE cursor after a commit. (For a workaround, see "Fetching Across Commits".)
When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE OF clause refers to a column in that table. For example, the following query locks rows in the emp table but not in the dept table:
DECLARE
CURSOR c1 IS SELECT ename, dname FROM emp, dept
WHERE emp.deptno = dept.deptno AND job = 'MANAGER'
FOR UPDATE OF sal;
As the next example shows, you use the CURRENT OF clause in an UPDATE or DELETE statement to refer to the latest row fetched from a cursor:
DECLARE
CURSOR c1 IS SELECT empno, job, sal FROM emp FOR UPDATE;
...
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO ...
...
UPDATE emp SET sal = new_sal WHERE CURRENT OF c1;
END LOOP;
PROCEDURE
1.DEFINE PROCEDURES,ADVANTAGES OF PROCEDURES.
A procedure is a group of PL/SQL statements that you can call by name. A call specification ("call spec") declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call spec tells Oracle which Java method to invoke when a call is made. It also tells Oracle what type conversions to make for the arguments and return value.
A procedure is a subprogram that can take parameters and be invoked. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional. Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name.
The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.
Stored procedures offer advantages in the areas of development, integrity, security, performance, and memory allocation.
a. Subprograms provide extensibility; that is, they let you tailor the PL/SQL language to suit your needs. For example, if you need a procedure that creates new departments, you can easily write one, as follows:
PROCEDURE create_dept (new_dname VARCHAR2, new_loc VARCHAR2) IS
BEGIN
INSERT INTO dept VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;
b. Subprograms also provide modularity; that is, they let you break a program down into manageable, well-defined modules. This supports top-down design and the stepwise refinement approach to problem solving.
c. Subprograms promote reusability and maintainability. Once validated, a subprogram can be used with confidence in any number of applications. If its definition changes, only the subprogram is affected. This simplifies maintenance.
d. Subprograms aid abstraction, the mental process of deriving a universal from particulars. To use subprograms, you must know what they do, not how they work. Therefore, you can design applications from the top down without worrying about implementation details. Dummy subprograms (stubs) allow you to defer the definition of procedures and functions until you test and debug the main program.
2.PARAMETER MODE.
Specifying Subprogram Parameter Modes
You use parameter modes to define the behavior of formal parameters. The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram. However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments (actual parameters) and return a single value. To have a function return multiple values is a poor programming practice. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
Using the IN Mode
An IN parameter lets you pass values to the subprogram being called. Inside the subprogram, an IN parameter acts like a constant. Therefore, it cannot be assigned a value. For example, the following assignment statement causes a compilation error:
PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS
minimum_purchase CONSTANT REAL DEFAULT 10.0;
service_charge CONSTANT REAL DEFAULT 0.50;
BEGIN
IF amount < minimum_purchase THEN
amount := amount + service_charge; -- causes compilation error
END IF;
...
END debit_account;
The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression. Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values.
Using the OUT Mode
An OUT parameter lets you return values to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. That means you can use an OUT formal parameter as if it were a local variable. You can change its value or reference the value in any way, as the following example shows:
PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS
hire_date DATE;
bonus_missing EXCEPTION;
BEGIN
SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp
WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE bonus_missing;
END IF;
IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN
bonus := bonus + 500;
END IF;
...
EXCEPTION
WHEN bonus_missing THEN
...
END calc_bonus;
The actual parameter that corresponds to an OUT formal parameter must be a variable; it cannot be a constant or an expression. For example, the following procedure call is illegal:
calc_bonus(7499, salary + commission); -- causes compilation error
An OUT actual parameter can have a value before the subprogram is called. However, when you call the subprogram, the value is lost unless you specify the compiler hint NOCOPY (see "Passing Large Data Structures with the NOCOPY Compiler Hint") or the subprogram exits with an unhandled exception.
Like variables, OUT formal parameters are initialized to NULL. So, the datatype of an OUT formal parameter cannot be a subtype defined as NOT NULL (that includes the built-in subtypes NATURALN and POSITIVEN). Otherwise, when you call the subprogram, PL/SQL raises VALUE_ERROR. An example follows:
DECLARE
SUBTYPE Counter IS INTEGER NOT NULL;
rows Counter := 0;
PROCEDURE count_emps (n OUT Counter) IS
BEGIN
SELECT COUNT(*) INTO n FROM emp;
END;
BEGIN
count_emps(rows); -- raises VALUE_ERROR
Before exiting a subprogram, explicitly assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
Using the IN OUT Mode
An IN OUT parameter lets you pass initial values to the subprogram being called and return updated values to the caller. Inside the subprogram, an IN OUT parameter acts like an initialized variable. Therefore, it can be assigned a value and its value can be assigned to another variable.
The actual parameter that corresponds to an IN OUT formal parameter must be a variable; it cannot be a constant or an expression.
If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.
Summary of Subprogram Parameter Modes
Table 8-1 summarizes all you need to know about the parameter modes.
Table 8-1 Parameter Modes
IN OUT IN OUT
the default must be specified must be specified
passes values to a subprogram returns values to the caller passes initial values to a subprogram and returns updated values to the caller
formal parameter acts like a constant formal parameter acts like a variable formal parameter acts like an initialized variable
formal parameter cannot be assigned a value formal parameter must be assigned a value formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression actual parameter must be a variable actual parameter must be a variable
actual parameter is passed by reference (a pointer to the value is passed in) actual parameter is passed by value (a copy of the value is passed out) unless NOCOPY is specified actual parameter is passed by value (a copy of the value is passed in and out) unless NOCOPY is specified
3.PROCEDURE VS AUNAMAYOUS BLOCK.
Stored Procedure
A stored procedure, function, or package is a PL/SQL program unit that:
• Has a name.
• Can take parameters, and can return values.
• Is stored in the data dictionary.
• Can be called by many users.
Because a procedure or function is stored in the database, it must be named. This distinguishes it from other stored procedures and makes it possible for applications to call it. Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier
Anonymous Blocks
An anonymous block is a PL/SQL program unit that has no name and it does not require the explicit presence of the BEGIN and END keywords to enclose the executable statements. An anonymous block consists of an optional declarative part, an executable part, and one or more optional exception handlers.
The declarative part declares PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as NO_DATA_FOUND or ZERO_DIVIDE) or as an exception that you define.
The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the Emp_tab table, using the DBMS_OUTPUT package:
DECLARE
Emp_name VARCHAR2(10);
Cursor c1 IS SELECT Ename FROM Emp_tab
WHERE Deptno = 20;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO Emp_name;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Emp_name);
END LOOP;
END;
4.AUTONOMUS TRANSACTIONS.
The AUTONOMOUS_TRANSACTION pragma instructs the PL/SQL compiler to mark a routine as autonomous (independent). An autonomous transaction is an independent transaction started by another transaction, the main transaction. Autonomous transactions let you suspend the main transaction, do SQL operations, commit or roll back those operations, then resume the main transaction
In this context, the term routine includes
• Top-level (not nested) anonymous PL/SQL blocks
• Local, standalone, and packaged functions and procedures
• Methods of a SQL object type
• Database triggers
You cannot use the pragma to mark all subprograms in a package (or all methods in an object type) as autonomous. Only individual routines can be marked autonomous. You can code the pragma anywhere in the declarative section of a routine. But, for readability, code the pragma at the top of the section.
Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. So, you can log events, increment retry counters, and so on, even if the main transaction rolls back.
Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK. Also, unlike regular triggers, autonomous triggers can execute DDL statements (such as CREATE and DROP) using native dynamic SQL.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default).
If you set the isolation level of the main transaction to SERIALIZABLE, as follows, changes made by its autonomous transactions are not visible to the main transaction when it resumes:
When in the main transaction, rolling back to a savepoint marked before you started an autonomous transaction does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. In that case, Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, the transaction is rolled back.
Examples
In the following example, you mark a packaged function as autonomous:
CREATE PACKAGE banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
CREATE PACKAGE BODY banking AS
...
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
PRAGMA AUTONOMOUS_TRANSACTION;
my_bal REAL;
BEGIN
...
END;
END banking;
In the example below, you mark a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements.
CREATE TRIGGER parts_trigger
BEFORE INSERT ON parts FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO parts_log VALUES(:new.pnum, :new.pname);
COMMIT; -- allowed only in autonomous triggers
END;
5.NO_COPY
Passing Large Data Structures with the NOCOPY Compiler Hint
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT parameter. When you call the subprogram, the IN parameter is passed by reference. That is, a pointer to the IN actual parameter is passed to the corresponding formal parameter. So, both parameters reference the same memory location, which holds the value of the actual parameter.
By default, the OUT and IN OUT parameters are passed by value. That is, the value of the IN OUT actual parameter is copied into the corresponding formal parameter. Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and instances of object types, all this copying slows down execution and uses up memory. To prevent that, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference.
In the following example, you ask the compiler to pass IN OUT parameter my_staff by reference instead of by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff by value despite your request. Usually, however, NOCOPY succeeds. So, it can benefit any PL/SQL application that passes around large data structures.
In the example below, 25000 records are loaded into a local nested table, which is passed to two local procedures that do nothing but execute NULL statements. However, a call to one procedure takes 21 seconds because of all the copying. With NOCOPY, a call to the other procedure takes much less than 1 second.
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 NUMBER(5);
5 t2 NUMBER(5);
6 t3 NUMBER(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
20 get_time(t3);
21 dbms_output.put_line('Call Duration (secs)');
22 dbms_output.put_line('--------------------');
23 dbms_output.put_line('Just IN OUT: ' || TO_CHAR(t2 - t1));
24 dbms_output.put_line('With NOCOPY: ' || TO_CHAR(t3 - t2));
25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0
The Trade-Off for Better Performance with NOCOPY
NOCOPY lets you trade well-defined exception semantics for better performance. Its use affects exception handling in the following ways:
• Because NOCOPY is a hint, not a directive, the compiler can pass NOCOPY parameters to a subprogram by value or by reference. So, if the subprogram exits with an unhandled exception, you cannot rely on the values of the NOCOPY actual parameters.
• By default, if a subprogram exits with an unhandled exception, the values assigned to its OUT and IN OUT formal parameters are not copied into the corresponding actual parameters, and changes appear to roll back. However, when you specify NOCOPY, assignments to the formal parameters immediately affect the actual parameters as well. So, if the subprogram exits with an unhandled exception, the (possibly unfinished) changes are not "rolled back."
• Currently, RPC protocol lets you pass parameters only by value. So, exception semantics can change silently when you partition applications. For example, if you move a local procedure with NOCOPY parameters to a remote site, those parameters will no longer be passed by reference.
FUNCTION
1.DEFINE FUNCTIONS,ADVANTAGES OF FUNCTIONS.
A function is a subprogram that can take parameters and be invoked. Generally, you use a function to compute a value. A function has two parts: the specification and the body. The specification (spec for short) begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional. Functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name.
The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part. The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the function. The executable part contains statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.
Inside a function, an IN parameter acts like a constant. So, you cannot assign it a value. An OUT parameter acts like a local variable. So, you can change its value and reference the value in any way. An IN OUT parameter acts like an initialized variable. So, you can assign it a value, which can be assigned to another variable. For summary information about the parameter modes, see Table 8-1.
Avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more parameters and return a single value. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.
Example
The following function returns the balance of a specified bank account:
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts WHERE acctno = acct_id;
RETURN acct_bal;
END balance;
A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause. You write (local) functions using the syntax:
[CREATE [OR REPLACE ] ]
FUNCTION function_name [ ( parameter [ , parameter ]... ) ] RETURN
datatype
[ AUTHID { DEFINER | CURRENT_USER } ]
[ PARALLEL_ENABLE
[ { [CLUSTER parameter BY (column_name [, column_name ]... ) ] |
[ORDER parameter BY (column_name [ , column_name ]... ) ] } ]
[ ( PARTITION parameter BY
{ [ {RANGE | HASH } (column_name [, column_name]...)] | ANY }
) ]
]
[DETERMINISTIC] [ PIPELINED [ USING implementation_type ] ]
[ AGGREGATE [UPDATE VALUE] [WITH EXTERNAL CONTEXT]
USING implementation_type ] {IS | AS}
[ PRAGMA AUTONOMOUS_TRANSACTION; ]
[ local declarations ]
BEGIN
executable statements
[ EXCEPTION
exception handlers ]
END [ name ];
The CREATE clause lets you create standalone functions, which are stored in an Oracle database. You can execute the CREATE FUNCTION statement interactively from SQL*Plus or from a program using native dynamic SQL.
2.PROCEDURE VS FUNCTIONS.
1.SQL STATEMENT
Function We Can Call From Sql Statements , Procedure Can ‘t
2.RETURN
In the Function reutrn is expression.
In the Procedure return is statement only.
3.PURPOSE
Function Used for Compute to the complex calculation.
Procedure Used for process to the sql and pl/sql statements.
3.RETURN TYPE , RETURN STATEMENT.
Using the RETURN Statement
The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution then resumes with the statement following the subprogram call. (Do not confuse the RETURN statement with the RETURN clause in a function spec, which specifies the datatype of the return value.)
A subprogram can contain several RETURN statements. The last lexical statement does not need to be a RETURN statement. Executing any RETURN statement completes the subprogram immediately. However, to have multiple exit points in a subprogram is a poor programming practice.
In procedures, a RETURN statement cannot return a value, and therefore cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.
However, in functions, a RETURN statement must contain an expression, which is evaluated when the RETURN statement is executed. The resulting value is assigned to the function identifier, which acts like a variable of the type specified in the RETURN clause. Observe how the function balance returns the balance of a specified bank account:
FUNCTION balance (acct_id INTEGER) RETURN REAL IS
acct_bal REAL;
BEGIN
SELECT bal INTO acct_bal FROM accts
WHERE acct_no = acct_id;
RETURN acct_bal;
END balance;
The following example shows that the expression in a function RETURN statement can be arbitrarily complex:
FUNCTION compound (
years NUMBER,
amount NUMBER,
rate NUMBER) RETURN NUMBER IS
BEGIN
RETURN amount * POWER((rate / 100) + 1, years);
END compound;
In a function, there must be at least one execution path that leads to a RETURN statement. Otherwise, you get a function returned without value error at run time.
RETURN Clause
For datatype, specify the datatype of the function's return value. Because every function must return a value, this clause is required. The return value can have any datatype supported by PL/SQL.
The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of the return value from the environment from which the function is called.
If the return type is SYS.AnyDataSet and you intend to use the function in the FROM clause of a query, then you must also specify the PIPELINED clause and define a describe method (ODCITableDescribe) as part of the implementation type of the function.
PACKAGE
1.DEFINE PACKAGES,ADVANTAGES OF PACKAGES.
A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec.
Packages offer several advantages: modularity, easier application design, information hiding, added functionality, and better performance.
Modularity
Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.
Easier Application Design
When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.
Information Hiding
With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.
Added Functionality
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.
Better Performance
When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the implementation of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.
2.PACKAGE SPEC, PACKAGE BODY.
Understanding The Package Spec
The package spec contains public declarations. The scope of these declarations is local to your database schema and global to the package. So, the declared items are accessible from your application and from anywhere in the package. Figure 9-2 illustrates the scoping.
Figure 9-2 Package Scope
The spec lists the package resources available to applications. All the information your application needs to use the resources is in the spec. For example, the following declaration shows that the function named fac takes one argument of type INTEGER and returns a value of type INTEGER:
FUNCTION fac (n INTEGER) RETURN INTEGER; -- returns n!
That is all the information you need to call the function. You need not consider its underlying implementation (whether it is iterative or recursive for example).
Only subprograms and cursors have an underlying implementation. So, if a spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. Consider the following bodiless package:
CREATE PACKAGE trans_data AS -- bodiless package
TYPE TimeRec IS RECORD (
minutes SMALLINT,
hours SMALLINT);
TYPE TransRec IS RECORD (
category VARCHAR2,
account INT,
amount REAL,
time_of TimeRec);
minimum_balance CONSTANT REAL := 10.00;
number_processed INT;
insufficient_funds EXCEPTION;
END trans_data;
The package trans_data needs no body because types, constants, variables, and exceptions do not have an underlying implementation. Such packages let you define global variables--usable by subprograms and database triggers--that persist throughout a session.
Understanding The Package Body
The package body implements the package spec. That is, the package body contains the implementation of every cursor and subprogram declared in the package spec. Keep in mind that subprograms defined in a package body are accessible outside the package only if their specs also appear in the package spec.
To match subprogram specs and bodies, PL/SQL does a token-by-token comparison of their headers. So, except for white space, the headers must match word for word. Otherwise, PL/SQL raises an exception, as the following example shows:
CREATE PACKAGE emp_actions AS
...
PROCEDURE calc_bonus (date_hired emp.hiredate%TYPE, ...);
END emp_actions;
CREATE PACKAGE BODY emp_actions AS
...
PROCEDURE calc_bonus (date_hired DATE, ...) IS
-- parameter declaration raises an exception because 'DATE'
-- does not match 'emp.hiredate%TYPE' word for word
BEGIN ... END;
END emp_actions;
The package body can also contain private declarations, which define types and items necessary for the internal workings of the package. The scope of these declarations is local to the package body. Therefore, the declared types and items are inaccessible except from within the package body. Unlike a package spec, the declarative part of a package body can contain subprogram bodies.
Following the declarative part of a package body is the optional initialization part, which typically holds statements that initialize some of the variables previously declared in the package.
The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be called or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package.
Remember, if a package spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. However, the body can still be used to initialize items declared in the package spec.
3.PUBLIC , PRIVATE SCOPE , GLOBAL VARIABLE.
The spec holds public declarations, which are visible to your application. You must declare subprograms at the end of the spec after all other items (except pragmas that name a specific function; such pragmas must follow the function spec).
The body holds implementation details and private declarations, which are hidden from your application. Following the declarative part of the package body is the optional initialization part, which typically holds statements that initialize package variables.
Private Versus Public Items in Packages
Look again at the package emp_actions. The package body declares a variable named number_hired, which is initialized to zero. Unlike items declared in the spec of emp_actions, items declared in the body are restricted to use within the package. Therefore, PL/SQL code outside the package cannot reference the variable number_hired. Such items are called private.
However, items declared in the spec of emp_actions, such as the exception invalid_salary, are visible outside the package. Therefore, any PL/SQL code can reference the exception invalid_salary. Such items are called public.
When you must maintain items throughout a session or across transactions, place them in the declarative part of the package body. For example, the value of number_hired is kept between calls to hire_employee within the same session. The value is lost when the session ends.
If you must also make the items public, place them in the package spec. For example, the constant minimum_balance declared in the spec of the package bank_transactions is available for general use.
4.One Time Use Package
5.OVERLOADING.
PL/SQL allows two or more packaged subprograms to have the same name. This option is useful when you want a subprogram to accept similar sets of parameters that have different datatypes. For example, the following package defines two procedures named journalize:
CREATE PACKAGE journal_entries AS
...
PROCEDURE journalize (amount REAL, trans_date VARCHAR2);
PROCEDURE journalize (amount REAL, trans_date INT);
END journal_entries;
CREATE PACKAGE BODY journal_entries AS
...
PROCEDURE journalize (amount REAL, trans_date VARCHAR2) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));
END journalize;
PROCEDURE journalize (amount REAL, trans_date INT) IS
BEGIN
INSERT INTO journal
VALUES (amount, TO_DATE(trans_date, 'J'));
END journalize;
END journal_entries;
The first procedure accepts trans_date as a character string, while the second procedure accepts it as a number (the Julian day). Each procedure handles the data appropriately.
6.SERIALLY RESUSABLE
Serially Reusable PL/SQL Packages
PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE (using pragma syntax).
For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).
The unit of work for serially reusable packages is implicitly a call to the server; for example, an OCI call to the server, or a PL/SQL RPC call from a client to a server, or an RPC call from a server to another server.
Package States
The state of a nonreusable package (one not marked SERIALLY_REUSABLE) persists for the lifetime of a session. A package's state includes global variables, cursors, and so on.
The state of a serially reusable package persists only for the lifetime of a call to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, then Oracle creates a new instantiation of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous calls to the server are not visible.
Note:
Creating a new instantiation of a serially reusable package on a call to the server does not necessarily imply that Oracle allocates memory or configures the instantiation object. Oracle looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in the SGA.
At the end of the call to the server, this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.
Why Serially Reusable Packages?
Because the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications, such as Oracle Office, a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session, after they are done using the package.
With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.
Syntax of Serially Reusable Packages
A package can be marked serially reusable by a pragma. The syntax of the pragma is:
PRAGMA SERIALLY_REUSABLE;
A package specification can be marked serially reusable, whether or not it has a corresponding package body. If the package has a body, then the body must have the serially reusable pragma, if its corresponding specification has the pragma; it cannot have the serially reusable pragma unless the specification also has the pragma.
Semantics of Serially Reusable Packages
A package that is marked SERIALLY_REUSABLE has the following properties:
• Its package variables are meant for use only within the work boundaries, which correspond to calls to the server (either OCI call boundaries or PL/SQL RPC calls to the server).
________________________________________
Note:
If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, then the application program can fail. PL/SQL cannot check for such cases.
________________________________________
• A pool of package instantiations is kept, and whenever a "unit of work" needs this package, one of the instantiations is "reused", as follows:
o The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).
o The initialization code in the package body is run again.
• At the "end work" boundary, cleanup is done.
o If any cursors were left open, then they are silently closed.
o Some non-reusable secondary memory is freed (such as memory for collection variables or long VARCHAR2s).
o This package instantiation is returned back to the pool of reusable instantiations kept for this package.
• Serially reusable packages cannot be accessed from within triggers. If you attempt to access a serially reusable package from a trigger, then Oracle issues the error message "cannot access Serially Reusable package
Examples of Serially Reusable Packages
Example 1: How Package Variables Act Across Call Boundaries
This example has a serially reusable package specification (there is no body).
CONNECT Scott/Tiger
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
N NUMBER := 5; -- default initialization
END Sr_pkg;
Suppose your Enterprise Manager (or SQL*Plus) application issues the following:
CONNECT Scott/Tiger
# first CALL to server
BEGIN
Sr_pkg.N := 10;
END;
# second CALL to server
BEGIN
DBMS_OUTPUT.PUT_LINE(Sr_pkg.N);
END;
This program prints: 5
________________________________________
Note:
If the package had not had the pragma SERIALLY_REUSABLE, the program would have printed '10'.
________________________________________
Example 2: How Package Variables Act Across Call Boundaries
This example has both a package specification and package body, which are serially reusable.
CONNECT Scott/Tiger
DROP PACKAGE Sr_pkg;
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
TYPE Str_table_type IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
Num NUMBER := 10;
Str VARCHAR2(200) := 'default-init-str';
Str_tab STR_TABLE_TYPE;
PROCEDURE Print_pkg;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2);
END Sr_pkg;
CREATE OR REPLACE PACKAGE BODY Sr_pkg IS
-- the body is required to have the pragma because the
-- specification of this package has the pragma
PRAGMA SERIALLY_REUSABLE;
PROCEDURE Print_pkg IS
BEGIN
DBMS_OUTPUT.PUT_LINE('num: ' || Sr_pkg.Num);
DBMS_OUTPUT.PUT_LINE('str: ' || Sr_pkg.Str);
DBMS_OUTPUT.PUT_LINE('number of table elems: ' || Sr_pkg.Str_tab.Count);
FOR i IN 1..Sr_pkg.Str_tab.Count LOOP
DBMS_OUTPUT.PUT_LINE(Sr_pkg.Str_tab(i));
END LOOP;
END;
PROCEDURE Init_and_print_pkg(N NUMBER, V VARCHAR2) IS
BEGIN
-- init the package globals
Sr_pkg.Num := N;
Sr_pkg.Str := V;
FOR i IN 1..n LOOP
Sr_pkg.Str_tab(i) := V || ' ' || i;
END LOOP;
-- now print the package
Print_pkg;
END;
END Sr_pkg;
SET SERVEROUTPUT ON;
Rem SR package access in a CALL:
BEGIN
-- initialize and print the package
DBMS_OUTPUT.PUT_LINE('Initing and printing pkg state..');
Sr_pkg.Init_and_print_pkg(4, 'abracadabra');
-- print it in the same call to the server.
-- we should see the initialized values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the same CALL...');
Sr_pkg.Print_pkg;
END;
Initing and printing pkg state..
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
Printing package state in the same CALL...
num: 4
str: abracadabra
number of table elems: 4
abracadabra 1
abracadabra 2
abracadabra 3
abracadabra 4
REM SR package access in subsequent CALL:
BEGIN
-- print the package in the next call to the server.
-- We should that the package state is reset to the initial (default) values.
DBMS_OUTPUT.PUT_LINE('Printing package state in the next CALL...');
Sr_pkg.Print_pkg;
END;
Statement processed.
Printing package state in the next CALL...
num: 10
str: default-init-str
number of table elems: 0
Example 3: Open Cursors in Serially Reusable Packages Across Call Boundaries
This example demonstrates that any open cursors in serially reusable packages get closed automatically at the end of a work boundary (which is a call). Also, in a new call, these cursors need to be opened again.
REM For serially reusable pkg: At the end work boundaries
REM (which is currently the OCI call boundary) all open
REM cursors will be closed.
REM
REM Because the cursor is closed - every time we fetch we
REM will start at the first row again.
CONNECT Scott/Tiger
DROP PACKAGE Sr_pkg;
DROP TABLE People;
CREATE TABLE People (Name VARCHAR2(20));
INSERT INTO People VALUES ('ET');
INSERT INTO People VALUES ('RAMBO');
CREATE OR REPLACE PACKAGE Sr_pkg IS
PRAGMA SERIALLY_REUSABLE;
CURSOR C IS SELECT Name FROM People;
END Sr_pkg;
SQL> SET SERVEROUTPUT ON;
SQL>
CREATE OR REPLACE PROCEDURE Fetch_from_cursor IS
Name VARCHAR2(200);
BEGIN
IF (Sr_pkg.C%ISOPEN) THEN
DBMS_OUTPUT.PUT_LINE('cursor is already open.');
ELSE
DBMS_OUTPUT.PUT_LINE('cursor is closed; opening now.');
OPEN Sr_pkg.C;
END IF;
-- fetching from cursor.
FETCH sr_pkg.C INTO name;
DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
FETCH Sr_pkg.C INTO name;
DBMS_OUTPUT.PUT_LINE('fetched: ' || Name);
-- Oops forgot to close the cursor (Sr_pkg.C).
-- But, because it is a Serially Reusable pkg's cursor,
-- it will be closed at the end of this CALL to the server.
END;
EXECUTE fetch_from_cursor;
cursor is closed; opening now.
fetched: ET
fetched: RAMBO
7. PIN PACKAGE.
Another way to improve performance is to pin frequently used packages in the shared memory pool. When a package is pinned, it is not aged out by the least recently used (LRU) algorithm that Oracle normally uses. The package remains in memory no matter how full the pool gets or how frequently you access the package.
You can pin packages with the help of the supplied package DBMS_SHARED_POOL.
TRIGGER
1.DEFINE TRIGGER,ADVANTAGES OF TRIGGER.
Triggers are procedures that are stored in the database and implicitly run, or fired, when something happens.
Traditionally, triggers supported the execution of a PL/SQL block when an INSERT, UPDATE, or DELETE occurred on a table or view. Starting with Oracle8i, triggers support system and other data events on DATABASE and SCHEMA. Oracle also supports the execution of a PL/SQL or Java procedure.
2.TYPE OF TRIGGERS.
Types of Triggers
A trigger is either a stored PL/SQL block or a PL/SQL, C, or Java procedure associated with a table, view, schema, or the database itself. Oracle automatically executes a trigger when a specified event takes place, which may be in the form of a system event or a DML statement being issued against the table.
Triggers can be:
• DML triggers on tables.
• INSTEAD OF triggers on views.
• System triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA, triggers fire for each event for that specific user.
Types of Triggers
This section describes the different types of triggers:
• Row Triggers and Statement Triggers
• BEFORE and AFTER Triggers
• INSTEAD OF Triggers
• Triggers on System Events and User Events
Row Triggers and Statement Triggers
When you define a trigger, you can specify the number of times the trigger action is to be run:
• Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
• Once for the triggering statement, no matter how many rows it affects
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-3 illustrates a row trigger that uses the values of each row affected by the triggering statement.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
• Make a complex security check on the current time or user
• Generate a single audit record
BEFORE and AFTER Triggers
When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.
BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.
BEFORE Triggers
BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:
• When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
• To derive specific column values before completing a triggering INSERT or UPDATE statement.
AFTER Triggers
AFTER triggers run the trigger action after the triggering statement is run.
Trigger Type Combinations
Using the options listed previously, you can create four types of row and statement triggers:
• BEFORE statement trigger
Before executing the triggering statement, the trigger action is run.
• BEFORE row trigger
Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
• AFTER statement trigger
After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
• AFTER row trigger
After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.
You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE).
INSTEAD OF Triggers
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
3.INSTEAD OF TRIGGERS.
Modifying Complex Views (INSTEAD OF Triggers)
The INSTEAD OF option can also be used in triggers. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement. The trigger must determine what operation was intended and perform UPDATE, INSERT, or DELETE operations directly on the underlying tables.
With an INSTEAD OF trigger, you can write normal UPDATE, INSERT, and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place.
INSTEAD OF triggers can only be activated for each row.
Notes:
• The INSTEAD OF option can only be used for triggers created over views.
• The BEFORE and AFTER options cannot be used for triggers created over views.
• The CHECK option for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.
Views that Require INSTEAD OF Triggers
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the following constructs:
• Set operators
• Group functions
• GROUP BY, CONNECT BY, or START WITH clauses
• The DISTINCT operator
• Joins (a subset of join views are updatable)
If a view contains pseudocolumns or expressions, then you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
INSTEAD OF Trigger Example
________________________________________
Note:
You may need to set up the following data structures for this example to work:
CREATE TABLE Project_tab (
Prj_level NUMBER,
Projno NUMBER,
Resp_dept NUMBER);
CREATE TABLE Emp_tab (
Empno NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Sal NUMBER(7,2),
Comm NUMBER(7,2),
Deptno NUMBER(2) NOT NULL);
CREATE TABLE Dept_tab (
Deptno NUMBER(2) NOT NULL,
Dname VARCHAR2(14),
Loc VARCHAR2(13),
Mgr_no NUMBER,
Dept_type NUMBER);
________________________________________
The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.
CREATE OR REPLACE VIEW manager_info AS
SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level,
p.projno
FROM Emp_tab e, Dept_tab d, Project_tab p
WHERE e.empno = d.mgr_no
AND d.deptno = p.resp_dept;
CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n -- new manager information
FOR EACH ROW
DECLARE
rowcnt number;
BEGIN
SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno;
IF rowcnt = 0 THEN
INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename);
ELSE
UPDATE Emp_tab SET Emp_tab.ename = :n.ename
WHERE Emp_tab.empno = :n.empno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno;
IF rowcnt = 0 THEN
INSERT INTO Dept_tab (deptno, dept_type)
VALUES(:n.deptno, :n.dept_type);
ELSE
UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type
WHERE Dept_tab.deptno = :n.deptno;
END IF;
SELECT COUNT(*) INTO rowcnt FROM Project_tab
WHERE Project_tab.projno = :n.projno;
IF rowcnt = 0 THEN
INSERT INTO Project_tab (projno, prj_level)
VALUES(:n.projno, :n.prj_level);
ELSE
UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level
WHERE Project_tab.projno = :n.projno;
END IF;
END;
The actions shown for rows being inserted into the MANAGER_INFO view first test to see if appropriate rows already exist in the base tables from which MANAGER_INFO is derived. The actions then insert new rows or update existing rows, as appropriate. Similar triggers can specify appropriate actions for UPDATE and DELETE.
4.DEFINE TERMS - WHEN CLAUSE , MUTATING TABLES,REFERENCE(:NEW,:OLD).
A. Firing Triggers Based on Conditions (WHEN Clause)
Optionally, a trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause.
If included, then the expression in the WHEN clause is evaluated for each row that the trigger affects.
If the expression evaluates to TRUE for a row, then the trigger body is fired on behalf of that row. However, if the expression evaluates to FALSE or NOT TRUE for a row (unknown, as with nulls), then the trigger body is not fired for that row. The evaluation of the WHEN clause does not have an effect on the execution of the triggering SQL statement (in other words, the triggering statement is not rolled back if the expression in a WHEN clause evaluates to FALSE).
For example, in the PRINT_SALARY_CHANGES trigger, the trigger body is not run if the new value of Empno is zero, NULL, or negative. In more realistic examples, you might test if one column value is less than another.
The expression in a WHEN clause of a row trigger can include correlation names, which are explained later. The expression in a WHEN clause must be a SQL expression, and it cannot include a subquery. You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.
Reference(:New,:old).
(REFERENCING Option)
The REFERENCING option can be specified in a trigger body of a row trigger to avoid name conflicts among the correlation names and tables that might be named old or new. Because this is rare, this option is infrequently used.
For example, assume you have a table named new with columns field1 (number) and field2 (character). The following CREATE TRIGGER example shows a trigger associated with the new table that can use correlation names and avoid naming conflicts between the correlation names and the table name:
________________________________________
Note:
You may need to set up the following data structures for certain examples to work:
CREATE TABLE new (
field1 NUMBER,
field2 VARCHAR2(20));
________________________________________
CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
:Newest.Field2 := TO_CHAR (:newest.field1);
END;
Notice that the new qualifier is renamed to newest using the REFERENCING option, and it is then used in the trigger body.
C.TRIGGER RESTRICTIONS ON MUTATING TABLES
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the FOR EACH ROW clause, and statement triggers that are fired as the result of a DELETE CASCADE. Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
Consider the following trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;
If the following SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
An error is returned because the table is mutating when the row is deleted:
ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger which is not subject to this restriction, and the trigger.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
Declarative integrity constraints are checked at various times with respect to row triggers.
Restrictions on Mutating Tables Relaxed
Before Oracle8i, there was a "constraining error" that prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint. Starting with Oracle8i, there is no constraining error. Also, checking of the foreign key is deferred until at least the end of the parent statement.
The mutating error still prevents the trigger from reading or modifying the table that the parent statement is modifying. However, starting in Oracle release 8.1, a delete against the parent table causes before/after statement triggers to be fired once. That way, you can create triggers (just not row triggers) to read and modify the parent and child tables.
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
create table p (p1 number constraint ppk primary key);
create table f (f1 number constraint ffk references p);
create trigger pt after update on p for each row begin
update f set f1 = :new.p1 where f1 = :old.p1;
end;
/
This implementation requires care for multirow updates. For example, if a table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
update p set p1 = p1+1;
The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid multirow updates to p that change the primary key and reuse existing primary key values. It could also be solved by tracking which foreign key values have already been updated, then modifying the trigger so that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that have been changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is called.
5.AUTONOMUS TRANSACTIONS.
Using Autonomous Triggers
Among other things, you can use database triggers to log events transparently. Suppose you want to track all inserts into a table, even those that roll back. In Example 6-47, you use a trigger to insert duplicate rows into a shadow table. Because it is autonomous, the trigger can commit changes to the shadow table whether or not you commit changes to the main table.
Example 6-47 Using Autonomous Triggers
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
-- create an autonomous trigger that inserts into the audit table before
-- each update of salary in the employees table
CREATE OR REPLACE TRIGGER audit_sal
BEFORE UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;
END;
/
-- update the salary of an employee, and then commit the insert
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 115;
COMMIT;
-- update another salary, then roll back the update
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 116;
ROLLBACK;
-- show that both committed and rolled-back updates add rows to audit table
SELECT * FROM emp_audit WHERE emp_audit_id = 115 OR emp_audit_id = 116;
Unlike regular triggers, autonomous triggers can execute DDL statements using native dynamic SQL, discussed in Chapter 7, "Performing SQL Operations with Native Dynamic SQL". In the following example, trigger drop_temp_table drops a temporary database table after a row is inserted in table emp_audit.
CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE TABLE temp_audit ( emp_audit_id NUMBER(6), up_date DATE);
CREATE OR REPLACE TRIGGER drop_temp_table
AFTER INSERT ON emp_audit
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE temp_audit';
COMMIT;
END;
/
6. SYSTEM TRIGGER
Tracking System Events Using Triggers
Fine-Grained Access Control Using Triggers: Example System triggers can be used to set application context. Application context is a relatively new feature that enhances your ability to implement fine-grained access control. Application context is a secure session cache, and it can be used to store session-specific attributes.
In the example that follows, procedure set_ctx sets the application context based on the user profile. The trigger setexpensectx ensures that the context is set for every user.
Responding to System Events through Triggers
System event publication lets applications subscribe to database events, just like they subscribe to messages from other applications. The system events publication framework includes the following features:
• Infrastructure for publish/subscribe, by making the database an active publisher of events.
• Integration of data cartridges in the server. The system events publication can be used to notify cartridges of state changes in the server.
• Integration of fine-grained access control in the server.
By creating a trigger, you can specify a procedure that runs when an event occurs. DML events are supported on tables, and system events are supported on DATABASE and SCHEMA. You can turn notification on and off by enabling and disabling the trigger using the ALTER TRIGGER statement.
This feature is integrated with the Advanced Queueing engine. Publish/subscribe applications use the DBMS_AQ.ENQUEUE() procedure, and other applications such as cartridges use callouts.
• Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)
7.WHO IS THE TRIGGER USER?
The following statement, inside a trigger, returns the owner of the trigger, not the name of user who is updating the table:
SELECT Username FROM USER_USERS;
8.VIEWING INFORMATION ABOUT TRIGGERS
The following data dictionary views reveal information about triggers:
• USER_TRIGGERS
• ALL_TRIGGERS
• DBA_TRIGGERS
The new column, BASE_OBJECT_TYPE, specifies whether the trigger is based on DATABASE, SCHEMA, table, or view. The old column, TABLE_NAME, is null if the base object is not table or view.
The column ACTION_TYPE specifies whether the trigger is a call type trigger or a PL/SQL trigger.
The column TRIGGER_TYPE includes two additional values: BEFORE EVENT and AFTER EVENT, applicable only to system events.
The column TRIGGERING_EVENT includes all system and DML events.
9.EXAMPLES OF TRIGGER APPLICATIONS
You can use triggers in a number of ways to customize information management in an Oracle database. For example, triggers are commonly used to:
• Provide sophisticated auditing
• Prevent invalid transactions
• Enforce referential integrity (either those actions not supported by declarative integrity constraints or across nodes in a distributed database)
• Enforce complex business rules
• Enforce complex security authorizations
• Provide transparent event logging
• Automatically generate derived column values
• Enable building complex views that are updatable
• Track system events
This section provides an example of each of these trigger applications. These examples are not meant to be used exactly as written: They are provided to assist you in designing your own triggers.
Auditing with Triggers: Example
Triggers are commonly used to supplement the built-in auditing features of Oracle. Although triggers can be written to record information similar to that recorded by the AUDIT statement, triggers should be used only when more detailed audit information is required. For example, use triggers to provide value-based auditing for each row.
Sometimes, the Oracle AUDIT statement is considered a security audit facility, while triggers can provide financial audit facility.
When deciding whether to create a trigger to audit database activity, consider what Oracle's auditing features provide, compared to auditing defined by triggers.
Audit Feature Comparison of Built-In Auditing and Trigger-Based Auditing
DML and DDL Auditing Standard auditing options permit auditing of DML and DDL statements regarding all types of schema objects and structures. Comparatively, triggers permit auditing of DML statements entered against tables, and DDL auditing at SCHEMA or DATABASE level.
Centralized Audit Trail All database audit information is recorded centrally and automatically using the auditing features of Oracle.
Declarative Method Auditing features enabled using the standard Oracle features are easier to declare and maintain, and less prone to errors, when compared to auditing functions defined by triggers.
Auditing Options can be Audited Any changes to existing auditing options can also be audited to guard against malicious database activity.
Session and Execution time Auditing Using the database auditing features, records can be generated once every time an audited statement is entered (BY ACCESS) or once for every session that enters an audited statement (BY SESSION). Triggers cannot audit by session; an audit record is generated each time a trigger-audited table is referenced.
Auditing of Unsuccessful Data Access Database auditing can be set to audit when unsuccessful data access occurs. However, unless autonomous transactions are used, any audit information generated by a trigger is rolled back if the triggering statement is rolled back. For more information on autonomous transactions, see Oracle9i Database Concepts.
Sessions can be Audited Connections and disconnections, as well as session activity (physical I/Os, logical I/Os, deadlocks, and so on), can be recorded using standard database auditing.
When using triggers to provide sophisticated auditing, AFTER triggers are normally used. By using AFTER triggers, auditing information is recorded after the triggering statement is subjected to any applicable integrity constraints, preventing cases where the audit processing is carried out unnecessarily for statements that generate exceptions to integrity constraints.
Choosing between AFTER row and AFTER statement triggers depends on the information being audited. For example, row triggers provide value-based auditing for each table row. Triggers can also require the user to supply a "reason code" for issuing the audited SQL statement, which can be useful in both row and statement-level auditing situations.
The following example demonstrates a trigger that audits modifications to the Emp_tab table for each row. It requires that a "reason code" be stored in a global package variable before the update. This shows how triggers can be used to provide value-based auditing and how to use public package variables.
________________________________________
Note:
You may need to set up the following data structures for the examples to work:
CREATE OR REPLACE PACKAGE Auditpackage AS
Reason VARCHAR2(10);
PROCEDURE Set_reason(Reason VARCHAR2);
END;
CREATE TABLE Emp99 (
Empno NOT NULL NUMBER(4)
Ename VARCHAR2(10)
Job VARCHAR2(9)
Mgr NUMBER(4)
Hiredate DATE
Sal NUMBER(7,2)
Comm NUMBER(7,2)
Deptno NUMBER(2)
Bonus NUMBER
Ssn NUMBER
Job_classification NUMBER);
CREATE TABLE Audit_employee (
Oldssn NUMBER
Oldname VARCHAR2(10)
Oldjob VARCHAR2(2)
Oldsal NUMBER
Newssn NUMBER
Newname VARCHAR2(10)
Newjob VARCHAR2(2)
Newsal NUMBER
Reason VARCHAR2(10)
User1 VARCHAR2(10)
Systemdate DATE);
________________________________________
CREATE OR REPLACE TRIGGER Audit_employee
AFTER INSERT OR DELETE OR UPDATE ON Emp99
FOR EACH ROW
BEGIN
/* AUDITPACKAGE is a package with a public package
variable REASON. REASON could be set by the
application by a command such as EXECUTE
AUDITPACKAGE.SET_REASON(reason_string). Note that a
package variable has state for the duration of a
session and that each session has a separate copy of
all package variables. */
IF Auditpackage.Reason IS NULL THEN
Raise_application_error(-20201, 'Must specify reason'
|| ' with AUDITPACKAGE.SET_REASON(Reason_string)');
END IF;
/* If the above conditional evaluates to TRUE, the
user-specified error number and message is raised,
the trigger stops execution, and the effects of the
triggering statement are rolled back. Otherwise, a
new row is inserted into the predefined auditing
table named AUDIT_EMPLOYEE containing the existing
and new values of the Emp_tab table and the reason code
defined by the REASON variable of AUDITPACKAGE. Note
that the "old" values are NULL if triggering
statement is an INSERT and the "new" values are NULL
if the triggering statement is a DELETE. */
INSERT INTO Audit_employee VALUES
(:old.Ssn, :old.Ename, :old.Job_classification, :old.Sal,
:new.Ssn, :new.Ename, :new.Job_classification, :new.Sal,
auditpackage.Reason, User, Sysdate );
END;
Optionally, you can also set the reason code back to NULL if you wanted to force the reason code to be set for every update. The following simple AFTER statement trigger sets the reason code back to NULL after the triggering statement is run:
CREATE OR REPLACE TRIGGER Audit_employee_reset
AFTER INSERT OR DELETE OR UPDATE ON Emp_tab
BEGIN
auditpackage.set_reason(NULL);
END;
Notice that the previous two triggers are both fired by the same type of SQL statement. However, the AFTER row trigger is fired once for each row of the table affected by the triggering statement, while the AFTER statement trigger is fired only once after the triggering statement execution is completed.
This next trigger also uses triggers to do auditing. It tracks changes made to the Emp_tab table and stores this information in AUDIT_TABLE and AUDIT_TABLE_VALUES.
________________________________________
Note:
You may need to set up the following data structures for the example to work:
CREATE TABLE Audit_table (
Seq NUMBER,
User_at VARCHAR2(10),
Time_now DATE,
Term VARCHAR2(10),
Job VARCHAR2(10),
Proc VARCHAR2(10),
enum NUMBER);
CREATE SEQUENCE Audit_seq;
CREATE TABLE Audit_table_values (
Seq NUMBER,
Dept NUMBER,
Dept1 NUMBER,
Dept2 NUMBER);
________________________________________
CREATE OR REPLACE TRIGGER Audit_emp
AFTER INSERT OR UPDATE OR DELETE ON Emp_tab
FOR EACH ROW
DECLARE
Time_now DATE;
Terminal CHAR(10);
BEGIN
-- get current time, and the terminal of the user:
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
-- record new employee primary key
IF INSERTING THEN
INSERT INTO Audit_table
VALUES (Audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'INSERT', :new.Empno);
-- record primary key of the deleted row:
ELSIF DELETING THEN
INSERT INTO Audit_table
VALUES (Audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'DELETE', :old.Empno);
-- for updates, record the primary key
-- of the row being updated:
ELSE
INSERT INTO Audit_table
VALUES (audit_seq.NEXTVAL, User, Time_now,
Terminal, 'Emp_tab', 'UPDATE', :old.Empno);
-- and for SAL and DEPTNO, record old and new values:
IF UPDATING ('SAL') THEN
INSERT INTO Audit_table_values
VALUES (Audit_seq.CURRVAL, 'SAL',
:old.Sal, :new.Sal);
ELSIF UPDATING ('DEPTNO') THEN
INSERT INTO Audit_table_values
VALUES (Audit_seq.CURRVAL, 'DEPTNO',
:old.Deptno, :new.DEPTNO);
END IF;
END IF;
END;
Integrity Constraints and Triggers: Examples
Triggers and declarative integrity constraints can both be used to constrain data input. However, triggers and integrity constraints have significant differences.
Declarative integrity constraints are statements about the database that are always true. A constraint applies to existing data in the table and any statement that manipulates the table.
Triggers constrain what a transaction can do. A trigger does not apply to data loaded before the definition of the trigger; therefore, it is not known if all data in a table conforms to the rules established by an associated trigger.
Although triggers can be written to enforce many of the same rules supported by Oracle's declarative integrity constraint features, triggers should only be used to enforce complex business rules that cannot be defined using standard integrity constraints. The declarative integrity constraint features provided with Oracle offer the following advantages when compared to constraints defined by triggers:
Centralized integrity checks. All points of data access must adhere to the global set of rules defined by the integrity constraints corresponding to each schema object.
Declarative method. Constraints defined using the standard integrity constraint features are much easier to write and are less prone to errors, when compared with comparable constraints defined by triggers.
While most aspects of data integrity can be defined and enforced using declarative integrity constraints, triggers can be used to enforce complex business constraints not definable using declarative integrity constraints. For example, triggers can be used to enforce:
• UPDATE and DELETE SET NULL, and UPDATE and DELETE SET DEFAULT referential actions.
• Referential integrity when the parent and child tables are on different nodes of a distributed database.
• Complex check constraints not definable using the expressions allowed in a CHECK constraint.
Referential Integrity Using Triggers
Many cases of referential integrity can be enforced using triggers. However, only use triggers when you want to enforce the UPDATE and DELETE SET NULL (when referenced data is updated or deleted, all associated dependent data is set to NULL), and UPDATE and DELETE SET DEFAULT (when referenced data is updated or deleted, all associated dependent data is set to a default value) referential actions, or when you want to enforce referential integrity between parent and child tables on different nodes of a distributed database.
When using triggers to maintain referential integrity, declare the PRIMARY (or UNIQUE) KEY constraint in the parent table. If referential integrity is being maintained between a parent and child table in the same database, then you can also declare the foreign key in the child table, but disable it; this prevents the corresponding PRIMARY KEY constraint from being dropped (unless the PRIMARY KEY constraint is explicitly dropped with the CASCADE option).
To maintain referential integrity using triggers:
• A trigger must be defined for the child table that guarantees values inserted or updated in the foreign key correspond to values in the parent key.
• One or more triggers must be defined for the parent table. These triggers guarantee the desired referential action (RESTRICT, CASCADE, or SET NULL) for values in the foreign key when values are updated or deleted in the parent key. No action is required for inserts into the parent table (no dependent foreign keys exist).
The following sections provide examples of the triggers necessary to enforce referential integrity. The Emp_tab and Dept_tab table relationship is used in these examples.
Several of the triggers include statements that lock rows (SELECT... FOR UPDATE). This operation is necessary to maintain concurrency as the rows are being processed.
Foreign Key Trigger for Child Table
The following trigger guarantees that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The mutating table exception included in the following example allows this trigger to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception can be removed if this trigger is used alone.
CREATE OR REPLACE TRIGGER Emp_dept_check
BEFORE INSERT OR UPDATE OF Deptno ON Emp_tab
FOR EACH ROW WHEN (new.Deptno IS NOT NULL)
-- Before a row is inserted, or DEPTNO is updated in the Emp_tab
-- table, fire this trigger to verify that the new foreign
-- key value (DEPTNO) is present in the Dept_tab table.
DECLARE
Dummy INTEGER; -- used for cursor fetch below
Invalid_department EXCEPTION;
Valid_department EXCEPTION;
Mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
-- Cursor used to verify parent key value exists. If
-- present, lock parent key's row so it can't be
-- deleted by another transaction until this
-- transaction is committed or rolled back.
CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM Dept_tab
WHERE Deptno = Dn
FOR UPDATE OF Deptno;
BEGIN
OPEN Dummy_cursor (:new.Deptno);
FETCH Dummy_cursor INTO Dummy;
-- Verify parent key. If not found, raise user-specified
-- error number and message. If found, close cursor
-- before allowing triggering statement to complete:
IF Dummy_cursor%NOTFOUND THEN
RAISE Invalid_department;
ELSE
RAISE valid_department;
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Invalid_department THEN
CLOSE Dummy_cursor;
Raise_application_error(-20000, 'Invalid Department'
|| ' Number' || TO_CHAR(:new.deptno));
WHEN Valid_department THEN
CLOSE Dummy_cursor;
WHEN Mutating_table THEN
NULL;
END;
UPDATE and DELETE RESTRICT Trigger for Parent Table
The following trigger is defined on the DEPT_TAB table to enforce the UPDATE and DELETE RESTRICT referential action on the primary key of the DEPT_TAB table:
CREATE OR REPLACE TRIGGER Dept_restrict
BEFORE DELETE OR UPDATE OF Deptno ON Dept_tab
FOR EACH ROW
-- Before a row is deleted from Dept_tab or the primary key
-- (DEPTNO) of Dept_tab is updated, check for dependent
-- foreign key values in Emp_tab; rollback if any are found.
DECLARE
Dummy INTEGER; -- used for cursor fetch below
Employees_present EXCEPTION;
employees_not_present EXCEPTION;
-- Cursor used to check for dependent foreign key values.
CURSOR Dummy_cursor (Dn NUMBER) IS
SELECT Deptno FROM Emp_tab WHERE Deptno = Dn;
BEGIN
OPEN Dummy_cursor (:old.Deptno);
FETCH Dummy_cursor INTO Dummy;
-- If dependent foreign key is found, raise user-specified
-- error number and message. If not found, close cursor
-- before allowing triggering statement to complete.
IF Dummy_cursor%FOUND THEN
RAISE Employees_present; -- dependent rows exist
ELSE
RAISE Employees_not_present; -- no dependent rows
END IF;
CLOSE Dummy_cursor;
EXCEPTION
WHEN Employees_present THEN
CLOSE Dummy_cursor;
Raise_application_error(-20001, 'Employees Present in'
|| ' Department ' || TO_CHAR(:old.DEPTNO));
WHEN Employees_not_present THEN
CLOSE Dummy_cursor;
END;
________________________________________
Caution:
This trigger does not work with self-referential tables (tables with both the primary/unique key and the foreign key). Also, this trigger does not allow triggers to cycle (such as, A fires B fires A).
________________________________________
UPDATE and DELETE SET NULL Triggers for Parent Table: Example
The following trigger is defined on the DEPT_TAB table to enforce the UPDATE and DELETE SET NULL referential action on the primary key of the DEPT_TAB table:
CREATE OR REPLACE TRIGGER Dept_set_null
AFTER DELETE OR UPDATE OF Deptno ON Dept_tab
FOR EACH ROW
-- Before a row is deleted from Dept_tab or the primary key
-- (DEPTNO) of Dept_tab is updated, set all corresponding
-- dependent foreign key values in Emp_tab to NULL:
BEGIN
IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
UPDATE Emp_tab SET Emp_tab.Deptno = NULL
WHERE Emp_tab.Deptno = :old.Deptno;
END IF;
END;
DELETE Cascade Trigger for Parent Table: Example
The following trigger on the DEPT_TAB table enforces the DELETE CASCADE referential action on the primary key of the DEPT_TAB table:
CREATE OR REPLACE TRIGGER Dept_del_cascade
AFTER DELETE ON Dept_tab
FOR EACH ROW
-- Before a row is deleted from Dept_tab, delete all
-- rows from the Emp_tab table whose DEPTNO is the same as
-- the DEPTNO being deleted from the Dept_tab table:
BEGIN
DELETE FROM Emp_tab
WHERE Emp_tab.Deptno = :old.Deptno;
END;
________________________________________
Note:
Typically, the code for DELETE CASCADE is combined with the code for UPDATE SET NULL or UPDATE SET DEFAULT to account for both updates and deletes.
________________________________________
UPDATE Cascade Trigger for Parent Table: Example
The following trigger ensures that if a department number is updated in the Dept_tab table, then this change is propagated to dependent foreign keys in the Emp_tab table:
-- Generate a sequence number to be used as a flag for
-- determining if an update has occurred on a column:
CREATE SEQUENCE Update_sequence
INCREMENT BY 1 MAXVALUE 5000
CYCLE;
CREATE OR REPLACE PACKAGE Integritypackage AS
Updateseq NUMBER;
END Integritypackage;
CREATE OR REPLACE PACKAGE BODY Integritypackage AS
END Integritypackage;
-- create flag col:
ALTER TABLE Emp_tab ADD Update_id NUMBER; .
CREATE OR REPLACE TRIGGER Dept_cascade1 BEFORE UPDATE OF Deptno ON Dept_tab
DECLARE
Dummy NUMBER;
-- Before updating the Dept_tab table (this is a statement
-- trigger), generate a new sequence number and assign
-- it to the public variable UPDATESEQ of a user-defined
-- package named INTEGRITYPACKAGE:
BEGIN
SELECT Update_sequence.NEXTVAL
INTO Dummy
FROM dual;
Integritypackage.Updateseq := Dummy;
END;
CREATE OR REPLACE TRIGGER Dept_cascade2 AFTER DELETE OR UPDATE
OF Deptno ON Dept_tab FOR EACH ROW
-- For each department number in Dept_tab that is updated,
-- cascade the update to dependent foreign keys in the
-- Emp_tab table. Only cascade the update if the child row
-- has not already been updated by this trigger:
BEGIN
IF UPDATING THEN
UPDATE Emp_tab
SET Deptno = :new.Deptno,
Update_id = Integritypackage.Updateseq --from 1st
WHERE Emp_tab.Deptno = :old.Deptno
AND Update_id IS NULL;
/* only NULL if not updated by the 3rd trigger
fired by this same triggering statement */
END IF;
IF DELETING THEN
-- Before a row is deleted from Dept_tab, delete all
-- rows from the Emp_tab table whose DEPTNO is the same as
-- the DEPTNO being deleted from the Dept_tab table:
DELETE FROM Emp_tab
WHERE Emp_tab.Deptno = :old.Deptno;
END IF;
END;
CREATE OR REPLACE TRIGGER Dept_cascade3 AFTER UPDATE OF Deptno ON Dept_tab
BEGIN UPDATE Emp_tab
SET Update_id = NULL
WHERE Update_id = Integritypackage.Updateseq;
END;
________________________________________
Note: Because this trigger updates the Emp_tab table, the Emp_dept_check trigger, if enabled, is also fired. The resulting mutating table error is trapped by the Emp_dept_check trigger. You should carefully test any triggers that require error trapping to succeed to ensure that they always work properly in your environment.
________________________________________
Trigger for Complex Check Constraints: Example
Triggers can enforce integrity rules other than referential integrity. For example, this trigger performs a complex check before allowing the triggering statement to run.
________________________________________
Note:
You may need to set up the following data structures for the example to work:
CREATE TABLE Salgrade (
Grade NUMBER,
Losal NUMBER,
Hisal NUMBER,
Job_classification NUMBER)
________________________________________
CREATE OR REPLACE TRIGGER Salary_check
BEFORE INSERT OR UPDATE OF Sal, Job ON Emp99
FOR EACH ROW
DECLARE
Minsal NUMBER;
Maxsal NUMBER;
Salary_out_of_range EXCEPTION;
BEGIN
/* Retrieve the minimum and maximum salary for the
employee's new job classification from the SALGRADE
table into MINSAL and MAXSAL: */
SELECT Minsal, Maxsal INTO Minsal, Maxsal FROM Salgrade
WHERE Job_classification = :new.Job;
/* If the employee's new salary is less than or greater
than the job classification's limits, the exception is
raised. The exception message is returned and the
pending INSERT or UPDATE statement that fired the
trigger is rolled back:*/
IF (:new.Sal < Minsal OR :new.Sal > Maxsal) THEN
RAISE Salary_out_of_range;
END IF;
EXCEPTION
WHEN Salary_out_of_range THEN
Raise_application_error (-20300,
'Salary '||TO_CHAR(:new.Sal)||' out of range for '
||'job classification '||:new.Job
||' for employee '||:new.Ename);
WHEN NO_DATA_FOUND THEN
Raise_application_error(-20322,
'Invalid Job Classification '
||:new.Job_classification);
END;
Complex Security Authorizations and Triggers: Example
Triggers are commonly used to enforce complex security authorizations for table data. Only use triggers to enforce complex security authorizations that cannot be defined using the database security features provided with Oracle. For example, a trigger can prohibit updates to salary data of the Emp_tab table during weekends, holidays, and non-working hours.
When using a trigger to enforce a complex security authorization, it is best to use a BEFORE statement trigger. Using a BEFORE statement trigger has these benefits:
• The security check is done before the triggering statement is allowed to run, so that no wasted work is done by an unauthorized statement.
• The security check is performed only once for the triggering statement, not for each row affected by the triggering statement.
This example shows a trigger used to enforce security.
________________________________________
Note:
You may need to set up the following data structures for the example to work:
CREATE TABLE Company_holidays (Day DATE);
________________________________________
CREATE OR REPLACE TRIGGER Emp_permit_changes
BEFORE INSERT OR DELETE OR UPDATE ON Emp99
DECLARE
Dummy INTEGER;
Not_on_weekends EXCEPTION;
Not_on_holidays EXCEPTION;
Non_working_hours EXCEPTION;
BEGIN
/* check for weekends: */
IF (TO_CHAR(Sysdate, 'DY') = 'SAT' OR
TO_CHAR(Sysdate, 'DY') = 'SUN') THEN
RAISE Not_on_weekends;
END IF;
/* check for company holidays:*/
SELECT COUNT(*) INTO Dummy FROM Company_holidays
WHERE TRUNC(Day) = TRUNC(Sysdate);
/* TRUNC gets rid of time parts of dates: */
IF dummy > 0 THEN
RAISE Not_on_holidays;
END IF;
/* Check for work hours (8am to 6pm): */
IF (TO_CHAR(Sysdate, 'HH24') < 8 OR
TO_CHAR(Sysdate, 'HH24') > 18) THEN
RAISE Non_working_hours;
END IF;
EXCEPTION
WHEN Not_on_weekends THEN
Raise_application_error(-20324,'May not change '
||'employee table during the weekend');
WHEN Not_on_holidays THEN
Raise_application_error(-20325,'May not change '
||'employee table during a holiday');
WHEN Non_working_hours THEN
Raise_application_error(-20326,'May not change '
||'Emp_tab table during non-working hours');
END;
Transparent Event Logging and Triggers
Triggers are very useful when you want to transparently perform a related change in the database following certain events.
The REORDER trigger example shows a trigger that reorders parts as necessary when certain conditions are met. (In other words, a triggering statement is entered, and the PARTS_ON_HAND value is less than the REORDER_POINT value.)
Derived Column Values and Triggers: Example
Triggers can derive column values automatically, based upon a value provided by an INSERT or UPDATE statement. This type of trigger is useful to force values in specific columns that depend on the values of other columns in the same row. BEFORE row triggers are necessary to complete this type of operation for the following reasons:
• The dependent values must be derived before the INSERT or UPDATE occurs, so that the triggering statement can use the derived values.
• The trigger must fire for each row affected by the triggering INSERT or UPDATE statement.
The following example illustrates how a trigger can be used to derive new column values for a table whenever a row is inserted or updated.
________________________________________
Note:
You may need to set up the following data structures for the example to work:
ALTER TABLE Emp99 ADD(
Uppername VARCHAR2(20),
Soundexname VARCHAR2(20));
________________________________________
CREATE OR REPLACE TRIGGER Derived
BEFORE INSERT OR UPDATE OF Ename ON Emp99
/* Before updating the ENAME field, derive the values for
the UPPERNAME and SOUNDEXNAME fields. Users should be
restricted from updating these fields directly: */
FOR EACH ROW
BEGIN
:new.Uppername := UPPER(:new.Ename);
:new.Soundexname := SOUNDEX(:new.Ename);
END;
Building Complex Updatable Views Using Triggers: Example
Views are an excellent mechanism to provide logical windows over table data. However, when the view query gets complex, the system implicitly cannot translate the DML on the view into those on the underlying tables. INSTEAD OF triggers help solve this problem. These triggers can be defined over views, and they fire instead of the actual DML.
Consider a library system where books are arranged under their respective titles. The library consists of a collection of book type objects. The following example explains the schema.
CREATE OR REPLACE TYPE Book_t AS OBJECT
(
Booknum NUMBER,
Title VARCHAR2(20),
Author VARCHAR2(20),
Available CHAR(1)
);
CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t;
Assume that the following tables exist in the relational schema:
Table Book_table (Booknum, Section, Title, Author, Available)
Booknum Section Title Author Available
121001 Classic Iliad Homer Y
121002 Novel Gone With the Wind Mitchell M N
Library consists of library_table(section).
Section
Geography
Classic
Now you can define a complex view over these tables to create a logical view of the library with sections and a collection of books in each section.
CREATE OR REPLACE VIEW Library_view AS
SELECT i.Section, CAST (MULTISET (
SELECT b.Booknum, b.Title, b.Author, b.Available
FROM Book_table b
WHERE b.Section = i.Section) AS Book_list_t) BOOKLIST
FROM Library_table i;
Make this view updatable by defining an INSTEAD OF trigger over the view.
CREATE OR REPLACE TRIGGER Library_trigger INSTEAD OF INSERT ON Library_view FOR
EACH ROW
Bookvar BOOK_T;
i INTEGER;
BEGIN
INSERT INTO Library_table VALUES (:NEW.Section);
FOR i IN 1..:NEW.Booklist.COUNT LOOP
Bookvar := Booklist(i);
INSERT INTO book_table
VALUES ( Bookvar.booknum, :NEW.Section, Bookvar.Title, Bookvar.Author,
bookvar.Available);
END LOOP;
END;
/
Now, the library_view is an updatable view, and any INSERTs on the view are handled by the trigger that gets fired automatically. For example:
INSERT INTO Library_view VALUES ('History', book_list_t(book_t(121330,
'Alexander', 'Mirth', 'Y');
Similarly, you can also define triggers on the nested table booklist to handle modification of the nested table element.
Tracking System Events Using Triggers
Fine-Grained Access Control Using Triggers: Example
System triggers can be used to set application context. Application context is a relatively new feature that enhances your ability to implement fine-grained access control. Application context is a secure session cache, and it can be used to store session-specific attributes.
In the example that follows, procedure set_ctx sets the application context based on the user profile. The trigger setexpensectx ensures that the context is set for every user.
CONNECT secdemo/secdemo
CREATE OR REPLACE CONTEXT Expenses_reporting USING Secdemo.Exprep_ctx;
REM =================================================================
REM Creation of the package which implements the context:
REM =================================================================
CREATE OR REPLACE PACKAGE Exprep_ctx AS
PROCEDURE Set_ctx;
END;
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY Exprep_ctx IS
PROCEDURE Set_ctx IS
Empnum NUMBER;
Countrec NUMBER;
Cc NUMBER;
Role VARCHAR2(20);
BEGIN
-- SET emp_number:
SELECT Employee_id INTO Empnum FROM Employee
WHERE Last_name = SYS_CONTEXT('userenv', 'session_user');
DBMS_SESSION.SET_CONTEXT('expenses_reporting','emp_number', Empnum);
-- SET ROLE:
SELECT COUNT (*) INTO Countrec FROM Cost_center WHERE Manager_id=Empnum;
IF (countrec > 0) THEN
DBMS_SESSION.SET_CONTEXT('expenses_reporting','exp_role','MANAGER');
ELSE
DBMS_SESSION.SET_CONTEXT('expenses_reporting','exp_role','EMPLOYEE');
END IF;
-- SET cc_number:
SELECT Cost_center_id INTO Cc FROM Employee
WHERE Last_name = SYS_CONTEXT('userenv','session_user');
DBMS_SESSION.SET_CONTEXT(expenses_reporting','cc_number',Cc);
END;
END;
CALL Syntax
CREATE OR REPLACE TRIGGER Secdemo.Setexpseetx
AFTER LOGON ON DATABASE
CALL Secdemo.Exprep_etx.Set_otx
DYNAMIC SQL
1.DEFINE DYNAMIC SQL,EXECUTE IMMDIATE,DBMS_SQL.
Dynamic SQL enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use the OPEN-FOR, FETCH, and CLOSE statements.
You need dynamic SQL in the following situations:
• You want to execute a SQL data definition statement (such as CREATE), a data control statement (such as GRANT), or a session control statement (such as ALTER SESSION). Unlike INSERT, UPDATE, and DELETE statements, these statements cannot be included directly in a PL/SQL program.
• You want more flexibility. For example, you might want to pass the name of a schema object as a parameter to a procedure. You might want to build different search conditions for the WHERE clause of a SELECT statement.
• You want to issue a query where you do not know the number, names, or datatypes of the columns in advance. In this case, you use the DBMS_SQL package rather than the OPEN-FOR statement.
If you have older code that uses the DBMS_SQL package, the techniques described in this chapter using EXECUTE IMMEDIATE and OPEN-FOR generally provide better performance, more readable code, and extra features such as support for objects and collections.
Using the EXECUTE IMMEDIATE Statement in PL/SQL
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The main argument to EXECUTE IMMEDIATE is the string containing the SQL statement to execute. You can build up the string using concatenation, or use a predefined string.
Except for multi-row queries, the dynamic string can contain any SQL statement or any PL/SQL block. The string can also contain placeholders, arbitrary names preceded by a colon, for bind arguments. In this case, you specify which PL/SQL variables correspond to the placeholders with the INTO, USING, and RETURNING INTO clauses.
When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark. When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block; there will be a semicolon immediately before the end of the string literal, and another following the closing single quotation mark.
The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.
The DBMS_SQL package provides an interface to use dynamic SQL to parse any data manipulation language (DML) or data definition language (DDL) statement using PL/SQL. For example, you can enter a DROP TABLE statement from within a stored procedure by using the PARSE procedure supplied with the DBMS_SQL package.
2.DEFINE TERMS - BIND VARIALE , USING CLAUSE
A.What is the use of Bind variable?
Bind Variables
When you embed an INSERT, UPDATE, DELETE, or SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the WHERE and VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the appropriate places.
You do need to specify bind variables with dynamic SQL, in clauses like WHERE and VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the USING clause. Using the USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements. For example:
'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
B. Using Clause
You can place all bind arguments in the USING clause. The default parameter mode is IN. For DML statements that have a RETURNING clause, you can place OUT arguments in the RETURNING INTO clause without specifying the parameter mode. If you use both the USING clause and the RETURNING INTO clause, the USING clause can contain only IN arguments.
3.DBMS_SQL VS EXECUTE IMMDIATE.
Choosing Between Native Dynamic SQL and the DBMS_SQL Package
1.Oracle Database provides two methods for using dynamic SQL within PL/SQL:
native dynamic SQL and the DBMS_SQL package. Native dynamic SQL lets you place dynamic SQL statements directly into PL/SQL code. These dynamic statements include DML statements (including queries), PL/SQL anonymous blocks, DDL statements, transaction control statements, and session control statements.
2.To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.
3.The DBMS_SQL package is a PL/SQL library that offers an API to execute SQL statements dynamically. The DBMS_SQL package has procedures to open a cursor, parse a cursor, supply binds, and so on. Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations
4. ADVANTAGES OF DBMS_SQL , EXECUTE IMMDIATE
Advantages of Native Dynamic SQL
Native dynamic SQL provides the following advantages over the DBMS_SQL package:
• Native Dynamic SQL is Easy to Use
• Native Dynamic SQL is Faster than DBMS_SQL
• Native Dynamic SQL Supports User-Defined Types
• Native Dynamic SQL Supports Fetching into Records
Native Dynamic SQL is Easy to Use
Because native dynamic SQL is integrated with SQL, you can use it in the same way that you use static SQL within PL/SQL code. Native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.
With the DBMS_SQL package you must call many procedures and functions in a strict sequence, which means that even simple operations require extensive code. You can avoid this complexity by using native dynamic SQL instead.
Table 8-1 illustrates the difference in the amount of code required to perform the same operation with the DBMS_SQL package and native dynamic SQL.
Table 8-1 Code Comparison of DBMS_SQL Package and Native Dynamic SQL
DBMS_SQL Package Native Dynamic SQL
CREATE OR REPLACE PROCEDURE insert_into_table
(p_table_name VARCHAR2, p_deptnumber NUMBER,
p_deptname VARCHAR2, p_location VARCHAR2)
IS
v_cur_hdl INTEGER;
v_stmt_str VARCHAR2(200);
v_rows_processed BINARY_INTEGER;
BEGIN
v_stmt_str := 'INSERT INTO '
|| p_table_name
|| ' VALUES (:g_deptno, :g_dname, :g_loc)';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str,
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl,':g_deptno',
p_deptnumber);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_dname',
p_deptname);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_loc',
p_location);
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/ CREATE OR REPLACE PROCEDURE insert_into_table
(p_table_name VARCHAR2, p_deptnumber NUMBER,
p_deptname VARCHAR2, p_location VARCHAR2)
IS
v_stmt_str VARCHAR2(200);
BEGIN
v_stmt_str := 'INSERT INTO '
|| p_table_name
|| ' VALUES (:g_deptno, :g_dname, :g_loc)';
EXECUTE IMMEDIATE v_stmt_str
USING p_deptnumber, p_deptname,
p_location;
END;
/
Native Dynamic SQL is Faster than DBMS_SQL
Native dynamic SQL in PL/SQL performs comparably to static SQL because the PL/SQL interpreter has built-in support. Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls. Of course, performance gains may vary depending on your application.
Native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.
The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead. Each time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for use during execution. Each time you execute a fetch, the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead.
Improving Performance Through Bind Variables
When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables because bind variables allow Oracle Database to share a single cursor for multiple SQL statements.
In Example 8-10 the native dynamic SQL code uses a parameter instead of a bind variable to construct the SQL statement.
Example 8-10 Using Native Dynamic SQL Without Bind Variables
CREATE OR REPLACE PROCEDURE del_dept
(p_department_id departments.department_id%TYPE)
IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = ' || TO_CHAR(p_department_id);
END;
For each distinct p_department_id parameter, the procedure creates a new cursor, which causes resource contention and poor performance. Instead, you can construct the SQL statement by using a bind variable, as shown in Example 8-11.
Example 8-11 Using Native Dynamic SQL with Bind Variables
CREATE OR REPLACE PROCEDURE del_dept
(p_department_id departments.department_id%TYPE)
IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = :1'
USING p_department_id;
END;
/
SHOW ERRORS;
In Example 8-11 the same cursor is reused for different values of the bind my_deptno, which improves performance and scalability.
Native Dynamic SQL Supports User-Defined Types
Native dynamic SQL supports all of the types supported by static SQL in PL/SQL, including user-defined types such as user-defined objects, collections, and REFs. The DBMS_SQL package does not support these user-defined types.
Note:
The DBMS_SQL package provides limited support for arrays. Refer to the Oracle Database PL/SQL Packages and Types Reference for information.
Native Dynamic SQL Supports Fetching into Records
Native dynamic SQL and static SQL both support fetching into records, but the DBMS_SQL package does not. With native dynamic SQL, the rows resulting from a query can be directly fetched into PL/SQL records. In Example 8-12 the rows from a query are fetched into the v_emp_rec variable.
Example 8-12 Using Native Dynamic SQL to Fetch into Records
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record emp%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job emp.job%TYPE;
BEGIN
v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
-- in a multi-row query
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
CLOSE v_emp_cursor;
-- in a single-row query
EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
END;
Advantages of the DBMS_SQL Package
The DBMS_SQL package provides the following advantages over native dynamic SQL:
• DBMS_SQL is Supported in Client-Side Programs
• DBMS_SQL Supports Statements with Unknown Number of Inputs or Outputs
• DBMS_SQL Supports SQL Statements Larger than 32 KB
• DBMS_SQL Lets You Reuse SQL Statements
DBMS_SQL is Supported in Client-Side Programs
The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC). These calls occur when you need to do any of the following:
• Bind a variable
• Define a variable
• Execute a statement
DBMS_SQL Supports Statements with Unknown Number of Inputs or Outputs
Native dynamic SQL does not support statements with an unknown number of inputs or outputs. The DBMS_SQL package does not have this limitation. One consequence is that you can use the DESCRIBE_COLUMNS procedure in the DBMS_SQL package to describe columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.
DBMS_SQL Supports SQL Statements Larger than 32 KB
The DBMS_SQL package supports SQL statements larger than 32 KB. Native dynamic SQL does not.
DBMS_SQL Lets You Reuse SQL Statements
The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.
Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the decrease in speed is typically outweighed by the performance benefits of native dynamic SQL.
Examples of DBMS_SQL Package Code and Native Dynamic SQL Code
The following examples illustrate the code differences necessary to complete operations with the DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are presented:
• Query
• DML operation
• DML returning operation
In general, the native dynamic SQL code is more readable and compact, which can improve developer productivity.
Querying with Dynamic SQL: Example
The following example includes a dynamic query statement with one bind variable (:g_jobname) and two select columns (ename and sal):
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :g_jobname';
This example queries for employees with the job description SALESMAN in the job column of the emp table. Table 8-2 shows sample code that accomplishes this query using the DBMS_SQL package and native dynamic SQL.
Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL
DBMS_SQL Query Operation Native Dynamic SQL Query Operation
DECLARE
v_stmt_str VARCHAR2(200);
v_cur_hdl INT;
v_rows_processed INT;
v_name VARCHAR2(10);
v_salary INT;
BEGIN
v_cur_hdl := DBMS_SQL.OPEN_CURSOR; -- open cursor
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :g_jobname';
DBMS_SQL.PARSE(v_cur_hdl,v_stmt_str,DBMS_SQL.NATIVE);
-- Supply binds (bind by name)
DBMS_SQL.BIND_VARIABLE(v_cur_hdl, 'g_jobname',
'SALESMAN');
-- Describe defines
DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 1, v_name, 200);
DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 2, v_salary);
-- Execute
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
LOOP
-- Fetch a row
IF DBMS_SQL.FETCH_ROWS(v_cur_hdl) > 0 THEN
-- Fetch columns from the row
DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 1, v_name);
DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 2, v_salary);
-- Process
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl); -- close cursor
END;
/ DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
v_stmt_str VARCHAR2(200);
v_name VARCHAR2(20);
v_salary NUMBER;
BEGIN
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :1';
OPEN v_emp_cursor FOR v_stmt_str
USING 'SALESMAN';
LOOP
FETCH v_emp_cursor
INTO v_name, v_salary;
EXIT WHEN v_emp_cursor%NOTFOUND;
-- Process data
END LOOP;
CLOSE v_emp_cursor;
END;
/
Performing DML with Dynamic SQL: Example
The following example includes a dynamic INSERT statement for a table with three columns.
v_stmt_str := 'INSERT INTO dept_new VALUES (:g_deptno, :g_dname, :g_loc)';
This example inserts a new row for which the column values are in the PL/SQL variables deptnumber, deptname, and location. Table 8-3 shows sample code that accomplishes this task with the DBMS_SQL package and native dynamic SQL.
Table 8-3 DML Operation Using the DBMS_SQL Package and Native Dynamic SQL
DBMS_SQL DML Operation Native Dynamic SQL DML Operation
DECLARE
v_stmt_str VARCHAR2(200);
v_cur_hdl NUMBER;
v_deptnumber NUMBER := 99;
v_deptname VARCHAR2(20);
v_location VARCHAR2(10);
v_rows_processed NUMBER;
BEGIN
v_stmt_str := 'INSERT INTO dept
VALUES (:g_deptno, :g_dname, :g_loc)';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str,
DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_dname', v_deptname);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_loc', v_location);
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/ DECLARE
v_stmt_str VARCHAR2(200);
v_deptnumber NUMBER := 99;
v_deptname VARCHAR2(20);
v_location VARCHAR2(10);
BEGIN
v_stmt_str := 'INSERT INTO dept
VALUES (:g_deptno, :g_dname, :g_loc)';
EXECUTE IMMEDIATE v_stmt_str
USING v_deptnumber, v_deptname,
v_location;
END;
/
Performing DML with RETURNING Clause Using Dynamic SQL: Example
The following example uses a dynamic UPDATE statement to update the location of a department, then returns the name of the department:
v_stmt_str := 'UPDATE dept_new
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
Table 8-4 shows sample code that accomplishes this operation using both the DBMS_SQL package and native dynamic SQL.
Table 8-4 DML Returning Operation Using the DBMS_SQL Package and Native Dynamic SQL
DBMS_SQL DML Returning Operation Native Dynamic SQL DML Returning Operation
DECLARE
deptname_array DBMS_SQL.VARCHAR2_TABLE;
v_cur_hdl INT;
v_stmt_str VARCHAR2(200);
v_location VARCHAR2(20);
v_deptnumber NUMBER := 10;
v_rows_procsd NUMBER;
BEGIN
v_stmt_str := 'UPDATE dept
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_newloc', v_location);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_ARRAY
(v_cur_hdl, ':g_dname', deptname_array);
-- Execute cursor
v_rows_procsd := DBMS_SQL.EXECUTE(v_cur_hdl);
-- Get RETURNING column into OUT bind array
DBMS_SQL.VARIABLE_VALUE
(v_cur_hdl, ':g_dname', deptname_array);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/ DECLARE
deptname_array DBMS_SQL.VARCHAR2_TABLE;
v_stmt_str VARCHAR2(200);
v_location VARCHAR2(20);
v_deptnumber NUMBER := 10;
v_deptname VARCHAR2(20);
BEGIN
v_stmt_str := 'UPDATE dept
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
EXECUTE IMMEDIATE v_stmt_str
USING v_location, v_deptnumber, OUT
v_deptname;
END;
/
UTIL_FILE
1.DEFINE UTIL_FILE,ADVANTAGES OF UTIL_FILE.
With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.
Rules and Limits
Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file name parameters.
UTL_FILE I/O capabilities are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitations. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any output and free resources associated with the file.
2.EXCEPTION @ FILE HANDLING.
Exceptions
Table 167-1 UTL_FILE Package Exceptions
Exception Name Description
INVALID_PATH File location is invalid.
INVALID_MODE The open_mode parameter in FOPEN is invalid.
INVALID_FILEHANDLE File handle is invalid.
INVALID_OPERATION File could not be opened or operated on as requested.
READ_ERROR Operating system error occurred during the read operation.
WRITE_ERROR Operating system error occurred during the write operation.
INTERNAL_ERROR Unspecified PL/SQL error
CHARSETMISMATCH A file is opened using FOPEN_NCHAR, but later I/O operations use nonchar functions such as PUTF or GET_LINE.
FILE_OPEN The requested operation failed because the file is open.
INVALID_MAXLINESIZE The MAX_LINESIZE value for FOPEN() is invalid; it should be within the range 1 to 32767.
INVALID_FILENAME The filename parameter is invalid.
ACCESS_DENIED Permission to access to the file location is denied.
INVALID_OFFSET Causes of the INVALID_OFFSET exception:
• ABSOLUTE_OFFSET = NULL and RELATIVE_OFFSET = NULL, or
• ABSOLUTE_OFFSET < 0, or
• Either offset caused a seek past the end of the file
DELETE_FAILED The requested file delete operation failed.
RENAME_FAILED The requested file rename operation failed.
Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.
3.METHODS OF UTIL_FILE (OPEN,CLOSE,PUT_LINE,....).
Summary of UTL_FILE Subprograms
Table 167-2 UTL_FILE Subprograms
Subprogram Description
FCLOSE Procedure
Closes a file
FCLOSE_ALL Procedure
Closes all open file handles
FCOPY Procedure
Copies a contiguous portion of a file to a newly created file
FFLUSH Procedure
Physically writes all pending output to a file
FGETATTR Procedure
Reads and returns the attributes of a disk file
FGETPOS Function
Returns the current relative offset position within a file, in bytes
FOPEN Function
Opens a file for input or output
FOPEN_NCHAR Function
Opens a file in Unicode for input or output
FREMOVE Procedure
Deletes a disk file, assuming that you have sufficient privileges
FRENAME Procedure
Renames an existing file to a new name, similar to the UNIX mv function
FSEEK Procedure
Adjusts the file pointer forward or backward within the file by the number of bytes specified
GET_LINE Procedure
Reads text from an open file
GET_LINE_NCHAR Procedure
Reads text in Unicode from an open file
GET_RAW Function
Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
IS_OPEN Function
Determines if a file handle refers to an open file
NEW_LINE Procedure
Writes one or more operating system-specific line terminators to a file
PUT Procedure
Writes a string to a file
PUT_LINE Procedure
Writes a line to a file, and so appends an operating system-specific line terminator
PUT_LINE_NCHAR Procedure
Writes a Unicode line to a file
PUT_NCHAR Procedure
Writes a Unicode string to a file
PUTF Procedure
A PUT procedure with formatting
PUTF_NCHAR Procedure
A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting
PUT_RAW Function
Accepts as input a RAW data value and writes the value to the output buffer
4.FILE STORAGE SPACE ,ORACLE DIRECTORY CONCEPT.
Operational Notes
The file location and file name parameters are supplied to the FOPEN function as separate strings, so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects. Together, the file location and name must represent a legal filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also accessible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.
UTL_FILE implicitly interprets line terminators on read requests, thereby affecting the number of bytes returned on a GET_LINE call. For example, the len parameter of UTL_FILE.GET_LINE specifies the requested number of bytes of character data. The number of bytes actually returned to the user will be the lesser of:
• The GET_LINE len parameter, or
• The number of bytes until the next line terminator character, or
• The max_linesize parameter specified by UTL_FILE.FOPEN
The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. The GET_LINE len parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies the default value of max_linesize. If max_linesize and len are defined to be different values, then the lesser value takes precedence.
UTL_FILE.GET_RAW ignores line terminators and returns the actual number of bytes requested by the GET_RAW len parameter.
When data encoded in one character set is read and Globalization Support is told (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NLS_LANG is set, it should be the same as the database character set.
DIRECTORY
Use the CREATE DIRECTORY statement to create a directory object. A directory object specifies an alias for a directory on the server's file system where external binary file LOBs (BFILEs) and external table data are located. You can use directory names when referring to BFILEs in your PL/SQL code and OCI calls, rather than hard coding the operating system path name, thereby providing greater file management flexibility.
All directories are created in a single namespace and are not owned by an individual's schema. You can secure access to the BFILEs stored within the directory structure by granting object privileges on the directories to specific users.
Creating a Directory: Examples
The following statement creates a directory database object that points to a directory on the server:
CREATE DIRECTORY admin AS 'oracle/admin';
The following statement redefines directory database object bfile_dir to enable access to BFILEs stored in the operating system directory /private1/lob/files:
CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';
JOB_SUBMIT
1.DEFINE JOB_SUBMIT,ADVANTAGES OF JOB_SUBMIT. & METHODS OF JOB_SUBMIT.
The DBMS_JOB package schedules and manages jobs in the job queue.
Working with Real Application Clusters
DBMS_JOB supports multi-instance execution of jobs. By default jobs can be executed on any instance, but only one single instance will execute the job. In addition, you can force instance binding by binding the job to a particular instance. You implement instance binding by specifying an instance number to the instance affinity parameter. Note, however, that in Oracle Database 10g Release 1 (10.1) instance binding is not recommended. Service affinity is preferred.
DBMS_JOB.SUBMIT
To submit a job to the job queue, use the following syntax:
DBMS_JOB.SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE,
interval IN VARCHAR2 DEFAULT 'NULL',
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
force IN BOOLEAN DEFAULT FALSE);
Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL.
The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428.
DBMS_JOB.INSTANCE
To assign a particular instance to execute a job, use the following syntax:
DBMS_JOB.INSTANCE( JOB IN BINARY_INTEGER,
instance IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);
The FORCE parameter in this example defaults to FALSE. If the instance value is 0 (zero), job affinity is altered and any available instance can execute the job despite the value of force. If the INSTANCE value is positive and the FORCE parameter is FALSE, job affinity is altered only if the specified instance is running, or Oracle displays error ORA-23428.
If the force parameter is TRUE, any positive integer is acceptable as the job instance and the job affinity is altered. Oracle displays error ORA-23319 if the instance value is negative or NULL.
DBMS_JOB.CHANGE
To alter user-definable parameters associated with a job, use the following syntax:
DBMS_JOB.CHANGE( JOB IN BINARY_INTEGER,
what IN VARCHAR2 DEFAULT NULL,
next_date IN DATE DEFAULT NULL,
interval IN VARCHAR2 DEFAULT NULL,
instance IN BINARY_INTEGER DEFAULT NULL,
force IN BOOLEAN DEFAULT FALSE );
Two parameters, instance and force, appear in this example. The default value of instance is null indicating that job affinity will not change.
The default value of force is FALSE. Oracle displays error ORA-23428 if the specified instance is not running and error ORA-23319 if the instance number is negative.
DBMS_JOB.RUN
The force parameter for DBMS_JOB.RUN defaults to FALSE. If force is TRUE, instance affinity is irrelevant for running jobs in the foreground process. If force is FALSE, the job can run in the foreground only in the specified instance. Oracle displays error ORA-23428 if force is FALSE and the connected instance is the incorrect instance.
DBMS_JOB.RUN(
job IN BINARY_INTEGER,
force IN BOOLEAN DEFAULT FALSE);
COLLECTIONS
1.DEFINE COLLECTIONS,ADVANTAGES OF COLLECTIONS.
Collections and records are composite types that have internal components that can be manipulated individually, such as the elements of an array, record, or table.
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other datatypes used in classic programming algorithms. Each element is addressed by a unique subscript.
A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns.
PL/SQL collection types let you declare high-level datatypes similar to arrays, sets, and hash tables found in other languages. In PL/SQL, array types are known as varrays (short for variable-size arrays), set types are known as nested tables, and hash table types are known as associative arrays. Each kind of collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection. When declaring collections, you use a TYPE definition.
2.ELEMENTS OF COLLECTIONS.
PL/SQL offers these collection types:
• Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.
• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
3.METHODS OF COLLECTIONS.
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections whose size is unknown or varies.
EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions that check the properties of a collection or individual collection elements. EXTEND, TRIM, and DELETE are procedures that modify a collection.
EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take integer parameters. EXISTS, PRIOR, NEXT, and DELETE can also take VARCHAR2 parameters for associative arrays with string keys. EXTEND and TRIM cannot be used with index-by tables.
2. Using Collection Methods
The following collection methods help generalize code, make collections easier to use, and make your applications easier to maintain:
EXISTS
COUNT
LIMIT
FIRST and LAST
PRIOR and NEXT
EXTEND
TRIM
DELETE
A collection method is a built-in function or procedure that operates on collections and is called using dot notation. The syntax follows:
collection_name.method_name[(parameters)]
Collection methods cannot be called from SQL statements. Also, EXTEND and TRIM cannot be used with associative arrays. EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions; EXTEND, TRIM, and DELETE are procedures. EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take parameters corresponding to collection subscripts, which are usually integers but can also be strings for associative arrays.
Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.
Checking If a Collection Element Exists (EXISTS Method)
EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. In the following example, PL/SQL executes the assignment statement only if element i exists:
IF courses.EXISTS(i) THEN courses(i) := new_course; END IF;
When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.
Counting the Elements in a Collection (COUNT Method)
COUNT returns the number of elements that a collection currently contains. For instance, if varray projects contains 25 elements, the following IF condition is true:
IF projects.COUNT = 25 THEN ...
COUNT is useful because the current size of a collection is not always known. For example, if you fetch a column of Oracle data into a nested table, how many elements does the table contain? COUNT gives you the answer.
You can use COUNT wherever an integer expression is allowed. In the next example, you use COUNT to specify the upper bound of a loop range:
FOR i IN 1..courses.COUNT LOOP ...
For varrays, COUNT always equals LAST. For nested tables, COUNT normally equals LAST. But, if you delete elements from the middle of a nested table, COUNT becomes smaller than LAST.
When tallying elements, COUNT ignores deleted elements.
Checking the Maximum Size of a Collection (LIMIT Method)
For nested tables and associative arrays, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition, and can change later with the TRIM and EXTEND methods). For instance, if the maximum size of varray PROJECTS is 25 elements, the following IF condition is true:
IF projects.LIMIT = 25 THEN ...
You can use LIMIT wherever an integer expression is allowed. In the following example, you use LIMIT to determine if you can add 15 more elements to varray projects:
IF (projects.COUNT + 15) < projects.LIMIT THEN ...
Finding the First or Last Collection Element (FIRST and LAST Methods)
FIRST and LAST return the first and last (smallest and largest) index numbers in a collection. For an associative array with VARCHAR2 key values, the lowest and highest key values are returned; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
If the collection is empty, FIRST and LAST return NULL.
If the collection contains only one element, FIRST and LAST return the same index value:
IF courses.FIRST = courses.LAST THEN ... -- only one element
The next example shows that you can use FIRST and LAST to specify the lower and upper bounds of a loop range provided each element in that range exists:
FOR i IN courses.FIRST..courses.LAST LOOP ...
In fact, you can use FIRST or LAST wherever an integer expression is allowed. In the following example, you use FIRST to initialize a loop counter:
i := courses.FIRST;
WHILE i IS NOT NULL LOOP ...
For varrays, FIRST always returns 1 and LAST always equals COUNT. For nested tables, FIRST normally returns 1. But, if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1. Also for nested tables, LAST normally equals COUNT. But, if you delete elements from the middle of a nested table, LAST becomes larger than COUNT.
When scanning elements, FIRST and LAST ignore deleted elements.
Looping Through Collection Elements (PRIOR and NEXT Methods)
PRIOR(n) returns the index number that precedes index n in a collection. NEXT(n) returns the index number that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. Likewise, if n has no successor, NEXT(n) returns NULL.
For associative arrays with VARCHAR2 keys, these methods return the appropriate key value; ordering is based on the binary values of the characters in the string, unless the NLS_COMP initialization parameter is set to ANSI, in which case the ordering is based on the locale-specific sort order specified by the NLS_SORT initialization parameter.
These methods are more reliable than looping through a fixed set of subscript values, because elements might be inserted or deleted from the collection during the loop. This is especially true for associative arrays, where the subscripts might not be in consecutive order and so the sequence of subscripts might be (1,2,4,8,16) or ('A','E','I','O','U').
PRIOR and NEXT do not wrap from one end of a collection to the other. For example, the following statement assigns NULL to n because the first element in a collection has no predecessor:
n := courses.PRIOR(courses.FIRST); -- assigns NULL to n
PRIOR is the inverse of NEXT. For instance, if element i exists, the following statement assigns element i to itself:
projects(i) := projects.PRIOR(projects.NEXT(i));
You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. In the following example, you use NEXT to traverse a nested table from which some elements have been deleted:
i := courses.FIRST; -- get subscript of first element
WHILE i IS NOT NULL LOOP
-- do something with courses(i)
i := courses.NEXT(i); -- get subscript of next element
END LOOP;
When traversing elements, PRIOR and NEXT ignore deleted elements.
Increasing the Size of a Collection (EXTEND Method)
To increase the size of a nested table or varray, use EXTEND. You cannot use EXTEND with index-by tables.
This procedure has three forms:
• EXTEND appends one null element to a collection.
• EXTEND(n) appends n null elements to a collection.
• EXTEND(n,i) appends n copies of the ith element to a collection.
For example, the following statement appends 5 copies of element 1 to nested table courses:
courses.EXTEND(5,1);
You cannot use EXTEND to initialize an atomically null collection. Also, if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.
EXTEND operates on the internal size of a collection, which includes any deleted elements. So, if EXTEND encounters deleted elements, it includes them in its tally. PL/SQL keeps placeholders for deleted elements so that you can replace them if you wish. Consider the following example:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(3); -- delete element 3
/* PL/SQL keeps a placeholder for element 3. So, the
next statement appends element 4, not element 3. */
courses.EXTEND; -- append one null element
/* Now element 4 exists, so the next statement does
not raise SUBSCRIPT_BEYOND_COUNT. */
courses(4) := 'Engl 2005';
When it includes deleted elements, the internal size of a nested table differs from the values returned by COUNT and LAST. For instance, if you initialize a nested table with five elements, then delete elements 2 and 5, the internal size is 5, COUNT returns 3, and LAST returns 4. All deleted elements (whether leading, in the middle, or trailing) are treated alike.
Decreasing the Size of a Collection (TRIM Method)
This procedure has two forms:
• TRIM removes one element from the end of a collection.
• TRIM(n) removes n elements from the end of a collection.
For example, this statement removes the last three elements from nested table courses:
courses.TRIM(3);
If n is too large, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT.
TRIM operates on the internal size of a collection. So, if TRIM encounters deleted elements, it includes them in its tally. Consider the following example:
DECLARE
TYPE CourseList IS TABLE OF VARCHAR2(10);
courses CourseList;
BEGIN
courses := CourseList('Biol 4412', 'Psyc 3112', 'Anth 3001');
courses.DELETE(courses.LAST); -- delete element 3
/* At this point, COUNT equals 2, the number of valid
elements remaining. So, you might expect the next
statement to empty the nested table by trimming
elements 1 and 2. Instead, it trims valid element 2
and deleted element 3 because TRIM includes deleted
elements in its tally. */
courses.TRIM(courses.COUNT);
dbms_output.put_line(courses(1)); -- prints 'Biol 4412'
In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.
PL/SQL does not keep placeholders for trimmed elements. So, you cannot replace a trimmed element simply by assigning it a new value.
Deleting Collection Elements (DELETE Method)
This procedure has various forms:
• DELETE removes all elements from a collection.
• DELETE(n) removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
• DELETE(m,n) removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.
For example:
BEGIN
courses.DELETE(2); -- deletes element 2
courses.DELETE(7,7); -- deletes element 7
courses.DELETE(6,3); -- does nothing
courses.DELETE(3,6); -- deletes elements 3 through 6
projects.DELETE; -- deletes all elements
nicknames.DELETE('Chip'); -- deletes element denoted by this key
nicknames.DELETE('Buffy','Fluffy'); -- deletes elements with keys
-- in this alphabetic range
END;
Varrays are dense, so you cannot delete their individual elements.
If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. PL/SQL keeps placeholders for deleted elements. So, you can replace a deleted element simply by assigning it a new value.
DELETE lets you maintain sparse nested tables. In the following example, you retrieve nested table prospects into a temporary table, prune it, then store it back in the database:
DECLARE
my_prospects ProspectList;
revenue NUMBER;
BEGIN
SELECT prospects INTO my_prospects FROM customers WHERE ...
FOR i IN my_prospects.FIRST..my_prospects.LAST LOOP
estimate_revenue(my_prospects(i), revenue); -- call procedure
IF revenue < 25000 THEN
my_prospects.DELETE(i);
END IF;
END LOOP;
UPDATE customers SET prospects = my_prospects WHERE ...
The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.
4.VARRAY VS PL/SQL TABLE VS NESTED TABLE.
Choosing Which PL/SQL Collection Types to Use
If you already have code or business logic that uses some other language, you can usually translate that language's array and set types directly to PL/SQL collection types.
• Arrays in other languages become varrays in PL/SQL.
• Sets and bags in other languages become nested tables in PL/SQL.
• Hash tables and other kinds of unordered lookup tables in other languages become associative arrays in PL/SQL.
When you are writing original code or designing the business logic from the start, you should consider the strengths of each collection type to decide which is appropriate for each situation.
Choosing Between Nested Tables and Associative Arrays
Both nested tables and associative arrays (formerly known as index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.
Nested tables can be stored in a database column, but associative arrays cannot. Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.
Associative arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because associative array subscripts can be negative, can be nonsequential, and can use string values instead of numbers.
PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).
Choosing Between Nested Tables and Varrays
Varrays are a good choice when:
• The number of elements is known in advance.
• The elements are usually all accessed in sequence.
When stored in the database, varrays keep their ordering and subscripts.
Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). You must update or retrieve all elements of the varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.
Nested tables are a good choice when:
• The index values are not consecutive.
• There is no set number of index values. However, a maximum limit is imposed. See "Referencing Collection Elements".
• You need to delete or update some elements, but not all the elements at once.
• You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.
Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.
Nested table data is stored in a separate store table, a system-generated database table associated with the nested table. The database joins the tables for you when you access the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.
You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.
5.PLS_INTEGER VS BINARY_INTEGER.
BINARY_INTEGER
You use the BINARY_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. Like PLS_INTEGER values, BINARY_INTEGER values require less storage than NUMBER values. However, most BINARY_INTEGER operations are slower than PLS_INTEGER operations.
BINARY_INTEGER Subtypes
A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following BINARY_INTEGER subtypes:
NATURAL
NATURALN
POSITIVE
POSITIVEN
SIGNTYPE
The subtypes NATURAL and POSITIVE let you restrict an integer variable to non-negative or positive values, respectively. NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. SIGNTYPE lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.
PLS_INTEGER
You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range.
Although PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. When a PLS_INTEGER calculation overflows, an exception is raised. However, when a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.
Because of this small semantic difference, you might want to continue using BINARY_INTEGER in old applications for compatibility. In new applications, always use PLS_INTEGER for better performance.
PL/SQL offers these collection types:
• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
• Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.
6.DEFINE BULK COLLECT
BULK BIND
Oracle uses two engines to run PL/SQL blocks and subprograms. The PL/SQL engine runs procedural statements, while the SQL engine runs SQL statements. During execution, every SQL statement causes a context switch between the two engines, resulting in performance overhead.
Performance can be improved substantially by minimizing the number of context switches required to run a particular block or subprogram. When a SQL statement runs inside a loop that uses collection elements as bind variables, the large number of context switches required by the block can cause poor performance. Collections include the following:
• Varrays
• Nested tables
• Index-by tables
• Host arrays
Binding is the assignment of values to PL/SQL variables in SQL statements. Bulk binding is binding an entire collection at once. Bulk binds pass the entire collection back and forth between the two engines in a single operation.
Typically, using bulk binds improves performance for SQL statements that affect four or more database rows. The more rows affected by a SQL statement, the greater the performance gain from bulk binds.
When to Use Bulk Binds
If you have scenarios like these in your applications, consider using bulk binds to improve performance.
A. DML Statements that Reference Collections
The FORALL keyword can improve the performance of INSERT, UPDATE, or DELETE statements that reference collection elements.
For example, the following PL/SQL block increases the salary for employees whose manager's ID number is 7902, 7698, or 7839, both with and without using bulk binds:
DECLARE
TYPE Numlist IS VARRAY (100) OF NUMBER;
Id NUMLIST := NUMLIST(7902, 7698, 7839);
BEGIN
-- Efficient method, using a bulk bind
FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY
UPDATE Emp_tab SET Sal = 1.1 * Sal
WHERE Mgr = Id(i);
-- Slower method, running the UPDATE statements within a regular loop
FOR i IN Id.FIRST..Id.LAST LOOP
UPDATE Emp_tab SET Sal = 1.1 * Sal
WHERE Mgr = Id(i);
END LOOP;
END;
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.
If you have a set of rows prepared in a PL/SQL table, you can bulk-insert or bulk-update the data using a loop like:
FORALL i in Emp_Data.FIRST..Emp_Data.LAST
INSERT INTO Emp_tab VALUES(Emp_Data(i));
B. SELECT Statements that Reference Collections
The BULK COLLECT INTO clause can improve the performance of queries that reference collections.
For example, the following PL/SQL block queries multiple values into PL/SQL tables, both with and without bulk binds:
-- Find all employees whose manager's ID number is 7698.
DECLARE
TYPE Var_tab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
Empno VAR_TAB;
Ename VAR_TAB;
Counter NUMBER;
CURSOR C IS
SELECT Empno, Ename FROM Emp_tab WHERE Mgr = 7698;
BEGIN
-- Efficient method, using a bulk bind
SELECT Empno, Ename BULK COLLECT INTO Empno, Ename
FROM Emp_Tab WHERE Mgr = 7698;
-- Slower method, assigning each collection element within a loop.
counter := 1;
FOR rec IN C LOOP
Empno(Counter) := rec.Empno;
Ename(Counter) := rec.Ename;
Counter := Counter + 1;
END LOOP;
END;
You can use BULK COLLECT INTO with tables of scalar values, or tables of %TYPE values.
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is selected, leading to context switches that hurt performance.
C.FOR Loops that Reference Collections and the Returning Into Clause
You can use the FORALL keyword along with the BULK COLLECT INTO keywords to improve the performance of FOR loops that reference collections and return DML.
For example, the following PL/SQL block updates the Emp_tab table by computing bonuses for a collection of employees; then it returns the bonuses in a column called Bonlist. The actions are performed both with and without using bulk binds:
DECLARE
TYPE Emplist IS VARRAY(100) OF NUMBER;
Empids EMPLIST := EMPLIST(7369, 7499, 7521, 7566, 7654, 7698);
TYPE Bonlist IS TABLE OF Emp_tab.sal%TYPE;
Bonlist_inst BONLIST;
BEGIN
Bonlist_inst := BONLIST(1,2,3,4,5);
FORALL i IN Empids.FIRST..empIDs.LAST
UPDATE Emp_tab SET Bonus = 0.1 * Sal
WHERE Empno = Empids(i)
RETURNING Sal BULK COLLECT INTO Bonlist;
FOR i IN Empids.FIRST..Empids.LAST LOOP
UPDATE Emp_tab Set Bonus = 0.1 * sal
WHERE Empno = Empids(i)
RETURNING Sal INTO BONLIST(i);
END LOOP;
END;
Without the bulk bind, PL/SQL sends a SQL statement to the SQL engine for each employee that is updated, leading to context switches that hurt performance.
2. Reducing Loop Overhead for Collections with Bulk Binds
As Figure 5-3 shows, the PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine, which executes the SQL statements and, in some cases, returns data to the PL/SQL engine.
Figure 5-3 Context Switching
Text description of the illustration pls81027_context_switching.gif
Too many context switches between the PL/SQL and SQL engines can harm performance. That can happen when a loop executes a separate SQL statement for each element of a collection, specifying the collection element as a bind variable. For example, the following DELETE statement is sent to the SQL engine with each iteration of the FOR loop:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST LOOP
DELETE FROM emp WHERE deptno = depts(i);
END LOOP;
END;
In such cases, if the SQL statement affects four or more database rows, the use of bulk binds can improve performance considerably.
How Do Bulk Binds Improve Performance?
The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:
• in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
• out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
A DML statement can transfer all the elements of a collection in a single operation, a process known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. With bulk binds, entire collections, not just individual elements, are passed back and forth.
To do bulk binds with INSERT, UPDATE, and DELETE statements, you enclose the SQL statement within a PL/SQL FORALL statement.
To do bulk binds with SELECT statements, you include the BULK COLLECT clause in the SELECT statement instead of using INTO.
For full details of the syntax and restrictions for these statements, see "FORALL Statement" and "SELECT INTO Statement".
Example: Performing a Bulk Bind with DELETE
The following DELETE statement is sent to the SQL engine just once, even though it performs three DELETE operations:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
Example: Performing a Bulk Bind with INSERT
In the example below, 5000 part numbers and names are loaded into index-by tables. All table elements are inserted into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9
10
11 BEGIN
12 FOR j IN 1..5000 LOOP -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := 'Part No. ' || TO_CHAR(j);
15 END LOOP;
16 t1 := dbms_utility.get_time;
17 FOR i IN 1..5000 LOOP -- use FOR loop
18 INSERT INTO parts VALUES (pnums(i), pnames(i));
19 END LOOP;
20 t2 := dbms_utility.get_time;
21 FORALL i IN 1..5000 -- use FORALL statement
22 INSERT INTO parts VALUES (pnums(i), pnames(i));
23 get_time(t3);
24 dbms_output.put_line('Execution Time (secs)');
25 dbms_output.put_line('---------------------');
26 dbms_output.put_line('FOR loop: ' || TO_CHAR(t2 - t1));
27 dbms_output.put_line('FORALL: ' || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL: 3
PL/SQL procedure successfully completed.
2. Retrieving Query Results into Collections with the BULK COLLECT Clause
The keywords BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:
... BULK COLLECT INTO collection_name[, collection_name] ...
The SQL engine bulk-binds all collections referenced in the INTO list. The corresponding columns can store scalar or composite values including objects. In the following example, the SQL engine loads the entire empno and ename database columns into nested tables before returning the tables to the PL/SQL engine:
DECLARE
TYPE NumTab IS TABLE OF emp.empno%TYPE;
TYPE NameTab IS TABLE OF emp.ename%TYPE;
enums NumTab; -- no need to initialize
names NameTab;
BEGIN
SELECT empno, ename BULK COLLECT INTO enums, names FROM emp;
...
END;
In the next example, the SQL engine loads all the values in an object column into a nested table before returning the table to the PL/SQL engine:
CREATE TYPE Coords AS OBJECT (x NUMBER, y NUMBER);
CREATE TABLE grid (num NUMBER, loc Coords);
INSERT INTO grid VALUES(10, Coords(1,2));
INSERT INTO grid VALUES(20, Coords(3,4));
DECLARE
TYPE CoordsTab IS TABLE OF Coords;
pairs CoordsTab;
BEGIN
SELECT loc BULK COLLECT INTO pairs FROM grid;
-- now pairs contains (1,2) and (3,4)
END;
The SQL engine initializes and extends collections for you. (However, it cannot extend varrays beyond their maximum size.) Then, starting at index 1, it inserts elements consecutively and overwrites any pre-existent elements.
The SQL engine bulk-binds entire database columns. So, if a table has 50,000 rows, the engine loads 50,000 column values into the target collection. However, you can use the pseudocolumn ROWNUM to limit the number of rows processed. In the following example, you limit the number of rows to 100:
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
...
END;
Examples of Bulk Fetching from a Cursor
Into One or More Collections
You can bulk-fetch from a cursor into one or more collections:
DECLARE
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
CURSOR c1 IS SELECT ename, sal FROM emp WHERE sal > 1000;
names NameList;
sals SalList;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
END;
Into a Collection of Records
You can bulk-fetch from a cursor into a collection of records:
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
Limiting the Rows for a Bulk FETCH Operation with the LIMIT Clause
The optional LIMIT clause, allowed only in bulk (not scalar) FETCH statements, lets you limit the number of rows fetched from the database. The syntax is
FETCH ... BULK COLLECT INTO ... [LIMIT rows];
where rows can be a literal, variable, or expression but must evaluate to a number. Otherwise, PL/SQL raises the predefined exception VALUE_ERROR. If the number is not positive, PL/SQL raises INVALID_NUMBER. If necessary, PL/SQL rounds the number to the nearest integer.
In the example below, with each iteration of the loop, the FETCH statement fetches ten rows (or less) into index-by table empnos. The previous values are overwritten.
DECLARE
TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
CURSOR c1 IS SELECT empno FROM emp;
empnos NumTab;
rows NATURAL := 10;
BEGIN
OPEN c1;
LOOP
/* The following statement fetches 10 rows (or less). */
FETCH c1 BULK COLLECT INTO empnos LIMIT rows;
EXIT WHEN c1%NOTFOUND;
...
END LOOP;
CLOSE c1;
END;
Retrieving DML Results into a Collection with the RETURNING INTO Clause
You can use the BULK COLLECT clause in the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement, as the following example shows:
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;
BEGIN
DELETE FROM emp WHERE deptno = 20
RETURNING empno BULK COLLECT INTO enums;
-- if there were five employees in department 20,
-- then enums contains five employee numbers
END;
Restrictions on BULK COLLECT
The following restrictions apply to the BULK COLLECT clause:
• You cannot bulk collect into an associative array that has a string type for the key.
• You can use the BULK COLLECT clause only in server-side programs (not in client-side programs). Otherwise, you get the error this feature is not supported in client-side programs.
• All targets in a BULK COLLECT INTO clause must be collections, as the following example shows:
• DECLARE
• TYPE NameList IS TABLE OF emp.ename%TYPE;
• names NameList;
• salary emp.sal%TYPE;
• BEGIN
• SELECT ename, sal BULK COLLECT INTO names, salary -- illegal target
• FROM emp WHERE ROWNUM < 50;
• ...
• END;
•
• Composite targets (such as objects) cannot be used in the RETURNING INTO clause. Otherwise, you get the error unsupported feature with RETURNING clause.
• When implicit datatype conversions are needed, multiple composite targets cannot be used in the BULK COLLECT INTO clause.
• When an implicit datatype conversion is needed, a collection of a composite target (such as a collection of objects) cannot be used in the BULK COLLECT INTO clause.
Using FORALL and BULK COLLECT Together
You can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk-binds column values incrementally. In the following example, if collection depts has 3 elements, each of which causes 5 rows to be deleted, then collection enums has 15 elements when the statement completes:
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE empno = depts(j)
RETURNING empno BULK COLLECT INTO enums;
The column values returned by each execution are added to the values returned previously. (With a FOR loop, the previous values are overwritten.)
You cannot use the SELECT ... BULK COLLECT statement in a FORALL statement. Otherwise, you get the error implementation restriction: cannot use FORALL and BULK COLLECT INTO together in SELECT statements.
7. FOR ALL.
The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code, because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses.
Using the FORALL Statement
The keyword FORALL instructs the PL/SQL engine to bulk-bind input collections before sending them to the SQL engine. Although the FORALL statement contains an iteration scheme, it is not a FOR loop. Its syntax follows:
FORALL index IN lower_bound..upper_bound
sql_statement;
The index can be referenced only within the FORALL statement and only as a collection subscript. The SQL statement must be an INSERT, UPDATE, or DELETE statement that references collection elements. And, the bounds must specify a valid range of consecutive index numbers. The SQL engine executes the SQL statement once for each index number in the range.
Example: Using FORALL with Part of a Collection
As the following example shows, the bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements:
DECLARE
TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(20,30,50,55,57,60,70,75,90,92);
BEGIN
FORALL j IN 4..7 -- bulk-bind only part of varray
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
END;
Example: Bulk Bind Requires Subscripted Collection
The SQL statement can reference more than one collection. However, the PL/SQL engine bulk-binds only subscripted collections. So, in the following example, it does not bulk-bind the collection sals, which is passed to the function median:
FORALL i IN 1..20
INSERT INTO emp2 VALUES (enums(i), names(i), median(sals), ...);
Example: Inserting into an Object Table with FORALL
In addition to relational tables, the FORALL statement can manipulate object tables, as the following example shows:
CREATE TYPE PNum AS OBJECT (n NUMBER);
/
CREATE TABLE partno OF PNum;
DECLARE
TYPE NumTab IS TABLE OF NUMBER;
nums NumTab := NumTab(1, 2, 3, 4);
TYPE PNumTab IS TABLE OF PNum;
pnums PNumTab := PNumTab(PNum(1), PNum(2), PNum(3), PNum(4));
BEGIN
FORALL i IN pnums.FIRST..pnums.LAST
INSERT INTO partno VALUES(pnums(i));
FORALL i IN nums.FIRST..nums.LAST
DELETE FROM partno WHERE n = 2 * nums(i);
FORALL i IN nums.FIRST..nums.LAST
INSERT INTO partno VALUES(100 + nums(i));
END;
How FORALL Affects Rollbacks
In a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as follows:
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(15));
Next, you insert some rows into the table, as follows:
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(10, 'Clerk');
INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- 10-char job title
INSERT INTO emp2 VALUES(30, 'Analyst');
INSERT INTO emp2 VALUES(30, 'Analyst');
Then, you try to append the 7-character string ' (temp)' to certain job titles using the following UPDATE statement:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 30);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp2 SET job = job || ' (temp)'
WHERE deptno = depts(j);
-- raises a "value too large" exception
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END;
The SQL engine executes the UPDATE statement three times, once for each index number in the specified range, that is, once for depts(10), once for depts(20), and once for depts(30). The first execution succeeds, but the second execution fails because the string value 'Bookkeeper (temp)' is too large for the job column. In this case, only the second execution is rolled back.
When any execution of the SQL statement raises an exception, the FORALL statement halts. In our example, the second execution of the UPDATE statement raises an exception, so the third execution is never done.
Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attribute
To process SQL data manipulation statements, the SQL engine opens an implicit cursor named SQL. This cursor's scalar attributes, %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT, return useful information about the most recently executed SQL data manipulation statement.
The SQL cursor has one composite attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. This attribute has the semantics of an index-by table. Its ith element stores the number of rows processed by the ith execution of an INSERT, UPDATE or DELETE statement. If the ith execution affects no rows, %BULK_ROWCOUNT(i) returns zero. An example follows:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts NumList := NumList(10, 20, 50);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(j);
-- Did the 3rd UPDATE statement affect any rows?
IF SQL%BULK_ROWCOUNT(3) = 0 THEN ...
END;
The FORALL statement and %BULK_ROWCOUNT attribute use the same subscripts. For example, if FORALL uses the range 5 .. 10, so does %BULK_ROWCOUNT.
%BULK_ROWCOUNT is usually equal to 1 for inserts, because a typical insert operation affects only a single row. But for the INSERT ... SELECT construct, %BULK_ROWCOUNT might be greater than 1. For example, the FORALL statement below inserts an arbitrary number of rows for each iteration. After each iteration, %BULK_ROWCOUNT returns the number of items inserted:
SET SERVEROUTPUT ON;
DECLARE
TYPE num_tab IS TABLE OF NUMBER;
deptnums num_tab;
BEGIN
SELECT deptno BULK COLLECT INTO deptnums FROM DEPT;
FORALL i IN 1..deptnums.COUNT
INSERT INTO emp_by_dept
SELECT empno, deptno FROM emp WHERE deptno =
deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
-- Count how many rows were inserted for each department; that is,
-- how many employees are in each department.
dbms_output.put_line('Dept '||deptnums(i)||': inserted '||
SQL%BULK_ROWCOUNT(i)||' records');
END LOOP;
dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT);
END;
/
You can also use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT with bulk binds. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement.
%FOUND and %NOTFOUND refer only to the last execution of the SQL statement. However, you can use %BULK_ROWCOUNT to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute
PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.
To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement. The syntax follows:
FORALL index IN lower_bound..upper_bound SAVE EXCEPTIONS
{insert_stmt | update_stmt | delete_stmt}
All exceptions raised during the execution are saved in the new cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields. The first field, %BULK_EXCEPTIONS(i).ERROR_INDEX, holds the "iteration" of the FORALL statement during which the exception was raised. The second field, %BULK_EXCEPTIONS(i).ERROR_CODE, holds the corresponding Oracle error code.
The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in the count attribute of %BULK_EXCEPTIONS, that is, %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.
The following example shows how useful the cursor attribute %BULK_EXCEPTIONS can be:
DECLARE
TYPE NumList IS TABLE OF NUMBER;
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp WHERE sal > 500000/num_tab(i);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
In this example, PL/SQL raised the predefined exception ZERO_DIVIDE when i equaled 2, 6, 10. After the bulk-bind completed, SQL%BULK_EXCEPTIONS.COUNT returned 3, and the contents of SQL%BULK_EXCEPTIONS were (2,1476), (6,1476), and (10,1476). To get the Oracle error message (which includes the code), we negated the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE and passed the result to the error-reporting function SQLERRM, which expects a negative number. Here is the output:
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
Keyword and Parameter Description
INDICES OF collection_name
A clause specifying that the values of the index variable correspond to the subscripts of the elements of the specified collection. With this clause, you can use FORALL with nested tables where some elements have been deleted, or with associative arrays that have numeric subscripts.
BETWEEN lower_bound AND upper_bound
Limits the range of subscripts in the INDICES OF clause. If a subscript in the range does not exist in the collection, that subscript is skipped.
VALUES OF index_collection_name
A clause specifying that the subscripts for the FORALL index variable are taken from the values of the elements in another collection, specified by index_collection_name. This other collection acts as a set of pointers; FORALL can iterate through subscripts in arbitrary order, even using the same subscript more than once, depending on what elements you include in index_collection_name.
The index collection must be a nested table, or an associative array indexed by PLS_INTEGER or BINARY_INTEGER, whose elements are also PLS_INTEGER or BINARY_INTEGER. If the index collection is empty, an exception is raised and the FORALL statement is not executed.
index_name
An undeclared identifier that can be referenced only within the FORALL statement and only as a collection subscript.
The implicit declaration of index_name overrides any other declaration outside the loop. You cannot refer to another variable with the same name inside the statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a value.
lower_bound .. upper_bound
Numeric expressions that specify a valid range of consecutive index numbers. PL/SQL rounds them to the nearest integer, if necessary. The SQL engine executes the SQL statement once for each index number in the range. The expressions are evaluated once, when the FORALL statement is entered.
SAVE EXCEPTIONS
Optional keywords that cause the FORALL loop to continue even if some DML operations fail. Instead of raising an exception immediately, the program raises a single exception after the FORALL statement finishes. The details of the errors are available after the loop in SQL%BULK_EXCEPTIONS. The program can report or clean up all the errors after the FORALL loop, rather than handling each exception as it happens. See "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute".
sql_statement
A static, such as UPDATE or DELETE, or dynamic (EXECUTE IMMEDIATE) DML statement that references collection elements in the VALUES or WHERE clauses.
Usage Notes
Although the SQL statement can reference more than one collection, the performance benefits apply only to subscripted collections.
If a FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous iterations of the FORALL loop are not rolled back.
Restrictions
The following restrictions apply to the FORALL statement:
• You cannot loop through the elements of an associative array that has a string type for the key.
• Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.
• You can use the FORALL statement only in server-side programs, not in client-side programs.
• The INSERT, UPDATE, or DELETE statement must reference at least one collection. For example, a FORALL statement that inserts a set of constant values in a loop raises an exception.
• When you specify an explicit range, all collection elements in that range must exist. If an element is missing or was deleted, you get an error.
• When you use the INDICES OF or VALUES OF clauses, all the collections referenced in the DML statement must have subscripts matching the values of the index variable. Make sure that any DELETE, EXTEND, and so on operations are applied to all the collections so that they have the same set of subscripts. If any of the collections is missing a referenced element, you get an error. If you use the SAVE EXCEPTIONS clause, this error is treated like any other error and does not stop the FORALL statement.
• You cannot refer to individual record fields within DML statements called by a FORALL statement. Instead, you can specify the entire record with the SET ROW clause in an UPDATE statement, or the VALUES clause in an INSERT statement.
• Collection subscripts must be just the index variable rather than an expression, such as i rather than i+1.
• The cursor attribute %BULK_ROWCOUNT cannot be assigned to other collections, or be passed as a parameter to subprograms.
7.HOW COLLECTION WILL HELP IN PERFORMANCE TUNING.
Reducing Loop Overhead for DML Statements and Queries with Bulk SQL
PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the result data to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL language features known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULK COLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably.
The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:
• in-bind: When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
• out-bind: When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
• define: When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.
Bulk SQL uses PL/SQL collections, such as varrays or nested tables, to pass large amounts of data back and forth in a single operation. This process is known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. Queries can pass back any number of results, without requiring a FETCH statement for each row.
To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.
Using the FORALL Statement
The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop.
The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code, because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses.
Tuning Dynamic SQL with EXECUTE IMMEDIATE and Cursor Variables
Some programs (a general-purpose report writer for example) must build and process a variety of SQL statements, where the exact text of the statement is unknown until run time. Such statements probably change from execution to execution. They are called dynamic SQL statements.
Formerly, to execute dynamic SQL statements, you had to use the supplied package DBMS_SQL. Now, within PL/SQL, you can execute any kind of dynamic SQL statement using an interface called native dynamic SQL. The main PL/SQL features involved are the EXECUTE IMMEDIATE statement and cursor variables (also known as REF CURSORs).
Native dynamic SQL code is more compact and much faster than calling the DBMS_SQL package. The following example declares a cursor variable, then associates it with a dynamic SELECT statement:
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
emp_cv EmpCurTyp;
v_ename VARCHAR2(15);
v_sal NUMBER := 1000;
table_name VARCHAR2(30) := 'employees';
BEGIN
OPEN emp_cv FOR 'SELECT last_name, salary FROM ' || table_name ||
' WHERE salary > :s' USING v_sal;
CLOSE emp_cv;
END;
/
Tuning PL/SQL Procedure Calls with the NOCOPY Compiler Hint
By default, OUT and IN OUT parameters are passed by value. The values of any IN OUT parameters are copied before the subprogram is executed. During subprogram execution, temporary variables hold the output parameter values. If the subprogram exits normally, these values are copied to the actual parameters. If the subprogram exits with an unhandled exception, the original parameters are unchanged.
When the parameters represent large data structures such as collections, records, and instances of object types, this copying slows down execution and uses up memory. In particular, this overhead applies to each call to an object method: temporary copies are made of all the attributes, so that any changes made by the method are only applied if the method exits normally.
To avoid this overhead, you can specify the NOCOPY hint, which allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference. If the subprogram exits normally, the behavior is the same as normal. If the subprogram exits early with an exception, the values of OUT and IN OUT parameters (or object attributes) might still change. To use this technique, ensure that the subprogram handles all exceptions.
The following example asks the compiler to pass IN OUT parameter v_staff by reference, to avoid copying the varray on entry to and exit from the subprogram:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (v_staff IN OUT NOCOPY Staff) IS ...
Example 11-17 loads 25,000 records into a local nested table, which is passed to two local procedures that do nothing. A call to the procedure that uses NOCOPY takes much less time.
Example 11-17 Using NOCOPY With Parameters
DECLARE
TYPE EmpTabTyp IS TABLE OF employees%ROWTYPE;
emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
PROCEDURE get_time (t OUT NUMBER) IS
BEGIN t := DBMS_UTILITY.get_time; END;
PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
BEGIN NULL; END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
BEGIN NULL; END;
BEGIN
SELECT * INTO emp_tab(1) FROM employees WHERE employee_id = 100;
emp_tab.EXTEND(49999, 1); -- copy element 1 into 2..50000
get_time(t1);
do_nothing1(emp_tab); -- pass IN OUT parameter
get_time(t2);
do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
get_time(t3);
DBMS_OUTPUT.PUT_LINE('Call Duration (secs)');
DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR((t2 - t1)/100.0));
DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR((t3 - t2))/100.0);
END;
/
Restrictions on NOCOPY
The use of NOCOPY increases the likelihood of parameter aliasing
Remember, NOCOPY is a hint, not a directive. In the following cases, the PL/SQL compiler ignores the NOCOPY hint and uses the by-value parameter-passing method; no error is generated:
• The actual parameter is an element of an associative array. This restriction does not apply if the parameter is an entire associative array.
• The actual parameter is constrained, such as by scale or NOT NULL. This restriction does not apply to size-constrained character strings. This restriction does not extend to constrained elements or attributes of composite types.
• The actual and formal parameters are records, one or both records were declared using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ.
• The actual and formal parameters are records, the actual parameter was declared (implicitly) as the index of a cursor FOR loop, and constraints on corresponding fields in the records differ.
• Passing the actual parameter requires an implicit datatype conversion.
• The subprogram is called through a database link or as an external procedure.
OVERVIEW OF PRODUCT-SPECIFIC PACKAGES
Oracle and various Oracle tools are supplied with product-specific packages that define application programming interfaces (APIs) you can call from PL/SQL, SQL, Java, or other programming environments. Here we mention a few of the more widely used ones. For more information, see Oracle Database PL/SQL Packages and Types Reference.
About the DBMS_ALERT Package
Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous (that is, they operate independently of any timing mechanism). For example, a company might use this package to update the value of its investment portfolio as new stock and bond quotes arrive.
About the DBMS_OUTPUT Package
Package DBMS_OUTPUT enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information. The procedure PUT_LINE outputs information to a buffer that can be read by another trigger, procedure, or package. You display the information by calling the procedure GET_LINE or by setting SERVEROUTPUT ON in SQL*Plus. Example 9-4 shows how to display output from a PL/SQL block.
Example 9-4 Using PUT_LINE in the DBMS_OUTPUT Package
REM set server output to ON to display output from DBMS_OUTPUT
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE('These are the tables that ' || USER || ' owns:');
FOR item IN (SELECT table_name FROM user_tables)
LOOP
DBMS_OUTPUT.PUT_LINE(item.table_name);
END LOOP;
END;
/
About the DBMS_PIPE Package
Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures PACK_MESSAGE and SEND_MESSAGE to pack a message into a pipe, then send it to another session in the same instance or to a waiting application such as a UNIX program.
At the other end of the pipe, you can use the procedures RECEIVE_MESSAGE and UNPACK_MESSAGE to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write a C program to collect data, then send it through pipes to stored procedures in an Oracle database.
About the HTF and HTP Packages
Packages HTF and HTP allow your PL/SQL programs to generate HTML tags.
About the UTL_FILE Package
Package UTL_FILE lets PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.
When you want to read or write a text file, you call the function FOPEN, which returns a file handle for use in subsequent procedure calls. For example, the procedure PUT_LINE writes a text string and line terminator to an open file, and the procedure GET_LINE reads a line of text from an open file into an output buffer.
About the UTL_HTTP Package
Package UTL_HTTP allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. It can retrieve data from the Internet or call Oracle Web Server cartridges. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in hypertext markup language (HTML) format.
About the UTL_SMTP Package
Package UTL_SMTP allows your PL/SQL programs to send electronic mails (emails) over Simple Mail Transfer Protocol (SMTP). The package provides interfaces to the SMTP commands for an email client to dispatch emails to a SMTP server.
Guidelines for Writing Packages
When writing packages, keep them general so they can be reused in future applications. Become familiar with the Oracle-supplied packages, and avoid writing packages that duplicate features already provided by Oracle.
Design and define package specs before the package bodies. Place in a spec only those things that must be visible to calling programs. That way, other developers cannot build unsafe dependencies on your implementation details.
To reduce the need for recompiling when code is changed, place as few items as possible in a package spec. Changes to a package body do not require recompiling calling procedures. Changes to a package spec require Oracle to recompile every stored subprogram that references the package.
Separating Cursor Specs and Bodies with Packages
You can separate a cursor specification (spec for short) from its body for placement in a package. That way, you can change the cursor body without having to change the cursor spec. For information on the cursor syntax, see "Cursor Declaration".
In Example 9-5, you use the %ROWTYPE attribute to provide a record type that represents a row in the database table employees:
Example 9-5 Separating Cursor Specifications With Packages
CREATE PACKAGE emp_stuff AS
CURSOR c1 RETURN employees%ROWTYPE; -- declare cursor spec
END emp_stuff;
/
CREATE PACKAGE BODY emp_stuff AS
CURSOR c1 RETURN employees%ROWTYPE IS
SELECT * FROM employees WHERE salary > 2500; -- define cursor body
END emp_stuff;
/
The cursor spec has no SELECT statement because the RETURN clause specifies the datatype of the return value. However, the cursor body must have a SELECT statement and the same RETURN clause as the cursor spec. Also, the number and datatypes of items in the SELECT list and the RETURN clause must match.
Packaged cursors increase flexibility. For example, you can change the cursor body in the last example, without having to change the cursor spec.
From a PL/SQL block or subprogram, you use dot notation to reference a packaged cursor, as the following example shows:
DECLARE emp_rec employees%ROWTYPE;BEGIN
OPEN emp_stuff.c1;
LOOP
FETCH emp_stuff.c1 INTO emp_rec;
-- do processing here ...
EXIT WHEN emp_stuff.c1%NOTFOUND;
END LOOP;
CLOSE emp_stuff.c1;
END;
/
The scope of a packaged cursor is not limited to a PL/SQL block. When you open a packaged cursor, it remains open until you close it or you disconnect from the session.
SAVEPOINT
The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction.
COMMIT Statement
The COMMIT statement explicitly makes permanent any changes made to the database during the current transaction. Changes made to the database are not considered permanent until they are committed. A commit also makes the changes visible to other users.
ROLLBACK Statement
The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.
Overview of Transaction Processing in PL/SQL
This section explains how to do transaction processing. You learn the basic techniques that safeguard the consistency of your database, including how to control whether changes to Oracle data are made permanent or undone.
The jobs or tasks that Oracle manages are called sessions. A user session is started when you run an application program or an Oracle tool and connect to Oracle. To allow user sessions to work "simultaneously" and share computer resources, Oracle must control concurrency, the accessing of the same data by many users. Without adequate concurrency controls, there might be a loss of data integrity. That is, changes to data might be made in the wrong order.
Oracle uses locks to control concurrent access to data. A lock gives you temporary ownership of a database resource such as a table or row of data. Thus, data cannot be changed by other users until you finish with it. You need never explicitly lock a resource because default locking mechanisms protect Oracle data and structures. However, you can request data locks on tables or rows when it is to your advantage to override default locking. You can choose from several modes of locking such as row share and exclusive.
A deadlock can occur when two or more users try to access the same schema object. For example, two users updating the same table might wait if each tries to update a row currently locked by the other. Because each user is waiting for resources held by another user, neither can continue until Oracle breaks the deadlock by signaling an error to the last participating transaction.
When a table is being queried by one user and updated by another at the same time, Oracle generates a read-consistent view of the data for the query. That is, once a query begins and as it proceeds, the data read by the query does not change. As update activity continues, Oracle takes snapshots of the table's data and records changes in a rollback segment. Oracle uses rollback segments to build read-consistent query results and to undo changes if necessary.
How Transactions Guard Your Database
A transaction is a series of SQL data manipulation statements that does a logical unit of work. Oracle treats the series of SQL statements as a unit so that all the changes brought about by the statements are either committed (made permanent) or rolled back (undone) at the same time. If your program fails in the middle of a transaction, the database is automatically restored to its former state.
The first SQL statement in your program begins a transaction. When one transaction ends, the next SQL statement automatically begins another transaction. Thus, every SQL statement is part of a transaction. A distributed transaction includes at least one SQL statement that updates data at multiple nodes in a distributed database.
The COMMIT and ROLLBACK statements ensure that all database changes brought about by SQL operations are either made permanent or undone at the same time. All the SQL statements executed since the last commit or rollback make up the current transaction. The SAVEPOINT statement names and marks the current point in the processing of a transaction.
Making Changes Permanent with COMMIT
The COMMIT statement ends the current transaction and makes permanent any changes made during that transaction. Until you commit the changes, other users cannot access the changed data; they see the data as it was before you made the changes.
Consider a simple transaction that transfers money from one bank account to another. The transaction requires two updates because it debits the first account, then credits the second. In the example below, after crediting the second account, you issue a commit, which makes the changes permanent. Only then do other users see the changes.
BEGIN
...
UPDATE accts SET bal = my_bal - debit
WHERE acctno = 7715;
...
UPDATE accts SET bal = my_bal + credit
WHERE acctno = 7720;
COMMIT WORK;
END;
The COMMIT statement releases all row and table locks. It also erases any savepoints (discussed later) marked since the last commit or rollback. The optional keyword WORK has no effect other than to improve readability. The keyword END signals the end of a PL/SQL block, not the end of a transaction. Just as a block can span multiple transactions, a transaction can span multiple blocks.
The optional COMMENT clause lets you specify a comment to be associated with a distributed transaction. When you issue a commit, changes to each database affected by a distributed transaction are made permanent. However, if a network or machine fails during the commit, the state of the distributed transaction might be unknown or in doubt. In that case, Oracle stores the text specified by COMMENT in the data dictionary along with the transaction ID. The text must be a quoted literal up to 50 characters long. An example follows:
COMMIT COMMENT 'In-doubt order transaction; notify Order Entry';
PL/SQL does not support the FORCE clause, which, in SQL, manually commits an in-doubt distributed transaction. For example, the following COMMIT statement is not allowed:
COMMIT FORCE '23.51.54'; -- not allowed
Undoing Changes with ROLLBACK
The ROLLBACK statement ends the current transaction and undoes any changes made during that transaction. Rolling back is useful for two reasons. First, if you make a mistake like deleting the wrong row from a table, a rollback restores the original data. Second, if you start a transaction that you cannot finish because an exception is raised or a SQL statement fails, a rollback lets you return to the starting point to take corrective action and perhaps try again.
Consider the example below, in which you insert information about an employee into three different database tables. All three tables have a column that holds employee numbers and is constrained by a unique index. If an INSERT statement tries to store a duplicate employee number, the predefined exception DUP_VAL_ON_INDEX is raised. In that case, you want to undo all changes, so you issue a rollback in the exception handler.
DECLARE
emp_id INTEGER;
...
BEGIN
SELECT empno, ... INTO emp_id, ... FROM new_emp WHERE ...
...
INSERT INTO emp VALUES (emp_id, ...);
INSERT INTO tax VALUES (emp_id, ...);
INSERT INTO pay VALUES (emp_id, ...);
...
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
...
END;
Statement-Level Rollbacks
Before executing a SQL statement, Oracle marks an implicit savepoint. Then, if the statement fails, Oracle rolls it back automatically. For example, if an INSERT statement raises an exception by trying to insert a duplicate value in a unique index, the statement is rolled back. Only work started by the failed SQL statement is lost. Work done before that statement in the current transaction is kept.
Oracle can also roll back single SQL statements to break deadlocks. Oracle signals an error to one of the participating transactions and rolls back the current statement in that transaction.
Before executing a SQL statement, Oracle must parse it, that is, examine it to make sure it follows syntax rules and refers to valid schema objects. Errors detected while executing a SQL statement cause a rollback, but errors detected while parsing the statement do not.
Undoing Partial Changes with SAVEPOINT
SAVEPOINT names and marks the current point in the processing of a transaction. Used with the ROLLBACK TO statement, savepoints let you undo parts of a transaction instead of the whole transaction. In the example below, you mark a savepoint before doing an insert. If the INSERT statement tries to store a duplicate value in the empno column, the predefined exception DUP_VAL_ON_INDEX is raised. In that case, you roll back to the savepoint, undoing just the insert.
DECLARE
emp_id emp.empno%TYPE;
BEGIN
UPDATE emp SET ... WHERE empno = emp_id;
DELETE FROM emp WHERE ...
...
SAVEPOINT do_insert;
INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK TO do_insert;
END;
When you roll back to a savepoint, any savepoints marked after that savepoint are erased. However, the savepoint to which you roll back is not erased. For example, if you mark five savepoints, then roll back to the third, only the fourth and fifth are erased. A simple rollback or commit erases all savepoints.
If you mark a savepoint within a recursive subprogram, new instances of the SAVEPOINT statement are executed at each level in the recursive descent. However, you can only roll back to the most recently marked savepoint.
Savepoint names are undeclared identifiers and can be reused within a transaction. This moves the savepoint from its old position to the current point in the transaction. Thus, a rollback to the savepoint affects only the current part of your transaction. An example follows:
BEGIN
SAVEPOINT my_point;
UPDATE emp SET ... WHERE empno = emp_id;
...
SAVEPOINT my_point; -- move my_point to current point
INSERT INTO emp VALUES (emp_id, ...);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO my_point;
END;
The number of active savepoints for each session is unlimited. An active savepoint is one marked since the last commit or rollback.
EXIT Statement
You use the EXIT statement to exit a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN. With either form, you can name the loop to be exited.
RAISE
The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to the appropriate exception handler. Normally, predefined exceptions are raised implicitly by the runtime system. However, RAISE statements can also raise predefined exceptions. User-defined exceptions must be raised explicitly by RAISE statements.
IF quantity_on_hand = 0 THEN
RAISE out_of_stock;
END IF;
