Re: Functions and ref cursors

Mark wrote:

> 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

Not as far as I know. And if you could ... the affect on performance would be rather negative. Also consider what would happen if the dblink went down? You are creating a very weak link.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)