Stored procedure selecting from another scheme

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

reverland - 29 Sep 2005 15:30 GMT

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;

Reply to this Message

Sybrand Bakker - 29 Sep 2005 16:59 GMT

>When I try to run this stored procedure I get this error
>
[quoted text clipped - 16 lines]
> 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

Reply to this Message

Mark D Powell - 29 Sep 2005 21:12 GMT

reverland, if you are a developer rather than a DBA you might want to
start with the Application Developers Guide - Fundamentals instead of
the Concepts manual since it repeats much of the Concept manual
information of most interest to a developer: datatypes, managing
objects, using packages and procedures, etc ...

But you will need to read the topics in the concepts manual related to
the Oracle read consistency model, transaction management, and undo
(rollback) management. You need to understand this in order to be able
to design processes that work correctly because in Oracle writers do no
block readers. Failure to understand what this really means to your
application can lead to unexpected results.

HTH -- Mark D Powell --

Reply to this Message