DBMS_JOB errors: PLS-00201: identifier 'package.proc_name' must be declared
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

  1. #1
    Join Date
    Dec 2001
    Posts
    337

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

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    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.
    this space intentionally left blank

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    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.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    Quote Originally Posted by 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.
    Are you passing in the wrong datatype for the parameter?
    Are you familiar with overloading procedures in a package?
    this space intentionally left blank

  5. #5
    Join Date
    May 2007
    Posts
    2

    I have this same problem

    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.

  6. #6
    Join Date
    May 2007
    Posts
    2
    Never mind. I needed to create a synonym for my package.

  7. #7
    Join Date
    Nov 2007
    Location
    Chennai, India
    Posts
    1

    Thanks

    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.

    Quote Originally Posted by RobKraft
    Never mind. I needed to create a synonym for my package.

  8. #8
    Join Date
    Aug 2011
    Posts
    1

    another cause of this error

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width