Home » SQL & PL/SQL » SQL & PL/SQL » FORALL INSERT loop break conditionally
FORALL INSERT loop break conditionally [message #339621] Fri, 08 August 2008 00:06 Go to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
Is there any way in PL/SQL, to continue FORALL INSERT loop only for ora error ORA-10000 and ORA-14400. If any other ORA error is there then FORALL loop will get break.

Version :
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
Re: FORALL INSERT loop break conditionally [message #339623 is a reply to message #339621] Fri, 08 August 2008 00:10 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
vipinsonkar2000 wrote on Thu, 07 August 2008 22:06
Hi
Is there any way in PL/SQL, to continue FORALL INSERT loop only for ora error ORA-10000 and ORA-14400. If any other ORA error is there then FORALL loop will get break.

Version :
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production

No, but it is the price you pay for running on a non-supported Oracle version.
Re: FORALL INSERT loop break conditionally [message #339629 is a reply to message #339623] Fri, 08 August 2008 00:33 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Thanks for reply but I did not get you.
Can you suggest me, how do that with FORALL INSERT.
Re: FORALL INSERT loop break conditionally [message #339630 is a reply to message #339621] Fri, 08 August 2008 00:37 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
With obsoleted V9, You're On Your Own.
With V10R2, work arounds exist.
Re: FORALL INSERT loop break conditionally [message #339634 is a reply to message #339630] Fri, 08 August 2008 00:47 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
Thanks. You mean to say that with higher oracle version, I can do this? OK I will upgrade Oracle version. Please tell me how is it possible with higher version of Oracle?
Any hints or sample code?
Re: FORALL INSERT loop break conditionally [message #339643 is a reply to message #339621] Fri, 08 August 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-10000: control file debug event, name 'control_file'

How could you get this?

If you mean forall stops at first error (but these expected ones), there is no way. Either it stops at first error, either it goes till end (or the error limit you gave using log errors clause) and you have to handle the errors afterwards (this in 10g, there is no log errors clause on insert in 9i).

Regards
Michel
Re: FORALL INSERT loop break conditionally [message #339646 is a reply to message #339643] Fri, 08 August 2008 01:10 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Michel
Thanks for reply.
Sorry it is for error ORA-00001 not ORA-10000.

Is ther any way to do that?

Actually I want to insert records from one table to other(also inserting some value in other coloumns of second table which are not present in first table). In this operation, I want if the partition does not map(for secod table,ORA-14400) or unique constraint error(ORA-00001) encountered, then those record get skipped and insertion get continue. But any other ORA- error occurs (except ORA-14400 and ORA-00001) then FORALL INSERT loop gets break.

I already did it with normal cursor insert but for performance, I want to do it with FORALL INSERT.
Please help/suggest me to do that?

Thanks and Regards
Re: FORALL INSERT loop break conditionally [message #339650 is a reply to message #339646] Fri, 08 August 2008 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I already said you can't in the way you choose.

Use a previous select to get rowid for rows that will generate these errors then insert the other ones (in a single INSERT).

Regards
Michel
Re: FORALL INSERT loop break conditionally [message #339654 is a reply to message #339650] Fri, 08 August 2008 01:28 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Thanks for reply.
Any sample code for this using rowid for requirement?
Re: FORALL INSERT loop break conditionally [message #339656 is a reply to message #339646] Fri, 08 August 2008 01:32 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
I want to do it with FORALL INSERT

Syntax of FORALL Statement in 10gR2 is documented in http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/forall_statement.htm#sthref2742.
Concentrate on SAVE EXCEPTIONS keyword and its description.
It contains a link to an example as well.
Re: FORALL INSERT loop break conditionally [message #339658 is a reply to message #339656] Fri, 08 August 2008 01:39 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
Ok Michel forget about FORALL INSERT. But I am unable to understand the how some other technique(except FORALL INSERT) can give better performance then normal cursor insert.

Please you suggest you solution for my scenario/requirement.

Thanks and regards.
Re: FORALL INSERT loop break conditionally [message #339683 is a reply to message #339658] Fri, 08 August 2008 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here are two ideas how to do it to prevent from ORA-00001:
SQL> create table emp2 as select * from emp where rownum<4;

Table created.

SQL> alter table emp2 add primary key (empno);

Table altered.

SQL> insert into emp2 
  2    select * from emp 
  3    where empno not in (select empno from emp2)
  4  /

9 rows created.

SQL> rollback;

Rollback complete.

SQL> create or replace type mytype as table of varchar2(18);
  2  /

Type created.

SQL> declare
  2    t mytype;
  3  begin
  4    select rowidtochar(rowid) bulk collect into t 
  5    from emp
  6    where empno in (select empno from emp2);
  7    insert into emp2 
  8      select * from emp 
  9      where rowid not in (select chartorowid(column_value) from table(t));
 10  end;
 11  /

PL/SQL procedure successfully completed.

Regards
Michel

[Updated on: Fri, 08 August 2008 02:20]

Report message to a moderator

Re: FORALL INSERT loop break conditionally [message #339694 is a reply to message #339683] Fri, 08 August 2008 02:41 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Michel

Thanks for reply. But will you tell me, how would you able to handle the ORA-14400 error (partition key does not map to any partition). Using your code we can only handle ORA-00001.

So my requirement is to skip for error ORA-14400 and ORA-00001 and continue processing for the remaining.

Thanks and regards.
Re: FORALL INSERT loop break conditionally [message #339699 is a reply to message #339694] Fri, 08 August 2008 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was an example, extend it for you other cases.
For partition, check the value that can be in partitions (querying dictionary) and retrieve rowids for those that are in not in the list or ranges.

Regards
Michel
Re: FORALL INSERT loop break conditionally [message #339927 is a reply to message #339658] Sat, 09 August 2008 17:07 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
vipinsonkar2000 wrote on Fri, 08 August 2008 07:39
Hi
Ok Michel forget about FORALL INSERT. But I am unable to understand the how some other technique(except FORALL INSERT) can give better performance then normal cursor insert.

"Normal"? I don't think so.

FORALL is more efficient than the obsolete Cursor FOR loop approach we were all using 15 years ago because it avoids a loop. It is a single call to the SQL engine, instead of many calls executed one at a time. This makes it almost as efficient as a plain SQL INSERT statement.

Quote:
FORALL INSERT loop break conditionally

FORALL is not a loop!

[Updated on: Sun, 10 August 2008 01:34]

Report message to a moderator

Re: FORALL INSERT loop break conditionally [message #339942 is a reply to message #339927] Sun, 10 August 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
FORALL is not a loop!

Very well said!
Should be in bold.

Regards
Michel
Re: FORALL INSERT loop break conditionally [message #340461 is a reply to message #339942] Wed, 13 August 2008 00:44 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
Thanks for reply.
Even after 10 days i am not able to find the solution i need. I also know that FORALL is not a loop.

You all senior guy always point out faults even though you quite understood the real need.

Are you really serious to help me out.
Re: FORALL INSERT loop break conditionally [message #340469 is a reply to message #340461] Wed, 13 August 2008 01:06 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Didn't flyboy's SAVE EXCEPTIONS help? There are examples around, such as this one on oracle-developer.net.

You didn't know that FORALL was not a loop at the start of this thread, so it has not been a waste of time.
Re: FORALL INSERT loop break conditionally [message #340474 is a reply to message #340469] Wed, 13 August 2008 01:23 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi Sir,

Thanks for reply. From very start I knew that FORALL is not a loop.

My requirment is to insert records from one table to other(also inserting some value in other coloumns of second table which are not present in first table). In this operation, I want if the partition does not map(for secod table,ORA-14400) or unique constraint error(ORA-00001) encountered, then those record get skipped and insertion get continue. But any other ORA- error occurs (except ORA-14400 and ORA-00001) then FORALL INSERT gets exit.

I already did it with normal cursor insert but the performance is very slow.

So I tried it with FORALL INSERT bulk insert. But came to know that either it stops at first error, or it goes till end.
But my requirement is to go till end, only if ORA-14400 or ORA-00001 errors are there. If any other ors- eroors then it stops at first error.


Is there any other way to do this, please also suggest me.

Thanks and Regards
Re: FORALL INSERT loop break conditionally [message #340478 is a reply to message #340474] Wed, 13 August 2008 01:32 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Couldn't you check the bulk exceptions you capture with SAVE EXCEPTIONS, and if any are not ORA-14400 or ORA-00001 then roll back to a savepoint?
Re: FORALL INSERT loop break conditionally [message #340480 is a reply to message #340474] Wed, 13 August 2008 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already suggested, I even posted code.

Regards
Michel
Re: FORALL INSERT loop break conditionally [message #340484 is a reply to message #340480] Wed, 13 August 2008 01:45 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi
Yes but I am unable to write the query for ORA-14400.
second table's partition are created on date range. How do I write a query to know a particular date's partiton is exits or not?
Re: FORALL INSERT loop break conditionally [message #340485 is a reply to message #340484] Wed, 13 August 2008 01:47 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi William

I am not able to understand? will you explain by some example.

Thanks and Regards.
Re: FORALL INSERT loop break conditionally [message #340488 is a reply to message #340485] Wed, 13 August 2008 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 08 August 2008 07:56
[code]If you mean forall stops at first error (but these expected ones), there is no way. Either it stops at first error, either it goes till end (or the error limit you gave using log errors clause) and you have to handle the errors afterwards (this in 10g, there is no log errors clause on insert in 9i).

Regards
Michel


Re: FORALL INSERT loop break conditionally [message #340489 is a reply to message #340484] Wed, 13 August 2008 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
vipinsonkar2000 wrote on Wed, 13 August 2008 08:45
Hi
Yes but I am unable to write the query for ORA-14400.
second table's partition are created on date range. How do I write a query to know a particular date's partiton is exits or not?

Query user_tab_partitions to get the known values.

Regards
Michel

Re: FORALL INSERT loop break conditionally [message #340491 is a reply to message #340488] Wed, 13 August 2008 01:56 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi all

By using all_tab_partition but how?
Thanks and regards

[Updated on: Wed, 13 August 2008 01:58]

Report message to a moderator

Re: FORALL INSERT loop break conditionally [message #340495 is a reply to message #340485] Wed, 13 August 2008 02:10 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
After doing the FORALL insert using SAVE EXCEPTIONS as in the example, you have an array containing all of the exceptions encountered in the FORALL operation. If you check that by looping through it, you should be able to tell if any were fatal according to your rules, and if so you can roll back the insert you just did.
Re: FORALL INSERT loop break conditionally [message #340501 is a reply to message #340495] Wed, 13 August 2008 02:27 Go to previous messageGo to next message
vipinsonkar2000
Messages: 38
Registered: May 2008
Member
Hi William
First see my code and understand my requirement and then reply.
It is better not to give advice then wrong one.
Thanks and Regard.

[Updated on: Wed, 13 August 2008 02:27]

Report message to a moderator

Re: FORALL INSERT loop break conditionally [message #340509 is a reply to message #340501] Wed, 13 August 2008 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe William didn't understand your requirements and in this case you have to repeat them in a clearler way and not try to discourage him to help you with bitter comments.

Regards
Michel

[Updated on: Wed, 13 August 2008 02:41]

Report message to a moderator

Re: FORALL INSERT loop break conditionally [message #340599 is a reply to message #340501] Wed, 13 August 2008 06:36 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I thought the requirement was for the FORALL insert to succeed if the only exceptions were ORA-14400 or ORA-00001, or fail if any other exceptions were encountered.

Regarding checking the partition definitions, that will not be straightforward since range dates are stored as text strings in a LONG column. Perhaps you could extract these into a local collection or temp table or something at the start of processing rather than querying the data dictionary for each row to be inserted, although I am not clear on the requirement here. It would seem simpler and more efficient to attempt the insert and capture exceptions as I think you proposed in your earlier posts.

[Updated on: Wed, 13 August 2008 06:37]

Report message to a moderator

Previous Topic: syntax of update stmt with literals in subquery
Next Topic: Continous Execution after Exception Handling
Goto Forum:
  


Current Time: Sun Dec 11 00:42:54 CST 2016

Total time taken to generate the page: 0.19560 seconds