Union All & REF CURSOR problem

From: news <jbfidelia_at_multicorpora.ca>
Date: Mon, 16 Dec 2002 16:39:24 -0700
Message-ID: <eIrL9.16819$C32.228099_at_weber.videotron.net>


Hi there,

  Using Oracle 8.1.7, I have a query of the form:

 SELECT
      F.ID, F.FDID, FD.NAME, FD.CLOBVALUE, FD.DBTYPE,  FROM
      MC.FLDA F, MC.FDEF FD
 WHERE
(F.TBID = 29) AND (F.OID = 3) AND (FD.ID = F.FDID)
 UNION ALL
 SELECT
      F.ID, F.FDID, FD.NAME, FD.CLOBVALUE, FD.DBTYPE,  FROM
      MC.FLDB F, MC.FDEF FD
 WHERE
(F.TBID = 29) AND (F.OID = 3) AND (FD.ID = F.FDID)
 UNION ALL
 SELECT
      F.ID, F.FDID, FD.NAME, FD.CLOBVALUE, FD.DBTYPE,  FROM
      MC.FLDC F, MC.FDEF FD
 WHERE
(F.TBID = 29) AND (F.OID = 3) AND (FD.ID = F.FDID)
The query works fine when I run it from SQLPlus worksheet. I want to make a stored function out of it, of the form:

 TYPE cFldOf is ref cursor;

 FUNCTION mc_QryFldOf(

      TBID IN INTEGER,
      OID IN INTEGER)

 RETURN cFieldOfOwner
 IS

      FCursor cFldOf;
 BEGIN
        OPEN FCursor FOR

        SELECT
            F.ID, F.FDID, FD.NAME, FD.DBTYPE,
        FROM
            MC.FLDA F, MC.FDEF FD
        WHERE
            (F.TBID = TBID) AND (F.OID = OID) AND (FD.ID = F.FDID)
        UNION ALL
        SELECT
            F.ID, F.FDID, FD.NAME, FD.DBTYPE,
            FROM
                MC.FLDB F, MC.FDEF FD
            WHERE
                (F.TBID = TBID) AND (F.OID = OID) AND (FD.ID = F.FDID)
        UNION ALL
        SELECT
            F.ID, F.FDID, FD.NAME, FD.DBTYPE,
        FROM
            MC.FLDC F, MC.FDEF FD
        WHERE
            (F.TBID = TBID) AND (F.OID = OID) AND (FD.ID = F.FDID);

  RETURN FCursor;
 END; END QRY; The problem is that when I run the following code the query from the stored function returned the first row correctly then enters what seemed to be an infinite loop with wrong result at each fetch.

 /* Invoke the stored object */
 RetVal := QRY.MC_QRYFIELDSOFOWNER(29,1);

 /* Inspect Output Parameter Values */
 IF RetVal%ISOPEN THEN

     LOOP
        FETCH RetVal INTO << variable list>>

        IF RetVal%NOTFOUND THEN
            EXIT;
        END IF;

        << process variable>>
     END LOOP;

  CLOSE RetVal;
 END IF; If somebody have any idea about what is going on, I would be glad to hear from him.

Thanks.

J.B. Fidelia. Received on Tue Dec 17 2002 - 00:39:24 CET

Original text of this message