unable to see tables within cursors

From: Rocr <rolland.cright_at_pwgsc.gc.ca>
Date: Tue, 13 Mar 2001 09:14:50 -0500
Message-ID: <98la0n$2ra1@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 Received on Tue Mar 13 2001 - 08:14:50 CST