Using the FOR Clause in Pro*C (Oracle)

Using the FOR Clause

You can use the optional embedded SQL FOR clause to set the number of array elements processed by any of the following SQL statements:

DELETE
EXECUTE
FETCH
INSERT
OPEN
UPDATE

The FOR clause is especially useful in UPDATE, INSERT, and DELETE statements. With these statements you might not want to use the entire array. The FOR clause lets you limit the elements used to just the number you need, as the following example shows:

char emp_name[100][20];
float salary[100];
int rows_to_insert;

/* populate the host arrays */
rows_to_insert = 25; /* set FOR-clause variable */
EXEC SQL FOR :rows_to_insert /* will process only 25 rows */
INSERT INTO emp (ename, sal)
VALUES (:emp_name, :salary);
The FOR clause can use an integer host variable to count array elements, or an integer literal. A complex C expression that resolves to an integer cannot be used. For example, the following statement that uses an integer expression is illegal:


EXEC SQL FOR :rows_to_insert + 5 /* illegal */
INSERT INTO emp (ename, empno, sal)
VALUES (:emp_name, :emp_number, :salary);
The FOR clause variable specifies the number of array elements to be processed. Make sure the number is not larger than the smallest array dimension. Also, the number must be positive. If it is negative or zero, no rows are processed and Oracle issues an error message.


Restrictions
Two restrictions keep FOR clause semantics clear. You cannot use the FOR clause in a SELECT statement or with the CURRENT OF clause.

In a SELECT Statement
If you use the FOR clause in a SELECT statement, you get the following error message:

PCC-E-0056: FOR clause not allowed on SELECT statement at ...
The FOR clause is not allowed in SELECT statements because its meaning is unclear. Does it mean "execute this SELECT statement n times"? Or, does it mean "execute this SELECT statement once, but return n rows"? The problem in the former case is that each execution might return multiple rows. In the latter case, it is better to declare a cursor and use the FOR clause in a FETCH statement, as follows:


http://www.lsbu.ac.uk/oracle/oracle7/api/doc/PC_22/ch10.htm

Oracle Trigger: Mutating Table

A mutating table is a table that is 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. 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.
See Also: Oracle Database Concepts for information about the interaction of triggers and integrity constraints
Because declarative referential integrity constraints are not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.

Bulk Collect NO_DATA_FOUND

Bulk collect clause do not generate an error when it do not find data in Select query. We need to check whether collection variable has any element or not.

Sample:

create table test_bulk (cola number, colb number) ;
/
Table created.

declare
TYPE tNum IS TABLE OF NUMBER ;
a tNum;
b tNum;
BEGIN
SELECT cola, colb
BULK COLLECT INTO a, b
FROM test_bulk ;
IF a.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE('BULK COLLECT: NO DATA FOUND') ;
END IF ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION: NO DATA FOUND') ;
END ;
/
BULK COLLECT: NO DATA FOUND
PL/SQL procedure successfully completed.

Happy Diwali

As the glow of diyas spread radiance all around…

As the sound of crackers fill the air…

May the Festival of light

Brighten your life with

Peace, prosperity and fortune.

Wish you Happy Diwali & prosperous New Year