Path: news.easynews.com!easynews!newsfeed.frii.com!newsfeed.sovam.com!news2!not-for-mail From: "Vladimir M. Zakharychev" Newsgroups: comp.databases.oracle.server Subject: Re: Subquery not valid in a cursor? Date: Thu, 4 Apr 2002 14:11:19 +0400 Organization: N-Networks Lines: 105 Message-ID: References: <8hQq8.43033$Be7.2088119@weber.videotron.net> NNTP-Posting-Host: 212.233.81.44 Mime-Version: 1.0 Content-Type: text/plain; charset="windows-1251" Content-Transfer-Encoding: 8bit X-Trace: babylon.agtel.net 1017914992 20045 212.233.81.44 (4 Apr 2002 10:09:52 GMT) X-Complaints-To: usenet@babylon.agtel.net NNTP-Posting-Date: 4 Apr 2002 10:09:52 GMT X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Xref: easynews comp.databases.oracle.server:142005 X-Received-Date: Thu, 04 Apr 2002 03:09:17 MST (news.easynews.com) 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 ''; LOOP FETCH Open_Order_Cur INTO ; 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" wrote in message news:8hQq8.43033$Be7.2088119@weber.videotron.net... > I forgot: > This is Oracle 816 > > "sylvain tremblay" a écrit dans le message de news: > t0Qq8.42841$Be7.2073405@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'; > > > > > > > >