Home » SQL & PL/SQL » SQL & PL/SQL » How can I continue insertion by skipping invalid records?
icon8.gif  How can I continue insertion by skipping invalid records? [message #302088] Sat, 23 February 2008 05:36 Go to next message
shyam538
Messages: 6
Registered: February 2008
Junior Member
Hello,
i have a situation where i would like to insert Table A info into Table B having same structure but with some constraints.

As we all know that even a single record falls... whole transaction will be rolled back. Actually i would like to put this discarded ones into some other table.

So, my main idea is some thing like Sql Loader with in sql.
i know it is possible but struggling to find some right method.

[MERGED by LF; basically, both questions refer to the same problem - how to continue processing when and exception is raised]

[Updated on: Sat, 23 February 2008 06:36] by Moderator

Report message to a moderator

Is there any way to switch between Execution section and Exception Section [message #302089 is a reply to message #302088] Sat, 23 February 2008 05:42 Go to previous messageGo to next message
shyam538
Messages: 6
Registered: February 2008
Junior Member
Hello,
i know that when ever a error is raised. the handle is thrown to exception section. But is there any way to overcome or switch back to my execution.

And what are the DO's & DONT's in exception section.
Re: How can i continue insertion skipping invalid records [message #302091 is a reply to message #302088] Sat, 23 February 2008 06:22 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Have a look here
Re: How can i continue insertion skipping invalid records [message #302092 is a reply to message #302088] Sat, 23 February 2008 06:34 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, you are not on 10g; otherwise, you might use the LOG ERRORS clause.

The way it is now, a simple loop (in certain situations) might also help:
BEGIN
  FOR cur_1 IN (SELECT ... )
  LOOP

    BEGIN
      do_some_processing;
      INSERT INTO ...

    EXCEPTION
      WHEN named_exception THEN
        INSERT INTO error_log_table (...)
    END;

  END LOOP;
END;

The inner BEGIN-EXCEPTION-END block will make it possible to continue processing records fetched in a cursor FOR loop, even if some record violates constraints you have mentioned.
Re: How can i continue insertion skipping invalid records [message #302152 is a reply to message #302092] Sun, 24 February 2008 03:38 Go to previous message
shyam538
Messages: 6
Registered: February 2008
Junior Member
Thanks LittleFoot
my job is done and all my doubts are clear.
your answers are more specific..
thanks again.

[Updated on: Sun, 24 February 2008 03:38]

Report message to a moderator

Previous Topic: does rebuild indexs generates a redo
Next Topic: How to create a single query with joins using these two below mentioned tables
Goto Forum:
  


Current Time: Sat Dec 03 04:20:31 CST 2016

Total time taken to generate the page: 0.09099 seconds