Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Unanswered: Create PROCEDURE using output "TABLE LIKE"

    I'm attempting to return a result set as an out parameter in a stored procedure using: CREATE PROCEDURE GetReconnectJobs ( OUT o_data TABLE LIKE GETRECONNECTJOBS_VIEW AS LOCATOR ) BEGIN SELECT fields1..n INTO o_data FROM cibjjo.GetReconnectJobs_VIEW; END; But am getting error: SQL0408N A value is not compatible with the data type of its assignment target. Target name is "O_DATA". Any ideas? Oracle has a ref_cursor, and I'm assuming this is the DB2 equivalent of that???

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Jim313 View Post
    I'm assuming this is the DB2 equivalent of that???
    Your assumption is incorrect.

    Example: Returning a REF CURSOR from a procedure (PL/SQL) - IBM DB2 9.7 for Linux, UNIX, and Windows

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    Is there a way to return a results set in DB2 v9.1? The link you provided is for pl/sql using a ref_cursor.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

  5. #5
    Join Date
    Feb 2012
    Posts
    7
    I have it compiling ok and will be testing it, but this seemed to work. Thank you!

  6. #6
    Join Date
    Feb 2012
    Posts
    7

    dml command with return cursor

    I can open a cursor and return the results from the SELECT, but I need to update a status flag before I retrieve the records and updated it again after the SELECT. Adding either update statement to the stored procedure causes it to not compile. I tried adding begin-end blocks around each of the Updates, but that didn't seem to matter. Any suggestions on how I can resolve this?

    CREATE PROCEDURE GetReconnectJobs ( )
    LANGUAGE SQL
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    OPTHINT ' '
    REOPT ONCE
    DYNAMIC RESULT SETS 1

    begin-- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE
    -- reserve records for select below...
    UPDATE T14TOPS.CI006v
    SET RP_SURV_FLPAL_FLG = 'X' -- in transit
    where RP_SURV_FLPAL_FLG = 'N';
    end;

    Re: BEGIN

    DECLARE c_ReonnRecs CURSOR WITH RETURN FOR
    SELECT
    CUST_NBR,
    PREM_NBR,
    FO_NBR,
    MTR_NES_ID,
    RECN_RCPT_CK_AMT,
    FO_INIT_CMT_TXT,
    ORDPEN_EFF_DAT,
    FO_TYPE_CDE,
    AS_CALL_NBR,
    FO_RSN_ISS_CDE,
    FO_ORG_DAT,
    FO_ORG_TIM,
    ORD_STAT_ID,
    CUST_NAM,
    CUST_PHN_NBR,
    HSE_NBR,
    ST_NAM,
    APT_ID,
    ZIP_CDE,
    MTR_CONSTA_EXIS_ID,
    XFMR_POLEPAD_NBR,
    MTR_LOC_CDE,
    AMI_RD_FLG -- AMI = 'Y'
    FROM GetReconnectJobs_VIEW
    where RP_SURV_FLPAL_FLG = 'X';

    end Re;

    begin -- ADDING THIS UPDATE STATEMENT BREAKS THE PROCEDURE AND CAUSES IT TO NOT COMPILE
    -- un-reserve records, and set status to In-process
    UPDATE T14TOPS.CI006v
    SET RP_SURV_FLPAL_FLG = 'I' -- In-process
    where RP_SURV_FLPAL_FLG = 'X';
    end;

    OPEN c_ReonnRecs;

    END
    ;
    Last edited by Jim313; 03-09-12 at 17:42.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Jim313 View Post
    Adding this breaks the proceedure.
    This isn't very informative. I guess we'll just wait for mindreaders to help diagnose the problem.

Tags for this Thread

Posting Permissions

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