2

I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema.

The package has a procedure that uses a cursor. When I create the package I get compilation errors:

Body 509 30 PL/SQL: ORA-00904: : invalid identifier

The error points to a table that the select of the Cursor is using and that it is located in the other Schema.

If I run the query directly in TOAD it works perfectly, I am new in Oracle and I don't understand why I get this error. Why am I getting this error?

  • It's little hard determining the problem based on this information. Perhaps you could post your code here ? – druzin Jun 17 '14 at 20:28
  • 3
    you must have granted select permission on this table to the dschema thaat owns the package. It is important that this permissions are granted directly to the package owner and not indirect using a role. Using a role is sufficient to select the table directly by Toad but it is not sufficient if you want to select the table in a package. – miracle173 Jun 17 '14 at 20:35
  • @miracle173 I didn't know that, I will try it out and let you know. Thanks! – Dzyann Jun 17 '14 at 20:54
  • @miracle173 this seems to trip all new DBAs up. Remember crying over it myself 15 years ago :) – Philᵀᴹ Jun 17 '14 at 23:27
  • @miracle173 thanks, that was the problem. I was confused also because I wasn't sure where to grant the access rights. If you post your comment as answer I will accept it. Thanks again! – Dzyann Jun 18 '14 at 13:42
4

From section "6 Coding PL/SQL Subprograms and Packages" in the "Oracle Database Advanced Application Developer's Guide, 11g Release 2 (11.2), E41502-05"

To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
- The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
- The owner cannot obtain required privileges through roles.

You must have granted select permission on this table to the schema that owns the package. It is important that this permissions are granted directly to the package owner and not indirect using a role. Using a role is sufficient to select the table directly by Toad but it is not sufficient if you want to select the table in a package.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.