Re: unable to see tables within cursors

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
==============================================================