Home » SQL & PL/SQL » SQL & PL/SQL » ORA:00604
ORA:00604 [message #255145] Mon, 30 July 2007 16:08 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

create or replace trigger login_audit_trig
  after logon on database
declare
  v_cnt number;
  p_cnt number;
begin
  select COUNT(*) 
    into v_cnt 
    from v$session where audsid = USERENV('sessionid') and upper(program) ='BUSOBJ.EXE';
  select count(*)
    into p_cnt
    from lc_monitor.authorize_users_tab a
   where upper(a.user_id) = upper(ora_login_user)
     and upper(a.osuser_id) in
         (select upper(osuser) from v$session where audsid = USERENV('sessionid'));
  if v_cnt > 0 then
    insert into lc_monitor.login_audit
    (user_id,user_sid,user_serial,login_date,osuserid,machinename,program) 
    select user,
           sid,
           serial#,
           logon_time,
           osuser,
           machine, 
           program  
           from v$session 
    where audsid= USERENV('sessionid')
    and user not in ('DBSNMP','SYS','PERFSTAT');
  else if p_cnt > 0 then 
    insert into lc_monitor.login_audit
    (user_id,user_sid,user_serial,login_date,osuserid,machinename,program) 
    select user,
           sid,
           serial#,
           logon_time,
           osuser,
           machine, 
           program  
           from v$session 
    where audsid= USERENV('sessionid')
    and user not in ('DBSNMP','SYS','PERFSTAT');
   else 
    Raise_application_error(-20501,'Error: login using ' || ora_login_user ||' prohibited');
  end if;
  end if;
  commit;
 end;



This trigger creates with no comiplation error. But It fired with the following error.
ORA-00604: error occured at recursive sql level 1.
ORA-20501: ERROR: Login using c1106440 prohibited.
ORA-06512: at line 41

The second error is fine. Is anybody help me with the soloution so i can avoid the first and last error.

Thanks in advance

Oracle 9.0.6.0
Re: ORA:00604 [message #255200 is a reply to message #255145] Tue, 31 July 2007 00:31 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
It looks like your trigger is working as expected.

The other error lines (ORA-00604 and ORA-06512) are just informational messages.
Re: ORA:00604 [message #255210 is a reply to message #255145] Tue, 31 July 2007 00:51 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't avoid these messages, they are part of the stack.

Regards
Michel
Previous Topic: Extract data in excel/csv and enail automatically
Next Topic: To get no. of working days in a month using a query
Goto Forum:
  


Current Time: Fri Dec 09 15:59:08 CST 2016

Total time taken to generate the page: 0.29162 seconds