Re: unable to see tables within cursors

"Rocr" <rolland.cright_at_pwgsc.gc.ca> wrote in message news:98lqi3$2u12_at_shark.ncr.pwgsc.gc.ca...
> Thank you. That worked. What type of beer? Molsons or Labatts? Send me
> an address via my email and I'll ship you a good Canadian beer.

Isn't that an oxymoron?
HJR
>
> Thanks
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:tasd1fi4ld3e6a_at_beta-news.demon.nl...
> >
> > "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 Wed Mar 14 2001 - 07:24:28 CST