Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: can not create exception handler

    I tried to create a sp in db2 as400. I got following error. no idea why

    CREATE PROCEDURE PROFILESCHEDULESCAN (
    IN INSYSDATE DATE,
    OUT p_sqlstate_out CHAR(5),
    OUT p_sqlcode_out INT)
    LANGUAGE SQL
    SPECIFIC PROFILESCHEDULESCAN
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    SET OPTION ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX
    PSS: BEGIN
    DECLARE SQLCODE INTEGER DEFAULT 0 ;
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

    DECLARE CONTINUE HANDLER FOR SQLSTATE '23503'
    VALUES (SQLSTATE, SQLCODE)
    INTO p_sqlstate_out
    ,p_sqlcode_out;

    DECLARE INTERNALID INTEGER DEFAULT 0 ;
    END PSS ;

    Message: [SQL0104] Token INTERNALID was not valid. Valid tokens: EXIT UNDO CONTINUE. Cause . . . . . : A syntax error was detected at token INTERNALID. Token INTERNALID is not a valid token. A partial list of valid tokens is EXIT UNDO CONTINUE. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token INTERNALID. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't know about iSeries, but on in DB2 LUW compound statements variable declarations must come before exception handlers.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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