Solved

Oracle - continue on insert error

Posted on 2010-08-12
16
1,478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
  • +3
16 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 33422517
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
ID: 33423585
The development requirment is that 100% of the code must be tested. But using the Begin execption method, requires to much testing.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 33423676
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 33424587
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
ID: 33425978
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 74

Expert Comment

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

Expert Comment

by:Geert Gruwez
ID: 33427391
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 37

Expert Comment

by:Geert Gruwez
ID: 33427422
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
 
LVL 8

Expert Comment

by:POracle
ID: 33428582
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
ID: 33428603
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 33429114
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
ID: 33429505
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 74

Expert Comment

by:sdstuber
ID: 33429547
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 32

Accepted Solution

by:
awking00 earned 250 total points
ID: 33430161
You might also consider error logging.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 33430265
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
ID: 33431965
not the full syntax of the log errors into.  
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

695 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