Results for FORALL


1 to 40 of 41 results. Prev  Next
Results per page:  20 |  40 


User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 22 of 52

FORALL StatementThe FORALL statement 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

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 1 of 52

FORALL StatementFunctions GOTO Statement

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

How FORALL Affects RollbacksIn a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all … (10, 20, 30); BEGIN FORALL j IN depts.FIRST..depts.LAST UPDATE emp2 SET job = job || ' (temp)' WHERE … the SQL statement raises an exception, the FORALL statement halts. In our example, the second

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Using the FORALL StatementThe 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

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Using FORALL and BULK COLLECT TogetherYou can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk … completes: FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp WHERE empno = depts(j) RETURNING empno … ... BULK COLLECT statement in a FORALL statement. Otherwise, you get the error implementation … restriction: cannot use FORALL

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Handling FORALL Exceptions with the %BULK_EXCEPTIONS AttributePL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement … 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 … first field, %BULK_EXCEPTIONS(i).ERROR_INDEX,

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT Attributeattribute, %BULK_ROWCOUNT, designed for use with the FORALL statement. This attribute has the … , 20, 50); BEGIN FORALL j IN depts.FIRST..depts.LAST UPDATE emp SET sal = sal * 1.10 WHERE deptno … ; The FORALL statement and %BULK_ROWCOUNT attribute use the same subscripts. For example, if FORALL … , %BULK_ROWCOUNT

Error Messages, 9i Release 2 (9.2.0.2)

PL/SQL and FIPS Messages (PLS)

PLS-00432 implementation restriction: cannot use FORALL and BULK COLLECT INTO together in SELECT statementsCause: SELECT statement contains both the FORALL and BULK COLLECT INTO phrases. Action: Do not use … FORALL and BULK COLLECT INTO together in SELECT statements.

Error Messages, 9i Release 2 (9.2.0.2)

PL/SQL and FIPS Messages (PLS)

PLS-00435 DML statement without BULK In-BIND cannot be used inside FORALLCause: The DML (SELECT/INSERT/DELETE/UPDATE) statement inside the FORALL statement does not contain … BULK IN-BIND variables. Action: The DML (SELECT/INSERT/DELETE/UPDATE) statement inside the FORALL statement must contain BULK IN-BIND variables.

Error Messages, 9i Release 2 (9.2.0.2)

PL/SQL and FIPS Messages (PLS)

PLS-00430 FORALL iteration variable string is not allowed in this contextCause: FORALL iteration variable can only be used as a subscript. It cannot be used directly or as … a part of an expression. Action: Use FORALL variable only as a collection subscript.

Error Messages, 9i Release 2 (9.2.0.2)

PL/SQL and FIPS Messages (PLS)

PLS-00437 FORALL bulk index cannot be used in string clauseCause: RETURNING table(bulk_index) is not supported. Action: Use RETURNING BULK COLLECT instead.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 22 of 52

Usage Notesonly 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.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 22 of 52

Keyword and Parameter Descriptionindex_name This is an undeclared identifier that can be referenced only within the FORALL statement … statement. Inside a FORALL statement, index_name cannot appear in expressions and cannot be assigned a … expressions are evaluated only when the FORALL statement is first entered. SAVE EXCEPTIONS These … optional keywords cause

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 22 of 52

Example-- fill varray here... FORALL j IN 6..10 -- bulk-bind middle third of varray UPDATE emp SET sal … the function median: FORALL i IN 1..20 INSERT INTO emp2 VALUES (enums(i), names(i), median(sals),...);

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 22 of 52

RestrictionsThe 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 FORALL statement only in server-side programs (not in client-side programs). Otherwise, you get … statement must reference

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

How Do Bulk Binds Improve Performance?FORALL statement. To do bulk binds with SELECT statements, you include the BULK COLLECT clause in … statements, see \"FORALL Statement\" and \"SELECT INTO Statement\". Example: Performing a Bulk Bind … (10, 30, 70); -- department numbers BEGIN FORALL i IN depts.FIRST..depts.LAST DELETE FROM emp WHERE … table twice: first using

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Oracle9i Application Developer's Guide - Fundamentals -- Index

FEACH ROW clause, 15-11 FORALL statement FORALL statement using, 9-18 foreign key constraints foreign

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 15 of 52

Examples; You can combine the BULK COLLECT clause with a FORALL statement, in which case, the SQL engine bulk … completes: FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp WHERE deptno = depts(j) RETURNING empno

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 48 of 52

SQL Cursorand %BULK_EXCEPTIONS, designed for use with the FORALL statement. For more information, see \"Managing Cursors\".

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Native Dynamic SQL

Syntax for Dynamic Bulk Bindsdynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL. Bulk EXECUTE IMMEDIATE This statement lets … number of columns in the query select-list, Oracle generates an error. Bulk FORALL This statement lets … IMMEDIATE statement inside a FORALL loop. The syntax follows: FORALL index IN lower bound..upper bound

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Tuning PL/SQL Applications

Tuning PL/SQL Performance with Bulk BindsSQL engine just once, with the entire nested table: FORALL i IN depts.FIRST..depts.LAST UPDATE emp … elements, move it into a FORALL statement. If a SELECT INTO, FETCH INTO, or RETURNING INTO clause … on a particular row is not a serious problem, include the keywords SAVE EXCEPTIONS in the FORALL

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 48 of 52

Usage Notes… %BULK_ROWCOUNT is not maintained for bulk inserts because that would be redundant. For example, the FORALL … : FORALL i IN 1..15 INSERT INTO emp (sal) VALUES (sals(i)); You can use the scalar attributes %FOUND

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Native Dynamic SQL

Examples of Dynamic Bulk BindsBULK COLLECT INTO enames; END; To bind the input variables in a SQL statement, you can use the FORALL … NameList; BEGIN empnos:= NumList(1,2,3,4,5); FORALL i IN 1..5 EXECUTE IMMEDIATE 'UPDATE emp SET sal = sal

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Querying Data into Collections of Records|:host_cursor_variable_name} BULK COLLECT INTO record_variable_name [LIMIT numeric_expression]; FORALL index IN … ]...}] VALUES (record_variable_name(index)) rest_of_insert_stmt FORALL index IN lower_bound..upper_bound … := CharTabTyp('Tim', 'Jon', 'Beth', 'Jenny'); BEGIN FORALL i IN 1..4 INSERT INTO tab1 VALUES(num_tab(i

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 48 of 52

Examples%BULK_ROWCOUNT: DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList:= NumList(10, 20, 50); BEGIN FORALL j

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Collections and Records

Using Host Arrays with Bulk Bindsin the host environment FORALL i IN:lower..:upper DELETE FROM emp WHERE deptno =:depts(i);... END;

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Native Dynamic SQL

Using Bulk Dynamic SQLstatement FORALL statement COLLECT INTO clause RETURNING INTO clause %BULK_ROWCOUNT cursor attribute The

Error Messages, 9i Release 2 (9.2.0.2)

PL/SQL and FIPS Messages (PLS)

PLS-00436 implementation restriction: cannot reference fields of BULK In-BIND table of recordsplain FORALL DML statement (SELECT/INSERT/DELETE/UPDATE) instead.

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL User's Guide and Reference -- Index

Frestriction on, 6-20 when to use, 6-50 FORALL statement, 5-41 syntax, 13-88 using with BULK COLLECT clause

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 48 of 52

Keyword and Parameter Description%BULK_ROWCOUNT This is a composite attribute designed for use with the FORALL statement. This

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Interaction Between PL/SQL and Oracle

Overview of Implicit Cursor Attributes: The SQL cursor has another attribute, %BULK_ROWCOUNT, designed for use with the FORALL statement … . For more information, see \"Counting Rows Affected by FORALL Iterations with the %BULK_ROWCOUNT

User's Guide and Reference, 9i Release 2 (9.2.0.2)

Handling PL/SQL Errors

Continuing after an Exception Is Raisedthe entire operation is complete, as described in \"Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute\".

User's Guide and Reference, 9i Release 2 (9.2.0.2)

List of PL/SQL Reserved Words

F List of PL/SQL Reserved WordsEXCEPTION EXCLUSIVE* EXECUTE EXISTS* EXIT EXTENDS EXTRACT FALSE FETCH FLOAT* FOR* FORALL FROM* FUNCTION

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL User's Guide and Reference -- Contents

PL/SQL User's Guide and Reference -- Contentsthe FORALL Statement How FORALL Affects Rollbacks Counting Rows Affected by FORALL Iterations with … the %BULK_ROWCOUNT Attribute Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute … Collection with the RETURNING INTO Clause Restrictions on BULK COLLECT Using FORALL and BULK COLLECT

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL User's Guide and Reference -- Index

Rsavepoints, 6-47 syntax, 13-161 rollbacks rollbacks implicit, 6-47 of FORALL statement, 5-42 statement-level

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL User's Guide and Reference -- Index

S, 6-23, 13-83 FORALL, 5-41 GOTO, 13-99 IF, 13-101 INSERT, 13-104 LOCK TABLE, 13-110 LOOP, 13-112

Application Developer's Guide - Fundamentals, 9i Release 2 (9.2.0.2)

Using Procedures and Packages

Stored Program Units (Procedures, Functions, and Packages)bulk binds to improve performance. DML Statements that Reference Collections The FORALL keyword can … FORALL i IN Id.FIRST..Id.LAST -- bulk-bind the VARRAY UPDATE Emp_tab SET Sal = 1.1 * Sal WHERE Mgr = Id … -insert or bulk-update the data using a loop like: FORALL i in Emp_Data.FIRST..Emp_Data.LAST INSERT … Loops that Reference

User's Guide and Reference, 9i Release 2 (9.2.0.2)

PL/SQL Language Elements, 20 of 52

Keyword and Parameter Description… %BULK_ROWCOUNT Designed for use with the FORALL statement, this is a composite attribute of the

Generic Documentation Master Index, 9i Release 2 (9.2.0.2)

Oracle9i Database Master Index: F (Release 2 (9.2))

Oracle9 i Database Master Index: F (Release 2 (9.2))Programmer's Guide) FORALL statement       [entry #2] (PL/SQL User's Guide and Reference) syntax

Generic Documentation Master Index, 9i Release 2 (9.2.0.2)

Oracle9i Database Master Index: R (Release 2 (9.2))

Oracle9 i Database Master Index: R (Release 2 (9.2))Reference) of FORALL statement (PL/SQL User's Guide and Reference) ORA-02067 error (Administrator's Guide


Was this page helpful?