Home » SQL & PL/SQL » SQL & PL/SQL » Log details for exception for the sql (Oracle 10.0.2.3)
Log details for exception for the sql [message #437024] Wed, 30 December 2009 06:23 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
In my script there are multiple dml statements which performs operation on a temporary table.

Is there a way to log information like which was the sql that failed during the process if failed?


Thanks
Re: Log details for exception for the sql [message #437026 is a reply to message #437024] Wed, 30 December 2009 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about putting prompt statements in your script and/or set echo on?

Regards
Michel
Re: Log details for exception for the sql [message #437028 is a reply to message #437026] Wed, 30 December 2009 06:34 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to log into a table with timestamp to track the problem.To know where it was failed.
For this do I need to store each sql as variable ? or there is any other way that i can go for.
Thanks
icon12.gif  Re: Log details for exception for the sql [message #437030 is a reply to message #437028] Wed, 30 December 2009 07:14 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
1) You may consider traceing the session.
2) Use prompt statements like Michel suggests
3) Use exit codes:
var rc number;
WHENEVER SQLERROR EXIT :rc;
exec :rc:=1;
{dml statement 1};
exec :rc:=2;
{dml statement 2};
-- Etc...

4) Create a log table and insert pertinent information.
Razz

Re: Log details for exception for the sql [message #437042 is a reply to message #437028] Wed, 30 December 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it with a table, a procedure and change your script to call the procedure for each line.
Something like:
create table log (
  sql varchar2(4000), 
  code integer, 
  tim timestamp default systimestamp
)
/
create or replace procedure exec (p_sql in varchar2) 
is
begin
  execute immediate p_sql
exception
  when others then
    insert into log (sql, code) values (p_sql, sqlcode);
    commit;
end;
/

And in your script, instead of:
sql1
sql2
sql3
you have
begin
exec (sql1);
exec (sql2);
exec (sql3);
end;
/

Regards
Michel

[Updated on: Wed, 30 December 2009 09:02]

Report message to a moderator

Re: Log details for exception for the sql [message #437046 is a reply to message #437042] Wed, 30 December 2009 08:37 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you!
Re: Log details for exception for the sql [message #437085 is a reply to message #437046] Wed, 30 December 2009 23:30 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Donot forget to use autonomous transactions for your logging mechanism
Previous Topic: All records in cursor
Next Topic: Need for Materialized Views
Goto Forum:
  


Current Time: Sat Dec 10 20:36:24 CST 2016

Total time taken to generate the page: 0.05191 seconds