Home » SQL & PL/SQL » SQL & PL/SQL » Making logs - CREATE MATERIALIZED VIEW
icon5.gif  Making logs - CREATE MATERIALIZED VIEW [message #206809] Fri, 01 December 2006 11:00 Go to next message
martarosz
Messages: 3
Registered: December 2006
Junior Member
Hello

How I can do this.

I need insert logs to table.
I making sql statement. for example create table.
How insert to table message "table create" or another text which will be generate by oracle database, for example errors.


Thx for help.

[Updated on: Fri, 01 December 2006 15:26]

Report message to a moderator

Re: Making Logs [message #206815 is a reply to message #206809] Fri, 01 December 2006 11:51 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I'm not entirely sure what you are asking ..

If you are looking for tracing of when database objects are created/altered such as tables ,triggers etc. You could use a Schema Level trigger to populate an audit table.

Re: Making Logs [message #206818 is a reply to message #206815] Fri, 01 December 2006 11:59 Go to previous messageGo to next message
martarosz
Messages: 3
Registered: December 2006
Junior Member
I'm must execute lot of sql statement. And I must check do they execute correctly.
Re: Making Logs [message #206827 is a reply to message #206818] Fri, 01 December 2006 12:25 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I think you want to look at WHENEVER SQLERROR.
Re: Making Logs [message #206830 is a reply to message #206827] Fri, 01 December 2006 12:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Spool your output to file.
Re: Making Logs [message #206856 is a reply to message #206809] Fri, 01 December 2006 14:56 Go to previous messageGo to next message
martarosz
Messages: 3
Registered: December 2006
Junior Member
I try use tigger
create or replace trigger catch_errors
   after servererror on database
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into 
    caught_errors (dt     , username      ,msg ,stmt )
           values (sysdate, ora_login_user,msg_,stmt_);
end;

I insert errors to caught_errors table.
I try create table witch errors - work ok - insert error to table caught_errors

When I try CREATE MATERIALIZED VIEW witch errors don't work- don't instert error to table caught_errors

Let me know why it doesn't work good.
Do you have any solutions?

Thx

[Updated on: Fri, 01 December 2006 15:00]

Report message to a moderator

Re: Making Logs [message #206869 is a reply to message #206856] Fri, 01 December 2006 18:03 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1380003556171

Be aware that you won't catch all types of errors in an error logging trigger. I don't have an example offhand though.
Previous Topic: Reporting
Next Topic: LEFT OUTER JOIN
Goto Forum:
  


Current Time: Wed Dec 07 16:22:47 CST 2016

Total time taken to generate the page: 0.05881 seconds