Re: subqueries in cursors?

From: Gerhard Moeller <Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.de>
Date: 1998/01/09
Message-ID: <694p85$hnv@news.Informatik.Uni-Oldenburg.DE>#1/1

Hi,

thanks for your reply, but somehow it does not work for me...

tkyte_at_us.oracle.com (Thomas Kyte) wrote:

>On Thu, 08 Jan 1998 17:17:29 GMT, Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.de
>(Gerhard Moeller) wrote:
>>I just experienced that there are no subqueries allowed in PL/SQL
>>cursors. A Cursor like
>>
>> CURSOR foo IS
>> SELECT bar FROM (SELECT ...) ...
>>
>>seems not allowed in PL/SQL 2.3.
 

>I just ran:
>
>declare
> cursor foo is
> select ename from ( select * from emp );
>begin
> for x in foo loop
> dbms_output.put_line( x.ename );
> end loop;
>end;
>/

Well... I just tried to run:

(i) explicit cursor check

[...]

    CURSOR result_cur ( conid_title_in    NUMBER, 
                        conid_author_in   NUMBER,
                        conid_keys_in     NUMBER,
                        conid_headings_in NUMBER )
    IS
        SELECT a.sum_score, b.seq, b.title
          FROM (SELECT textkey, SUM( score ) sum_score
                   FROM ctx_results
                  WHERE conid = conid_title_in  OR
                        conid = conid_author_in OR
                        conid = conid_keys_in   OR
                        conid = conid_headings_in
                  GROUP BY textkey) a,
               fullsoifs b
         WHERE a.textkey = b.seq
         ORDER BY a.sum_score;

[...]
/

Error in line 1:

ORA-06550: line 21, column 16:
PLS-00320: Type declaration of expression incomplete or error-prone.
ORA-06550: line 20, Spalte 9:

PL/SQL: SQL Statement ignored

[...complaints about cursor not valid...]

(ii) implicit cursor check
DECLARE
   id NUMBER;
   id1 NUMBER;
BEGIN
   id := 1; id1 := 2;
   INSERT INTO results

       SELECT 2102, a.sum_score, b.seq, b.title
         FROM ( SELECT textkey, SUM( score ) sum_score
                  FROM ctx_results
                 WHERE conid = id or conid = id1 
                 GROUP BY textkey) a,
              fullsoifs b
        WHERE a.textkey = b.seq;

END;
   /

Error in line 1:

ORA-06550: line 8, column 15:
PLS-00320: Type declaration of expression incomplete or error-prone.
ORA-06550: line 6, Spalte 4:

PL/SQL: SQL Statement ignored

(As always, I retranslated the error messages from German back to English, so they might not be literally what you would get.)

BOTH Cursors work when issued direktly as SQL-statements from SQL*Plus. I checked three times, never found an error. Maybe I should quit programming, switching back to theory. ;-)

Then I had a look at my documentation, which is unfortunatelly still PL/SQL 2.2 -- where you will find, as Thomas said, that subqueries are not possible. (It is not stated explicitly, but the syntax makes it quite clear.) I was not able to check the documentation for PL/SQL 2.3, as I do not have it handy right now. (I only have 2.2 and 8.0 online)

Oh, yes. My system:

Oracle7 Server Release 7.3.3.0.0 with the 64-bit option - Production Release
With the distributed option
PL/SQL Release 2.3.3.0.0 - Production

on a DEC alpha with OSF.

Any help is more than appreciated, as the solution I now use (a loop with calls to a second cursor that always have to be reopened and closed) is not exacly what I'd call maintainable and elegant...

        Thanks alot, Gerhard.

-- 
Dipl. Inform. Gerhard Möller -- Gerhard.Moeller_at_OFFIS.Uni-Oldenburg.DE
                                      ICQ PIN: 2804938
OFFIS                  | | | | | |    Tel.:    0441/9722-122
Escherweg 2            | | | | | |    Sekr.:   0441/9722-113 oder -101
D-26121 Oldenburg      |O|F|F|I|S|    Fax:     0441/9722-102

Received on Fri Jan 09 1998 - 00:00:00 CST