I'm just waiting for him to mention a beer in the list and not a lager
--
Niall Litchfield
Oracle DBA
Audit Commission UK
and hairy real ale geek
"Howard J. Rogers" <howardjr_at_www.com> wrote in message
news:3aaf70c7_at_news.iprimus.com.au...
>
> "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 - 10:24:11 CST
