Re: Schema References in PLSQL

Here is a chunk of code of mine. You need to make sure that you have select priv for the desired table/view. You might want to check out $ORACLE_HOME/rdbms/admin there is bunch of pl/sql code there to look and learn from.  

  procedure wco_cwq is
    NOT_EXIST_OR_NO_PRIV exception;
    pragma EXCEPTION_INIT(NOT_EXIST_OR_NO_PRIV, -20000);     CURSOR c1 IS select m.site,m.parameter,m.qualifier,

          m.result,m.units from rmp.conv_water_qual m
                 where m.site like 'S%';

regards
Todd F.                                                                                 

Scott Guthery (guthery_at_slb.com) wrote:
: In SQL one can refer to tables in other schemas using the . notation; e.g.
 

: SELECT * FROM systext.txsqe_name;
 

: where txsqe_name is a table in the systext schema.
 

: The same statement in PLSQL yields a "must be declared" error..
 

: Going by way of a remote database link is one way to work around this shortcoming
: but I wonder if there isn't a direct way to refer to tables in other schemas in the
: same database instance from PLSQL code.
 

: Comments? Advice?
 

: Thanks, Scott
: guthery_at_slb.com
Received on Sun Jul 16 1995 - 00:00:00 CEST