Union All & REF CURSOR problem
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
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