Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    206

    Unanswered: DB2 Procedure Error

    Hello there,

    I am having a problem with a stored Procedure:

    Code:
    CREATE PROCEDURE ERBUSER.UDOX019 
    ( OUT OOSQLCODE INTEGER 
    , IN IIKONTONUMMER DECIMAL(18,0) 
    , IN IIBEZUGSDATUM DATE 
    , OUT OOKONTOSALDO DECIMAL(15,2) 
    , OUT OOVORZINS DECIMAL(15,2) 
    , OUT OOZINSSATZ DECIMAL(13,4) 
    , OUT OOSPERRE_18 CHAR(01) 
    , OUT OOSPERRE_20 CHAR(01) 
    , OUT OOSPERRE_26 CHAR(01) 
    , OUT OOADTM DATE 
    , OUT OOSPARFORM INTEGER 
    , OUT OOFAELLIGKEIT DATE 
    ) 
    RESULT SET 0 
    LANGUAGE SQL 
    READS SQL DATA 
    
    P1:BEGIN 
    DECLARE SQLCODE INTEGER DEFAULT 0; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET OOSQLCODE = SQLCODE;
    
    SELECT Account / 100
    From Myschema.MYTABLE
    WHERE ACCOUNT = IIACCOUNT
    ;
    
    SET OOSQLCODE = SQLCODE;
    
    END P1;

  2. #2
    Join Date
    Jan 2010
    Posts
    206
    The error on this is:

    Code:
    An unexpected token "END-OF-STATEMENT" was found following "DE INTEGER DEFAULT 0".  Expected tokens may include:  "<psm_semicolon>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65 
    
    An unexpected token "<space>" was found following "EXIT".  Expected tokens may include:  "HANDLER".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65 
    
    "IIKONTONUMMER" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.61.65 
    
    "OOSQLCODE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.61.65 
    
    An unexpected token "END-OF-STATEMENT" was found following "END P1".  Expected tokens may include:  "JOIN <joined_table>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65

    Does anyone have an idea for this? Please help.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes. The problem is your statement delimiter. You are trying to use the semicolon, but that is used for the compound statements inside. Try this:

    Code:
    CREATE PROCEDURE ERBUSER.UDOX019 
    ( OUT OOSQLCODE INTEGER 
    , IN IIKONTONUMMER DECIMAL(18,0) 
    , IN IIBEZUGSDATUM DATE 
    , OUT OOKONTOSALDO DECIMAL(15,2) 
    , OUT OOVORZINS DECIMAL(15,2) 
    , OUT OOZINSSATZ DECIMAL(13,4) 
    , OUT OOSPERRE_18 CHAR(01) 
    , OUT OOSPERRE_20 CHAR(01) 
    , OUT OOSPERRE_26 CHAR(01) 
    , OUT OOADTM DATE 
    , OUT OOSPARFORM INTEGER 
    , OUT OOFAELLIGKEIT DATE 
    ) 
    RESULT SET 0 
    LANGUAGE SQL 
    READS SQL DATA 
    
    P1:BEGIN 
    DECLARE SQLCODE INTEGER DEFAULT 0; 
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET OOSQLCODE = SQLCODE;
    
    SELECT Account / 100
    From Myschema.MYTABLE
    WHERE ACCOUNT = IIACCOUNT
    ;
    
    SET OOSQLCODE = SQLCODE;
    
    END P1 @
    The use the @ as the statement delimiter.

    Andy

Posting Permissions

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