• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1613
  • Last Modified:

Oracle - continue on insert error

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
fpkeegan
Asked:
fpkeegan
  • 5
  • 4
  • 3
  • +3
2 Solutions
 
sdstuberCommented:
you will have to wrap each insert  with begin/end and exception block

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

0
 
fpkeeganAuthor Commented:
The development requirment is that 100% of the code must be tested. But using the Begin execption method, requires to much testing.
0
 
sdstuberCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Geert GruwezOracle dbaCommented:
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
 
fpkeeganAuthor Commented:
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
 
sdstuberCommented:
sorry, there is not,  you must code for exception handling, even if your "handle" is to ignore the error
0
 
Geert GruwezOracle dbaCommented:
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
 
Geert GruwezOracle dbaCommented:
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
 
POracleCommented:
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
 
POracleCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
fpkeeganAuthor Commented:
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
 
sdstuberCommented:
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
 
awking00Commented:
You might also consider error logging.
0
 
sdstuberCommented:
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
 
fpkeeganAuthor Commented:
not the full syntax of the log errors into.  
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now