Home » SQL & PL/SQL » SQL & PL/SQL » how to put record in error log file
how to put record in error log file [message #253682] Tue, 24 July 2007 10:52 Go to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Hi,

If i am getting a error in a procedure. how can i keep the record of this error in error log file. Do using Raise_application_error will automaticaly put the record in error log file? And how can i access error log file.

Regards,
Prashant
Re: how to put record in error log file [message #253685 is a reply to message #253682] Tue, 24 July 2007 11:02 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you could create a log error TABLE and insert records in there (using an exception handler). Something like this:
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO log_error_table (when, who, what) 
    VALUES 
    (sysdate, 'procedure_1', 'No data found for customer ' || l_cust);
  WHEN TOO_MANY_ROWS THEN
    ...
After doing so, it is an easy task to write a report which would give you required information.
Re: how to put record in error log file [message #253691 is a reply to message #253685] Tue, 24 July 2007 11:13 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Thanx for the response.
How can i use sqlcode and sqlerrm? can i insert this values in my log table?
Re: how to put record in error log file [message #253694 is a reply to message #253691] Tue, 24 July 2007 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
How can i use sqlcode and sqlerrm?

Like you spell it.
Quote:
can i insert this values in my log table?

Yes like any other integer/string.

Regards
Michel
Re: how to put record in error log file [message #253697 is a reply to message #253694] Tue, 24 July 2007 11:20 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

Can you please give me any example? or can you refer any document for this.

Regards,
Prashant
Re: how to put record in error log file [message #253698 is a reply to message #253697] Tue, 24 July 2007 11:22 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, it is just like MIchel told you and the way you said:
INSERT INTO log_error_table (err_code, err_message)
VALUES (SQLCODE, SQLERRM);
Re: how to put record in error log file [message #253712 is a reply to message #253698] Tue, 24 July 2007 12:33 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

I am using the below code:


create table log_error_table (when date,err varchar2(20), msg varchar2(100))

declare
av sttm_customer.customer_no%type;
begin
select customer_no into av from sttm_customer where 1=2;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO log_error_table  
    VALUES 
    (sysdate, sqlcode,sqlerrm);
end;


and i got following error while executing the code:

ORA-06550: line 9, column 23:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored


Please advice.

Regards,
Prashant
Re: how to put record in error log file [message #253714 is a reply to message #253682] Tue, 24 July 2007 12:46 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You need to create your table outside of the procedure

when is probably not a good choice for a column name. Choose something like update_date or error_date.
Re: how to put record in error log file [message #253717 is a reply to message #253712] Tue, 24 July 2007 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you can't do it directly, then do it via a local variable.
Developing needs to switch one's brain on.

Regards
Michel
Re: how to put record in error log file [message #253755 is a reply to message #253717] Tue, 24 July 2007 14:58 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is me to blame ... I apologize.

It won't work for the reason Michel said - sqlcode and sqlerrm can not be directly used; one has to use a variable. Here is an example:
SQL> CREATE TABLE log_err (datum DATE, err_code NUMBER, err_txt VARCHAR2(255));

Table created.

SQL>
SQL> DECLARE
  2    l_code log_err.err_code%TYPE;
  3    l_text log_err.err_txt%TYPE;
  4
  5    l_salary EMP.sal%TYPE;
  6  BEGIN
  7    -- this will raise NO_DATA_FOUND exception
  8    SELECT sal INTO l_salary
  9      FROM EMP
 10      WHERE empno = '1234';
 11
 12  EXCEPTION
 13    WHEN NO_DATA_FOUND THEN
 14      l_code := SQLCODE;
 15      l_text := SQLERRM;
 16
 17      INSERT INTO log_err
 18        (datum, err_code, err_txt)
 19         VALUES
 20        (SYSDATE, l_code, l_text);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM log_err;

DATUM      ERR_CODE ERR_TXT
-------- ---------- -----------------------------------------------------------

24.07.07        100 ORA-01403: no data found

SQL>
Re: how to put record in error log file [message #253908 is a reply to message #253755] Wed, 25 July 2007 05:20 Go to previous messageGo to next message
spsbombay
Messages: 29
Registered: June 2007
Junior Member

thanx.now its working fine.
1 more query.
Is there any table provided by the oracle for error log.
if yes will the error code defined by the user(by raise_application_error) will be inserted into that table?

Regards,
Prashant
Re: how to put record in error log file [message #253910 is a reply to message #253908] Wed, 25 July 2007 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No there is no such table.

Regards
Michel
Re: how to put record in error log file [message #253928 is a reply to message #253910] Wed, 25 July 2007 05:44 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you'll be interested in reading an article about DML error logging on OraFAQ pages, posted by Natalka Roshak (or even search for more information about this feature on the Internet / official Oracle documentation).
Previous Topic: Sorting for stragg function column
Next Topic: suggest to user sql%found or sql%rowcount
Goto Forum:
  


Current Time: Tue Feb 11 02:38:59 CST 2025