Home » SQL & PL/SQL » SQL & PL/SQL » Error Creating a Database trigger (Oracle 11.1, Windows 2K3)
Error Creating a Database trigger [message #353010] Fri, 10 October 2008 10:06 Go to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi guys,

I created this database logoff trigger but the code does not compile on 11g though it works well on oracle 10.2. Has anyone had a similar experience with this? The trigger is owned by sys, while the tables are created in the system schema. This is intentional.

By the way, the error I get when I try to login is ora-24315 illegal attribute type.

I have also added the DDL for the tables I am referring to. Thanks in advance for any feedback.

create or replace trigger sys.cc_logoff_trig
before logoff on database
declare
logoff_sid pls_integer;
logoff_time date :=sysdate;
begin
select sid into logoff_sid
from v$mystat
where rownum < 2;

insert into system.cc_session_event_history
(sid
, serial#, username, osuser, paddr, process, logon_time, type, event, total_waits, total_timeouts,
time_waited, average_wait, max_wait, logoff_timestamp
)
select se.sid
, s.serial#, s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits,
se.total_timeouts, se.time_waited, se.average_wait, se.max_wait, logoff_time
from v$session_event se, v$session s
where se.sid = s.sid
and s.username = login_user
and s.sid = logoff_sid;

insert into system.cc_sesstat_history
(username, osuser, sid, serial#, paddr, process, logon_time, statistic#, name, value, logoff_timestamp)
select s.username, s.osuser, ss.sid, s.serial#, s.paddr, s.process, s.logon_time, ss.statistic#,
sn.name, ss.value, logoff_time
from v$sesstat ss, v$statname sn, v$session s
where ss.statistic# = sn.statistic#
and ss.sid = s.sid
and sn.name in ('CPU used when call started',
'CPU used by this session',
'recursive cpu usage',
'parse time cpu')
and s.sid = logoff_sid
and s.username = login_user;

end;

create table system.cc_session_event_history
tablespace user_data
as select s.sid, s.serial#, s.username,s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event,
se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, se.max_wait, sysdate as logoff_timestamp
from v$session_event se, v$session s
where 1=2;

create table system.cc_sesstat_history
tablespace user_data
as select c.username, c.osuser, a.sid, c.serial#, c.paddr, c.process, c.logon_time,
a.statistic#, b.name, a.value, sysdate as logoff_timestamp
from v$sesstat a, v$statname b, v$session c
where 1=2;
Re: Error Creating a Database trigger [message #353016 is a reply to message #353010] Fri, 10 October 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Use SQL*Plus and copy and paste your session including the error.

Regards
Michel
Re: Error Creating a Database trigger [message #353022 is a reply to message #353010] Fri, 10 October 2008 11:15 Go to previous messageGo to next message
efachim
Messages: 42
Registered: July 2008
Member
Hey Michel,

Thanks for the feedback. I have reposted the question below. By the way, I must have missed where to edit a post. Does that feature exist?

Hi guys,

I created this database logoff trigger but the code does not compile on 11.1.0 though it works well on oracle 10.2.0. Has anyone had a similar experience with this? The trigger is owned by sys, while the tables are created in the system schema. This is intentional.

By the way, the error I get when I try to login is ora-24315 illegal attribute type.

Thanks in advance for any feedback.

create or replace trigger sys.cc_logoff_trig
before logoff on database
declare
logoff_sid pls_integer;
logoff_time date :=sysdate;
begin
select sid into logoff_sid
from v$mystat
where rownum < 2;

insert into system.cc_session_event_history
(sid, serial#, username, osuser, paddr, process, logon_time, type, event, total_waits, 
total_timeouts, time_waited, average_wait, max_wait, logoff_timestamp
)
select se.sid, s.serial#, s.username, s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event, se.total_waits,
se.total_timeouts, se.time_waited, se.average_wait, se.max_wait, logoff_time
from v$session_event se, v$session s
where se.sid = s.sid
and s.username = login_user
and s.sid = logoff_sid;

insert into system.cc_sesstat_history
(username, osuser, sid, serial#, paddr, process, logon_time, statistic#, name, value
, logoff_timestamp)
select s.username, s.osuser, ss.sid, s.serial#, s.paddr, s.process, s.logon_time, ss.statistic#,
sn.name, ss.value, logoff_time
from v$sesstat ss, v$statname sn, v$session s
where ss.statistic# = sn.statistic#
and ss.sid = s.sid
and sn.name in ('CPU used when call started',
'CPU used by this session',
'recursive cpu usage',
'parse time cpu')
and s.sid = logoff_sid
and s.username = login_user;

end; 


I have also added below, the DDL for the tables I am referring to.

create table system.cc_session_event_history
tablespace user_data
as select s.sid, s.serial#, s.username,s.osuser, s.paddr, s.process, s.logon_time, s.type, se.event,
se.total_waits, se.total_timeouts, se.time_waited, se.average_wait, se.max_wait, sysdate as 
logoff_timestamp from v$session_event se, v$session s
where 1=2;

create table system.cc_sesstat_history
tablespace user_data
as select c.username, c.osuser, a.sid, c.serial#, c.paddr, c.process, c.logon_time,
a.statistic#, b.name, a.value, sysdate as 
logoff_timestamp
from v$sesstat a, v$statname b, v$session c
where 1=2;


This is the sqlplus printout
SQL> conn / as sysdba;
Connected.
SQL> conn autodba/autodba;
ERROR:
ORA-04098: trigger 'AUTODBA.CC_LOGOFF_TRIG' is invalid and 
failed re-validation


ERROR:
ORA-24315: illegal attribute type

[Updated on: Fri, 10 October 2008 11:17]

Report message to a moderator

Re: Error Creating a Database trigger [message #353025 is a reply to message #353022] Fri, 10 October 2008 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and reexecute the trigger creation.
Is your code so ugly than in your post? Do you never format your code? I then understand why there are errors.

Regards
Michel
Re: Error Creating a Database trigger [message #353029 is a reply to message #353025] Fri, 10 October 2008 12:10 Go to previous messageGo to next message
efachim
Messages: 42
Registered: July 2008
Member
Hi Michel,

Clearly I can only get better. Thanks for the feedback. The query worked on a sqlplus worksheet. I initially ran it off a dos command window Sad.

rgds, efachim.

Michel Cadot wrote on Fri, 10 October 2008 10:38
Use SQL*Plus and reexecute the trigger creation.
Is your code so ugly than in your post? Do you never format your code? I then understand why there are errors.

Regards
Michel


Re: Error Creating a Database trigger [message #353032 is a reply to message #353029] Fri, 10 October 2008 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it is ok now?

Regards
Michel
Re: Error Creating a Database trigger [message #353035 is a reply to message #353032] Fri, 10 October 2008 12:37 Go to previous message
efachim
Messages: 42
Registered: July 2008
Member
Yes it is. Thanks.
Previous Topic: Single quote problem
Next Topic: index help
Goto Forum:
  


Current Time: Wed Dec 07 20:35:17 CST 2016

Total time taken to generate the page: 0.05437 seconds