PLS-00201 DBMS_SQL must be declared

[Sambo8]

Hi there,

I'm running Oracle 10.2.0.1.0 and the following package created under system DBMS_REPCAT_AUTH is giving me a status of invalid. when I try to recompile it says identifier DBMS_SQL must be declared. Does anyone know what this package does?

Many thanks,


Sam

 

[SantaMufasa]

Sam,

Did you personally install the Oracle 10.2.0.1.0 instance that you are using? If so, do you remember running the scripts that create the data dictionary (such as catalog.sql, catproc.sql, catexp.sql, et cetera)? DBMS_SQL (if I am not mistaken) gets created while running catproc.sql.

If you are receiving an error relating to DBMS_SQL, then you (or your DBA) should (re-)run all of those scripts.

After having run those scripts, re-attempt to run the code that threw the error and let us know what happens.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Sambo8]

Hi Dave,

Fantastic, running Catproc.sql seems to have done the trick. I didn't create the instance and am a new DBA.

Should I also run catexp? Many thanks

Sam

 

[SantaMufasa]

There is never any harm in re-running any of the data dictionary scripts. If running catproc.sql solved your original problem, then I would be very suspicious about whether the other scripts have been run.

So, yes, I would highly recommend your re-running all of the data-dictionary-related scripts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]