Re: unable to see tables within cursors

You got it. Thanks.
I'm Canadian so when I say beer I mean it. Send me an address and Ill send a beer. Molson or Labatts?

"Jürgen Simonsen" <jsimonse_at_hamburg.dag.de> wrote in message news:3AAE33BF.371E45DF_at_hamburg.dag.de...
> Hallo Rocr,
>
> i think the reason is, that you have the privaleges to
> access the table in sql-plus, but in pl/sql only rights
> given directly to the user (not through roles) are used, so
> the same sql-statement can work in sql-plus but not as
> pl/sql-procedure.
>
>
> Rocr schrieb:
> >
> > Hi gang,
> >
> > I have a cursor that selects from an object (table or view). If I run
 a
> > the same select statement from SQLPLUS (select * from t1) I get results.
> > However if I place this same select statement in a cursor inside a
 stored
> > proc the stored proc will fail to compile.
> >
> > Here is the code:
> >
> > SQL> create or replace procedure test3 as
> > 2 CURSOR PROJ_CURSOR is
> > 3 SELECT
> > 4 count(*)
> > 5 FROM pbms.tb__projs;
> > 6 PROJ_NUMS PROJ_CURSOR%ROWTYPE ;
> > 7 begin
> > 8 for PROJ_NUMS in PROJ_CURSOR loop
> > 9 null;
> > 10 end loop;
> > 11 end;
> > 12 /
> >
> > Warning: Procedure created with compilation errors.
> >
> > SQL> show errors
> > Errors for PROCEDURE TEST3:
> > 2/9 PLS-00341: declaration of cursor 'PROJ_CURSOR' is incomplete or
> > malformed
> > 3/6 PL/SQL: SQL Statement ignored
> > 5/12 PLS-00201: identifier 'PBMS.TB__PROJS' must be declared
> > 6/15 PL/SQL: Item ignored
> >
> > SQL> SELECT
> > 2 count(*)
> > 3 FROM pbms.tb__projs;
> > 15286
> >
> > Notice that the select statement was cut and pasted into the SQL*Plus
 editor
> > and it ran fine. I did not change user and have privileges(obviously)
 to
> > select from this table(hence the result of 15286).
> >
> > Beer to whoever figures this one out.
> >
> > Cheers,
> > ROCR
>
> --
>
> Mit freundlichen Grüßen
> Jürgen Simonsen
>
> ==============================================================
> Softwareentwicklung | DAG, OWF-EDV-SWE | FON: 040-34915-394
> | Joh.-Brahms-Platz 1 | FAX: 040-2880951-394
> | D-20355 Hamburg | http://www.dag.de
> ==============================================================
Received on Tue Mar 13 2001 - 12:55:03 CST