CODEWITHSUNDEEP
oracle-databaseplsqloracle11gbulkinsert
Em Ae
Em Ae

Reputation: 8724

Visibilty of inserted record in FORALL Bulk statement

I have 2 arrays which I will be using to use with FORALL to insert/update. I am not sure if i can use "MERGE" statement with FORALL so I am using two arrays.

While populating data in array, I check if its already in db. If it is, i put the populated data in an array which is meant to be updated otherwise it goes to insert array. 

TYPE T_TABLE IS TABLE OF TABLE_1%ROWTYPE INDEX BY PLS_INTEGER;
TAB_I T_TABLE;
TAB_U T_TABLE;
--..more code
BEGIN
 SELECT COUNT(*) INTO rec_exist FROM TABLE_1 where COL_1 = ID;
EXCEPTION
 WHEN NO DATA FOUND THEN
   rec_exist := 0;
END;
--.... more code
-- COL1 is PK
IF rec_exist = 0 THEN
 TAB_I(IDX_I).COL1 = col1;
 TAB_I(IDX_I).COL2 = col2;
 IDX_I = IDX_I+ 1;
ELSE
 TAB_U(IDX_U).COL1 = col1;
 TAB_U(IDX_U).COL2 = col2;
 IDX_U = IDX_U+ 1;
END IF:

I send these table to insert/update after every 1000 records.

Now the question is, that imagine I receive a record whcih doesn't exist already in table_1. I decide to put it in tab_i array. I receive the another record update for this record. Since it is not in table, i will decide to put in tab_i, which will then give me a problem when i insert it in forall loop.

Now if my forall loop is like

FORALL .. INSERT

FORALL .. UPDATE

COMMIT;

If, I update the record as part of "update" statement which is not in table yet but was insert in table as part of forall above it, would that update work ?

Upvotes: 1

Views: 205

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50057

MERGE with FORALL - not supported, unless something has changed recently (12c perhaps?). The reasoning behind this is that a MERGE statement implicitly performs the same action as a FORALL because the USING clause can select multiple values from a variety of sources, including PL/SQL collections. I've never tried this but I've seen examples, including this AskTom posting.

If you do the INSERTs before the UPDATEs, the INSERTed values will be visible when the UPDATEs are performed.

Share and enjoy.

Upvotes: 2

Related Questions