Re: Getting PLS-00904 when trying to compile (even as sysdba)

In article <41625d17$0$10349$afc38c87_at_news.optusnet.com.au>, howardjr_at_dizwell.com says...
> Marc Eggenberger wrote:
>
> > Hi there.
> >
> > System is Oracle 8.1.7.0.0 on Windows2000 SP4 ADV Server with Oracle
> > Fail Safe
>
> Ever considered patching? 8.1.7 should be at least 8.1.7.3.

Oh yeah. Even tried to convince them upgrading every database to 9R2 (we have 8.0.0.6, 8.1.6, 8.1.7, 9R2) so that we only have one version to suppord. Even did a detailed plan how, when, who, how much ... nada ... and yes I'm getting out of here, and not only because I have to do the dba stuff although I'm not really the dba .... but I have to work to the end of the year here so this is the environment I have ....
>
> > I found several Packages in status invalid in the sys schema. I tried to
> > compile them but I'm getting errors. For example when trying to
> > recompile SYS.DBMS_AQADM I get an PLS-00904: insufficient privilege to
> > access object SYSTEM.AQ$_QUEUES
>
> It would help seeing the exact sequence of events. What you typed, what
> Oracle replied, and so on.

output from spool:

SQL> connect sys/xxx_at_database
Connected.
SQL> alter package sys.dbms_aqadm compile;

Warning: Package altered with compilation errors.

SQL> select * from dba_errors where owner = 'SYS' and name = 'DBMS_AQADM' order by sequence;

OWNER                          NAME                           TYPE              
------------------------------ ------------------------------ 
------------      
  SEQUENCE       LINE   POSITION                                                
---------- ---------- ----------                                                
TEXT                                                                            
------------------------------------------------------------------------
--------
SYS                            DBMS_AQADM                     PACKAGE 
BODY      
         1        625         17                                                
PLS-00904: insufficient privilege to access object SYSTEM.AQ$_QUEUES            
                                                                                
SYS                            DBMS_AQADM                     PACKAGE 
BODY      
         2        624          5                                                
PL/SQL: SQL Statement ignored                                                   

OWNER                          NAME                           TYPE              
------------------------------ ------------------------------ 
------------      
  SEQUENCE       LINE   POSITION                                                
---------- ---------- ----------                                                
TEXT                                                                            
------------------------------------------------------------------------
--------
                                                                                

SQL> spool off

> > I even tried to connect as sysdba.
>
> Well, what does that mean? You *tried* to connect as sysdba but failed
> miserably? Or that you tried and actually succeeded. Again, a spool of what
> you actually typed in (presuming you are in SQL Plus at the time) would
> help.

My wording was unclear, I apologize. I mean I even tried it when connected as sysdba:

SQL> connect sys/xxx as sysdba;
Connected.
SQL> alter package sys.dbms_aqadm compile;

Warning: Package altered with compilation errors.

SQL> select * from dba_errors where owner = 'SYS' and name = 'DBMS_AQADM' order by sequence;

OWNER                          NAME                           TYPE              
------------------------------ ------------------------------ 
------------      
  SEQUENCE       LINE   POSITION                                                
---------- ---------- ----------                                                
TEXT                                                                            
------------------------------------------------------------------------
--------
SYS                            DBMS_AQADM                     PACKAGE 
BODY      
         1        625         17                                                
PLS-00904: insufficient privilege to access object SYSTEM.AQ$_QUEUES            
                                                                                
SYS                            DBMS_AQADM                     PACKAGE 
BODY      
         2        624          5                                                
PL/SQL: SQL Statement ignored                                                   

OWNER                          NAME                           TYPE              
------------------------------ ------------------------------ 
------------      
  SEQUENCE       LINE   POSITION                                                
---------- ---------- ----------                                                
TEXT                                                                            
------------------------------------------------------------------------
--------
                                                                                

SQL> spool off  

> And do use SQL Plus for this, because Enterprise Manager is not up to the
> job in 8i, IMHO.

Yes I did use sqlplus. Not a big fan of EM

> > No luck. But sys has access to this
> > table.
>
> One would certainly hope so. So one can only presume that you aren't
> actually logged on as SYS. Or that something else is going on...

That what I think too .. something is going on here ...

> > Anyone knows what I am doing wrong here?
>
> Show us what you're actually doing, and then we can (hopefully) spot what
> you're doing wrong. But the description you give here is a bit too vague.

Hope I was clear enough with my additional comments. Sorry for not posting them in the first place.

-- 
mfg
Marc Eggenberger