Solved

Oracle - continue on insert error

Posted on 2010-08-12
16
1,396 Views
Last Modified: 2013-12-19
What function or statement in oracle do you use to "On execption resume" or "whenever sqlerror continue"  I have a PLSQL script that has over 1000 inserts statements that must be applied to over 100 DBs.  If there are any exception on the insert I want the script to continue on to the next statment. The script has begin and end blocks an is written in PL/SQL.
0
Comment
Question by:fpkeegan
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you will have to wrap each insert  with begin/end and exception block

begin
   insert.....
exception
   when others
      -log  the error
end;

0
 

Author Comment

by:fpkeegan
Comment Utility
The development requirment is that 100% of the code must be tested. But using the Begin execption method, requires to much testing.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you can't do what you want without modifying your script

are your insert statements distinct or are they effectively the same inserts but with different values?

that is

are they like this...
insert a (x,y,z) values(1,2,3)
insert a (x,y,z) values(3,4,5)

or are they

insert a(x,y,z) values(1,2,3)
insert b(m,n,o) values(9,8,7)


if the former you could turn your inserts into a procedure call that does the inserts, logs errors and returns.

you will have to test this too, but it's unavoidable.  Your requirement is to modify every insert, which, "could" mean they will all need retested.

0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
you could catch the errors if you created a view with a instead of trigger on the view

off course, you still need to modify your script

not better for performance
0
 

Author Comment

by:fpkeegan
Comment Utility
There is about 1000 inserts going into 27 different tables.  I do not want to use exception process.  There should be a way to turn off the error proccesing.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
sorry, there is not,  you must code for exception handling, even if your "handle" is to ignore the error
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
how did you generate the insert scripts ?

regenerate them with begin  insert into ...;  except when others then null; end;

you still can't let your computer get the coffee
and this a other such feature, nothing to do about that
except get up and get the coffee yourself ... or ask someone else to
0
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
well there is a other option:
use toad
this has a built in option to parse each line and ignore all errors in a script

i think the latest version has an automation feature too
haven't tested that yet
http://www.quest.com/toad-for-oracle/

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:POracle
Comment Utility
From plsql not possible without exception handling, from sql script it it possible.

create table test(a number primary key);

Here is content of TI.sql file
#############
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(1);  --generate error
insert into test values(4);
insert into test values(5);
##############

sql>@TI.sql

sql>select * from test
0
 
LVL 8

Expert Comment

by:POracle
Comment Utility
sorry I miss one important line from sql script TI.sql
(copy-paste bug in comment window in EE result in lots of time wasting)

Here is corrected content of TI.sql file
#############
WHENEVER SQLERROR CONTINUE;

insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(1);  --generate error
insert into test values(4);
insert into test values(5);
##############

"Whenever Sqlerror continue" is important line in script to continue insertion despite error.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Is there a reason you use a pl/sql block to do inserts?

If you are absolutely 100% set on not touching the PL/SQL you have:

-Create an Oracle Exceptions table: ORACLE_HOME\rdbms\admin\utlexcpt.sql
-Disable the constraints on the tables
-Run your script
:loop until no exceptions
-Enable the constraints
-Check the exceptions table
-Remove the offending rows
:end loop
0
 

Author Comment

by:fpkeegan
Comment Utility
I did not say I am 100% against touching the script. I said I am not going to put a exception handilig around each insert.  

The WHENEVER SQLERROR CONTINUE; is the best approch yet.   But I am getting PLS-00103: Encountered the symbol "SQLERROR" when expecting one of the following:     := . ( @ % ;  
When I try to use it in the script.

The use of toad is also good.

If TOAD can catch the errors and keep the transaction open, and commit the changed rows. How does it do that ?  Does it execute each statement in a begin / execption /end block ?

The script is not executed by me, I do not have access to any of the databases that this is to be executed on.  I do not even have read access to the DB. These databases contain personal and financial data.  The access is highly restricted.   It requires a minimum of two people to observer and execute each change script.  

0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
you use the WHENEVER SQLERROR CONTINUE   in sql*plus WITHOUT pl/sql.

Toad can do it, but again, only if you run it as a script of inserts, with no pl/sql.

IF you use pl/sql,  you MUST use pl/sql error handling.

If you do NOT use pl/sql, you have other options.

The criteria of your question has imposed the restriction.  It's not a question of what you or I would "like" to do, it's simply the syntax rules of the environment you are using.  In your case pl/sql.

Change your environment,  new rules (and effectively a new question since no answers would apply to this question)
0
 
LVL 31

Accepted Solution

by:
awking00 earned 250 total points
Comment Utility
You might also consider error logging.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
Comment Utility
if you mean the "log errors into" clause, that has to be appended to every insert statement as well

and requires creation of the logging table.

not that it's a bad suggestion, but it's another example of the code requiring some sort of change in order to handle the exceptions
0
 

Author Closing Comment

by:fpkeegan
Comment Utility
not the full syntax of the log errors into.  
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now