PL-SQL Privilege Problem?

From: Dave Smith <james.strickland_at_dfas.mil>
Date: 5 Apr 2002 13:32:12 -0800
Message-ID: <8d36c9e1.0204051332.63234a15@posting.google.com>

I'm trying to use a view name in the from clause of a select statement of a cursor definition in a stored procedure and it's not working. I can do it in an anonymous block just fine but when I try to compile it to a stored procedure I get the dreaded "PLS-00201: identifier 'schema_owner.view1' must be declared" message. I'm using the same Oracle account when doing either the anonymous block or compile attempt. The view has a public synonym and select privilege granted to a role that my account is part of.

Here is the anonymous block that works fine...

set serveroutput on
declare

   cursor lcur_test is

      select col1, col2, col3 from view1;

   lrec_test lcur_test%ROWTYPE;

begin

   DBMS_OUTPUT.ENABLE(10000);
   open lcur_test;

   LOOP

      FETCH lcur_test INTO lrec_test;
      EXIT WHEN lcur_test%NOTFOUND;
      dbms_output.put_line('col1: ' || lrec_test.col1 || ' col2: ' ||

lrec_test.col2);

   END LOOP;    close lcur_test;
end;
/

Here is the procedure that won't compile... create or replace procedure my_test as

   cursor lcur_test is

      select col1, col2, col3 from view1;

   lrec_test lcur_test%ROWTYPE;

begin

   DBMS_OUTPUT.ENABLE(10000);
   open lcur_test;

   LOOP

      FETCH lcur_test INTO lrec_test;
      EXIT WHEN lcur_test%NOTFOUND;
      dbms_output.put_line('col1: ' || lrec_test.col1 || ' col2: ' ||

lrec_test.col2);

   END LOOP;    close lcur_test;
end my_test;

Now why would the anonymous block run fine but the procedure fail to compile? Any ideas? We are using Oracle 8.1.6.2.0. I'd sure appreciate some help on this!

Dave Received on Fri Apr 05 2002 - 15:32:12 CST