Re: Sub-query in Oracle cursor

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 9 Aug 2002 12:23:28 +0100
Message-ID: <3d53a631$0$227$ed9e5944@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 Fri Aug 09 2002 - 06:23:28 CDT