Exception Handling in a proc having lots of sql statement

Hi All,

I am facing a problem. I have a interface procedure having arround 200 sql staement. Inserting all the returning value of sql in a table. So it will insert 200 rows in the table.

I am handling the exception through Savepoint. But the problem is that suppose i will get a exception after executing 60 sql stmt, then the proc is inserting 59 rows in table and i want to continue the excecution up to 200 stmt.

Can anybody explain me how i can continue the execution after getting the exception.(Without using New Block Begin ... End)
chinmaya224Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
if you want to use Oracle automatic error logging

Then, for each table you will modify,  create a corresponding log table

EXEC dbms_errlog.CREATE_ERROR_LOG('YOUR_TABLE','YOUR_TABLE_LOG');

then change every sql  to include a "LOG ERRORS" clause with a REJECT LIMIT

then in your overall exception handler,  read all of the log tables and raise/report or otherwise handle whatever exceptions you find.

this is likely to be less convenient than simply wrapping each statement with whatever handler is most appropriate

0
 
sdstuberCommented:
if you can consolidate the statements to a single statement (may not be feasible)  then you can use error logging limits in Oracle.

 if the statements are all effectively the same statement, use variables
and a forall  statement - again, Oracle

I don't know if sybase has an equivalent.

last option and most versatile, even if you don't like it, wrap each statement in it's own begin/exception/end  and handle each error as you encounter them
0
 
chinmaya224Author Commented:
No, all the statements are totally different, and not related to each other ..... we can't make it as single stmt ....

Begin/Exception/End will decrease the performance ........ as i have to write 200 block for that ...... and i have to schedule it on every day morning ....

Is there any other method .....
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sdstuberCommented:
"any other method"

oracle -yes, the first option still applies, but it takes extra setup maybe not worth it

sybase-i don't konw
0
 
HainKurtSr. System AnalystCommented:
try this:

begin
  insert here
catch
  when others then
  null; -- or do something...
end
0
 
sdstuberCommented:
>>> Begin/Exception/End will decrease the performance

Why do you believe that to be true?

Even if it is, I doubt it will be by an amount that you'll be able to measure easily

I just tested the results of wrapping 1000000 statements in begin/exception/end blocks and the extra time of the begin/end was smaller than I could capture.

The total time difference added (if any) was smaller than the variability of execution time of the actual statements
0
 
sdstuberCommented:
HainKurt, - that's exactly what the asker is protesting against doing.

I've already recommended doing it anyway and I just tested a million executions to confirm the extra begin/end doesn't add anything, or if it does, not something measurable compared to the execution of the statements themselves
0
 
chinmaya224Author Commented:
i am using Oracle database not sybase .... you can give me the solution on Oracle .... If i willn't get any other method then i will do by using begin end;  block ....
0
 
HainKurtSr. System AnalystCommented:
the solutions are for oracle
0
 
Franck PachotCommented:
Hi,
Enclosing the statements in a begin ... exception ... end is the right way to do that  in pl/sql.
Regards,
Franck.
0
 
HainKurtSr. System AnalystCommented:
everybody says the same thing, so just use:

BEGIN
  insert here
EXCEPTION
   WHEN OTHERS
   THEN
      --DBMS_OUTPUT.put_line ('error is :' || SQLERRM);
      null;
END;
0
 
sdstuberCommented:
yes, everybody is saying the same thing

please don't do that.  if you're only going to repeat what has already been posted, then don't post.
0
 
chinmaya224Author Commented:
Not adject result
0
 
sdstuberCommented:
why the C?  You might not have gotten an answer you liked, but that doesn't make it wrong
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.