Re: Union All & REF CURSOR problem

I think it is this part that gives you the problem:

(F.TBID = TBID) AND (F.OID = OID) Your procedure parmeter names are same as the table column name. Change the parameter names or fully qualify them with the function name like:

(F.TBID = mc_QryFldOf.TBID) AND (F.OID = mc_QryFldOf.OID)

The function as you currently coded would effectively give you the rows from all 3 tables with FD.ID = F.FDID as the only limiting condition.

• Jusung Yang

"news" <jbfidelia_at_multicorpora.ca> wrote in message news:<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 - 06:12:35 CET