Bulk Collections and Inserts in Oracle 9i

1
Bulk Collectionsand Inserts inOracle 9i 10g

• Simay Alpöge
• Next Information Systems, Inc.

2
AGENDA

• Performance gains with Bulk Binding
• Array processing with BULK COLLECT and FORALL
• Error handling.
• Native Dynamic SQL with Bulk binding.
• Typical problems.
• Oracle 10g FORALL improvements.

3
Performance Gains with Bulk Binding
Context Switching
SQL Engine
PL/SQL Engine
SQL Statement Executor
Procedural statement Executor
DATA
4
Bulk Binding Categories

• With SELECT or FETCH statements
• BULK COLLECT INTO
• In-Bind binding.
• INSERT or UPDATE
• Out-Bind binding.
• RETURNING clause

5
SELECT / FETCH statements

• Data may be Bulk Collected/Fetched into
• Table.columnTYPE 8i and above
• Record of arrays 8i and above
• TableROWTYPE 9.0.2.3 and above
• CursorROWTYPE 9.0.2.3 and above
• Array of records 9.0.2.3 and above
• Nested tables 8i and above

6
SELECT statementTable.columnTYPE

• DECLARE
• TYPE cust_tab IS TABLE OF customer.customer_accoun
  t_idTYPE
• INDEX BY BINARY_INTEGER
• Custs cust_tab
• BEGIN
• SELECT customer_account_id
• BULK COLLECT INTO custs
• FROM customer
• WHERE effective_date BETWEEN
• TO_DATE(01-Jan-2004,DD-MON-RRRR) AND
  TRUNC(SYSDATE)
• END

7
FETCH statements Table.columnTYPE

• DECLARE
• TYPE CustList IS TABLE OF Customer.Customer_Accou
  nt_IdTYPE
• INDEX BY BINARY_INTEGER
• Custs CustList
• CURSOR c1 IS
• SELECT customer_account_id FROM Customer
• WHERE effective_date BETWEEN
• TO_DATE(01-JAN-2004, DD-MON-RRRR) AND
  TRUNC(SYSDATE)
• BEGIN
• OPEN c1
• FETCH c1 BULK COLLECT INTO Custs
• CLOSE c1
• END

8

FETCH statementRecord of Arrays

• DECLARE
• TYPE CustRec IS RECORD
• (R_Customer_Account_id Customer.Customer_Account
  _idTYPE,
• R_Effective_date Customer.Effective_DateTYPE
  ,
• R_Expired_date Customer.Expired_DateTYPE)
• v_Custrec CustRec
• v_Array_Size NUMBER 100
• CURSOR c1 IS
• SELECT Customer_Account_Id, Effective_Date,
  Expired_Date
• FROM Customer
• BEGIN
• OPEN c1
• LOOP
• FETCH c1 BULK COLLECT INTO v_Custrec.R_Customer_a
  ccount_id,v_Custrec.R_Effective_Date,
  v_Custrec.R_Expired_Date LIMIT
  v_Array_Size
• END LOOP
• CLOSE c1 END

9
SELECT statement TableROWTYPE

• DECLARE
• TYPE Cust_tab IS TABLE OF Customers_Active
  ROWTYPE
• Custs Cust_tab
• BEGIN
• SELECT Customer_Account_Id, Effective_Date,
• Expired_Date
• BULK COLLECT INTO Custs
• FROM Customers_Active
• WHERE Effective_date BETWEEN
• TO_DATE(01-JAN-2004 , DD-MON-RRRR) AND
  TRUNC(SYSDATE)
• END

10
FETCH statementCURSORROWTYPE

• DECLARE
• CURSOR c1
• IS
• SELECT Customer_Account_Id, Effective_Date,Expired
  _Date
• FROM Customer
• WHERE Effective_Date BETWEEN
• TO_DATE(01-Jan-2004, DD-MON-RRRR)
  ANDTRUNC(SYSDATE)
• TYPE Cust_tab IS TABLE OF C1ROWTYPE
• Custs Cust_tab
• BEGIN
• OPEN c1
• LOOP
• FETCH c1 BULK COLLECT INTO Custs LIMIT 100
• EXIT WHEN c1NOTFOUND
• END LOOP
• END

11
FETCH statement Array Of Records

• DECLARE
• TYPE CustRec IS RECORD
• (R_Customer_Account_id Customer.Customer_Account_
  idTYPE,
• R_Effective_Date Customer.Effective_DateTY
  PE,
• R_Expired_Date Customer.Expired_DateTYPE)
• TYPE CustRecTab IS TABLE OF CustRec
• Cust_Recs CustRecTab
• v_Array_Size NUMBER 100
• CURSOR c1 IS
• SELECT Customer_Account_Id, Effective_Date,
  Expired_Date
• FROM Customer
• BEGIN
• OPEN c1
• FETCH c1 BULK COLLECT INTO Cust_Recs LIMIT
  v_Array_Size
• CLOSE c1
• END

12
FETCH statementNested Tables

• 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

13
In-Bind Binding

• DECLARE
• CURSOR c1
• SELECT Customer_Account_Id, Effective_Date,Expired
  _Date
• FROM Customer
• WHERE Effective_Date BETWEEN TO_DATE(01-Jan-2003
  , DD-MON-RRRR) And TO_DATE(01-Jan-2004,
  DD-MON-RRRR)
• TYPE Cust_tab IS TABLE OF C1ROWTYPE
• Custs Cust_tab
• BEGIN
• OPEN c1
• LOOP
• FETCH c1 BULK COLLECT INTO Custs LIMIT 100
• END LOOP
• FORALL i IN 1 .. Custs.COUNT
• SAVE EXCEPTIONS
• INSERT into Customer_History VALUES Custs (i)
• .

14
FORALL Error Handling

• No more FULL rollback in case of an EXCEPTION
• SAVE EXCEPTIONS
• SQLBULK_EXCEPTIONS Collection of records
• SQLBULK_EXCEPTIONS(i).ERROR_INDEX stores
  itireration when exception is raised.
• SQLBULK_EXCEPTIONS(i).ERROR_CODE - stores Oracle
  error code.

15
Typical ProblemsFORALL limitations

• SINGLE DML (INSERT, UPDATE, DELETE) statement is
  allowed.
• No IF condition within FORALL.
• For multi table INSERT/UPDATE use WHEN clause
  instead of IF condition.
• No DBMS_OUTPUT or other OUTPUT statements within
  FORALL.
• No SELECT .. BULK COLLECT within FORALL
  statement.

16
Out-Bind binding

• DECLARE
• TYPE AcctTab IS TABLE OF Acct_Install.Customer_Acc
  ount_idTYPE
• Acusts AcctTab
• BEGIN
• DELETE FROM Acct_Install
• WHERE INSTALL_DATE
• TRUNC(TO_DATE(01-jan-1970,DD-MON-RRRR)
• RETURNING Customer_Account_Id
• BULK COLLECT INTO Acusts
• END

17
NATIVE Dynamic SQL

• EXECUTE IMMEDIATE
• FORALL
• RETURNING INTO
• USING
• COLLECT INTO
• BULK_ROWCOUNT - cursor attribute shows total
  cumulative execution.

18
NATIVE Dynamic SQL

• DECLARE
• TYPE CustList IS TABLE OF NUMBER
• TYPE NameList IS TABLE OF VARCHAR2(15)
• Custnos CustList
• Custnames NameList
• BEGIN Custnos CustList(1,2,3,4,5)
• FORALL i IN 1..5
• EXECUTE IMMEDIATE 'UPDATE Customer SET Cust_Name
  TRIM(Cust_name) WHERE Custno 1
• RETURNING Cust_Name INTO 2'
• USING Custnos(i)
• RETURNING BULK COLLECT INTO Custnames ...
• END

19
Oracle 10g FORALL

• Indices of When binding area contains
  gaps
• Values of Subset of element selection
  in the array

20
Oracle 10g FORALL VALUES OF

• DECLARE
• TYPE CUST_IDS IS TABLE OF CUSTOMER.CUSTOMER_IDTYP
  E
• TYPE PHONE_NOS IS TABLE OF CUSTOMER.PHONE_NBRTYPE
  
• TYPE EDATES IS TABLE OF CUSTOMER.EFF_DATETYPE
• TYPE EXPDATES IS TABLE OF CUSTOMER.EXPIRED_DATETY
  PE
• TYPE A_INDX IS TABLE OF PLS_INTEGER
• INDEX BY PLS_INTEGER
• EXPIRED_CUSTS A_INDX
• ACTIVE_CUSTS A_INDX

21
Oracle 10g FORALL VALUES OF (Continue)

• CUSTID CUST_ID
• PHONES PHONE_NOS
• EFFDTS EDATES
• EXPDTS EXPDATES
• V_I BINARY_INTEGER
• BEGIN
• SELECT CUSTOMER_ID, PHONE_NBR, EFF_DATE,
  EXPIRED_DATE
• BULK COLLECT INTO CUSTIDS, PHONES, EFFDTS,
  EXPDTS
• FROM CUSTOMER
• WHERE Effective_Date BETWEEN
• TO_DATE(01-Jan-2003, DD-MON-RRRR) And
• TO_DATE(01-Jan-2004, DD-MON-RRRR)
• END

22
Oracle 10g FORALL VALUES OF (Continue)

• FOR v_i IN CUSTIDS.FIRST .. CUSTIDS.LAST
• LOOP
• IF FN_EXPIRED THEN
• EXPIRED_CUSTS (V_I) v_i
• ELSE
• ACTIVE_CUSTS(V_I) v_i
• END IF
• END LOOP
• BEGIN
• FORALL V_ I VALUES OF EXPIRED_CUSTS
• SAVE EXCEPTIONS
• INSERT INTO CUSTOMER_HISTORY
• VALUES
• (CUSTIDS (V_I), PHONES(V_I), EFFDTS(V_I),
  EXPDTS(V_)
• FORALL V_ I VALUES OF ACTIVE_CUSTS
• SAVE EXCEPTIONS
• INSERT INTO CUSTOMER_ACTIVE
• VALUES

23
Questions
24
THANK YOU

• dbasig_at_nyoug.org
• alpoge_at_nextinfosys.com