?
Solved

Exception Handling in a proc having lots of sql statement

Posted on 2011-05-11
15
Medium Priority
?
334 Views
Last Modified: 2013-12-07
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)
0
Comment
Question by:chinmaya224
  • 7
  • 3
  • 3
  • +1
14 Comments
 
LVL 74

Expert Comment

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

Author Comment

by:chinmaya224
ID: 35739888
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35739924
"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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 61

Expert Comment

by:HainKurt
ID: 35739986
try this:

begin
  insert here
catch
  when others then
  null; -- or do something...
end
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35740015
>>> 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
 
LVL 74

Expert Comment

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

Author Comment

by:chinmaya224
ID: 35740230
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35740345
the solutions are for oracle
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 35740407
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
 
LVL 15

Expert Comment

by:Franck Pachot
ID: 35740848
Hi,
Enclosing the statements in a begin ... exception ... end is the right way to do that  in pl/sql.
Regards,
Franck.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35741464
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
 
LVL 74

Expert Comment

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

Author Closing Comment

by:chinmaya224
ID: 35896748
Not adject result
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35896930
why the C?  You might not have gotten an answer you liked, but that doesn't make it wrong
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses

839 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