Go Directly To
Oracle Technology Network Community
My Oracle Support Community
OPN Cloud Connection
Oracle Employee Community
Oracle User Group Community
OTN Speaker Bureau
Get Started Guide
Join the world’s largest interactive community dedicated to Oracle technologies.
Learn from thousands of community experts
Get answers to your technical questions
Share your knowledge with peers
Please enter a title.
You can not post a blank message. Please type your message and try again.
SQL and PL/SQL
Database Application Development
SQL and PL/SQL
This discussion is archived
on Jan 18, 2013 5:19 PM by rp0428
Type Table reference lost on Upgrading from 10g Express to 11g Express
Jan 18, 2013 11:19 AM
I have a problem regarding Type Table Referencing. I have a Type table in one schema and this table is referenced in a Stored procedure in another schema. This was working in 10g Express database.
On upgrading the 10g Express to 11g Express the reference by property of the Type table lost the reference of the Stored procedure of the other schema. The Stored Pocedure is now showing the error
PLS-00201: identifier "TEMP.TBL" must be declared
The "TEMP.TBL" is declared in another schema.
Please suggest as to which privilege should be given so that "TEMP.TBL" which is created in one schema can be used in another schema.
Thanks & Regards
I have the same question
Show 0 Likes
This content has been marked as final.
Show 1 reply
Re: Type Table reference lost on Upgrading from 10g Express to 11g Express
Jan 18, 2013 5:19 PM
in response to
Welcome to the forum!
Your question isn't clear. Provide the specifics about your types, tables and procedure.
What is a 'Type table'? How is that table referenced?
One possibility is that there is a privilege missing.
In stored procedures priviliges to objects (e.g. to tables) need to be granted directly to the user and not thru a role.
So if the table is in the SCOTT schema and the user that owns the procedure is HR then HR needs to be granted privileges to the table directly
GRANT SELECT ON SCOTT.EMP TO HR;