Re: PL-SQL Privilege Problem?

Dave Smith wrote:

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

Hi Dave

PL*SQL doesn't go with permissions granted by roles. Explicit permissions is needed. Grant select on view to procedure owner. /Svend Received on Sat Apr 06 2002 - 08:46:35 CST