3

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?

Dzyann
  • 355
  • 3
  • 10
  • 26

1 Answers1

5

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.

miracle173
  • 7,797
  • 28
  • 42