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.
fpkeeganAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Geert GOracle 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 GOracle 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 GOracle 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
awking00Information Technology SpecialistCommented:
You might also consider error logging.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.