PL/SQL: could not find program unit being called, in LBACSYS.LBAC_EVENTS

I have an Oracle 9i database
installed on an SuSE 7.2 Linux system.
It's mostly brand new.
I've copied most of the schema I'm working with from another database/site of ours, because we want to put a new face on an existing model.

While I was porting the schema (side note, does anybody know of a single tool that actually does this correctly????????), I ran into a missing requirement: in one of my procedures, I use DBMS_SQL to dynamically evaluate an SQL statement. When compiling this procedure, I got "package not found" errors.  (Yes, I've since learned about "EXECUTE IMMEDIATE" in trying to find a solution to my problem...)

So, I went over to the database machine, su'd to oracle, and located the dbmssql.sql script. I executed the following commands:
sqlplus /nolog
connect sys/*******@database as sysdba
@dbmssql

From that moment, I've been unable to update any complex items on my database. I keep seeing this error block:

ORA-04045: errors during recompilation/revalidation of STATS.TRG_LOG_SITEINFO
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "LBACSYS.LBAC_EVENTS", line 440
ORA-06512: at line 2
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "LBACSYS.LBAC_EVENTS", line 440
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute)


IN attempting to make this error go away, I've even gone so far as to locate the definition of LBACSYS.LBAC_EVENTS in the original installation media, and manually recompile:
connect / as sysdba
@prvtlbac.plb

But it seems to be self-referential, and when one part of the file is evaluated, it nukes the state of the rest of it... <sigh>

HELP!!!
LVL 1
lordvorpAsked:
Who is Participating?
 
ramkbCommented:

Hi,

Don't try to recompile SYS objects manually (it will drive you crazy).  To compile all invalid objects, use utlrp.sql
This file is located under $ORACLE_HOME/rdbms/admin

Login as sqlplus internal and run
SQL> @?/rdbms/admin/utlrp.sql

SQL> SELECT COUNT(*) FROM ALL_OBJECTS WHERE STATUS = 'INVALID';

If still there are any invalid objects
SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS WHERE STATUS = 'INVALID'

Pls. let us know if the invalidation gets resolved.

Cheers,
Ramesh
0
 
lordvorpAuthor Commented:
De nada.  Same error block.

I even tried to rebuild prvtsql.plb:
@prvtsql.plb
That made my problems even worse.  Now, instead of the original error block on @utlrp, it complains about the missing package body. <sigh>

Tellya what, you can have the points.  I'm not getting this database back.  It's a good thing I hadn't actually put any data into it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.