Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: return multiple variables from function via DBLINK

    hey,

    I've created a function for use by another database through a dblink.
    I created the function to return a ref cursor but
    have since found out that this is not allowed so i'm looking for
    alternatives?

    Assuming I want to keep the function on our database (for maintainability reasons) what other options do i have?

    Two suggestions i've got are 1) to return a ',' delimited string
    or to have a number of OUT parameters. Is there any reason
    why either of these would not work or would be preferable to the other?

    Thanks for reading, any help much appreciated.
    BTW i'm new to PL/SQL so feel free to dumb it down.

    cheers.

    Code:
    CREATE OR REPLACE FUNCTION "GET_SCHEME_DETAILS" 
    (
        IN_BRANCH                     IN VARCHAR2,
        IN_AGENCY                     IN VARCHAR2,
        IN_PRODUCT_SUFFIX              IN VARCHAR2,
        IN_TERM                    IN INTEGER,
        IN_LOAN_START_DATE           IN DATE
    )
    RETURN sys_refcursor AS TYPE RESULTSET IS REF CURSOR;

  2. #2
    Join Date
    Nov 2009
    Posts
    2
    some additional info

    they are currently calling the function in the following way:

    Code:
    select GET_SCHEME_DETAILS@PPPRO ( '864' , '500086' , 'M7' , 10 , TO_DATE ( '01/02/2010' , 'DD/MM/YYYY' ) ) FROM DUAL ;

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If you have out parameters, you have to convert to a procedure.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •