Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle BULK Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: BULK

Definition:
Bulk Binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type (index-by table, nested table, or varray). The collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER. Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.

EXECUTE IMMEDIATE
You can use the BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store values from each column of a query's result set in a separate collection.

You can use the RETURNING BULK COLLECT INTO clause with the EXECUTE IMMEDIATE statement to store the results of an INSERT, UPDATE, or DELETE statement in a set of collections.

FETCH
You can use the BULK COLLECT INTO clause with the FETCH statement to store values from each column of a cursor in a separate collection.

FORALL
You can put an EXECUTE IMMEDIATE statement with the RETURNING BULK COLLECT INTO inside a FORALL statement. You can store the results of all the INSERT, UPDATE, or DELETE statements in a set of collections.

You can pass subscripted collection elements to the EXECUTE IMMEDIATE statement through the USING clause. You cannot concatenate the subscripted elements directly into the string argument to EXECUTE IMMEDIATE; for example, you cannot build a collection of table names and write a FORALL statement where each iteration applies to a different table.


BULK SQL

Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of a loop that issues a SQL statement in every iteration.

Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time:

  • BULK FETCH statement
  • BULK EXECUTE IMMEDIATE statement
  • FORALL statement
  • COLLECT INTO clause
  • RETURNING INTO clause
  • %BULK_ROWCOUNT cursor attribute

Example of BULK COLLECT INTO Clause

You can bind define variables in a dynamic query using the BULK COLLECT INTO clause, and you can use that clause in a bulk FETCH or bulk EXECUTE IMMEDIATE statement as shown here:

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
emp_cv EmpCurTyp;
empnos NumList;
enames NameList;
sals NumList;
BEGIN
OPEN emp_cv FOR 'SELECT empno, ename FROM emp';
FETCH emp_cv BULK COLLECT INTO empnos, enames;
CLOSE emp_cv;

EXECUTE IMMEDIATE 'SELECT sal FROM emp'
BULK COLLECT INTO sals;
END;
/



Related Links:

Related Code Snippets:
  • BULK COLLECT - The SQL engine will bulk bind all the values present for a \column in a table befor...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 95 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?