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?
Best Answer
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"
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.