Re: Sub-query in Oracle cursor

Thanks for your response. Sorry for the missed out info in my orginal posting. I'm using Oracle Release 8.1.6.3.0 on Unix. Looks like it's time for an upgrade. I have also found some postings where it is suggested to pass the SQL as an string literal when opening the cursor, I'll give that a whizz as well.

Many thanks

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3d53a631$0$227$ed9e5944_at_reading.news.pipex.net>...
> I suspect that it is version related. You don't give the version but up
> until quite recently (9i release 1?) the sql engine and the pl/sql engine
> were out of sync. The solution would either be to upgrade or to use dynamic
> sql.
>
> for the record this is what I get on 9i release 2.
>
> SQL> create table table1(column1 varchar2(20), column2 number);
>
> Table created.
>
> SQL> insert into table1 values('hello world!',1);
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> CREATE OR REPLACE PROCEDURE PRC_MyTest AS
> 2
> 3 CURSOR MyCursor IS
> 4 SELECT
> 5 (SELECT COUNT(*) FROM Table1) MyCount ,
> 6 Column1,
> 7 Column2
> 8 FROM Table1;
> 9
> 10 BEGIN
> 11 return;
> 12 END;
> 13 /
>
> Procedure created.
>
> SQL>
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Ola" <ola_aminu_at_hotmail.com> wrote in message
> news:176ea8c0.0208090301.3633d1ba_at_posting.google.com...
> > I'm trying to create a cursor with a subquery and this seems to upset
> > Oracle...
> > The following snippet shows what I'm trying to do.
> >
> > CREATE OR REPLACE PROCEDURE PRC_MyTest AS
> >
> > CURSOR MyCursor IS
> > SELECT
> > (SELECT COUNT(*) FROM Table1) MyCount ,
> > Column1,
> > Column2
> > FROM Table1;
> >
> > BEGIN
> > return;
> > END;
> >
> > The procedure compiles when I take out the line "(SELECT COUNT(*) FROM
> > MyTable) MyCount ,"
> >
> > Executing the SQL below, in isolation from the procedure also works
> > fine from the command line
> >
> > SELECT (SELECT COUNT(*) FROM Table1) MyCount,
> > Column1,
> > Column2
> > FROM Table1;
> >
> > Any ideas of what the problem is and any solutions will save my sanity
> > and hence will be most appreciated.
> >
> > Thanks
Received on Mon Aug 12 2002 - 04:02:37 CDT