Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: Can we OUT complete Cursor as a single parameter in DB2 Stored Procedures

    Hi

    I would like to know if we cn specify CURSOR in the OUT parementers list . I am fetching all my result in a CURSOR and i would like to return that CURSOR as an OUT , instead of result set concept

    In oracle we have something like this , Do we have similary for DB2 to return whole CURSOR as a single OUT parameter

    Code:
    CREATE OR REPLACE PROCEDURE PR_RS_GET_ACC_DETAILS (
        PA_IN_ACCOUNTID           IN       NUMBER,
        PA_IN_SITEID              IN       NUMBER,
        PA_OUT_RETCURSOR          OUT      RS_GENERAL.REFCURSOR
    )
    IS
    
    /BEGIN
        OPEN PA_OUT_RETCURSOR FOR
            SELECT ACC.ACCOUNT_ID AS ACCOUNT_ID,
                   ACC.STATUS AS ACC_STA,
                   DEV.D_ID AS D_ID,
                   DEV.STA AS D_STA,
                   DEV.CRT_DATE AS CRT_DATE,
                   DEV.NK_NA AS NK_NA,
                   AUT.AUT_ID AS AUT_ID,
                   AUT.STA AS AUT_STA,
                   AUT.EXP_DATE AS EXP_DATE,
                   AUT.LT_FRDIGIT_SN AS LTFR_DIGIT_SN,
                   AUT.PT_DATA AS PT_DATA,
                   AUT.SL_NUM
            FROM   RS_ANT_MST AC,
                   RS_DE_MST DEV,
                   RS_AUT_MST AUT
            WHERE  ACC.ACC_ID = DEV.ACC_ID
            AND    AUT.AUT_ID = DEV.AUT_ID
            AND    DEV.ACC_ID = PA_IN_ACCOUNTID
            AND    ACC.SITE_ID = PA_IN_SITEID;
    END PR_RS_GET
    Thanks in Anticipation .
    Neha

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I don't think you can do it, unfortunately ..

    The closest thing I can think of is the ARRAY (if you are on 9.5) or Global Temp Table

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your procedure is so simple that you can convert it to a table UDF.

  4. #4
    Join Date
    Dec 2007
    Posts
    49
    Can you please let me know if you have any sample for using arrays in OUT parameter. I have done with the concept of global temporary table

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Example from the manuals

    Code:
    create type intArray as integer array[100] % 
    create type stringArray as varchar(10) array[100] %  
    
    create table persons (id integer, name varchar(10)) % 
    insert into persons values(2, 'Tom') % 
    insert into persons values(4, 'Jill') % 
    insert into persons values(1, 'Joe') % 
    insert into persons values(3, 'Mary') %  
    
    create procedure processPersons(out witho stringArray) 
    begin 
    declare ids intArray; 
    declare names stringArray;  
    
    set ids = ARRAY[5,6,7]; 
    set names = ARRAY['Bob', 'Ann', 'Sue'];  
    
    insert into persons(id, name) 
    (select T.i, T.n from UNNEST(ids, names) as T(i, n));  
    
    set witho = (select array_agg(name order by id) 
    from persons 
    where name like '%o%'); 
    end %
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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