Is there any way of know which of my update or insert statements executed the catch block by failing
A:
Oracle PL/SQL has exception handling: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/07_errs.htm.
Otherwise you can use programming language exception handling facilities.
The MYYN
2010-05-25 12:50:49
+1
A:
How about to surround your update and insert statements with different try-catch blocks?
Also you can check SELECT ERROR_MESSAGE(), ERROR_STATE() to determine what does throw current exception
abatishchev
2010-05-25 12:51:47
and duplicate the catch logic each time?
KM
2010-05-25 17:24:18
@KM: Usually this is just several strings of code.. In my application - 5 (which are duplicated 97 times for each sp). Why not? Of course if it contains more string, more logic - it's bad idea to duplicate. But why don't carry out it to additional stored proc?
abatishchev
2010-05-25 17:34:54
if you use a temp table or any other thing that causes a procedure recompile you add load to the system because it will have to recompile that repetitive code each time the procedure is run, not to mention cluttering up the actual code and making each procedure way longer than it needs to be.
KM
2010-05-25 18:42:35
A:
The stack dump can contain some information, like what line the error occured on, but that's not directly useful.
The simplest is probably to set a variable before each query, then you can check it's value in the catch.
Guffa
2010-05-25 12:52:19
A:
One thing that comes to mind is logging an error code to the event viewer right after its been executed.
For example
Try err_code=1 --Query 1-- err_code=2 --Query 2-- . . . Catch -- log err_code to event viewer--
Then by viewing the event viewer you will see the err_code of the last succesfull query.
hope it helps
A:
try something like this:
Declare @Where varchar(50)
BEGIN TRY
SET @Where='1st update'
UPDATE....
SET @Where=NULL
...
SET @Where='2nd update'
UPDATE....
SET @Where=NULL
...
SET @Where='1 insert'
INSERT....
SET @Where=NULL
END TRY
BEGIN CATCH
PRINT @Where
END CATCH
KM
2010-05-25 13:03:52
+1
A:
Start by creating a table variable Insert the steps you have run or the error messages into the table varaiable as you go through the proc. After the rollback inthe catch, the table variable is still available, then use it to insert into your logging table.
HLGEM
2010-05-25 16:55:49