Skip to Main Content
  • Questions
  • How to suppress ORA-00604: error occurred at recursive SQL level 1

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, S.

Asked: July 25, 2006 - 5:32 pm UTC

Last updated: April 02, 2009 - 6:22 pm UTC

Version: 9.2.0.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Could you tell me how to suppress "ORA-00604: error occurred at recursive SQL level 1" when I raise application error using "RAISE_APPLICATION_ERROR". I am using it in after logon trigger and restricting few users. I am displaying my error message. But I am getting the below errors also with my application error message.

Is it possible to suppress the below error messages and want to display only the application error message.
********************************************************
1)ORA-00604: error occurred at recursive SQL level 1
2)ORA-06512: at line 53
********************************************************

Thanks,
SC.

and Tom said...

you cannot, it works the way it works. Applications are responsible for displaying to end users what the application thinks is appropriate. This is something your application would do - eg: put up a message box that says "login failed, sorry"

SQLPlus displays the entire call stack because the developers of sqlplus felt that was most appropriate.

You can have your applications display whatever you feel is appropriate.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Sreekanth, July 26, 2006 - 3:16 pm UTC

Thanks for the reply. It was useful to convince other people that it's not possible.

One more question

S, July 26, 2006 - 3:28 pm UTC

Tom, Thank you for your response. I have an other question. Could you pls tell me if it is possible to use dbms_output and display a message to the user in after logon trigger. The user can be logged in from sqlplus or any other tool.

Tom Kyte
July 26, 2006 - 4:14 pm UTC

but sqlplus is pretty much the only tool that would get it - and sqlplus wouldn't even get it because it doesn't realize it would be there to be gotten.

that and the logon trigger doesn't really work in the "same space", the dbms-output stuff isn't going into a buffer we have access to.

Susan, April 07, 2008 - 10:59 pm UTC

if you try instead of raise application error and try to kill the session it still gives the error. The error appears within a trace file, is there no way of suppressing the errors?

Tom Kyte
April 09, 2008 - 1:57 pm UTC

I did not follow this - if you try what instead of what? not sure what you mean

Trigger

Susan, March 22, 2009 - 10:14 pm UTC

Hi Tom,

I have been requested to create a script that would restrict a user to use a particular schema . Now i managed to get a trigger up and running :

#############################
CREATE OR REPLACE TRIGGER session_access
AFTER LOGON ON DATABASE

DECLARE
v_username sys.v_$session.username%type;
v_osuser sys.v_$session.osuser%type;
BEGIN
select s.username ,s.osuser into v_username,v_osuser
from v$session s where s.username='SUSANJO' and s.osuser='oracle';

IF (v_username ='SUSANJO' AND V_OSUSER='oracle' )
THEN
RAISE_APPLICATION_ERROR(-20001, 'You are not allowed to login using the program');
END IF;

END session_access;

###############################################
Output of the result:
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Mar 23 13:37:37 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options

SQL> connect susanjo
Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You are not allowed to login using the program
ORA-06512: at line 10


Warning: You are no longer connected to ORACLE.
SQL> exit

From the articles i have understood you cannot suppress the ORA-00604 error number BUT ( this is a huge but)

When my grid control tries to logon using dbsnmp account, it fails. therefore i tried on pl/sql the output is :

SQL> connect dbsnmp
Enter password:
ERROR:
ORA-04088: error during execution of trigger 'SYS.SESSION_ACCESS'
ORA-01403: no data found
ORA-06512: at line 5

The trigger has nothing to do with the account dbsnmp? what has happened?
Tom Kyte
March 24, 2009 - 11:07 am UTC

... AFTER LOGON ON DATABASE ....

that trigger is for EVERY user

and your select into is failing, it returns no rows in some cases, you need to catch the no_data_found and "ignore it", since no data found means "you are OK"



and this trigger of yours would be so simple to get around... the OS user can be very easy to change on you... I run linux, I'll sqlnet in as any user I want, I'll just make up an OS user

Susan, March 22, 2009 - 10:20 pm UTC

It doesnt let any of the users other than sys / system to login :( what has happened???


SQL> connect dbsnmp
Enter password:
ERROR:
ORA-04088: error during execution of trigger 'SYS.SESSION_ACCESS'
ORA-01403: no data found
ORA-06512: at line 5


Warning: You are no longer connected to ORACLE.
SQL> exit

SQL> exit


SQL> connect test
Enter password:
ERROR:
ORA-04088: error during execution of trigger 'SYS.SESSION_ACCESS'
ORA-01403: no data found
ORA-06512: at line 5


Warning: You are no longer connected to ORACLE.

Tom Kyte
March 24, 2009 - 11:08 am UTC

see above...

Susan, March 29, 2009 - 6:31 pm UTC

I have edited the trigger as such :

DECLARE
v_username VARCHAR2 (30);
v_osuser VARCHAR2 (30);
BEGIN
IF sys_context('userenv','session_user') ='DSSADM' AND sys_context('userenv','os_user') = lower('susanjo')
THEN
raise_application_error (-20001, 'Please use your own account to login');
END IF;
END;

it seems to have worked, i dont get it .. how can a persn create a os user and fool.
Could you suggest some better ways to make this business analyst use his own login !
Tom Kyte
March 30, 2009 - 4:08 pm UTC

I run linux on my laptop (in a virtual machine).

I am root

I will do whatever I want. I will connect to you over the network from my machine, my machine that I control entirely.

Susan John, April 02, 2009 - 6:12 pm UTC

he doesnt not have root access, he uses an application to login. Issue is that he logs in as dssadm where he is suppose to login as sharmaas,

Now i edited the sql ::
CREATE OR REPLACE TRIGGER "SYS".SESSION_ACCESS AFTER
LOGON ON "DSSADM".SCHEMA DECLARE
v_username VARCHAR2 (30);
v_osuser VARCHAR2 (30);
BEGIN
IF sys_context('userenv','session_user') = 'DSSADM' AND sys_context('userenv','os_user') = lower('susanjo')
THEN
raise_application_error (-20001, 'Please use your own account to login');
END IF;
END;

If i were to login as dssadm through sql developer it stops but the minute i edit the trigger from os user to erikaw. It allows the person to login, which is absolutely weird.

Tom Kyte
April 02, 2009 - 6:22 pm UTC

does he have his own laptop. if so, he has root.

If you believe this is bullet proof (it is not, especially if he doesn't follow rules in the first place), go for it.

but - very easy to workaround.


I would drop the "please" from the error message ;)

and bear in mind also that if DSSADM is a DBA, this will not work, failed logon triggers do not affect them

ops$tkyte%ORA10GR2> CREATE OR REPLACE TRIGGER SESSION_ACCESS AFTER
  2  LOGON ON "OPS$TKYTE".SCHEMA DECLARE
  3  v_username VARCHAR2 (30);
  4  v_osuser VARCHAR2 (30);
  5  BEGIN
  6  raise_application_error (-20001, 'Please use your own account to login');
  7  END;
  8  /

Trigger created.

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> !tail /home/ora10gr2/rdbms/log/alert_ora10gr2.log
Thu Apr  2 17:09:28 2009
Errors in file /home/ora10gr2/rdbms/log/ora10gr2_ora_25592.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Please use your own account to login
ORA-06512: at line 5
Thu Apr  2 17:09:35 2009
Errors in file /home/ora10gr2/rdbms/log/ora10gr2_ora_25593.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Please use your own account to login
ORA-06512: at line 5



... If i were to login as dssadm through sql developer ....

and if I were on your system, it would work, because I'd just create an OS user account.... on my own machine.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library