DBMS_JOB errors: PLS-00201: identifier 'package.proc_name' must be declared

[Chucks_k]

Hi all,

I am new to writing packages and procedures. I have 2 boxes a live box and a test box. I have a job which runs a procedure which is part of a package via dbms_job and another which deletes rows from a certain table . Now i have exported the schema to the test box and set up the jobs but am finding these errors when it tries to execute the jobs:

ORA-12012: error on auto execute of job 22
ORA-06550: line 1, column 96:
PLS-00201: identifier 'package_name.proc_name' must be declared
ORA-06550: line 1, column 96:

PL/SQL: Statement ignored

ORA-12012: error on auto execute of job 7
ORA-06550: line 1, column 117:
PL/SQL: ORA-01031: insufficient privileges
ORA-06550: line 1, column 96:
PL/SQL: SQL Statement ignored

Can anyone shed some light on this?

Thanks in advance,
Chucks

[gandolf989]

It sounds like you need to grant execute on the package from one user to another. Grants must be explicit and not through a role.

[Chucks_k]

Hi,

Thats what i thought as well. Hence i gave execute persmission on (grant execute on package to sys) the package to sys. Then as sys i tried running the procedure as such:

exec package.proc

I still got the same error:

PLS-00201: identifier 'procedure.package' must be declared

Any ideas will be greatly appreciated.

[gandolf989]

Hi,

Thats what i thought as well. Hence i gave execute persmission on (grant execute on package to sys) the package to sys. Then as sys i tried running the procedure as such:

exec package.proc

I still got the same error:

PLS-00201: identifier 'procedure.package' must be declared

Any ideas will be greatly appreciated.

Are you passing in the wrong datatype for the parameter?
Are you familiar with overloading procedures in a package?

[RobKraft]

The package runs fine when I am logged in as Admin (the creator of the package), but it fails when I log in as USER. Even after I grant USER all permissions on the package it fails. I believe the user also needs permission to the procedure inside the package; but I don't see any options in the admin tool to grant such a permission.

[RobKraft]

Never mind. I needed to create a synonym for my package.

[romeoarchie]

Thanks for the Synonym suggestion. I had a Synonym already, but dropping it and creating again resolved the issue. Never realized that synonym could be an issue with this error message.

Never mind. I needed to create a synonym for my package.

[youngwolf]

I also had this error.
It actually turned out to be something simple. In Oracle, when you declare a package name with double quotes, it defaults to upper case. Object names are case sensitive.
So, CREATE OR REPLACE PACKAGE "DBO"."PkgName" would create
DBO.PKGNAME. However, depending on the editor, it may (in my case it did) display PkgName, but will register PKGNAME in the database.

This means a call to PkgName.procedure would raise PLS-00201: identifier PkgName must be declared.

The answer? Either use caps as a standard naming convention or don't use the double quotes. The quotes are only needed if there is a space in the object name.