Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

Contact Us

Anshuman Ghosh
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

×

Warning

JUser: :_load: Unable to load user with ID: 868

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

 

Building on my previous post on BULK COLLECT usage for better performance in PLSQL, this post will talk on bulk DML operations and also on combining bulk DML with bulk collect operation.

While Bulk Collect is used for a bulk fetch of data from cursors, what if the requirement is to do DML on a data collection?

The answer is - use FORALL.

 

Without using FORALL

The direct way to do a DML after BULK COLLECT would be something like -

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_collection;
BEGIN
....
.... build l_employees collection
....

FOR indx IN 1 .. l_employees.COUNT
LOOP
INSERT INTO selected_employees_table values l_employees(indx).emp_id;
END LOOP;
END process_all_rows;

 

Using FORALL

Using FORALL, a bulk DML can be run in one go, instead of multiple independent DML operations (like bulk collect, saving the associated overhead time consumed in context switching between SQL fetches and PLSQL program process).

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_collection;
BEGIN
....
.... build l_employees collection
....

FORALL indx IN 1 .. l_employees.COUNT
INSERT INTO selected_employees_table values l_employees(indx).emp_id;

END process_all_rows;

 

Using FORALL with BULK COLLECT

The following example demonstrates using FORALL with BULKCOLLECT, wherein you wish to collect specific data involved in the DML.

Example-

PROCEDURE process_all_rows
IS
TYPE employees_collection IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;

TYPE employee_names IS TABLE OF VARCHAR2(300) INDEX BY PLS_INTEGER;

l_employees employees_collection;

l_selected_emp_names employee_names;
BEGIN
....
.... build l_employees collection
....

FORALL indx IN 1 .. l_employees.COUNT
UPDATE selected_employees_table
SET selected_flag = 'Y'
WHERE emp_id = l_employees(indx).emp_id
RETURNING emp_full_name BULK COLLECT INTO l_selected_emp_names;

END process_all_rows;

 


Comments   

0 #1 Ramkumar 2008-05-28 01:42
Hi

i got one sanerio.The client has send the XML file of legacy system.I have to upload it to Database table.How can i achieve this.
Quote
0 #2 Arun Sista 2008-05-30 10:00
Anshuman, I would like to take this one on your behalf.

Ramkumar you can do this by reading / parsing your xml file using JAXP. Then populate business objects(OA BC4J) / use plain jdbc to dump the relevant data into the db.

You can bulk enable this at the jdbc/OA BC4J layer if you wish to do so.

You can find relevant examples of JAXP/JDBC api's on the web.


Regards,
Aru n.
Quote
0 #3 lak 2012-08-29 10:09
first make the xml using jaxb to a collection and pass that collection to plsql procedure and using rowtype populate to db
Quote

Add comment


Security code
Refresh

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Jun 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
   1  2  3  4  5  6
  7  8  910111213
14151617181920
21222324252627
282930    

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner