Re: Stored procedure selecting from another scheme
- From: Sybrand Bakker <postbus@xxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Sep 2005 17:59:54 +0200
On 29 Sep 2005 07:30:40 -0700, "reverland" <reverland@xxxxxxxxx>
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
.
- Follow-Ups:
- Re: Stored procedure selecting from another scheme
- From: Mark D Powell
- Re: Stored procedure selecting from another scheme
- References:
- Stored procedure selecting from another scheme
- From: reverland
- Stored procedure selecting from another scheme
- Prev by Date: long dynamic SQL statement (Pro*C)
- Next by Date: Re: CONNECT BY / START WITH query to count "families"
- Previous by thread: Stored procedure selecting from another scheme
- Next by thread: Re: Stored procedure selecting from another scheme
- Index(es):
Relevant Pages
|
