1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Stored Procedures Variable Arguments

Discussion in 'SQL PL/SQL' started by saracooper, Feb 26, 2010.

  1. saracooper

    saracooper Active Member

    Messages:
    14
    Likes Received:
    0
    Trophy Points:
    80
    I am working on a project of migration where I need to have Stored Procedures for DB access routines cursor - Declare, Open, Fetch etc.
    I need to use Dynamic SQL, so I have written the routines as External Procedures in ProC and they work fine.
    The Select statement for Cursor in Declare routine is different every time & so the Fetch receives variable arguments. In External Procedures it is done by using va_list, va_arg .....
    All the Calling programs in C/Fortran call the Stored Procedures.

    I have 2 problems -
    1. how to pass the variable arguments to the Stored Procedure Fetch. Is there any way to declare parameters for Stored Procedure that are variable
    2. can i have out parameters that have more than one value or out parameters that take address to an array because Fetch needs to pass the address of the first element of the arrays

    Calling Program -
    CALL DECLARE(VCURS, SELECT_STMT)
    CALL OPEN(VCURS)
    CALL FETCH(VCURS, 2, CVAL1, CVAL2)

    where VCURS is the cursor name
    SELECt_STMT is the Select statement
    CVAL1 is a float array
    CVAL2 is an integer array

    Declare Stored Procedure -
    create or replace procedure "DECLARE"
    (cursorname IN OUT VARCHAR2,
    selectstmt IN VARCHAR2)
    is
    EXTERNAL LIBRARY externProcedures
    NAME "declarerout"
    LANGUAGE C
    PARAMETERS (
    cursorName char,
    selectstmt char );


    Declare External Procedure -
    void declarerout(char* cursorName, char* selectStr)
    {
    ......... }


    Fetch Stored Procedure -
    create or replace procedure "FETCH"
    ( ??????? )
    is
    EXTERNAL LIBRARY externProcedures
    NAME "fetchrout"
    LANGUAGE C;


    Fetch External Procedure -
    char fetchrout(char *cursorName, int numArgs, ...)
    {
    ............}


    Any help or suggestion is highly appreciated. Thank you in advance.
     
  2. sravanthi

    sravanthi Active Member

    Messages:
    6
    Likes Received:
    3
    Trophy Points:
    65
    Hi,

    I think may be by declare table type variable for that cursor and that can be out through FETCH procedure.

    Regards,
    Sravanthi :D
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You might also consider a refcursor passed into the procedure which can hold results from any table you'd want. Pass the refcursor to the calling procedure and fetch the data.