When I logon to my database via SQLPlus I get an error about a PL/SQL
package.
I dont have a when logon trigger either.

I do have a on db startup trigger ( which I dont think applies ).

Any ideas? I understand what the error is just dont know why. Im loggin
in as SYS ( DBA ).

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Apr 25 13:20:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

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>

"g3000" <carlton_gregory@yahoo.com> wrote in message
news:1114450167.383225.59200@f14g2000cwb.googlegro ups.com...
> When I logon to my database via SQLPlus I get an error about a PL/SQL
> package.
> I dont have a when logon trigger either.
>
> I do have a on db startup trigger ( which I dont think applies ).
>
> Any ideas? I understand what the error is just dont know why. Im loggin
> in as SYS ( DBA ).
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Apr 25 13:20:57 2005
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> 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>
>

Did you try to install a patchset and did it fail (for example because
SYSTEM tablespace was full) ?
What's the result of : SELECT count(*) FROM DBA_OBJECTS WHERE STATUS =
'INVALID'; ?

Matthias

this is the result
SQL> select object_name, object_type from dba_objects where
status='INVALID';

OBJECT_NAME
------------------------------------------------------------------------------
OBJECT_TYPE
------------------
MY_ALL_OBJECTS_AGGS
MATERIALIZED VIEW

SI_LIB_XML
PACKAGE BODY


SQL>

did not have a patchset fail

On 25 Apr 2005 11:26:05 -0700, "g3000" <carlton_gregory@yahoo.com>
wrote:

>this is the result
>SQL> select object_name, object_type from dba_objects where
>status='INVALID';
>
>OBJECT_NAME
>------------------------------------------------------------------------------
>OBJECT_TYPE
>------------------
>MY_ALL_OBJECTS_AGGS
>MATERIALIZED VIEW
>
>SI_LIB_XML
>PACKAGE BODY
>
>
>SQL>
>
>did not have a patchset fail

The original error messages are quite clear.
You are calling the procedure sams.sam_user_lib.

The procedure tries to set roles by means of the dbms_session
package.
However the roles sam_user and sam_security don't exist.
You need to create them and possibly grant privileges to them.
The package sams is third party code, not Oracles


--
Sybrand Bakker, Senior Oracle DBA

g3000 wrote:

> When I logon to my database via SQLPlus I get an error about a PL/SQL
> package.
> I dont have a when logon trigger either.

Are you sure? The database seems to think otherwise.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

g3000 wrote:
> When I logon to my database via SQLPlus I get an error about a PL/SQL
> package.
> I dont have a when logon trigger either.
>
> I do have a on db startup trigger ( which I dont think applies ).
>
> Any ideas? I understand what the error is just dont know why. Im loggin
> in as SYS ( DBA ).
>
> SQL*Plus: Release 9.2.0.6.0 - Production on Mon Apr 25 13:20:57 2005
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.6.0 - Production
>
> 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>
>

Does this *only* happen when using SQL*PLUS, or does this also occur
when using something like TOAD, PL/SQL Developer, etc.?

If this is only happening with SQL*PLUS, then maybe you have something
in your login.sql/glogin.sql that calls the procedure SAMS.SAMS_USER_LIB.

HTH
Holger

well I know that I dont have any ON STARTUP or ON LOGON triggers from
this:

SQL> select trigger_name, owner from dba_triggers where
triggering_event='LOGON';

no rows selected

SQL> select trigger_name, owner from dba_triggers where
triggering_event='STARTUP';

no rows selected

SQL>

It is OBVIOUS that the user I am loggin on as (SYS as SYSDBA ) does NOT
have the roles sams_base and sams_security granted.

My point is WHAT code is executing ( without it being an on logon
trigger ) that causes this error just by logging in? Does it have to be
one of those types of triggers?
Does 9i perform some kind of implicit check of object privs upon simply
connecting?

The errror is in code that is in another schema.

Now when I grant sams_base and sams_security to sys with admin option I
get an error saying role does not exist when I log on.

The error is a user defined exception string which leads me to believe
the name of the role is misspelled in the code.

I just want to know how the code is executing.

Also my login.sql script is not doing it I checked that out.

I dont get this error via TOAD.

g3000 wrote:
> well I know that I dont have any ON STARTUP or ON LOGON triggers from
> this:
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='LOGON';
>
> no rows selected
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='STARTUP';
>
> no rows selected
>
> SQL>
>
> It is OBVIOUS that the user I am loggin on as (SYS as SYSDBA ) does NOT
> have the roles sams_base and sams_security granted.
>

iirc, logging on as sysdba, using the sys account, does *not*
fire on logon triggers - else it would be possible to lock
everybody out

--
Regards,
Frank van Bortel

g3000 wrote:

> well I know that I dont have any ON STARTUP or ON LOGON triggers from
> this:
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='LOGON';
>
> no rows selected
>
> SQL> select trigger_name, owner from dba_triggers where
> triggering_event='STARTUP';
>
> no rows selected
>
> SQL>
>
> It is OBVIOUS that the user I am loggin on as (SYS as SYSDBA ) does NOT
> have the roles sams_base and sams_security granted.
>
> My point is WHAT code is executing ( without it being an on logon
> trigger ) that causes this error just by logging in? Does it have to be
> one of those types of triggers?
> Does 9i perform some kind of implicit check of object privs upon simply
> connecting?
>
> The errror is in code that is in another schema.
>
> Now when I grant sams_base and sams_security to sys with admin option I
> get an error saying role does not exist when I log on.
>
> The error is a user defined exception string which leads me to believe
> the name of the role is misspelled in the code.
>
> I just want to know how the code is executing.

So do I.

Perhaps it is time to start looking at that wealth of objects
found with the following query:

SELECT object_name
FROM dba_objects
WHERE object_name LIKE 'V%SQL%';

Then please let us know what you found.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)