| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Baffled by basics (can select from sqlplus but procedure pukes)
A copy of this was sent to James Knowles <kaa_at_xmission.com>
(if that email address didn't require changing)
On Wed, 16 Jun 1999 19:12:10 +0000, you wrote:
>
>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?
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant <priv> on <whatever> to <OWNER>;
>Thanks!
>
>James
>
>--
>It's coming... http://www.countdown9199.com
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |