Re: Stored procedure selecting from another scheme

On 29 Sep 2005 07:30:40 -0700, "reverland" <reverland_at_gmail.com> wrote:

>When I try to run this stored procedure I get this error
>
>PLS-00201: identifier schema2.table3 must be declared
>
>
>I have looked everywhere and can't find the answer to this. I come from
>a T/SQL background so I am at a loss as to why this doesn't work.
>
>CREATE OR REPLACE procedure schema1.select_ssstudent
>IS
>begin
> select a.*,
> b.*,
> c.sitename
> from schema1.table1 a,
> schema1.table2 b,
> schema2.table3 c
> where a.permnum=b.permnum
> and substr(c.siteid,2,3) = trim(a.schoolnum)
> order by a.schoolnum, UPPER(a.lastname), UPPER(a.firstname);
>end;

I'm not sure why you didn't find this, because this is a a FAQ, as has been answered at least a 1000 times by me alone. The answer is : roles are ignored in stored procedures, and you have privilege through a role. You need either a) to grant access to the schema1 user directly b) add authid current_user after the procedure name (assuming you use 8i or higher, you don't mention the version)

To avoid your next question:
in Oracle stored procedures don't return a cursor. You need to look up how to return a REF CURSOR from a stored procedure.

You are well advised you REALLY should UNLEARN anything you learned about T/SQL (and sqlserver), and start learning PL/SQL. Your code betrays you assume Oracle is sqlserver sold by a different vendor, and PL/SQL is another T/SQL implementation. You are wrong on both counts.
You definitely need to read at least the first 3 chapters of Oracle Expert One on One by Thomas Kyte, and the Oracle Concepts Manual. Otherwise you are going to create unscalable applications.

--
Sybrand Bakker, Senior Oracle DBA