Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    1

    Unanswered: DB2 procedure error

    very new to DB2 procedures so i have this simple code you'd think but get the following error

    CREATE PROCEDURE "USP_GETODAVMXERRORCROSSREFERENCE" ( IN SCERROR_KEY VARCHAR(50),
    IN SCMODULE_KEY VARCHAR(30) )
    SPECIFIC "USP_GETODAVMXERRORCROSSREFERENCE"
    DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    -- SCERROR_KEY
    -- SCMODULE_KEY
    ------------------------------------------------------------------------
    P1: BEGIN

    -- Check if the error code starts with ODA if so then....else it will be trans specific
    IF SUBSTR(SCERROR_KEY,1,3) = 'ODA' THEN
    SET SCMODULE_KEY = 'ODA';
    END IF;
    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT "errorIdentifier", "errorModule", "mappedExceptionDescription",
    "mappedExceptionName"
    FROM ESB."LogicalServiceException"
    WHERE "errorIdentifier" = SCERROR_KEY AND "errorModule" = SCMODULE_KEY ;
    -- Cursor left open for client application
    OPEN cursor1;

    END P1
    get deployment error of

    Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.

    KEVIN.USP_GETODAVMXERRORCROSSREFERENCE: 17: An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>".

    KEVIN.USP_GETODAVMXERRORCROSSREFERENCE - Deploy failed.
    if i comment out the if condition section works no worries so presume there must be a rule on not having IF's before Declaring cursors

    any help appreciated if only to point to the relevant section in a manual as been looking at the online examples and not found what i want really

    TIA

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    that is correct. declare all of your cursors, then get into the guts of your proc.

  3. #3
    Join Date
    Jun 2011
    Posts
    1

    Declaring cursor

    It is not necessary to declare the cursor at the beginning

    You can declare it in the Begin and End block as
    C1: BEGIN
    --Declare your cursor here.
    END C1;

    The remaining code can remain as it is.

Posting Permissions

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