Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Kashif.

Asked: March 30, 2003 - 12:19 pm UTC

Last updated: August 01, 2022 - 3:15 pm UTC

Version: 9.2.0.2

Viewed 50K+ times! This question is

You Asked

Hi Tom,

I'm using the FORALL statement to update tables in my applications. When the update raises an error, it can be saved using the SAVE EXCEPTIONS clause and then continuing with the update, or the update can be aborted without using that clause. In both these situations however, I am unable to pin down which specific record caused the error, i.e. the id of the record. More specifically, is there a way to store user defined data upon encountering an error during a FORALL execution? Small example:

declare
type intArray is table of number index by binary_integer;
type charArray is table of varchar2 (2000) index by binary_integer;
type recType is record
(idArray intArray,
array2, charArray);
l_tabrec recType;
lt_id intArray;
begin
...
... /* populating the l_tabrec record of arrays here. */
...
begin
forall i in 1..l_tabrec.idArray.count
update table2
set value = l_tabrec.array2(i)
where id = l_tabrec.idArray(i);
exception
when others then
/* here is where I'd like to log the id of the record which fails. */
raise;
end;
end;

I know the SAVE EXCEPTIONS gives us information about the type of error and the iteration on which it encountered the error, but I'm not sure how I can use that information to find which record actually caused the error. Hope my question is clear. Thanks in advance.

Kashif

and Tom said...

Take a block like this:

ops$tkyte@ORA920> DECLARE
2 type array is table of t%rowtype index by binary_integer;
3 data array;
4 errors NUMBER;
5 dml_errors EXCEPTION;
6 l_cnt number := 0;
7 PRAGMA exception_init(dml_errors, -24381);
8
9 cursor c is select * from t;
10 BEGIN
11 open c;
12 loop
13 fetch c BULK COLLECT INTO data LIMIT 100;
14 begin
15 FORALL i IN 1 .. data.count SAVE EXCEPTIONS
16 insert into t2 values data(i);
17 EXCEPTION
18 WHEN dml_errors THEN
19 errors := SQL%BULK_EXCEPTIONS.COUNT;
20 l_cnt := l_cnt + errors;
21 FOR i IN 1..errors LOOP
22 dbms_output.put_line
23 ('Error occurred during iteration ' ||
24 SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
25 ' Oracle error is ' ||
26 SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
27 end loop;
28 end;
29 exit when c%notfound;
30 END LOOP;
31 close c;
32 dbms_output.put_line( l_cnt || ' total errors' );
33 end;
34 /

There -- the ERROR_INDEX is the index into the arrays you passed to forall. if SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 5, then the 5th record in the bulk operation has the error.

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8784259916366 <code>

for a full example.

Rating

  (82 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Forall and Remote tables

A reader, September 22, 2003 - 1:29 pm UTC

Can you please confirm this . Forall does not work on Remote table updates (I did not try insert ,delete ).
U need to loop thru like a simple for loop and do the update.
Surprisingly the manual does not list this in the restrictions on Forall.
Are there any alternatives with the bulk collect option for remote dmls?
Thanx Sir.

Tom Kyte
September 22, 2003 - 1:49 pm UTC

correct, forall does not function over dblinks.


Do u also agree

A reader, September 22, 2003 - 3:32 pm UTC

Do u agree we cannot gain by bulk collecting into a plsql table and then use simpe for loop to update remote
table.
Instead its better to use for i in ( select ....) loop
update table
end loop.
Here I found a sql single correlated update of a remote table takes longer 3 hrs compared to plsql
takes 2 minutes.

Thanx for your answers

Tom Kyte
September 22, 2003 - 8:08 pm UTC

no i do not agree, not 100% anyway.

there are times, yes, that the above may be true.

there are times, yes, that the above may NOT be true.




May I ask for examples to prove your point

A reader, September 22, 2003 - 8:17 pm UTC


Tom Kyte
September 22, 2003 - 9:20 pm UTC

ops$tkyte@ORA817DEV> /*
DOC>drop table t1;
DOC>drop table t2;
DOC>
DOC>create table t1 as select * from all_objects;
DOC>create table t2 as select * from t1;
DOC>
DOC>alter table t1 add constraint t1_pk primary key(object_id);
DOC>alter table t2 add constraint t2_pk primary key(object_id);
DOC>
DOC>analyze table t1 compute statistics for table for all indexes;
DOC>analyze table t2 compute statistics for table for all indexes;
DOC>*/
ops$tkyte@ORA817DEV> set timing on
ops$tkyte@ORA817DEV> update (select t1.object_name t1_oname, t2.object_name t2_oname
  2                    from t1, t2@ora817dev.us.oracle.com@loopback t2
  3                   where t1.object_id = t2.object_id )
  4  set t1_oname = t2_oname;

26859 rows updated.

Elapsed: 00:00:22.29
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> commit;

Commit complete.

Elapsed: 00:00:00.04
ops$tkyte@ORA817DEV> update t1
  2     set object_name = (select nvl(max(object_name),t1.object_name)
  3           from t2@ora817dev.us.oracle.com@loopback t2
  4          where t1.object_id = t2.object_id )
  5  /

26859 rows updated.

Elapsed: 00:00:31.34
ops$tkyte@ORA817DEV> commit;

Commit complete.

Elapsed: 00:00:00.02
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> begin
  2          for x in ( select object_id, object_name from t2@ora817dev.us.oracle.com@loopback )
  3          loop
  4                  update t1 set object_name = x.object_name where object_id = x.object_id;
  5          end loop;
  6          commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:32.77
ops$tkyte@ORA817DEV>
 

Excellant but loopback ?

A reader, September 22, 2003 - 9:49 pm UTC

You are always precise and correct. That was convincing enough.
You may ignore this question if you are busy( duh.. you are always ) what was that loopback thing in the dblink ?
Did you connect to the same schema you are at.
Whats that used for ?

Tom Kyte
September 23, 2003 - 6:34 am UTC

it was a loopback dblink -- goes through all of the work of a dblink, without me having to actually setup 2 databases.

exception while select

Vipin, September 23, 2003 - 8:20 am UTC

Hi Tom,


Is there any technique using which we can save exceptions while we are SELECTing BULK COLLECT into PL/SQL tables.

Suppose we need to bulk update some rows and so i want BULK LOCK it rather than going through a loop and lock it using a cursor, how will I know that locking of which rows threw RESOURCE BUSY NOWAIT ERROR.

I want to use:-

SELECT EMPNO, MODIFIED_TIMESTAMP
BULK COLLECT INTO TB_EMPNO, TB_TIMESTAMP
FROM EMP
FOR UPDATE NOWAIT;

in place of :-

CURSOR C_EMP
IS
SELECT EMPNO, MODIFIED_TIMESTAMP
FROM EMP
FOR UPDATE NOWAIT;

because the latter involves again a looping and processing.



Tom Kyte
September 23, 2003 - 11:04 am UTC

that would not "make sense". what would the use be there? I mean -- you want to update only rows that are not locked? I don't see the benefit?

what are you trying to do, to accomplish -- maybe I can suggest something there.

Vipin, September 23, 2003 - 11:58 am UTC

Hi tom,

Sorry for not being clear. The requirement is locking a set of transactions and updating them. If some of the transactions fails the locking, as somebody else has already locked it, form a list of these falied transactions and send it to front end while the other transaction will be processed successfully.

I know that this can be acheived using cursor, but then I will have to loop through each records and process for each record, or else I have to loop through each record, lock and then close the loop and finally process all the transactions in bulk.

What I wanted to know is If I am able to save exceptions while bulk collect, I can lock using bulk collect, for each of the saved exceptions I can collect the already locked(hence failed while locking) transactions, continue batch processing the rest and send frond end the list of failed transactions which is nothing but the transactions which threw RESOURCE BUSY exception while bulk collect.

So I wanted to know if we have a SAVE EXCEPTIONS provision or something similar to that while BULK COLLECTing or else looping through the cursor is the only option.


Tom Kyte
September 23, 2003 - 1:54 pm UTC

there is no such concept for a SELECT statment -- it either "works" or it does not.

you see with

forall i in 1 .. 10 insert into t values ( i );

is it AS IF there were 10 separate executions of the insert, the statement is run over and over and over really.

with

select rownum bulk collect into l_data from all_objects where rownum <= 10;

there is only ONE statement - there is not any concept of saving exceptions row by row, only by "statement execution". forall just lets you say "execute this statement N times"


you will have to loop through the records issuing a select for update nowait against each.

A reader, September 23, 2003 - 4:23 pm UTC

Hi tom,

so what is the real difference between

forall i in 1..10 insert into emp..;

and

for i in 1..10 loop
insert into emp....;
end loop;

Is it that 10 context switchs between PL/SQL and SQL is avoided ?

Tom Kyte
September 23, 2003 - 6:22 pm UTC

yes, that is the crux of it, yes.

one sends the statment with 10 inputs to the sql engine.

the other sends 10 statements with 1 input each to the sql engine

forall for select.

joe, October 13, 2003 - 2:17 pm UTC

Tom,

Since there is no forall for select, what is the best approach for collecting the entire recordset when you have an index-by-table coming from a web form submittal containing the primary keys of the rows you want.

I have some ideas, but not sure..
1. for loop fetching each row individually
-advantages, I know how to do this.
-disadvantages, don't do in many statements what can be done in a single statement.
2. an in query, casting your index-by-table type to a sql type and doing something similar to what you do for a varying in-list.
- you have documented this well, so assuming an index-by-table can be cast to a database type table, this should be easy also
3. casting the index-by table to a real table using a table function, and joining to its values from the real table. (not sure if this is wise or even possible)
4. Something designed for this, that I am overlooking?


Tom Kyte
October 13, 2003 - 8:05 pm UTC

select ... BULK COLLECT into .....

or

open c
loop
fetch c bulk collect into ..... limit 100;
....

exit when c%notfound
end loop;


there are "forall" bulk constructs for selects.

bulk collect not what I was looking for ( I think)

joe, October 13, 2003 - 9:43 pm UTC

Tom,

I wasn't very clear. I have an index-by table that has all the primary keys I want.

How do I get the full rows that match up to the values in my index-by table, without getting them one at a time?

for i in 1 .. pk_table.count loop

SELECT ... INTO l_rec FROM table WHERE pk = pk_table(i);

end loop;

do I do this or some of my earlier suggestions?
like #2 above..

bulk collect could be part of the answer if I do #2 or #3 above, but I was asking about the query part to accomodate a list of rows from the index-by-table



Tom Kyte
October 14, 2003 - 6:36 am UTC

ahh -- ok, easy enough.  don't use an index by table, use a sql collection and you can:

select * from table where pk in ( select * from table( collection_variable ) );


ops$tkyte@ORA920LAP> create or replace type array as table of number
  2  /

Type created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> declare
  2      l_data array := array(1,2,3,4,5);
  3  begin
  4      for x in ( select *
  5                   from all_users
  6                  where user_id in ( select *
  7                                       from table(cast(l_data as array)) ) )
  8      loop
  9          dbms_output.put_line( x.username );
 10      end loop;
 11  end;
 12  /
SYSTEM

PL/SQL procedure successfully completed.
 

Mirjana, October 14, 2003 - 9:47 am UTC


Joe, October 14, 2003 - 11:27 am UTC

When receiving multiple parameters from a web form, aren't I stuck with:

type vc_arr is table of varchar2(32767) index by binary_integer;

or (as you like to do)

create or replace package types
as
type array is table of varchar2(4000) index by binary_integer;
end;

but regardless an index-by table?

Is the process of looping through the index-by table and extending nested table type variable populating it with the values from the index-by table worth it for its use in SQL like you demonstrated above? (no need to answer that, I can determine that on my own, assuming that is the approach that would be taken)

We are talking about a hard limit of 100 keys in this index-by-table





Tom Kyte
October 14, 2003 - 11:39 am UTC

you would have to move it over, yes.

I do "want to update only rows that are not locked?"

Jim Dickson, October 22, 2003 - 5:22 am UTC

I have scenario where i do want to update only rows that are not locked.

A selection of rows from master table are output (ascii file) and go through a Data Quality improvment process (typically re-parsing and geocoding of postal address info).

No locks are held on these rows, since DQ process is external to Oracle.

Input file read as external table and address line elements (columns) need to be updated on master table.

If particular row is currently being updated by another user, then logic is to skip locked row. They will be processed by Data Quality process at a later date.

For the rows that are updated, expect transaction to be atomic.

I can do this with row-by-row cursor, with check for -00054 exception but can this be done using BULK EXCEPTIONS?

(Also believe this can be done in using undocumented/unsupported SKIP LOCK but not planning to go there).

9.2.0.3.0 Standard on Solaris 2.8

Tom Kyte
October 22, 2003 - 6:32 am UTC



This has to be done row by row -- as the NOWAIT is an option of the SELECT and there is no "bulk exceptions" processing for selects as there is for modifications.

Clearly explained

Jim Dickson, October 23, 2003 - 6:38 am UTC

Clearly explained - as ever. Thanks.

Appears to be a bug on partition table with forall save exceptions.

ramasamy, January 15, 2004 - 10:18 am UTC

platform :
Linux fdlxdv13 2.4.9-e.25enterprise #1 SMP Fri Jun 6 17:55:13 EDT 2003 i686 unknown

oracle ver#  :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Problem Description:

Save exception clause loosing the errors , error counts
when we are inserting into a partitioned destination table with bulk insert. If you drop down the partion number to 1 in is woking good and listing all errors. But the higher the number of partitions the more error counts are lost.

my test case.

1.Data Setup.

DROP TABLE dest CASCADE CONSTRAINTS ;

CREATE TABLE dest (
  order_no       NUMBER (9)    NOT NULL,
  RECTYPE        VARCHAR2 (10),
  CONSTRAINT PK_dest PRIMARY KEY(order_no)
 )
PARTITION BY HASH (RECTYPE)
   PARTITIONS 12;

DROP TABLE source CASCADE CONSTRAINTS ;

CREATE TABLE source (
  order_no       NUMBER (9)    NOT NULL,
  RECTYPE        VARCHAR2 (10),
  CONSTRAINT PK_source PRIMARY KEY(order_no)
 );

insert into source values (1,'HDR');
insert into source values (2,'TAX');
insert into source values (3,'SKU');
insert into source values (4,'TND');
insert into source values (5,'TND');
insert into source values (6,'HDR');
insert into source values (7,'TAX');
insert into source values (8,'SKU');
insert into source values (9,'TND');
insert into source values (10,'TND');
insert into source values (11,'TND');
insert into source values (12,'TND');
insert into source values (13,'TND');
insert into source values (14,'TND');

commit;

exit;

2.) Bulk insert code. (simple_bulk.sql)

set serveroutput on
DECLARE
        cursor c is select *
from source;

        type array is table of dest%rowtype
                      index by binary_integer;
        data   array;
        errors NUMBER;
        dml_errors EXCEPTION;
        l_cnt number := 0;
        PRAGMA exception_init(dml_errors, -24381);
   BEGIN
       open c;
       loop
           fetch c BULK COLLECT INTO data LIMIT 100;
           begin
              FORALL i IN 1 .. data.count SAVE EXCEPTIONS
insert into dest values data(i);
           EXCEPTION
               WHEN dml_errors THEN
                    errors := SQL%BULK_EXCEPTIONS.COUNT;
                    l_cnt := l_cnt + errors;
                    FOR i IN 1..errors LOOP
                        dbms_output.put_line
                        ('Error occurred during iteration ' ||
                          SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                        ' Oracle error is ' ||
                          SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
                    end loop;
           end;
           exit when c%notfound;
        END LOOP;
        close c;
       dbms_output.put_line( l_cnt || ' total errors' );
end;
/

3.) run it two times.

SQL> select count(*) from dest;

  COUNT(*)
----------
         0

SQL> select count(*) from source;

  COUNT(*)
----------
        14

SQL> @simple_bulk.sql
0 total errors

PL/SQL procedure successfully completed.

SQL> select count(*) from dest;

  COUNT(*)
----------
        14
SQL> @simple_bulk.sql
Error occurred during iteration 1 Oracle error is 1
Error occurred during iteration 3 Oracle error is 1
Error occurred during iteration 5 Oracle error is 1
Error occurred during iteration 7 Oracle error is 1
Error occurred during iteration 9 Oracle error is 1
Error occurred during iteration 10 Oracle error is 1
Error occurred during iteration 11 Oracle error is 1
Error occurred during iteration 12 Oracle error is 1
Error occurred during iteration 13 Oracle error is 1
Error occurred during iteration 14 Oracle error is 1
10 total errors

PL/SQL procedure successfully completed.

missing the errors 2,4,6,8 and so on.

Hope i explained the problem clearly.
Your assistance to confirm this is as a bug is greatly
appreciated.

Thanks
Ramasamy T


 

Tom Kyte
January 15, 2004 - 10:53 am UTC

i just filed this as bu 3377931, please open a tar and reference that.

bulk collect

sanjay, January 29, 2004 - 12:28 am UTC

hi TOM

pls help me to understand where i am doing wrong .

DECLARE
TYPE LOAD_REC IS RECORD (
HS_SEQ_CODE EMP.EMPNO%TYPE,
HS_SAL EMP.SAL%TYPE);
TYPE LOAD_TAB IS TABLE OF LOAD_REC
INDEX BY BINARY_INTEGER;
LOAD_DATA_TAB LOAD_TAB ;
BULK_ERRORS EXCEPTION;
PRAGMA EXCEPTION_INIT(BULK_ERRORS,-24381);
BEGIN
SELECT EMPNO,SAL
BULK COLLECT INTO LOAD_DATA_TAB
FROM EMP;

FORALL I IN LOAD_DATA_TAB.FIRST..LOAD_DATA_TAB.LAST --SAVE EXCEPTIONS

INSERT INTO TRY(empno,sal) VALUES LOAD_DATA_TAB(I);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
END;
/

it show error
ORA-06550: line 16, column 31:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 16, column 4:
PL/SQL: SQL Statement ignored

BUT IF IN THE LINE 16 i change
"INSERT INTO TRY VALUES LOAD_DATA_TAB(I);"
it works fine

can i pick a the reqried col from the record.

regards

sanjay



Tom Kyte
January 29, 2004 - 8:01 am UTC

14
15 FORall I IN LOAD_DATA_TAB.FIRST..LOAD_DATA_TAB.LAST --SAVE EXCEPTIONS
16 INSERT INTO TRY VALUES LOAD_DATA_TAB(I);
17


you don't use column names with records in the values clause.

sanjya, January 30, 2004 - 12:28 am UTC

thanks tom for the prompt reply .


can u pls explain why i cannot use column names with records in the values clause .

regards.



Tom Kyte
January 30, 2004 - 8:13 am UTC

because it doesn't work that way?

see
</code> https://asktom.oracle.com/Misc/oramag/on-procedures-flushes-and-writes.html <code>

and look for "Insert and Update from a Record"

How to correct this

Ram, March 17, 2004 - 10:59 am UTC

Dear Tom,
How to correct the following code?
 SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                  NUMBER

SQL> begin
  2   forall i in 1..10
  3    insert into t values(i);
  4  end;
  5  /
  insert into t values(i);
                       *
ERROR at line 3:
ORA-06550: line 3, column 24: 
PLS-00430: FORALL iteration variable I is not allowed in this context 
ORA-06550: line 3, column 3: 
PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL 
Please do reply.



 

Tom Kyte
March 17, 2004 - 11:09 am UTC

use an array, like the examples in the docs?



declare
type array is table of number index by binary_integer;
l_data array;
begin
for i in 1 .. 10
loop
l_data(i) := i;
end loop;

forall i in 1 .. l_data.count insert into t values ( l_data(i) );
end;
/

Please see this

Richard, March 22, 2004 - 1:38 am UTC

Dear Sir,
Please see the code below
SQL> select banner from v$version;
BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> create table t(x number check(x <= 10));

Table created.

SQL> commit;

Commit complete.

SQL>declare
  2   bulk_errors exception;
  3   pragma exception_init(bulk_errors,-20012);
  4   type t is table of number
  5    index by binary_integer;
  6   data t;
  7  cursor c is select rownum from emp;
  8  begin
  9   open c;
 10   loop
 11  fetch c bulk collect into data;
 12   begin
 13    forall j in 1..data.count save exceptions
 14     insert into t values(data(j));
 15   exception
 16   when bulk_errors then
 17    for i in 1..sql%bulk_exceptions.count loop
 18     dbms_output.put_line(sql%bulk_exceptions(i).error_index ||chr(9)||
 19                          sqlerrm(-sql%bulk_exceptions(i).error_code));
 20    end loop;
 21   end;
 22   exit when c%notfound;
 23  end loop;
 24  close c;
 25* end;
SQL> /
ERROR:
ORA-24381: error(s) in array DML 
ORA-06512: at line 13 



Warning: PL/SQL compilation errors.

My effort was to simulate "Getting the error_code and error_index of sql%bulk_exceptions".
How to correct this?But it has inserted values into Table "t" upto 10.
SQL> select * from t;

         X
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 

Tom Kyte
March 22, 2004 - 6:57 am UTC

you used the wrong error code in your exception mapping.

Look at the orig example,

PRAGMA exception_init(dml_errors, -24381)

ok

Richard, March 22, 2004 - 12:52 pm UTC

Dear Tom,
*Cannot* other error numbers be used?Why is it so specific
about "-24381"?Could you please clarify?
Thanks for your followup.
Bye!

Tom Kyte
March 22, 2004 - 3:23 pm UTC

well, the error being thrown is 24381 -- that is the one you want to catch.

you would not want this to be fired for 1401 for example!

[tkyte@localhost tkyte]$ oerr ora 24381
24381, 00000, "error(s) in array DML"
// *Cause: One or more rows failed in the DML.
// *Action: Refer to the error stack in the error handle.


you have to catch the error that is fired here...

OK

Stephenson, March 29, 2004 - 7:57 am UTC

Hi Tom,
Any simple example for the cursor attribute
"SQL%BULK_ROWCOUNT"?How to use this attribute?Please do reply.
Bye!


Tom Kyte
March 29, 2004 - 10:34 am UTC

when you put SQL%BULK_ROWCOUNT into the search field and hit enter.... what happened?

getting ORA-06500 PL/SQL: storage error after DB upgrade

June, April 28, 2004 - 11:30 am UTC

I have one procedure use bulk collect/insert. It works all fine until we upgraded database from 8.1.7.4 to 9.2.0.4. I used to bulk collect with 'limit 1000', but now it even errors out at limit 500. I am testing on using 'limit 100', but afraid if we fail again, I might have to use old procedure without bulk insert.

What might cause this problem?

thanks!

Tom Kyte
April 28, 2004 - 6:55 pm UTC

"errors out", hmmm -- well, there are lots of errors out there. Not knowing which one you are hitting -- makes it sort of "hard"

can you reproduce an example that I can myself reproduce -- at the very very least -- show us your technique and the corresponding error you are receiving.

follow up

June, April 28, 2004 - 11:59 am UTC

follow up with previous note - it even fails out when I use 'limit 100'. Is there anything we could look at? thanks!

Tom Kyte
April 28, 2004 - 7:07 pm UTC

follwup -- still have no no no idea what error you might even be getting here.

i'll need "an example" (that I can run) along with a cut and paste of your error

Looks like one for support

Gary, April 28, 2004 - 7:31 pm UTC

The title of June's comment does give the error :
ORA-06500 PL/SQL: storage error

The manual says just what to do about it :
Cause: This is a rare internal error message. Memory has been exhausted or corrupted.

Action: Contact Oracle Customer Support.



Tom Kyte
April 28, 2004 - 7:47 pm UTC

I need to see the *entire* call stack and the example............

more information

June, April 29, 2004 - 9:52 am UTC

the procedure flow is as following:

.....
--there are two arrays defined as:
last_ind1 t_last_ind ;
last_org1 t_last_org ;
rgstn_start_dt1 t_rgstn_start_dt ;
rgstn_end_dt1 t_rgstn_end_dt ;
emp_start_dt1 t_emp_start_dt ;
emp_end_dt1 t_emp_end_dt ;
emp_seq_nb1 t_emp_seq_nb;

---one as real array holds data which should be inserted into table.
last_ind2 t_last_ind :=t_last_ind() ;
last_org2 t_last_org :=t_last_org() ;
rgstn_start_dt2 t_rgstn_start_dt :=t_rgstn_start_dt() ;
rgstn_end_dt2 t_rgstn_end_dt :=t_rgstn_end_dt() ;
emp_start_dt2 t_emp_start_dt :=t_emp_start_dt();
emp_end_dt2 t_emp_end_dt :=t_emp_end_dt() ;
emp_seq_nb2 t_emp_seq_nb :=t_emp_seq_nb();

open C_RGSTN;
--cursor for about 14mm records. after business logic, about 2mm records will insert into target table.

LOOP /*out loop */
fetch C_RGSTN bulk collect into
last_ind1 ,
last_org1 ,
rgstn_start_dt1 ,
rgstn_end_dt1 ,
emp_start_dt1 ,
emp_end_dt1 ,
emp_seq_nb1 LIMIT 500; --error out even use 100. in 8i, it is no problem with LIMIT 1000.

.....
complex business logic to go through records one by one and pop data into real array for bulk insert:
.....
forall i in 1..rec_cnt
INSERT INTO EAR
( ID, MBR_ID,
APRVD_DT, TRMTD_DT,
START_DT, END_DT, SEQ_NB)
VALUES ( last_ind2(i), last_org2(i),
TRUNC(rgstn_start_dt2(i)), TRUNC(rgstn_end_dt2(i)),
TRUNC(emp_start_dt2(i)), TRUNC(emp_end_dt2(i)), emp_seq_nb2(i));

exit when c_rgstn%notfound;

rec_cnt := 0; --reset rec_cnt for real array

end loop; /*out loop */

.....

the error message is only for the one 'PL/SQL storage'. DBA insisted nothing has been changed in database, and I looked at parameter file, pretty much stay same except we are in 9.2.0.4 now. but it seems we have 2 new databases installed in same unix box. My question is, if it is due to memory issue, I will expect something other generic error message instead of this specific one regarding 'PL/SQL storage'. DBA is kind of reluctant to open tar, as we can always switch back to old traditional PL/SQL though it takes much longer to run.

Let me know if this information is sufficient. Thanks in advance for any suggestion.



Tom Kyte
April 29, 2004 - 10:39 am UTC

I'd need a complete example -- table create, type creates, etc. I cannot reproduce without them.

Can I use bulk processing?

Steve, July 28, 2004 - 8:13 pm UTC

I need to move data from an oltp db (db1) to a dss db (db2). There are 15 related tables (parent/child to 3 levels) and I want to move all or none for each key value, so if any problems, I want to rollback everything for that key value.

Currently, this is being done with a 3GL program with multiple loops one row at a time. It processes about 3800 key values per hour and needs to process about 50K per day, so it runs for a very long time.

I'm currently rewriting this process in PL/SQL and I was hoping to be able to use bulk processing techniques to speed up the process as much as possible.

Current Process (running on db2):

For each row in the main table, select the key value

Loop thru each row in the other 14 tables and for each
key value:

If row already exists in db2, delete it
Insert into db2 from db1
Delete from db1

This is probably the worst way possible to do this.

Since the FORALL statement only allows for one insert/update/delete statement, it doesn't seem to be an option.

Are there any other bulk processing techniques that would be applicable for this kind of processing? Do you have any suggestions for a reasonably efficient way of doing it?

Thanks in advance for your help.

Tom Kyte
July 29, 2004 - 7:30 am UTC

if this is a "complete refresh" why not:

truncate t1, t2, t3;
insert into t1 select * from t1@oltp
same for t2, t3;

commit;?

Followup

Steve, July 29, 2004 - 1:36 pm UTC

It's not a complete refresh, just an archive of data which is no longer needed in the oltp environment to an historical backup. There are a few million rows on the oltp db and around 100 million in history. Each day we move about 50,000 more rows into history.

The main table links to other tables via foreign keys which in turn link to more tables through other foreign keys. I need to move all related rows after ensuring that they meet some relatively simple archive criteria.

I'm hoping to make use of whatever bulk processing techniques might be beneficial for this kind of processing. We have several more similar processes that run for hours which we may be able to rewrite if I can prove that the rewrite is worthwhile in this example.

Are there any bulk techniques you can advise trying?

Tom Kyte
July 29, 2004 - 2:11 pm UTC

your process looks like a "merge" to me then. merge in bulk to do the update or insert, followed by a delete.

not really a merge

Steve, July 29, 2004 - 4:18 pm UTC

Thanks for the quick responses, Tom.

It's very unlikely that the data will already be on db2, so the delete on db2 is just to cover rare situations. Even in those cases, it's unlikely that the number of rows in each of the tables will match, so there will be various combinations of deletes, inserts, and updates to get things in sync. It seems easier to just delete all existing data related to the key value from db2 and then just do inserts.

The process is mainly just an archive process that is appending to db2. The tricky part is handling the relationships between the tables.

What I would like to be able to do is nest the forall loops, like so:

open c_db1_tbl1; -- cursor of key values to archive
loop
fetch c_db1_tbl1 BULK COLLECT INTO key1_array LIMIT 100;
begin
forall i IN 1 .. key1_array.count SAVE EXCEPTIONS
delete all rows from 15 db2 tables for key1_array(i)
insert into db2.tbl1 select * from db1.tbl1
delete from db1.tbl1
open then fetch bulk tbl2
forall tbl2
insert into db2.tbl2 select * from db1.tbl2
delete from db1.tbl2
open then fetch bulk tbl3
forall tbl3
insert/delete tbl3
open then fetch bulk tbl4
forall tbl4
etc.

But I don't think this is possible with forall. Is there any other way to accomplish something like this? I've looked through many of the questions on this site, but haven't found anything that covers what I'm trying to do.


Tom Kyte
July 29, 2004 - 4:26 pm UTC

forall can only do a single statement at a time (you need a forall for each insert/delete)


seems you want to do 15 deletes:

delete from t1 where key in ( that query );
...

delete from t15 where key in ( that query );

and then
insert into t1 select * from whatever where key in ( that query );

for the 15 tables?

no bulk this time

Steve, July 29, 2004 - 4:57 pm UTC

That sounds like the best approach. 15 deletes from db2 followed by 15 inserts into db2 followed by 15 deletes from db1. The 'key in' subqueries will be different for each table and some will get complex because of the table relationships, but it's probably still going to be better than the alternatives.

Like you've said on this site many times, if you can do it in sql, then use sql.

Thanks for the input, Tom.

forall with merge

sudipta, July 30, 2004 - 4:43 am UTC

Is there anty tentative deadline when merge will be supported with bulk binds and forall .

Tom Kyte
July 30, 2004 - 4:36 pm UTC

as I've said before -- forall doesn't *make sense* with merge!  merge already *works* on sets.  merge is only about sets.  You don't *need* forall.

ops$tkyte@ORA9IR2> create or replace type myScalarType as object
  2  ( x int,
  3    a int,
  4    b date,
  5    c varchar2(10)
  6  )
  7  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace type myTableType as table of myScalarType
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int primary key, a int, b date, c varchar2(10) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
  2  select rownum, object_id, created, substr(object_name,1,10)
  3  from all_objects where rownum <= 10;
 
10 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data myTableType;
  3  begin
  4          select myScalarType( x,a,b,c ) bulk collect into l_data
  5            from t;
  6
  7          for i in 1 .. l_data.count
  8          loop
  9                  l_data(i).c := lower(l_data(i).c);
 10          end loop;
 11
 12          l_data.extend;
 13          l_data(l_data.count) := myScalarType( 20, 1, sysdate, 'xxx' );
 14
 15          l_data.extend;
 16          l_data(l_data.count) := myScalarType( 21, 2, sysdate, 'xxx' );
 17
 18          merge into t
 19          using ( select * from table(cast(l_data as mytableType)) ) d
 20          on (t.x = d.x)
 21          when matched then update set c = d.c
 22          when not matched then insert values ( d.x, d.a, d.b, d.c );
 23  end;
 24  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          A B         C
---------- ---------- --------- ----------
         1      17287 12-MAY-02 /1005bd30_
         2       7559 12-MAY-02 /10076b23_
         3      22543 12-MAY-02 /10297c91_
         4      13615 12-MAY-02 /103a2e73_
         5      22346 12-MAY-02 /1048734f_
         6      10173 12-MAY-02 /10501902_
         7      22748 12-MAY-02 /105072e7_
         8      22798 12-MAY-02 /106ba0a5_
         9       9849 12-MAY-02 /106faabc_
        10      11261 12-MAY-02 /10744837_
        20          1 30-JUL-04 xxx
        21          2 30-JUL-04 xxx
 
12 rows selected.



or if you were totally wed to the concept of index by tables and didn't want the (easier to use) types -- you could:


ops$tkyte@ORA9IR2> declare
  2          type array is table of varchar2(10) index by binary_integer;
  3          l_x array;
  4          l_a array;
  5          l_b array;
  6          l_c array;
  7  begin
  8          select x,a,b,c bulk collect into l_x, l_a, l_b, l_c
  9            from t;
 10
 11          for i in 1 .. l_c.count
 12          loop
 13                  l_c(i) := lower(l_c(i));
 14          end loop;
 15
 16          l_x(l_x.count+1) := 20;
 17          l_a(l_a.count+1) := 1;
 18          l_b(l_b.count+1) := sysdate;
 19          l_c(l_c.count+1) := 'xxx';
 20
 21          forall i in 1 .. l_x.count
 22          merge into t
 23          using ( select l_x(i) x, l_a(i) a, l_b(i) b, l_c(i) c from dual )d
 24             on (t.x = d.x)
 25           when matched then update set c = d.c
 26           when not matched then insert values ( d.x, d.a, d.b, d.c );
 27  end;
 28  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          A B         C
---------- ---------- --------- ----------
         1      17287 12-MAY-02 /1005bd30_
         2       7559 12-MAY-02 /10076b23_
         3      22543 12-MAY-02 /10297c91_
         4      13615 12-MAY-02 /103a2e73_
         5      22346 12-MAY-02 /1048734f_
         6      10173 12-MAY-02 /10501902_
         7      22748 12-MAY-02 /105072e7_
         8      22798 12-MAY-02 /106ba0a5_
         9       9849 12-MAY-02 /106faabc_
        10      11261 12-MAY-02 /10744837_
        20          1 30-JUL-04 xxx
 
11 rows selected.
 
 

FORALL with MERGE

A reader, October 29, 2004 - 12:30 pm UTC

One reason I would like to see FORALL support MERGE is just so I can use the 'save exceptions' clause.

In other words, MERGE is a single set-based statement, its all or nothing. Some records in my "using" set contain some bad values for some columns.

1 bad column in 1 record out of 1000s fails the entire merge!

Instead I would like to just skip that bad record and continue.

Suggestions? Thanks

Tom Kyte
October 29, 2004 - 4:54 pm UTC

forall does support merge, hit "page up", it is right there?

FORALL with merge

A reader, October 29, 2004 - 8:03 pm UTC

Not sure I understand. Right now, I have a simple merge statement using the traditional staging and main table like

MERGE into main
using (select * from staging) stage
on (main.pk=stage.pk)
when matched then update set
main.col1=stage.col1
main.col2=stage.col2
...
when not matched then insert
(
pk,
col1,
col2,
...
)
values
(
stage.pk,
stage.col1,
stage.col2,
...
);

1. How can I convert this to use FORALL so that I can use the save exceptions clause to save exceptions and log them to another table, report on them, whatever?

2. How can I use this with dynamic SQL where I want the target table above (main) to be passed in as a variable. Right now, I am doing

execute immediate 'merge into '||l_table_name||' ...'

How can I do this with FORALL?

Thanks

Tom Kyte
October 30, 2004 - 2:01 pm UTC

well -- you cannot -- that would be like asking how do I convert this:

delete from t where x = 5;

into a forall - you cannot unless you

a) read out all of the x=5's primary keys/rowids (using select for update)
b) process them row by row


you see, your MERGE is the functional equivalent of "delete from t where x = 5", "update t set y = 42 where x = 55", "insert into t select * from t where x = 2"


Tell me -- if you had "insert into t select * from t2" and wanted to do "slow by slow" -- what would you do? Do the same exact thing to merge!


A reader, October 30, 2004 - 2:22 pm UTC

You are confusing me even more. You just said that FORALL supports MERGE and I gave you a simple example of a MERGE statement and you say I cannot use it for FORALL?

Using your example above on the page, are you saying I need to something like

stage_array staging%rowtype;

select * bulk collect into stage_array from staging;

forall i in 1..stage_array.count save exceptions
merge into main
using (select pk(i) pk,col1(i) col1,col2(i) col2,... from dual) stage
on (main.pk=stage.pk)
when matched then update set
main.col1=stage.col1
main.col2=stage.col2
...
when not matched then insert
(
pk,
col1,
col2,
...
)
values
(
stage.pk,
stage.col1,
stage.col2,
...
);

If so, why didnt you say that? Do I need to list out all the columns in the select ... from dual, cant I just get the whole %rowtype?

Also, how about my other question about the dynamic SQL. Can I use FORALL with EXECUTE IMMEDIATE and pass in the table name ('main' above) as a variable?

Thanks

Tom Kyte
October 30, 2004 - 2:38 pm UTC

Merge supports forall in EXACTLY the same way delete does, update does, insert does.

That is -- a single row at a time, in a loop


I did say just that -- sorry if I was confusing.  


forall supports insert, update, delete, merge in EXACTLY the same fashion.  you have arrays, you subscript arrays, you work a row at a time.  No difference, none.

(i thought the example of how to use merge - above - showed how to use merge in forall -- forall is a method of taking a HOST ARRAY and executing a single sql statement over and over and over using the values from the array, that is the very definition of what "forall" does.  It is used with merge in the same fashion as anything else).


delete from t where x = 5;

can you use that with forall?  No, there is no array of values to be passed into it -- one of the prereqs for using forall.


MERGE into main
using (select * from staging) stage
on (main.pk=stage.pk)
when matched then update set
 main.col1=stage.col1
 main.col2=stage.col2
...

can you use that with forall?  No, there is no array of values to be passed into it -- one of the prereqs for using forall.



as for the dynamic sql thing -- why would you have multiple tables with the identical structure, but just the name changes (bad idea, not a good way to implement)

The PLSQL guide has the gory details about FORALL -- including in the Native Dynamic SQL chapter that you can:


Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time: 

BULK FETCH statement 
BULK EXECUTE IMMEDIATE statement 
FORALL statement 
COLLECT INTO clause 
RETURNING INTO clause 
%BULK_ROWCOUNT cursor attribute

<b>and here is an example...</b>



ops$tkyte@ORA9IR2> create table t ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X          Y
---------- ----------
         1
 
ops$tkyte@ORA9IR2> declare
  2          type array is table of number;
  3          l_x array := array(1,2);
  4          l_y array := array(42,55);
  5  begin
  6          forall i in 1 .. l_x.count
  7                  execute immediate '
  8                  merge into t
  9                  using (select :x x, :y y from dual) d
 10                  on (t.x=d.x)
 11                  when matched then update set y = d.y
 12                  when not matched then insert values (d.x,d.y)' using l_x(i), l_y(i);
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
         X          Y
---------- ----------
         1         42
         2         55
 

 

A reader, October 30, 2004 - 3:15 pm UTC

OK Thanks for clearing that up. Now that we have talked about all this, I am still a little unclear as to what your suggested solution is.

Problem: I have 2 tables, a staging table with all varchar2 columns and a main table with proper columns (date, number, varchar2). Since staging is all varchar2, it acepts bad data as well (i.e. alpha in numeric field, bad dates etc).

I want to merge the staging into the main as fast as possible. MERGE seemed like the best way, but since the staging can contain bad data, a single bad column can fail the entire load. Thats why FORALL with its save exceptions seemed attractive.

Having said all that, how do you suggest I use FORALL with MERGE in this case? (forget about the dynamic sql part for now)

Was my understanding in the earlier post correct? i.e. I bulk collect my staging table into a %rowtype array and FORALL that array with MERGE and select from dual?

If so, can I do

select <entire record>(i) from dual

instead of

select pk(i), col1(i), col2(i),... from dual;

Thanks

Tom Kyte
October 30, 2004 - 4:31 pm UTC

if your goal is to_merge_asap
then
scrub stage
merge in a single statement
else your_goal_isnt_to_merge_asap
then
slow by slow processing to merge
end if;


if you want to use merge with forall -- you must "array collect" N rows from stage and "forall i" merge them with real table. Since in the merge you will need individual columns, you will select individual columns.

A reader, October 30, 2004 - 6:02 pm UTC

This process is not that critical, so I dont want to waste time preprocessing (scrubbing) stage. I just want to MERGE, skip errors and proceed.

"Since in the merge you will need individual columns, you will select individual columns"

Well, I was hoping I could use something like you used above

select * from table(cast(l_row(i) as rowtype))

I dont have access to Oracle right now, but can I do something like

type x_t is table of staging%rowtype index by binary_integer;
x_array x_t;

select * bulk collect into x_array from staging;

forall i in 1..x_array.count
merge into main
using (select * from table(cast (x_array(i) as x_t)) z
on (z.pk=main.pk)

Or is the cast() used only when I have a real user-defined SQL object type?

Thanks

Tom Kyte
October 31, 2004 - 2:46 am UTC

since speed is obviously not the concern -- skip forall, use

update
if sql%rowcount = 0
then
insert
end if

and you can use your records. you could probably use the collection in the query but so what? you have to list columns in the when matched, you have to list columns in the when not matched.

If your primary concern is keystrokes, use update/else insert.

FORALL is slow-by-slow?

A reader, October 30, 2004 - 6:05 pm UTC

Are you saying that FORALL is row-by-row processing? Surely not. So, for a given data set, is FORALL dm lslower than just the plain dml?

Tom Kyte
October 31, 2004 - 2:49 am UTC

forall is bulk row by row processing. It sends to the sql engine a single statement to be executed N times.


insert into t select * from t2; <<<=== single sql statement


cursor c is select * from t;
....
begin
open c;
loop
fetch c bulk collect into ..... limit 100;
forall i in 1 .. N
insert into t2 values ( ..... );
exit when c%notfound;
end loop;
close c;
end;


is row by row processing done with host arrays, better than


for x in ( select * from t )
loop
insert into t2 values ( .... );
end loop;

but not nearly as good as


insert into t select * from t2;



Question on FORALL

Ajums TT, November 16, 2004 - 4:55 am UTC

Hi Tom,


We wanted to write delete statements for a number of tables using the FORALL construct in PL/SQL in the following fashion.

FORALL indx IN table_list.FIRST..table_list.LAST
EXECUTE IMMEDIATE 'DELETE FROM '|| table_list(indx) ||
' WHERE '|| field_list(indx) ||'= :val'
USING value;
END LOOP;

In the above delete statement the table_list and field_list are single column tables holding the values of the table names and field names respectively. Thus we avoid hardcoding mulitple delete statements. However the above statement does not compile with FORALL but runs fine with FOR. We assume that it is because we are passing the name of a schema object as a variable which the FORALL construct does not allow. We would like to know if we are right in our assumption. Also please let us know if there is any way in which we could stick to this idea and still use FORALL for all its performance advantages.


Thanks & Regards,
Ajums TT

Tom Kyte
November 16, 2004 - 6:43 am UTC

that is not going to work. you cannot bind identifiers. forall executes a SINGLE STATEMENT N times.

You have N distinct statements to execute.

there is no way to "performance enhance" a series of statements like that, you have N sql statements to execute.

How to measure # of context switches

Matthias Rogel, November 16, 2004 - 8:06 am UTC

Hallo Tom,

it is well known that context switches between the PL/SQL and SQL engines are expensive.

Where do I see the number of context switches a session has
performed so far (Version 9.2)  ?

SQL> select name from v$sysstat where upper(name) like '%SWITCH%';

NAME
----------------------------------------------------------------
switch current to new buffer
redo log switch interrupts
OS Voluntary context switches
OS Involuntary context switches

doesn't seem to be the right approach.

 

Tom Kyte
November 16, 2004 - 12:53 pm UTC

tkprof -- number of execute, fetch, parse calls.



To send or not to send..

A reader, November 16, 2004 - 8:28 am UTC

Tom, what implications can we have if we send all of the elements in just one FORALL call ?

Let me explain further, suppose we are doing bulk processing, alas:

loop
fetch cur bulk collect into var limit 100;
-- some processing, and stuff..
forall i in 1..var.count
insert into some_table ( x ) values ( var( i ) );
exit when cur%notfound;
end loop;

for 1000 elements, 1 parses, 10 executes.. okay.. but what if:

loop
-- some processing, and stuff with of course, a
-- table array with indexes and what not..
exit when cur%notfound;
end loop;
forall i in 1..var.count
insert into some_table ( x ) values ( var( i ) );

.. on the litle testing I have done ( no production as of yet ) it does seems to be a slight faster, but of course, I have only do testing with some thousands rows.. what do you think ?

Tom Kyte
November 16, 2004 - 12:57 pm UTC

eats more ram and won't be faster in the long run (you sort of slam the database, the redo log buffer with lots of work all at once)

a couple hundred rows at a time, that is all you need or want to do.

I don't know how to update records and to lock it.

MARY, November 26, 2004 - 4:52 pm UTC

Hello,Tom!
Your answers abou forall help me.But I want to clarify something.I have to make update in some table - approximitely 300 records,and i have to do it in cursor "for",because i take the sum of values from another table.it looks like

declare cursor cur
is select * from km_sales
where month between to_date('01/02/2004','dd/mm/yyyy')
and to_date('01/04/2004','dd/mm/yyyy')
for update;
for i in cur loop
here i make select sum from another table and where of
sum depends on value cur in main table
and make
update km_sales set i.sum=sum from another table
i don't know how it is better to do : by this for cursor or to load this cursot into plsql table and then to update this table by for all and if do it in a such what will be whith locking.Generaly is it better to run through
table by regular for cursor or to load the DATA INTO PLSQL TABLE and to run through plsql table.
I am confused about it.

.Tnak you ver much for explanation.



is se

Tom Kyte
November 26, 2004 - 4:56 pm UTC

that looks like a single update statement to me, i don't see a reason for slow by slow processing.

why have any code at all? sql is quite powerful.

CONTINUE TO "I DON'T KNOW HOW TO UPDATE RECORDS AND TO LOCK IT"

Mary, November 27, 2004 - 5:37 am UTC

Hello, Tom!
Thank you very much for the answer.
But when i run over the main table that i want to update
i have very complex logic that the reason i don't know how
to make update in one statement
and generally i don't understand what is the advantage to run through the PLSQL TABLE instead of running in "FOR" cursor and how can i lock records when RUNNING THROUGH THE PLSQL TABLE.

I 'll show my code:

DECLARE
CURSOR CUR IS SELECT * FROM KM_SALES
FOR UPDATE
WHERE MONTH BETWEEN TO_DATE('01/02/04','DD/MM/YY')
AND TO_DATE('01/04/04','DD/MM/YY')
ORDER BY PROD_NO,MONTH,LEVEL_NO;

L_PREV_PROD NUMBER(4);
L_PREV_MONTH DATE;
L_SUM1 NUMBER(7);
L_SUM2 NUMBER(7);
L_SUM3 NUMBER(7);
L_COUNT_EDITIONS NUMBER(4);
BEGIN
FOR I IN CUR LOOP
--HERE I WANT TO CHECK THAT IF THERE IS A BREAK ON --
PROD_NO OR MONTH THEN DO SOME FUNCTION--
IF NVL(L_PREV_PROD_NO,-1)!=I.PROD_NO
OR NVL(L_PREV_MONTH,TO_DATE('31/12/19','DD/MM/YY')! =I.MONTH
THEN
L_COUNT_EDITIONS:=FUNC(I.PROD_NO,I.MONTH);
END IF;
IF L_COUNT_EDDITIONS!=0 THEN
--HERE THE WHERE AND SUMS OF MY SELECT DEPEND ON
-- VALUES OF LEVEL_NO AND PROD_NO FROM CURSOR.
-- I didn't want to use execute immediate with
-- dinamic SQL BECAUSE I KNOW THAT IN LOOPS IT WORKS SLOWER THAN REGULAR SQL--
IF I.LEVEL_NO=0 THEN
IF I.PROD_NO=1 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE NULL) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE NULL) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
ELSIF PROD_NO=2 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE NULL) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE NULL) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.DAY_IN_WEEK!='HOLIDAY'
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
ELSE --I.PROD_NO NOT IN (1,2)
THEN
SELECT
SUM(A.QTY) SUM3 INTO L_SUM3
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM3/L_COUNT_EDITIONS);
L_SUM1:=NULL;
L_SUM2:=NULL;
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
ELSIF I.LEVEL_NO=1 THEN
IF I.PROD_NO=1 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY1 ELSE NULL) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY2 ELSE NULL) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BRANCH_NO=I.VALUE_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
ELSIF PROD_NO=2 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE NULL) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE NULL) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.DAY_IN_WEEK!='HOLIDAY'
AND B.BRANCH_NO=I.VALUE_NO
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
ELSE --I.PROD_NO NOT IN (1,2)
THEN
SELECT
SUM(A.QTY) SUM3 INTO L_SUM3
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.BRANCH_NO=I.BRANCH_NO
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM3/L_COUNT_EDITIONS);
L_SUM1:=NULL;
L_SUM2:=NULL;
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;
END IF; ---PROD_NO
END IF; --LEVEL_NO
END IF; --L_COUNTER_EDDITIONS;
END LOOP;
END;

Tom Kyte
November 27, 2004 - 9:27 am UTC

sorry -- that code does not compute (it never ran....)

for update is in the wrong place.

--HERE I WANT TO CHECK THAT IF THERE IS A BREAK ON -- PROD_NO OR MONTH THEN DO SOME FUNCTION--
IF NVL(L_PREV_PROD_NO,-1)!=I.PROD_NO
OR NVL(L_PREV_MONTH,TO_DATE('31/12/19','DD/MM/YY')!=I.MONTH
THEN
L_COUNT_EDITIONS:=FUNC(I.PROD_NO,I.MONTH);
END IF;


you never ever set l_prev_month, so that is fairly faulty logic.

the if/else/blocks do not "line up" -- they are not opened/closed properly.


so, i'm afraid to say how to "redo it" since it is not quite right.


Basically, if you have logica like:


for x in ( select .... )
loop
compute something;
update that row you just fetched
end loop


you'll


select ROWID rid, .... bulk collect into arrays with for update;

for i in 1 .. arrays.count
loop
fill in more arrays
end loop

forall i in 1 .. array.count
update table set ..... where rowid = rids(i);

or, if you have lots of rows you will:


open that same cursor
loop
fetch bulk collect into arrays limit 500;
for i in 1 .. arrays.count
loop
fill in more arrays
end loop;
forall i in 1 .. array.count
update table set .... where rowid = rids(i);
exit when cursor%notfound;
end loop;
close cursor

CONTINUE TO " I DON'T KNOW HOW TO UPDATE"

Mary, November 27, 2004 - 12:54 pm UTC

Hello, Tom!
I am sorry for a wrong code, i just didn't run it because i think it is a bad code, just plan it,i am new in ORACLE and it will help me if you could explain me how to make this code in sql statement (NOT IN FOR CURSOR) as you said before.The problem here that i have a complex logic with select for all the records in cursor,here is the same example as i gave above, i just corrected it a little bit.
EVEN though if the solution of my problem ise the update sql statement i want to understand a few points:
1.Why it is better to run through the PLSQL table than running through the regular FOR cursor (with no connection to FORALL UPDATE). DO I have to run through plsql table even though i have about 300 records?What about locks if i run through plsql table and need to update records?
Is it always more effective to load data into plsq table
and to run through the table instead of regular cursor with fetch?
I would like to see it in examples.

2.What is the difference between "FOR X IN (SELECT....)
and "declare cursor cur is select * from km_sales;
for x in cur loop..."

3.What is the difference between "SELECT NUM1,NUM2
BULK COLLECT INTO
T_NUM1,T_NUM2"
and "FETCH INTO BULK COLLECT"?
Can you show it by examples?

My problematic code is written bellow :
What is complex here is that the structure of select statements changes when level_no and prod_no change.

DECLARE
CURSOR CUR IS SELECT PROD_NO,
MONTH,
LEVEL_NO,
VALUE_NO FROM KM_SALES;
WHERE MONTH BETWEEN TO_DATE('01/02/04','DD/MM/YY')
AND TO_DATE('01/04/04','DD/MM/YY')
FOR UPDATE
ORDER BY PROD_NO,MONTH,LEVEL_NO;

L_PREV_PROD NUMBER(4);
L_PREV_MONTH DATE;
L_SUM1 NUMBER(7);
L_SUM2 NUMBER(7);
L_SUM3 NUMBER(7);
L_COUNT_EDITIONS NUMBER(4);
BEGIN
FOR I IN CUR LOOP
--HERE I WANT TO CHECK THAT IF THERE IS A BREAK ON --
PROD_NO OR MONTH THEN DO SOME FUNCTION--

IF NVL(L_PREV_PROD_NO,-1)!=I.PROD_NO
OR NVL(L_PREV_MONTH,TO_DATE ('31/12/19','DD/MM/YY')! =I.MONTH
THEN
--THIS FUNCTION GET TO ANOTHER TABLE AND RETURNS
--COUNT OF RECORDS FINDING THEM BY I.PROD_NO,I.MONTH
L_COUNT_EDITIONS:=FUNC(I.PROD_NO,I.MONTH);

END IF;
IF L_COUNT_EDDITIONS!=0 THEN
--HERE THE STRUCTURE OF MY SELECT DEPENDS ON
-- VALUES OF I.LEVEL_NO AND I.PROD_NO FROM THE CURSOR.
-- I didn't use execute immediate with
-- dinamic SQL BECAUSE I KNOW THAT IN LOOPS IT WORKS SLOWER THAN REGULAR SQL--
IF I.LEVEL_NO=0 THEN
IF I.PROD_NO=1 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE
NULL END) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE
NULL END) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
ELSIF PROD_NO=2 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE
NULL END ) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE
NULL END) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.DAY_IN_WEEK!='HOLIDAY'
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);

ELSE --I.PROD_NO NOT IN (1,2)
THEN
SELECT
SUM(A.QTY) SUM3 INTO L_SUM3
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM3/L_COUNT_EDITIONS);
L_SUM1:=NULL;
L_SUM2:=NULL;
ELSIF I.LEVEL_NO=1 THEN
IF I.PROD_NO=1 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY1
ELSE NULL END) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY2 ELSE
NULL END) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BRANCH_NO=I.VALUE_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);

ELSIF PROD_NO=2 THEN
SELECT
SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY ELSE
NULL END) SUM1,
SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY ELSE
NULL END) SUM2 INTO L_SUM1,L_SUM2
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.DAY_IN_WEEK!='HOLIDAY'
AND B.BRANCH_NO=I.VALUE_NO
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM1+L_SUM2/L_COUNT_EDITIONS);
L_SUM1:=ROUND(L_SUM1/L_COUNT_EDITIONS);
L_SUM2:=ROUND(L_SUM2/L_COUNT_EDITIONS);
ELSE --I.PROD_NO NOT IN (1,2)
SELECT
SUM(A.QTY) SUM3 INTO L_SUM3
FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
WHERE PROD_NO=I.PROD_NO
AND MONTH=I.MONTH
AND A.BRANCH_NO=I.BRANCH_NO
AND A.CSTMR_NO=B.CSTMR_NO
AND B.BSNSS_SW='YES';
L_SUM3:=ROUND(L_SUM3/L_COUNT_EDITIONS);
L_SUM1:=NULL;
L_SUM2:=NULL;
END IF; ---PROD_NO
END IF; --LEVEL_NO
UPDATE KM_SALES SET ACTUAL_QTY1=L_SUM1,
ACTUAL_QTY2=L_SUM2,
ACTUAL_QTY3=L_SUM3
FOR CURRENT OF;

END IF; --L_COUNTER_EDITIONS;
L_PREV_PROD:=I.PROD;
L_PREV_MONTH:=I.MONTH;
END LOOP;
END;

Sorry for a complication.
Mary.


Tom Kyte
November 27, 2004 - 1:13 pm UTC

1) bulk operations are faster and more efficient than non-bulk operations.

the more you can do in a single execution sql wise, the better. (if you have any of my books -- i have many examples in there. Connor McDonalds PL/SQL book goes into this in great detail as well)


for 300 records done once, you might not notice it. but for 3000, 30000 records... or if you do 300 records over and over and over -- you will.


2) for x in ( select .... )

is a) faster (marginally), b) easier to code (definitely), c) less code (obviously) and d) my totally preferred method of doing it.

but if you want to bulk collect with a LIMIT clause, you have to use an explicit cursor.


3) I have, in a book called "Effective Oracle by Design" -- the effective PL/SQL chapter.

If you were to fetch all of the rows, select into is the way to go -- less code, less chance of a programmer induced bug (because of less code), and marginally faster.

if you need to use LIMIT (eg: you have 5000 records to process) the explicit cursor is the way to go.

CONTINUE "I DON'T KNOW TO UPDATE"

MARY, November 27, 2004 - 1:29 pm UTC

Tom,thank you very much for the questions,
but i don't know how to make more effective my problematic code that i wrote above.i thought about it a lot of hours and haven't found any solution.you told me that i can do
this by update sql statement but i THOUGT ABOUT IT AND don't know how to do that.Please READ MY CODE AND help me to find the more effective solution to this problematic code and as i mentioned before
the problem there that the select structure depends on prod_no and level_no from the cursor AND I DO IT IN IF CONDITIONS AND I THINK IT IS NOT GOOD.
I am going to buy your books,but i need to find the solution till tomorrow.
THANK YOU VERY MUCH
MARY


Tom Kyte
November 27, 2004 - 1:33 pm UTC

confusion? i outlined what I might do to it in the initial block of code.

bulk fetch
fill in arrays with whatever computation
forall i update

that would be step one. I cannot rewrite every piece of code out there.

CONTINUE "I CANNOT UPDATE"

Mary, November 27, 2004 - 3:00 pm UTC

HI TOM,
I am just confused about the select structure.
Can you give me tip teoretically to make this DEMO CODE in a better way without bulkcoolect into plsql table.
I THOUGHT about EXECUTE_IMMEDIATE
but as i understand it's slower than static sql.
MAY BE there is another more efficient way to do that?
MAY BE BY SQL STATEMENT?

FOR A IN 1..5 LOOP
IF A=1 THEN
SELECT C,D,E FROM TEMP INTO VAR_DEMO
WHERE D=1 AND E=2;
ELSIF A=2
THEN SELECT C,D,E FROM TEMP INTO VAR_DEMO
WHERE C=10 AND E=11;
ELSIF A=3
THEN SELECT C/D,E-1 FROM TEMP
INTO VAR_DEMO
WHERE D=1;
ELSIF A=5
THEN SELECT C,D,E FROM TEMP
INTO VAR_DEMO
WHERE D=5;
END IF;
END LOOP;

Thank you very much.


Tom Kyte
November 27, 2004 - 3:04 pm UTC

<quote>
Can you give me tip teoretically to make this DEMO CODE in a better way without
bulkcoolect into plsql table.
</quote>

nope -- bulk collect is my advice. your choices are slow by slow or bulk collect here.

Sorry,Tom!

Mary, November 27, 2004 - 3:34 pm UTC

I am not sure i understood you well.
I want to go by this solution:
select ROWID rid, .... bulk collect into arrays with for update;

for i in 1 .. arrays.count
loop
fill in more arrays
end loop

forall i in 1 .. array.count
update table set ..... where rowid = rids(i);

But for each row in arrays table
i need to do this checks:
if i.prod_no=1 then select1 into l_sum
if i.prod_no=2 then select2 into l_sum
and so on..
and eventually i have to update the sum of the row with l_sum, so where i need to do this checks: after the forall and before the update or in "fill in more arrays" and to put l_sum into right position in arrays?What do you mean
in "fill in more arrays"? IS the logic of
"if i.prod_no=1 then select1 into l_sum
if i.prod_no=2 then select2 into l_sum"
instead of execute immediate is right?
THANK'S

Tom Kyte
November 27, 2004 - 4:33 pm UTC

You can get everything you need out in one query:

create or replace type sum_type as object
( sum1 number, sum2 number, sum3 number )
/
                                                                                                              
SELECT i.rowid,
       i.PROD_NO,
       i.MONTH,
       i.LEVEL_NO,
       i.VALUE_NO,
       case  when i.level_no = 0 and i.prod_no = 1
             then ( SELECT sum_type( SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY END),
                                      SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY END),
                                      NULL )
                       FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
                      WHERE PROD_NO=I.PROD_NO
                        AND MONTH=I.MONTH
                        AND A.CSTMR_NO=B.CSTMR_NO
                        AND B.BSNSS_SW='YES')
             when i.level_no = 0 and i.prod_no = 2
             then ( SELECT sum_type( SUM(CASE WHEN B.BSNSS_NO=6 THEN A.QTY END),
                                     SUM(CASE WHEN B.BSNSS_NO!=6 THEN A.QTY end),
                                     NULL )
                      FROM KM_CSTMR_SUM A,KM_CUSTOMERS B
                     WHERE PROD_NO=I.PROD_NO
                       AND MONTH=I.MONTH
                       AND A.DAY_IN_WEEK!='HOLIDAY'
                       AND A.CSTMR_NO=B.CSTMR_NO
                       AND B.BSNSS_SW='YES' )
             ......
             end DATA
  from KM_SALES I
 WHERE i.MONTH BETWEEN TO_DATE('01/02/04','DD/MM/YY') AND TO_DATE('01/04/04','DD/MM/YY')
 ORDER BY i.PROD_NO, i.MONTH, i.LEVEL_NO
for update;


Your column "data" will have up to three sums in it -- based on the scalar subquery that was actually executed.  so you would open that, fetch 500 rows, fill an arrays:

  L_SUM3(i):=...
         L_SUM1(i):=...;
         L_SUM2(i):=...;

so you can then

  forall i in 1 .. l_rowid_array.count
     update t set column = l_sum1(i), .... where rowid = l_rowid_array(i);


          
eg:


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace type sum_type as object
  2  ( sum1 number, sum2 number, sum3 number )
  3  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename, sal, comm from emp;
 
ENAME             SAL       COMM
---------- ---------- ----------
SMITH             800
ALLEN            1600        300
WARD             1250        500
JONES            2975
MARTIN           1250       1400
BLAKE            2850
CLARK            2450
SCOTT            3000
KING             5000
TURNER           1500          0
ADAMS            1100
JAMES             950
FORD             3000
MILLER           1300
 
14 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2  cursor c
  3  is
  4  select e.rowid rid,
  5         e.ename,
  6         e.job,
  7         case  when job = 'CLERK'
  8               then (select sum_type( sum(comm), sum(sal), null )
  9                       from emp
 10                      where job = 'CLERK'
 11                        and emp.empno <> e.empno )
 12               when job = 'SALESMAN'
 13               then (select sum_type( sum(sal), count(*), max(comm) )
 14                       from emp
 15                      where job = 'SALESMAN'
 16                        and emp.empno <> e.empno )
 17              else sum_type( null, null, null )
 18          end data
 19    from emp e
 20   order by job, ename
 21    for update;
 22
 23  type array is table of c%rowtype index by binary_integer;
 24  type numArray is table of number index by binary_integer;
 25  type ridArray is table of rowid index by binary_integer;
 26  l_data array;
 27  l_sum1 numArray;
 28  l_sum2 numArray;
 29  l_sum3 numArray;
 30  l_rid  ridArray;
 31
 32  begin
 33      open c;
 34      loop
 35          fetch c bulk collect into l_data LIMIT 5;
 36
 37          for i in 1 .. l_data.count
 38          loop <b>-- do your computations here....</b>
 39              l_sum1(i) := l_data(i).data.sum1;
 40              l_sum2(i) := l_data(i).data.sum2;
 41              l_sum3(i) := l_data(i).data.sum3;
 42              l_rid(i)  := l_data(i).rid;
 43          end loop;
 44
 45          forall i in 1 .. l_data.count
 46              update emp set comm = l_sum1(i), sal = l_sum2(i) where rowid = l_rid(i);
 47
 48          exit when c%notfound;
 49      end loop;
 50      close c;
 51  end;
 52  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select ename, sal, comm from emp;
 
ENAME             SAL       COMM
---------- ---------- ----------
SMITH            3350
ALLEN               3       4000
WARD                3       4350
JONES
MARTIN              3       4350
BLAKE
CLARK
SCOTT
KING
TURNER              3       4100
ADAMS            3050
JAMES            3200
FORD
MILLER           2850
 
14 rows selected.

 

TOM ,IT IS GREAT! What is the differenece between record type and object type?

Mary, November 27, 2004 - 4:57 pm UTC

TOM,Thank you very much.I am a little bit confused about the difference between record type and object type?

Tom Kyte
November 27, 2004 - 5:23 pm UTC

record types are PLSQL types only, only plsql can "see them"

the object type is both a SQL and PLSQL type -- since we wanted a 'record' in SQL, we need an object type.

I work with Oracle 8i and i have to define tables as number of fields in cursor.

mary, November 27, 2004 - 5:40 pm UTC

So,how to behave with the object type for sum's - just to define type sums_array is table of sum_type index by binary_integer ?Why we need object type sum_type and not separate fields sum1,sum2,sum3

Tom Kyte
November 27, 2004 - 6:13 pm UTC

you need an object type, or you'll be "parsing" -- a scalar subquery returns a single row/column. object types let us do that.

you can fetch into a table of object types -- however, you won't be able to use the scalar subquery in 8i (you never mentioned that -- your code above contained CASE in a plsql block so I assumed you were at least on 9i but sigh, no version...)

and well, it'll just start falling apart from there if you need the for update, cause that isn't going to happen in 8i.




sorry -- you say "need by tomorrow", time to go back to "slow by slow" i guess. wish I would have known the version along time ago.

TOM,YOUR SOLUTION IS GREAT BECAUSE WHEN WE 'LL UPGRADE TO ORACLE 9I I CAN USE IT IN OTHER SOLUTIONS

mary, November 27, 2004 - 6:31 pm UTC

It can help me very much in another type of programs in FUTURE ORACLE 9I.
I i use case statement in slq statement in plsql programs
doesn't it works in Oracle 8i even IF I'LL USE it in SQL STATEMENT LIKE CURSOR DECLARATIONS AND IN SQL INTO....?
IT IS STRANGE BECAUSE the sql statements are performed by sql engine and sql parser?

IT 'IS

Tom Kyte
November 27, 2004 - 7:22 pm UTC

in 8i and before, plsql had its own SQL parser. It did not always understand 100% of what was possible

o case
o order by in a subquery
o analytics
o group by rollup/cube

and others. the workaround was to use dynamic sql or a view. Dynamic sql will get in the way of the bulk binds in 8i and the view will prevent the for update in 8i.

is this a bug?

A reader, March 11, 2005 - 8:22 am UTC

using FORALL we are forced to use this

TABLE X has 2 columns, COL_A and COL_B

FORALL i IN 1..DATA.COUNT
INSERT INTO X VALUES DATA(i);

however if i specify the column names as follows

INSERT INTO X (COL_A, COL_B) VALUES DATA(i);

it complains that there are not enough values, is this a bug? 9.2.0.4 and 10.0.1.2 both give error

or this is expected behaviour?


Tom Kyte
March 11, 2005 - 10:51 am UTC

insert into (select col_a, col_b from x ) values data(i);

no bug, the way records work.

</code> https://www.oracle.com/technetwork/issue-archive/2013/13-jul/o43asktom-1958803.html <code>

talks about them

PLS-00331 Illegal reference to BULK_EXCEPTION

Chotu, April 01, 2005 - 2:21 pm UTC

Tom,
When i try to display error mesage captured during FORALL execution, iam getting this error .

Here is the code i am using
---------------------------------------------
create or replace procedure test_forall(p_id IN integer,
p_field_name in dbms_sql.varchar2_table,
p_field_value in dbms_sql.varchar2_table)
as
errors NUMBER;
dml_errors EXCEPTION;
l_cnt number := 0;
error_mssg dbms_sql.varchar2_table;
iteration_no dbms_sql.number_table;

PRAGMA exception_init(dml_errors, -24381);

Begin
begin
forall i in 1..p_field_name.count Save Exceptions
Insert into my_tab_details(id,
field_name,
field_value)
values (p_id,
p_field_name(i),
p_field_value(i));

exception
when dml_errors then
errors :=SQL%BULK_EXCEPTIONS.count;
For i in 1..errors
loop

dbms_output.put_line('Error occured while inserting into Table for '||
'id '||p_id||' during iteration '||sql%bulk_exceptions(i).error_index||
' error is '||sql%bulk_exception(i).error_code));
end loop;
end;

end;
-------------------------------------------
I am getting error at sql%bulk_exception.error_code. I checked Oracle 10g documentation, and it shows a different way of displaying this message
sqlerrm(-sql%bulk_exceptions(i).error_code)
I tried this one too, but getting same message.

Where am i going wrong?


Tom Kyte
April 01, 2005 - 2:23 pm UTC

I see no errors?

PLS-00331

Chotu, April 01, 2005 - 2:44 pm UTC

I was compiling this proc from TOAD and thought since you are not getting errors, i might as well check with sqlplus. and here is the error from sqlplus too..

test@CMS10G> alter function test_forall compile;

Warning: Function altered with compilation errors.

test@CMS10G> sho err
Errors for FUNCTION TEST_FORALL:

LINE/COL ERROR
-------- ----------------------------------------------------------
37/7 PL/SQL: Statement ignored
37/26 PLS-00331: illegal reference to BULK_EXCEPTION
test@CMS10G>

Tom Kyte
April 01, 2005 - 3:02 pm UTC

well, you see, I cannot compile your code, I haven't any of your tables.

can you cut it down and make a simple, yet complete, example.

PLS-00331

Chotu, April 01, 2005 - 2:46 pm UTC

Sorry, changed procedure to function for some reason. Forgot to mention that in my previous post.
and i am using Oracle 10g

Thanks,

PLS-00331

Chotu, April 01, 2005 - 3:19 pm UTC

Here is the code for table and pl/sql

create table test_my_tab(id integer);


declare
errors number:=0;
dml_errors EXCEPTION;
l_cnt number := 0;
my_num dbms_sql.Number_Table;

PRAGMA exception_init(dml_errors, -24381);

Begin
for i in 1..200
loop
my_num(i):=i;
end loop;

begin
forall i in 1..my_num.count save exceptions
insert into test_my_tab
values(my_num(i));
exception
when dml_errors then
errors:=sql%bulk_exception.count;
for i in 1..errors
loop
dbms_output.put_line('Error is '||sql%bulk_exception(i).error_code||' at iteration '||sql%bulk_exception(i).error_index);
end loop;
end;
end;

For some reason, it has no problems with
sql%bulk-exception(i).error_index.

Hope this helps for your testing.
Thanks

PLS00331: Found the problem

Chotu, April 01, 2005 - 3:27 pm UTC

Tom,I've found the problem. I was missing exception with an "s". sql%bulk_exception(s).error_code..
Sorry for wasting your time on this and thanks for your help.



Referential integrity

David E, May 04, 2005 - 4:28 pm UTC

After reading this page and the one on "efficient upsert", I would like to determine the most efficient way to upsert several tables with foreign key constraints.

I see that in general I want to avoid row by row processing and let the database do the heavy lifting, and for updating a single table the merge statement is the most efficent method. But given the tables below, if I merge into dest_a and then merge into dest_b, I may try to insert a record into dest_b without its parent from dest_a existing.

Parent Table child table
src_a src_b
dest_a dest_b

I am picturing creating a cursor on src_a, merging into dest_a based on rowid, and then merging into dest_b based on the foreign key join between src_a and src_b. Is there a more efficient way of coding this that would still ensure I'm not trying to violate foreign key constraints?

Tom Kyte
May 04, 2005 - 6:09 pm UTC

I don't understand why if you do "A" (the parent) and then "B" the child, you would be trying to insert a record into b without its parent? All of the parents would be in A already


not following the logic here.

Question Clarification

David E, May 05, 2005 - 9:37 am UTC

Sorry, I should have added that inserts and updates are happening to the source tables throughout the day. If a parent-child combination is added between the time that I merge into a and the time that I merge into b, I will try to insert b records without any a's.

Tom Kyte
May 05, 2005 - 9:47 am UTC

serializable -- you'll get a point in time view of A and B.

they'll be consistent with regards to eachother.

FORALL Question

Yoav, October 16, 2005 - 10:35 am UTC

Hi Tom

I want to use the Forall clause in the following procedure.
The problem is that SET clause is based on select statment that call to function(GetRate).
can you please demonstrate how to use FORALL despit those limitation ?

Thank You.


CREATE OR REPLACE PROCEDURE UpdZeroCostPrice
IS
v_active_year date;
TYPE t_rec IS RECORD (company_no NUMBER,price_list_code NUMBER, doc_type_code NUMBER,currency_cd NUMBER);
TYPE t_tab IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
v_tab t_tab;
i NUMBER := 1;

CURSOR c IS
SELECT m.company_no,m.compare_price_list price_list_code ,m.doc_type_code,nvl(p.CURRENCY_CD,c.CURRENCY_CD) currency_cd
FROM document_main_prm m,company c, main_price_list p
WHERE m.company_no = c.company_no
AND (income_cd = Document_Main_Prm_C.entity3 OR
outcome_cd = Document_Main_Prm_C.entity3)
AND c.company_no = p.company_no
AND m.compare_price_list = p.price_list_code
ORDER BY m.company_no,m.doc_type_code,m.compare_price_list;

BEGIN
v_active_year := to_date('01-jan-'||to_char(sysdate,'rrrr'),'dd-mon-rrrr');
FOR c_rec IN c LOOP
v_tab(i).company_no := c_rec.company_no;
v_tab(i).price_list_code := c_rec.price_list_code;
v_tab(i).doc_type_code := c_rec.doc_type_code;
v_tab(i).currency_cd := c_rec.currency_cd;
i := i+1;
END LOOP;

FOR i IN v_tab.first..v_tab.last LOOP
UPDATE doc_analisys
SET cost_price =
(SELECT item_price*Getrate(v_tab(i).currency_cd,
App_Currency_Rate_C.currentrate,
doc_analisys.doc_value_date)/
Getrate(APP_CURRENCY_RATE_C.YEN,
App_Currency_Rate_C.currentrate,
doc_analisys.doc_value_date)
FROM item_price_list a
WHERE company_no = v_tab(i).company_no
AND item_seq_no = doc_analisys.item_seq_no
AND price_list_code = v_tab(i).price_list_code
AND trunc(a.date_from) = (SELECT MAX(trunc(date_from))
FROM item_price_list b
WHERE b.company_no=a.company_no
AND b.item_seq_no=a.item_seq_no
AND b.price_list_code=a.price_list_code
AND b. date_from<=TRUNC(doc_analisys.doc_value_date))
AND a.insert_date = (SELECT MAX(insert_date)
FROM item_price_list c
WHERE c.company_no=a.company_no
AND c.item_seq_no=a.item_seq_no
AND c.price_list_code=a.price_list_code
AND c. date_from<=TRUNC(doc_analisys.doc_value_date)
AND trunc(c.date_from)=trunc(a.date_from)))
WHERE company_no=v_tab(i).company_no
AND active_year=v_active_year
AND doc_type_code=v_tab(i).doc_type_code
AND nvl(cost_price_nis,0)=0;
COMMIT;
END LOOP;
END;
/


Tom Kyte
October 16, 2005 - 4:23 pm UTC

why do you believe the function call would prevent you from doing something here?


FORall i IN v_tab.first..v_tab.last
UPDATE doc_analisys
SET cost_price = ........



I'd really take a look at HOW and WHEN you commit - commit is there to END your logical unit of work, your transaction.

What happens if this fails 50% of the way through as you have it coded???? You've committed 50% of the work?

commit should be called when the entire transaction is complete and not a single statement sooner.

how can i update this two tables use forall clause?

jxau, April 10, 2006 - 12:34 am UTC

HI,tom :
i want to update two table's infomation ,and the two table have the same columns my procedure is :
create or replace procedure UpdatePOPInfo is
cursor C_ZDPHSDATA is select t.wxjzbm,t.longitude,t.latitude from gisphsdata t;
type TWxjzbmInfo is table of gisphsdata.wxjzbm%type index by BINARY_INTEGER;
type TLongitude is table of gisphsdata.longitude%type index by Binary_Integer;
type Tlatitude is table of gisphsdata.latitude%type index by binary_integer;
Surveyinfo TWxjzbmInfo;
Longitudeinfo TLongitude;
latitudeinfo Tlatitude;
begin
select t.wxjzbm,t.longitude,t.latitude ---SGA&#20013;
bulk collect into surveyinfo,Longitudeinfo,latitudeinfo
from gisphsdata t;
forall Survey_index in surveyinfo.first..surveyinfo.last
update pop_cs t1
set t1.longitude=longitudeinfo(Survey_index),
t1.latitude=latitudeinfo(Survey_index)
where t1.survey_id=surveyinfo(Survey_index);
forall Survey_index in surveyinfo.first..surveyinfo.last
update pop_cs2 t1
set t1.longitude=longitudeinfo(Survey_index),
t1.latitude=latitudeinfo(Survey_index)
where t1.survey_id=surveyinfo(Survey_index);

/* for PHSDATA in C_ZDPHSDATA loop
update pop_cs t1
set t1.longitude=phsdata.longitude,t1.latitude=phsdata.latitude
where t1.survey_id=phsdata.wxjzbm;
--commit;
update pop_cs2 t1
set t1.longitude=phsdata.longitude,t1.latitude=phsdata.latitude
where t1.survey_id=phsdata.wxjzbm;
--commit;
end loop;*/
commit;
end UpdatePOPInfo;

like here i must write the same code
forall Survey_index in surveyinfo.first..surveyinfo.last
..update table
how can i write once so i can update the two tables info

i'am also the question : why i use forall clause ,it is also nearly the sametime that i use a cursor to update this two tables ?

Tom Kyte
April 10, 2006 - 5:35 am UTC

why are we doing procedural code here at all?


update (select t1.longitude long, t1.latitude lat,
t2.longitude new_long,
t2.latitude new_lat
from pop_cs t1, gisphsdata t2
where t1.survey_id = t2.wxjzbm )
set long = new_long, lat = new_lat;

update (select t1.longitude long, t1.latitude lat,
t2.longitude new_long,
t2.latitude new_lat
from pop_cs2 t1, gisphsdata t2
where t1.survey_id = t2.wxjzbm )
set long = new_long, lat = new_lat;



forall does a bulk update - it is more efficient than slow by slow code as you had originally programmed.


but you need no procedural code for this whatsoever.



can't update

jxau, April 10, 2006 - 6:25 am UTC

update (select t1.longitude longi, t1.latitude lat,
t2.longitude new_long,
t2.latitude new_lat
from pop_cs t1, gisphsdata t2
where t1.survey_id = t2.wxjzbm )
set longi = new_long, lat = new_lat
ora-01779 : can not modify a column which maps to a non-key
preserved table


Tom Kyte
April 10, 2006 - 6:34 am UTC

then you are missing the mandatory UNIQUE/PRIMARY key on gisphsdata(wxjzbm)

You have a missing key - it is a very very necessary key to ensure the integrity and validity of your data.

Just add it and it'll work.

jxau, April 10, 2006 - 10:18 am UTC

Tom :
can you tell me what's the difference between this two update methods ?
update pop_cs t set (t.longitude,t.latitude)=(select t1.longitude,t1.latitude from gisphsdata t1
where t.survey_id=t1.wxjzbm )

update (select t1.longitude longi, t1.latitude lat,
t2.longitude new_long,
t2.latitude new_lat
from pop_cs t1, gisphsdata t2
where t1.survey_id = t2.wxjzbm )
set longi = new_long, lat = new_lat
thank you !


Tom Kyte
April 11, 2006 - 10:17 am UTC

the first one updates every row in pop_cs.

the second one only updates rows that have a mate in gisphsdata.

they are as different as you can get.

ERROR_INDEX in sparse collection doesn't work.

Dihan Cheng, July 21, 2006 - 4:06 pm UTC

Tom said:

There -- the ERROR_INDEX is the index into the arrays you passed to forall. if
SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 5, then the 5th record in the bulk
operation has the error.

and we asked...

If the array is sparse, the error_index seems not be useful to locate the error record anymore. Let's see the following example:

create table p1(id varchar2(2));
create table p(id varchar2(1));
insert into p1 values('1');
insert into p1 values('2');
insert into p1 values('3');
insert into p1 values('4');
insert into p1 values('10'); -- This record raises error
insert into p1 values('5');
insert into p1 values('6');
insert into p1 values('7');
insert into p1 values('8');
insert into p1 values('9');


create or replace procedure ptest is
v_error_index number;
v_error_count number;

type t is table of number index by pls_integer;
t_array t;

cursor cur is select * from p1;


begin
open cur;

fetch cur bulk collect into t_array;

t_array.delete('3');

forall i in indices of t_array save exceptions
insert into p values(t_array(i));
exception
when others then
v_error_count := sql%bulk_exceptions.count;

for indx in 1 .. v_error_count
loop
v_error_index := sql%bulk_exceptions (indx).error_index;

dbms_output.put_line('The index of the error is ' || v_error_index);
dbms_output.put_line('The id is ' || t_array(v_error_index));
end loop;

close cur;

end;
/


Output:
The index of the error is 4.
The id is 4.

THIS IS WRONG. What we want is 'The id is 10'.

the record from table p1 with ID '10' will raise an exception when inserted into table p. However, since I've already deleted the third record(ID = '3'), ERROR_INDEX will be 4, not 5.

Why does oracle regenerate the index for sparse collection. At least one shortcoming for regenerating index is as above example, how can we get the error record information?


Tom Kyte
July 23, 2006 - 7:58 am UTC

it is the fourth element in your array. In days of old, this always corresponded to the fourth "index" because the arrays went from 1 .. n and were not sparse.

so, you have to iterate to the fourth element in the array.

Under the covers, when you bind (down to the OCI'ish level here), the arrays are not sparse, they are just de-sparsing the array for us.

ERROR_INDEX in sparse collection doesn't work.

Dihan Cheng, July 23, 2006 - 8:34 am UTC

But how can we get the record causes the error and write this record in the error log for a sparse collection.

As the above example shows: we deleted 3, thus, the array becomes sparse like:

1,2,_,4,10,5,6,7,8,9

ERROR_INDEX will be 4 since the record 10 will raise the exception. But if we use this ERROR_INDEX(4) to try to get the record causes the error, we will get 4, which is the fourth record in the original array(before delete).

Is there any way to get 10?

Thanks Tom!

Tom Kyte
July 23, 2006 - 9:52 am UTC

ops$tkyte%ORA10GR2> create or replace procedure ptest
  2  is
  3     v_error_index number;
  4     v_error_count number;
  5     type t is table of number index by pls_integer;
  6     t_array t;
  7     cursor cur is select * from p1;
  8
  9     function ith_element_of( p_array in t, p_idx in number ) return number
 10     is
 11          l_idx number;
 12     begin
 13                  l_idx := p_array.first;
 14                  for i in 2 .. p_idx
 15                  loop
 16                          l_idx := p_array.next(l_idx);
 17                          exit when l_idx is null;
 18                  end loop;
 19                  return l_idx;
 20          end;
 21  begin
 22     open cur;
 23     fetch cur bulk collect into t_array;
 24     close cur;
 25     t_array.delete('3');
 26
 27     forall i in indices of t_array save exceptions
 28         insert into p values(t_array(i));
 29  exception
 30  when others
 31  then
 32     v_error_count := sql%bulk_exceptions.count;
 33     for indx in 1 .. v_error_count
 34     loop
 35        v_error_index := sql%bulk_exceptions (indx).error_index;
 36        dbms_output.put_line('The index of the error is ' ||
 37            ith_element_of(t_array,v_error_index));
 38        dbms_output.put_line('The id is ' ||
 39            t_array( ith_element_of(t_array,v_error_index) ) );
 40     end loop;
 41  end;
 42  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec ptest
The index of the error is 5
The id is 10

PL/SQL procedure successfully completed.

 

A reader, August 29, 2006 - 5:27 pm UTC

I have a huge table names SEOSDATA that needs to be put into separate tables based on year. All data having timestamp of 2004 goes to a table SEOSDATA_2004 and so on.

Following below is the code. How Do I insert the data in the table name dynamically based. I get the following error the moment i use execute immediate " PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL"

The part of the code is below.
create or replace package body seodata_bkp
as
procedure datalog_failure
is

begin
null;
end;

procedure datalog_success
is

begin
null;
end;

procedure start_archive
is

n_START number := dbms_utility.get_time;
v_ENTRYID SEOSDATAARRAY;
v_DOMAINNAME SEOSDATAARRAY;
v_USERNAME SEOSDATAARRAY;
v_EVENTTYPE SEOSDATAARRAY;
v_LOGNAME SEOSDATAARRAY;
v_TIMSTAMP SEOSDATAARRAY;
v_SOURCE SEOSDATAARRAY;
v_COMPUTERNAME SEOSDATAARRAY;
v_EVENTID SEOSDATAARRAY;
v_EVENTCATEGORY SEOSDATAARRAY;
v_SEARCHSTRINGS SEOSDATAARRAY;
v_MSGTEXT SEOSDATAARRAY;
n_CURRYEAR NUMBER := substr(SYSDATE,8,9);

begin

--open cu_SEOSDATA;
--loop
--ar_DATA := SEOSDATAARRAY();
select * bulk collect into v_ENTRYID,v_DOMAINNAME,v_USERNAME,v_EVENTTYPE,v_LOGNAME,v_TIMSTAMP,
v_SOURCE,v_COMPUTERNAME,v_EVENTID,v_EVENTCATEGORY,v_SEARCHSTRINGS,v_MSGTEXT
from SEOSDATA;

forall i in 1 .. v_ENTRYID.count
insert
when (substr(TIMSTAMP,8,9) < n_CURRYEAR)then
into SEOSDATA_BKP
(ENTRYID,
DOMAINNAME,
USERNAME,
EVENTTYPE,
LOGNAME,
TIMSTAMP,
SOURCE,
COMPUTERNAME,
EVENTID,
EVENTCATEGORY,
SEARCHSTRINGS,
MSGTEXT
)
values (ENTRYID,
DOMAINNAME,
USERNAME,
EVENTTYPE,
LOGNAME,
TIMSTAMP,
SOURCE,
COMPUTERNAME,
EVENTID,
EVENTCATEGORY,
SEARCHSTRINGS,
MSGTEXT
)
select v_ENTRYID(i) ENTRYID,
v_DOMAINNAME(i) DOMAINNAME,
v_USERNAME(i) USERNAME,
v_EVENTTYPE(i) EVENTTYPE,
v_LOGNAME(i) LOGNAME,
v_TIMSTAMP(i) TIMSTAMP,
v_SOURCE(i) SOURCE,
v_COMPUTERNAME(i) COMPUTERNAME,
v_EVENTID(i) EVENTID,
v_EVENTCATEGORY(i) EVENTCATEGORY,
v_SEARCHSTRINGS(i) SEARCHSTRINGS,
v_MSGTEXT(i) MSGTEXT
from
DUAL;

-- exit when cu_SEOSDATA%notfound;
--end loop;
--close cu_SEOSDATA;

dbms_output.put_line('The archiving of SEOSDATA table completed in '||round((dbms_utility.get_time-n_START)/100,2)||' secs');
end;

end seodata_bkp;


Tom Kyte
August 29, 2006 - 5:42 pm UTC

I don't understand why there are lines of procedural code here? why isn't this a simple insert as select??? What is the purpose of the code?

A reader, August 30, 2006 - 11:27 am UTC

I think my last message got drowned.
The reason is
a) The client wants to use Packages/Procedures rather than simple CTAS which he cannot have control over.
b) The SEOSDATA has a date field. All data other than the current year will go to a archive table based on year, SEOSDATA_BKP_2004, SEOSDATA_BKP_2005 etc.
c)It should also log who ran the procedure, when it was run, how many rows got processed etc. The log_failure and log_success procedure will do that. (Currently it is null).

Regards,
prasanna

Tom Kyte
August 30, 2006 - 5:27 pm UTC

your package should have a single INSERT as SELECT, it should have no procedural code in there. You can put an insert in a package, erase the code that FETCHES and then INSERTS rows - just INSERT the SELECT!

do whatever logging/auditing you want, but get rid of the code to do what SQL already does quite nicely.

A reader, August 31, 2006 - 9:38 am UTC

But I have table with more than 10 million rows that will get moved/backed up during this archive operation. Will not just insert as select cause lot of round trips and have overheads. There will be other simulteneous operations also going on when this table is being backed up.



Tom Kyte
August 31, 2006 - 9:57 am UTC

umm, your PROCEDURAL code will cause "lot of round trips and have overheads"

The insert as select will read and write, entirely self contained in the server.

Your procedural code will

a) read N records
b) go back to plsql and put them into a memory array
c) take the memory array you just assembled using, well, you know memory and cpu and send it back to the database layer
d) while will insert it

repeat over and over.



A reader, August 31, 2006 - 5:05 pm UTC

Yes, but I want to insert into tables based on Year in the date field of the table. So effectively I have to check the field, if it falls in 2005, put them in SEOSDATA_BKP_2005 and if in 2004, then put them in SEOSDATA_BKP_2004. So the table names will dynamically change.

I dont want to create partitions currently as table is already in live.

Will that "overhead" still hold true for the procedure?


Tom Kyte
August 31, 2006 - 7:07 pm UTC

yeah but your procedural code does do that.

sounds like a multi-table insert to me, that's all.

A reader, September 01, 2006 - 10:02 am UTC

Yes, it is supposed to do that. But How do I dynamically change the table names, the execute immediate fails with a error "PLS-00435: DML statement without BULK In-BIND cannot be used".
Any lead to progress will help.

Regards,

Tom Kyte
September 01, 2006 - 10:37 am UTC

I'm saying it one last time:

use a single sql statement, there will be NO BULK BINDS, if you have procedural code, you have done it wrong.



A reader, September 06, 2006 - 10:43 am UTC

Thanks, I got the logic. It was kind of fixed in my mind that bulk collect will fasten things.
Supposing this same table contains millions of rows and I want to precreate the backup tables based on the year field in the date column within this same procedure, would you suggest

a)Check each record for year field, check whether the table TABLE<year> exists, if not create it. It will scan all records.
b) Distinct(year) field in a cursor, then loop to create the table if not exists. However I am not sure how distinct will behave for huge table. I dont have the environment yet to check it but I should have it in another week. So wanted to clear the confusion.

Regards,

Tom Kyte
September 06, 2006 - 3:37 pm UTC

why wouldn't your procedure know the year it was to process as it was called?

Merge and forall

saradha, September 15, 2006 - 1:37 pm UTC

Hi Tom,
I have to do a merge with forall statement. So I declared a type of emp%rowtype. I fetch all the records from the emp table into the object with bulk collect. Then with forall i want to do the merge into emp_dump table. If there are some records in emp_dump i want to maintain them as well. So the merge. But this is not working. Can you help me?

DECLARE
TYPE myarray IS TABLE OF emp%ROWTYPE;
l_data myarray;
CURSOR r IS
SELECT * FROM emp;
BEGIN
OPEN r;
LOOP
FETCH r BULK COLLECT INTO l_data LIMIT 1000;
FORALL i IN 1..l_data.COUNT
merge into emp_dump t
using (select l_data(i) from dual) s
on (t.empno = s.empno)
when matched then update set t.ename = s.ename,
t.sal = s.sal
when not matched then insert (t.empno,t.ename, t.sal) values (s.empno,s.ename,s.sal);
--INSERT INTO emp_dump VALUES l_data(i);
EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
CLOSE r;
END;

Tom Kyte
September 15, 2006 - 2:00 pm UTC

ops$tkyte%ORA10GR2> create type myScalarType as object
  2  ( empno number,
  3    ename varchar2(10),
  4    sal   number
  5  )
  6  /

Type created.

ops$tkyte%ORA10GR2> create type myTableType as table of myScalarType
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table emp as select empno,ename,sal from scott.emp where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
  2      l_data myTableType;
  3  begin
  4      select myScalarType(empno,ename,sal)
  5        bulk collect into l_data
  6        from scott.emp;
  7
  8      merge into emp
  9      using (select * from table(cast(l_data as myTabletype))) d
 10      on (emp.empno = d.empno)
 11      when matched then update set ename = d.ename, sal = d.sal
 12      when not matched then insert(empno,ename,sal)
 13                            values(d.empno,d.ename,d.sal);
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select * from emp where rownum <= 5;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250


use a collection and you can use it as the set itself in the merge. 

merge

saradha, September 15, 2006 - 2:24 pm UTC

Thank you. Works fine

Please Suggest - Multiple Rows

Nikhil, December 22, 2006 - 4:23 am UTC

Hello Tom,
is it possible to fetch multiple rows from DUAL table. I have done the following. To my best of knowledge it cannot be done :)

Table T1 (OBJECT_NAME VARCHAR2(30))

CREATE OR REPLACE FUNCTION F1 (X IN VARCHAR2 )
RETURN dbms_sql.varchar2_table
IS
l_name dbms_sql.varchar2_table;
begin
select object_name bulk collect into l_name from t1;
return l_name;
end;
/

sql>SELECT F1('X') FROM DUAL;

SELECT F1('X') FROM DUAL
*
ERROR at line 1:
ORA-00902: invalid datatype

Instead it should return all the rows existing in T1 table.

If there anyway to achieve this please let me know

Thanks


Tom Kyte
December 22, 2006 - 6:34 am UTC

search this site for pipelined

you want to select from a pipelined FUNCTION, not from dual

how to delete multiple rows from a table using bulk delete

A reader, January 03, 2007 - 1:08 pm UTC

i have a table say t
and it hac columns ( a varchar2 b varchar2 c varchar2)

now i need to delete from this table using BULK collect
for the columns a and b

like cursor C ids select a , b
from t
where some condition

i need to delete specifically for the value which i get for a and b



Tom Kyte
January 05, 2007 - 8:26 am UTC

ok, go ahead?



PL?SQL ORA-00904: : Invalid identifier what is the issue

A reader, January 03, 2007 - 2:08 pm UTC

CREATE OR REPLACE procedure APPS.PEL_SHIP_DATA_PURGE_COLLECT ( p_days number ) as

TYPE X_ROUTE_ID is TABLE of PELLA.PEL_SHIP_MOV_SEG.ROUTE_ID%TYPE;
X_ROUTE_ID_ARRAY X_ROUTE_ID;
TYPE X_TRAILER_ID is TABLE of PELLA.PEL_SHIP_MOV_SEG.TRAILER_ID%TYPE;
X_TRAILER_ID_ARRAY X_TRAILER_ID;

BEGIN

select ROUTE_ID, TRAILER_ID BULK COLLECT
INTO X_ROUTE_ID_ARRAY , X_TRAILER_ID_ARRAY
FROM PELLA.PEL_SHIP_MOV_SEG
where trunc(creation_date) < trunc(sysdate) - p_days
and nvl(status_code, 'CL') ='CL';

FORALL i IN X_ROUTE_ID_ARRAY.FIRST..X_ROUTE_ID_ARRAY.LAST

DELETE FROM PELLA.PEL_SHIP_MOV_SEG
WHERE route_id = X_ROUTE_ID_ARRAY(i)
and trailer_id = X_TRAILER_ID_ARRAY(i);

delete from pella.pel_ship_stop_seg
where route_stop_id like X_ROUTE_ID_ARRAY(i) || '%'
and trailer_id=X_TRAILER_ID_ARRAY(i);

END PEL_SHIP_DATA_PURGE_COLLECT;

Tom Kyte
January 05, 2007 - 8:30 am UTC

no idea since I don't even have your tables.

You must have an invalid identifier in there somewhere.


but I do think I see it.

You are missing a forall i in .... in front of the second delete.

forall is a modifier for a SINGLE DML STATEMENT, it is not a loop, it says "execute this SINGLE DML statement over and over for the values in this array"

forall & subquery

Ranjith, June 08, 2007 - 3:32 pm UTC

If we use subqueries in DMLs using FORALL statement, it doesnt work as intended.

Pls see the below e.g.

create table t1 (n number);
create table t2 (n number, a varchar2(1));
create table t3 (n number, a varchar2(1));

insert into t1 (n) values (1);
insert into t1 (n) values (2);

insert into t2 (n,a) values (1,'a');
insert into t2 (n,a) values (2,'b');


declare
cursor c is select * from t1;
m dbms_sql.number_table;
begin
open c;
loop
fetch c bulk collect into m limit 10;
forall i in m.first..m.last
insert into t3 (n,a)
values (m(i), (select a from t2 where n = m(i)));
exit when c%notfound;
end loop;
end;
/

select * from t3;

N A
------------------- -
1.0000 a
2.0000 a

I would have expected second record to have value 'b' for col A.

At the same time if we use a function for insert, it works as expected.

create function f (p in number) return varchar2
is
l t2.a%type;
begin
select a into l from t2 where n = p;
return l;
end;
/

declare
cursor c is select * from t1;
m dbms_sql.number_table;
begin
delete t3;
open c;
loop
fetch c bulk collect into m limit 10;
forall i in m.first..m.last
insert into t3 (n,a)
values (m(i), f(m(i)));
exit when c%notfound;
end loop;
end;
/

select * from t3;

N A
------------------- -
1.0000 a
2.0000 b

Could you pls explain why?
Tom Kyte
June 09, 2007 - 10:37 am UTC

ops$tkyte%ORA9IR2> declare
  2  cursor c is select * from t1;
  3  m dbms_sql.number_table;
  4  begin
  5  open c;
  6  loop
  7  fetch c bulk collect into m limit 10;
  8  forall i in m.first..m.last
  9  insert into t3 (n,a)
 10  values (m(i), (select a from t2 where n = m(i)));
 11  exit when c%notfound;
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select * from t3;

         N A
---------- -
         1 a
         2 b



I tested in 9.2.0.8 and 10.2.0.3, worked.

perhaps you have something old and unpatched and this was a fixed issue?

Ranjith, June 09, 2007 - 1:09 pm UTC

I am on 10.2.0.2.0 on AIX. I do not have any other version to test.

Looks like a bug in this version?


SQL*Plus: Release 10.2.0.2.0 - Production on Sat Jun 9 18:57:56 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.2.0 - Productio
NLSRTL Version 10.2.0.2.0 - Production

SQL> create table t1 (n number);

Table created.

SQL> create table t2 (n number, a varchar2(1));

Table created.

SQL> create table t3 (n number, a varchar2(1));

Table created.

SQL> insert into t1 (n) values (1);

1 row created.

SQL> SQL> insert into t1 (n) values (2);

1 row created.

SQL> insert into t2 (n,a) values (1,'a');

1 row created.

SQL> insert into t2 (n,a) values (2,'b');

1 row created.

SQL> declare
cursor c is select * from t1;
2 3 m dbms_sql.number_table;
4 begin
5 open c;
6 loop
7 fetch c bulk collect into m limit 10;
8 forall i in m.first..m.last
9 insert into t3 (n,a)
10 values (m(i), (select a from t2 where n = m(i)));
11 exit when c%notfound;
12 end loop;
13 end;
14 /

select * from t3;


PL/SQL procedure successfully completed.

SQL>
N A
---------- -
1 a
2 a

Tom Kyte
June 09, 2007 - 1:29 pm UTC

yes.

Ranjith, June 09, 2007 - 1:26 pm UTC

It takes the record of first index when used in sub-queries in DML.


SQL> select * from t1;

N
----------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

SQL> declare
2 cursor c is select * from t1;
3 m dbms_sql.number_table;
4 begin
5 open c;
6 loop
7 fetch c bulk collect into m limit 2;
8 forall i in m.first..m.last
9 insert into t3 (n,a)
10 values (m(i), (select m(i) from dual));
11 exit when c%notfound;
12 end loop;
13 end;
14 /

PL/SQL procedure successfully completed.

SQL> select * from t3;

N A
---------- -
1 1
2 1
3 3
4 3
5 5
6 5
7 7
8 7
9 9
10 9

10 rows selected.

Question on forall

pranav, June 27, 2012 - 2:19 pm UTC

Hi Tom,

Is FORALL used only for simple DML commands? Because I have a requirement wherein we fetch the record from cursor, do some calculations and use IF conditions for validation and then insert/update accordingly. Can we use FORALL in this case instead of "FOR" loop? Please provide your valuable suggestions. If time permits, can you please give some examples as well?

Appreciate all your help.
Tom Kyte
June 27, 2012 - 5:09 pm UTC

give psuedo code for existing logic.

i'll give you psuedo code back for bulk logic.


basically, it will probably look like:

<code>

open c;
loop
fetch c bulk collect into l_data limit N;

for i in 1 .. l_data.count
loop
... process l_data(i) right here ...
end loop;

forall i in 1 .. l_data.count
update .....;
end loop;
<code>


if it is "update or insert", it might be a merge in the forall

Question on FORALL

pranav, June 27, 2012 - 9:28 pm UTC

Awesome. Thank you very much for providing valuable suggestions.

Our code looks similar to the below one

declare
v_var1 number;
v_var2 number;
v_var3 number;
begin
for dat_curs in (select * from dba_objects where object_type='TABLE')
loop
BEGIN
v_var1 := null;
if dat_crs.owner='APPS' then
begin
v_var2 := null;
select testseq.nextval into v_var2 from dual;
end;
begin
insert into tab1(id,objid,objname) values(v_var2,dat_curs.object_id,dat_curs.object_name);
end;
elsif dat_crs.owner='GL' then
begin
v_var2 := null;
select testseq.nextval into v_var2 from dual;
end;
begin
v_var3 := null;
select newseq.nextval into v_var3 from dual;
end;
begin
insert into tab2(id,objid,objname) values(v_var3,dat_curs.object_id,dat_curs.object_name);
end;
end;
end loop;
end;

Could you please tell me if we can use FORALL in above scenario?

Also, I didn't understand the below part(Sorry, most probably I mislead you with my requirement). Considering your example, processing l_data(i) in the for loop? Wouldn't it make multiple calls to database?

for i in 1 .. l_data.count
loop
... process l_data(i) right here ...
end loop;


As always, appreciate your help.

Tom Kyte
June 28, 2012 - 9:44 am UTC

I won't show you forall but only because it is not useful, this is what the code should be:

insert first
  when owner = 'APPS' then
  into tab1(id,objid,objname)
       values (testseq.nextval, object_id, object_name)
  when owner = 'GL'   then
  into tab2(id,objid,objname)
values (newseq.nextval,  object_id, object_name)
select object_id, object_name, owner
  from dba_objects
 where object_type = 'TABLE'
   and owner in ( 'APPS', 'GL' );

that is, there should be NO PROCEDURAL CODE - it isn't necessary, it only makes things go slow.

As for the for i in 1..l_data.count...


Look at the psuedo code. You are array fetching N rows into an array called l_data.

You then have to loop over the elements in the array to process them.

You then use a forall over the array to put them back into the database.


It is just a loop to process the data, what you said you wanted to do.


single dml vs bulk dml

Tony, September 15, 2012 - 12:52 pm UTC

Hi Tom,

I have read from your articles that a single insert/update/delete is better than a bulk insert/update/delete using forall clause.

a) Is this true always ( in both datawarehousing and OLTP environments)?

b) Can there be any situation where a bulk insert/update/delete is better than a single insert/update/delete ( ie which goes against the rule - If we can do something in a single sql, we should do it without using procedural code)

Thank you
Tom Kyte
September 16, 2012 - 4:21 am UTC

a) nothing is ever always 100% true in technology. In general, in almost every case, a single bulk statement is better than procedural code.

and in a data warehouse, it is likely that a single bulk DDL statement is superior to a single bulk DML.

that is, rather than delete millions of rows - use partitioning and drop partition (ddl) or use CREATE TABLE AS SELECT <rows to keep> - instead of delete.

rather than update millions of rows, use CREATE TABLE AS SELECT <modified rows> to 'update' them.

avoid DML as much as you can - in general - for large bulk operations

and if you cannot, try to do it in a single dml statement almost all of the time

and if you cannot, make sure to use a reasonable bulk collect limit - somewhere between 100 and 1000 probably (although sometimes higher numbers work better - best to parameterize that setting and benchmark)

b) sure, but that is true of all "best practices", every single one. For every single best practice you have been taught - there are hundreds or thousands of counter cases where you will find your best practice is a horrible practice.


As a mental exercise, try to think of a case where a tiny bit of procedural processing might outperform a big bulk statement....

close your eyes and envision the work that needs to be performed by some operation running as a single statement - versus a shortcut you might be able to program if you did it procedurally...

FORALL vs FOR loop

Suresh, August 06, 2013 - 3:45 pm UTC

Hi Tom,

I am using oracle 11g. And we have only 2000 record in a table. And these records needs to be updated depending on some condition.

So I have a doubt like which one will give good performance when we are having 2000 records in a table. FORALL or For loop.? Will FORALL always give better performance than FOR loop? please let me know.
Tom Kyte
August 08, 2013 - 4:54 pm UTC

neither.


just use UPDATE.


why would you write procedural code when you don't have to??????


just use SQL.

Ib Kjeldgaard, August 26, 2013 - 10:30 am UTC

Hi Tom.
We are using FORALL statements for performance issues.
However we then miss what went wrong.
Then to cover this we started using it with SAVE EXCEPTIONS and are then able to exactly what went wrong.
Only problem is, the job as such is still raising errors which are catched by our job-flow causing the flow to stop.
Is there anyway to kind of 'override' the exception so the job doesn't appeared being in error?
Tom Kyte
August 28, 2013 - 7:05 pm UTC

need example, small example, i don't know what you mean?

if you mean "we are still raising an exception", then catch it - handle it.

Caio, December 09, 2013 - 1:20 pm UTC

Hi tom, 
i have writing a code to modify an update using normal loop to use forall.
I did a lot of tests but i could not see difference on time of executions, this amazed me why always thought this would be faster.
Below a code and time spent..


drop table rest purge;
drop table rest2 purge;
create table rest as select * from dba_segments;
insert into rest select * from rest;
insert into rest select * from rest;
insert into rest select * from rest;
insert into rest select * from rest;
insert into rest select * from rest;
insert into rest select * from rest;
create table rest2 as select * from rest;
create index segment_name on rest(segment_name);
create index segment_type on rest2(segment_type);
alter system  flush buffer_cache;
alter system flush shared_pool;
set timing on 


without forall

declare
var1 number;
type is_ven_tp is table of dba_segments.SEGMENT_NAME%type
index by pls_integer;
is_ven_clt is_ven_tp;
begin
select distinct segment_name bulk collect into is_ven_clt from rest2 where segment_type in ('TABLE','INDEX');
for indx in 1 .. is_ven_clt.count 
loop 
update rest set owner='TABELAEINDICE' where segment_NAME= is_ven_clt(indx);
end loop;
end;
/


PL/SQL procedure successfully completed.

Elapsed: 00:00:41.32





With forall




SQL> declare
  2  var1 number;
type is_ven_tp is table of dba_segments.SEGMENT_NAME%type
index by pls_integer;
is_ven_clt is_ven_tp;
begin
select distinct segment_name bulk collect into is_ven_clt from rest2 where segment_type in ('TA  3    4    5    6    7  BLE','INDEX');
var1:=is_ve  8  n_clt.count;
dbms_output.p  9  ut_line(var1);
forall indx 10   in 1 .. is_ven_clt.count
update rest set owner='TA 11  BELAEINDICE' where segment_NAME= is_ven_clt(indx);
end;
/ 12   13

PL/SQL procedure successfully completed.

Elapsed: 00:00:40.29




Thanks in advance.

bhagabannayak08@gmail.com

Bhagaban nayak, August 01, 2022 - 9:54 am UTC

When ever useing for all save exception transfer the another table that time having some record are error ,I want to only errors recor insert the new table.
Chris Saxon
August 01, 2022 - 3:15 pm UTC

What exactly are you doing? Please show us a (working!) example

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library