Re: unable to see tables within cursors

"Rocr" <rolland.cright_at_pwgsc.gc.ca> wrote in message news:98la0n$2ra1_at_shark.ncr.pwgsc.gc.ca...
> 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
>
>

From my answer to someone else with a similar question , *less than an hour ago*

> This is a FAQ (at least once a week).
> Roles are not observed during compilation of stored procedures
> You have two solutions:
> define your procedures with invoker rights (Oracle 8i and higher)
> grant *direct* access to your tables etc, instead of by means of a role.
> Usually when you make sure everything is being handled by the owner of the
> table, you don't need this at all.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Mar 13 2001 - 08:54:38 CST