?
Solved

Processing within the FORALL Loop

Posted on 2013-05-10
8
Medium Priority
?
399 Views
Last Modified: 2013-05-15
Can I perform logic inside of a forall loop?
What I would like to do is increment a record id each time a row of data is inserted.

CREATE OR REPLACE PROCEDURE fast_proc (p_param IN VARCHAR2)
 IS

 TYPE ARRAY IS TABLE OF T1%ROWTYPE;
 l_data ARRAY;
 max_rec NUMBER;

 CURSOR c IS
 SELECT *
 FROM test_table t1
 inner join test_table_2 t2
 on t2.col1 = t1.col1;

 BEGIN
     max_rec := getMaxRecNumber; -- from function
     OPEN c;
     LOOP
     FETCH c BULK COLLECT INTO l_data LIMIT 100;
     
     FORALL i IN 1..l_data.COUNT
     
      -- Need to increment record number each time inserted
     
     -- Add max_rec
     max_rec  := max_rec + 1;

     INSERT INTO t1 VALUES l_data(i);

     EXIT WHEN c%NOTFOUND;
     END LOOP;
     CLOSE c;
 END fast_proc;
 /
0
Comment
Question by:cookiejar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 39156346
are you getting any erros?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39156441
I believe you can only perform a single DML statement in a FORALL.

I don't understand the need to increment maxvalue.  Would it not end up the same as getMaxRecNumber + l_data.COUNT?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39156455
I also don't understand why you want to create in in-memory table and insert loop.

Why not just do it as a single insert?

INSERT INTO some_table
SELECT *
 FROM test_table t1
 inner join test_table_2 t2
 on t2.col1 = t1.col1;
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39156462
Did you try this?:
 . . .
FORALL i IN 1..l_data.COUNT
     INSERT INTO t1 VALUES l_data(i);

     -- Add max_rec
     max_rec  := max_rec + l_data.COUNT;

Open in new window

:p
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39156509
"Can I perform logic inside of a forall loop?"
Maybe.  But, do you even need to?

I agree with slightwv.  If you really want a "fast_proc" don't use a PL\SQL loop.  Just do a SQL insert based on a select, and use SQL%ROWCOUNT to get the number of records processed, like this:

CREATE OR REPLACE PROCEDURE fast_proc IS

 max_rec NUMBER;

 BEGIN
     max_rec := getMaxRecNumber; -- from function

     INSERT INTO t1
     SELECT *
     FROM test_table t1
     inner join test_table_2 t2
     on t2.col1 = t1.col1;
      
     max_rec  := max_rec + SQL%ROWCOUNT;

 END fast_proc;
 /

Note: the code you posted here did not use the input parameter at all, so I left it out.
0
 

Author Comment

by:cookiejar
ID: 39157166
I also don't understand why you want to create in in-memory table and insert loop.

So doing the following will be more efficient?

INSERT INTO t1
     SELECT *
     FROM test_table t1
     inner join test_table_2 t2
     on t2.col1 = t1.col1;
       
     max_rec  := max_rec + SQL%ROWCOUNT
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39157399
>>So doing the following will be more efficient?

Set up a quick test and try both methods.

Think about it.

Your way:
Create a cursor.
Load the entire result set into memory in a PL/SQL table.
Loop through it and insert the rows into another table.

or

Have the database create an implicit cursor and insert directly.

Which seems best?

Faster yet, use an APPEND hint on the insert.  Before you just make the jump to that you need to understand what that is doing.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39157635
>>So doing the following will be more efficient?
yes, absolutely

a: "set operations" performed by internal dbms facilities -v- b: procedural code you build

a: will win
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question