Functions and ref cursors

From: Mark <mark.harris.nospam_at_ukonline.co.uk.nospam>
Date: Fri, 13 Feb 2004 11:11:52 -0000
Message-ID: <402cb156$0$29792$afc38c87@news.easynet.co.uk>

Version 8.1.7

Hello,

Can you call functions using the "OPEN x FOR" syntax with REF CURSORs?

The scenario:

I have a function, funcA in a package in an Oracle database which returns a REF CURSOR. I have a function, funcB in a package in a different Oracle database which does the same.

funcB encapsulates the same functionality as funcA, but rather than copying funcA's code into funcB (and to avoid the re-coding of funcB if funcA changes), can I do something like this? (It's complicated by the use of a parameter to the function & the link across to the remote d/b)

CREATE OR REPLACE PACKAGE myPackageB
AS

    TYPE myRefCursor IS REF CURSOR;

    FUNCTION funcB(myParam IN VARCHAR) RETURN myRefCursor;

END myPackageB;

CREATE OR REPLACE PACKAGE BODY myPackageB AS

    FUNCTION funcB(myParam IN VARCHAR) RETURN myRefCursor     AS

        rc myRefCursor;
    BEGIN

        OPEN rc FOR
            'userA.myPackageA.funcA_at_dbLinkA(paramA => :b1)'
            USING IN myParam
        ;

        RETURN rc;

    END funcB;

END myPackageB;

Thanks for any thoughts.

Mark Received on Fri Feb 13 2004 - 05:11:52 CST