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

Using Cursor Expressions [Cursor in Select statement]

A cursor expression returns a nested cursor. Each row in the result set can contain values as usual, plus cursors produced by subqueries involving the other values in the row. A single query can return a large set of related values retrieved from multiple tables. You can process the result set with nested loops that fetch first from the rows of the result set, then from any nested cursors within those rows.

PL/SQL supports queries with cursor expressions as part of cursor declarations, REF CURSOR declarations and ref cursor variables. You can also use cursor expressions in dynamic SQL queries. Here is the syntax:

CURSOR(subquery)

A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when:

The nested cursor is explicitly closed by the user

The parent cursor is reexecuted

The parent cursor is closed

The parent cursor is canceled

An error arises during a fetch on one of its parent cursors. The nested cursor is closed as part of the clean-up.

Restrictions on Cursor Expressions
The following are restrictions on cursor expressions:

You cannot use a cursor expression with an implicit cursor.

Cursor expressions can appear only:

In a SELECT statement that is not nested in any other query expression, except when it is a subquery of the cursor expression itself.

As arguments to table functions, in the FROM clause of a SELECT statement.

Cursor expressions can appear only in the outermost SELECT list of the query specification.

Cursor expressions cannot appear in view declarations.

You cannot perform BIND and EXECUTE operations on cursor expressions.

Example of Cursor Expressions
In Example 6-35, we find a specified location ID, and a cursor from which we can fetch all the departments in that location. As we fetch each department's name, we also get another cursor that lets us fetch their associated employee details from another table.

Example 6-35 Using a Cursor Expression

DECLARE
   TYPE emp_cur_typ IS REF CURSOR;
   emp_cur emp_cur_typ;
   dept_name departments.department_name%TYPE;
   emp_name employees.last_name%TYPE;
   CURSOR c1 IS SELECT
      department_name,
-- second item in the result set is another result set,
-- which is represented as a ref cursor and labelled "employees".
      CURSOR
      ( SELECT e.last_name FROM employees e
         WHERE e.department_id = d.department_id) employees
      FROM departments d WHERE department_name like 'A%';
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO dept_name, emp_cur;
      EXIT WHEN c1%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Department: ' || dept_name);
-- for each row in the result set, the result set from a subquery is processed
-- the set could be passed to a procedure for processing rather than the loop
      LOOP
         FETCH emp_cur INTO emp_name;
         EXIT WHEN emp_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE('-- Employee: ' || emp_name);
      END LOOP;
   END LOOP;
   CLOSE c1;
END;
/
Constructing REF CURSORs with 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)));

Cursor subqueries are often used with table functions, which are explained in "Setting Up Transformations with Pipelined Functions".