Re: Subquery not valid in a cursor?

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 4 Apr 2002 14:11:19 +0400
Message-ID: <a8h8pg$jid$1@babylon.agtel.net>

Seems that this syntax is not supported in PL/SQL (PL/SQL is always at least one step behind the SQL engine when it comes to SQL syntax). Similar syntax works perfectly well in plain SQL in 8.1.7:

SQL> select 1, 2, 3, (select sysdate from dual) d from dual /

         1 2 3 D
---------- ---------- ---------- ---------

         1 2 3 04-APR-02 Using REF CURSOR you can workaround this problem (since SQL engine will be processing the statement in this case):

...
TYPE myCursor IS REF CURSOR;
Open_Order_Cur myCursor;
...
BEGIN

• here's the main magic - PL/SQL engine will call SQL engine to parse the
• statement and create the cursor, and it will work! OPEN Open_Order_Cur FOR '<your select here>'; LOOP FETCH Open_Order_Cur INTO <wherever>; EXIT WHEN Open_Order_Cur%NOTFOUND; .... END LOOP; END;

Here's a demo with my simple example query:

create or replace procedure subqtest
as
 type cur is ref cursor;
 c1 cur;

 a number;
 b number;
 c number;

 d date;
begin
 dbms_output.enable(10000);
 open c1 for 'select 1 a, 2 b, 3 c, (select sysdate from dual) d from dual';  loop
  fetch c1 into a, b, c, d;
  exit when c1%notfound;
  dbms_output.put_line('a = '||a||' b = '||b||' c = '||c||' d = '||d);  end loop;
 close c1;
end;
/

Procedure created.

SQL> set serveroutput on
SQL> exec subqtest
a = 1 b = 2 c = 3 d = 04-APR-02

PL/SQL procedure successfully completed.

SQL>

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"sylvain tremblay" <syltrem_at_videotron.ca> wrote in message
news:8hQq8.43033$Be7.2088119_at_weber.videotron.net...


> I forgot:

> This is Oracle 816

>


> "sylvain tremblay" <syltrem_at_videotron.ca> a écrit dans le message de news:

> t0Qq8.42841$Be7.2073405_at_weber.videotron.net...

> > Hi

> >

> > From the doc, subqueries are valid inside the FROM and WHERE clause

> > but can someone tell me if this is valid? I get an error at compile time.

> > If it`s not valid, then how should I do this kind of query in PL/SQL ?

> >

> > Thanks

> > Syltrem

> >

> >         Cursor  Open_Order_Cur

> >         is      Select  ORDER_NUMBER, SALES_ORDER_TYPE,

> >                         CUSTOMER_REFERENCE, ORDER_DATE, USERNAME,

> >                         WEIGHT, VOLUME, SOP_ORDER_TOTAL_CURRENCY,

> >                         STATUS_FLAG,

> >                         (Select Count(*)

> >                         From    FINANCE.SALES_ORDER_LINES L

> >                         Where   L.COMPANY_CODE = H.COMPANY_CODE

> >                           And   L.DIVISION = H.DIVISION

> >                           And   L.ORDER_NUMBER = H.ORDER_NUMBER)

> >                 From    FINANCE.SALES_ORDER_HEADERS H

> >                 Where   H.COMPANY_CODE = P_Company

> >                   And   H.DIVISION = P_Division

> >                   And   H.CUSTOMER_NUMBER = P_Customer_Id

> >                   And   H.STATUS_FLAG <> 'C';

> >

> >

> >

>
>

Received on Thu Apr 04 2002 - 04:11:19 CST