Re: Successful login via SQLPlus produces a server error

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Thu, 28 Apr 2005 08:32:01 +0200
Message-ID: <d4q012$89g$1@news.BelWue.DE>

g3000 wrote:
> There is nothing to comment out. I looked at the code and there is no
> typo of the role name.
>
> And commenting out does not tell me HOW and WHY its executing.
>

Excuse me? You've got an trigger sys_logon executing logon_proc. If you modify the sys_logon trigger to not execute logon_proc that *will* tell you if that is the source of the error message when using SQL*PLUS (if this is still your issue).

And since the code of the procedure is wrapped, how did you look at the code anyway?

Finally, you say that this also happens when connecting with sysdba privileges. So it *can't* be a logon trigger, since those wouldn't execute for sys logons:

	sys_at_DEMO10G>create or replace trigger sys_logon after logon
	  2  on database
	  3  begin
	  4    dbms_session.set_role('BOGUS_ROLE');
	  5* END;
	sys_at_DEMO10G>/
	
	Trigger created.
	
	sys_at_DEMO10G>exit
	Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr 

	oduction
	With the Partitioning and Data Mining options
	baer_at_casp003:~> sqlplus / as sysdba
	
	SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 28 08:15:51 2005

	Copyright (c) 1982, 2004, Oracle.  All rights reserved.


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

	sys_at_DEMO10G>exit

Ok, no trigger fired for sys.

        Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr

	oduction
	With the Partitioning and Data Mining options
	baer_at_casp003:~> sqlplus /
	
	SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 28 08:15:55 2005
	
	Copyright (c) 1982, 2004, Oracle.  All rights reserved.
	
	ERROR:
	ORA-00604: error occurred at recursive SQL level 1
	ORA-04092: cannot SET ROLE in a trigger
	ORA-06512: at "SYS.DBMS_SESSION", line 124
	ORA-06512: at line 2

Oh dear, you can't even set a role in a trigger, so we can safely dismiss this track.

Which means, that we have to go back to my initial suggestion backed up by Rauf Sarwar to check login.sql *and* glogin.sql which you deliberately ignored.

> DECLARE
> *
> ERROR at line 1:
> ORA-01924: role 'SAMS_BASE' not granted or does not exist
> ORA-06512: at "SYS.DBMS_SESSION", line 120
> ORA-06512: at "SAMS.SAMS_USER_LIB", line 66
> ORA-01924: role 'SAMS_SECURITY' not granted or does not exist
> ORA-06512: at "SAMS.SAMS_USER_LIB", line 219
> ORA-06512: at line 4
>
>
> SQL>
Look at the first line: DECLARE. This is an anonymous pl/sql block. This *has* to be executed through some startup scripts, except if logon_proc does use native dynamic sql, but no, wait:

sys_at_DEMO10G>create or replace procedure logon_proc

   2 is
   3 begin
   4 execute immediate 'declare
   5 l_dummy int;
   6 begin

   7      l_dummy := 0;
   8      dbms_session.set_role (''BOGUS'');

   9 end;';
  10 end;
  11 /

Procedure created.

sys_at_DEMO10G>exec logon_proc;
BEGIN logon_proc; END;

*
ERROR at line 1:

ORA-06565: cannot execute SET ROLE from within stored procedure
ORA-06512: at "SYS.DBMS_SESSION", line 124
ORA-06512: at line 5
ORA-06512: at "SYS.LOGON_PROC", line 4
ORA-06512: at line 1

So check your glogin.sql. Or better: try from a different client - do you still get this error?

Regards,
Holger Received on Thu Apr 28 2005 - 01:32:01 CDT