Baffled by basics (can select from sqlplus but procedure pukes)

I'm somewhat baffled by the behaviour that I'm seeing.

I have a script that creates tablespaces, users, tables, etc. from scratch. I also have a corresponding script that removes everything, leaving Oracle8 with nothing but the scott/tiger example tables.

When I create the tables, I do so using user names, for example:

CREATE TABLE user2.table1
etc.

I can then use sqlplus and log on as user1. I can SELECT from the user2 tables using
"SELECT <columns> FROM user2.table1 WHERE ..."

However, if I ever reference "user2.table1" within a procedure, function, or package (I've tried all of them) then I get an error thus:

PLS-00201: identifier 'USER2.TABLE1' must be declared

To me this is rather baffling as has been granted SELECT on user2.table1 and I can query any table successfully from sqlplus. It appears that within procedures, functions, and packages that they don't know how to find "user2.table" -- very strange to me. I can connect to the database using Delphi and fire off any number of selects on any userX.tableY combination.

I can create procedures/function/packages that use tables owned by user1.

What am I overlooking and/or not understanding?

Thanks!

James

--
It's coming... http://www.countdown9199.com Received on Wed Jun 16 1999 - 14:12:10 CDT