BULK COLLECT and FORALL in PL/SQL

20 pts.
Tags:
BULK COLLECT statement
FORALL statement
Oracle PL/SQL
PL/SQL
How do I update multiple columns in a table using the BULK COLLECT... FORALL methods in PL/SQL.

Answer Wiki

Thanks. We'll let you know when a new response is added.

I found the answer on my own and here is the code I wrote:

-- ts_to_td.sql
--
-- This script is called by Q3.sql
-- and runs after the scripts:
-- 1.) ts_insert_table.sql - creates the TS_INSERT_TABLE
-- and its index;
-- 2.) ts_update_table.sql - creates the TS_UPDATE_TABLE
-- and its index.
--
create or replace procedure TS_TO_TD
IS
type TS_INSERTS is table of TS_INSERT_TABLE%ROWTYPE;
TD_IN TS_INSERTS;
type TS_UPDATES is table of TS_UPDATE_TABLE%ROWTYPE;
type TS_UPDATES_ID is table of TS_UPDATE_TABLE.KEYVAL%TYPE;
TD_UP TS_UPDATES;
TD_UP_ID TS_UPDATES_ID;
BEGIN


– This section inserts rows into TDEST from TSOURCE.

select * BULK COLLECT
into TD_IN
from TS_INSERT_TABLE

FORALL

TS_INSERT_NDX
in
TD_IN.FIRST .. TD_IN.LAST
insert into
TDEST
values
TD_IN(TS_INSERT_NDX)


– This section updates TDEST table rows with a
– matching KEYVAL value with the data in TSOURCE.

select * BULK COLLECT into
TD_UP
from TS_UPDATE_TABLE
select KEYVAL BULK COLLECT into
TD_UP_ID
from TS_UPDATE_TABLE

FORALL

TS_UPDATE_NDX
in
TD_UP.FIRST .. TD_UP.LAST
update TDEST
set
row = TD_UP(TS_UPDATE_NDX)
WHERE
KEYVAL = TD_UP_ID(TS_UPDATE_NDX)
;
END

Good Luck, RangjungDorje

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following