Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Question Unanswered: Stored procedure error

    Hi, i'm new at db2, and i'm trying to make a procedure which catches Error codes with handlers. I've done this code:

    CREATE PROCEDURE prueba (equery IN NUMBER)
    IS
    exception1 EXCEPTION;
    exception2 EXCEPTION;
    PRAGMA EXCEPTION_INIT(exception2,-942);
    exception3 EXCEPTION;
    PRAGMA EXCEPTION_INIT(exception3,42601);
    ecode INTEGER DEFAULT 0;
    SQLCODE INTEGER DEFAULT 0;
    BEGIN

    EXECUTE IMMEDIATE equery;
    IF SQLCODE <> 0 THEN -- Validación para alcanzar la primer excepción.
    RAISE OTHERS;
    END IF;
    EXCEPTION
    WHEN exception1 THEN
    DBMS_OUTPUT.PUT_LINE('1ra. excepción de usuario capturara');
    WHEN exception2 THEN
    DBMS_OUTPUT.PUT_LINE('2da. excepción de usuario (Indeterminada) capturada');
    WHEN OTHERS THEN
    SET (dcode = SYSPROC.SQLERRM (SQLCODE));
    DBMS_OUTPUT.PUT_LINE(dcode);
    END @




    But when I try do deploy, send me the next error message:
    En ejecución
    NOM_ESQ.PRUEBA - Despliegue iniciado.
    Crear procedimiento almacenado retornos SQLCODE: -104, SQLSTATE: 42601.
    NOM_ESQ.PRUEBA: 1: An unexpected token "VARCHAR" was found following "RE PRUEBA (equery IN". Expected tokens may include: ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.11.69
    An unexpected token "VARCHAR" was found following "RE PRUEBA (equery IN". Expected tokens may include: ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.11.69
    NOM_ESQ.PRUEBA - Despliegue anómalo.
    NOM_ESQ.PRUEBA - Anulación completada satisfactoriamente.

    Please help me

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the syntax for the CREATE PROCEDURE statement in the DB2 manual. There is no "IS" after the CREATE PROCEDURE <name>(<params>) and the definition of exception handlers is also not used the proper syntax.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Sep 2011
    Posts
    57

    Thanks

    Thanks a lot for your answare Stolze, but I made this code based on this example from IBM web page:

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    Actualy also I send a mail to see if they can help me, because I try this in my Data Studio and didn't work. How cold I use a pragma exception if I use
    LANGUAJE SQL at the begining of my procedure?

    Best regards.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You are writing PL/SQL statements(Oracle compatibility features).
    (PL/SQL is different from SQL PL which is original of DB2.)

    You will see the description
    PL/SQL statement execution is not enabled from these interfaces by default. PL/SQL statement execution support must be enabled on the DB2 data server.
    in parent topic of parent of your referenced topic.
    PL/SQL support - IBM DB2 9.7 for Linux, UNIX, and Windows

    See also this topic:
    Setting up DB2 for Oracle application enablement - IBM DB2 9.7 for Linux, UNIX, and Windows

  5. #5
    Join Date
    Sep 2011
    Posts
    57

    Thanks

    Thanks a lot for your answares, i'll work on it.

    Best regards.

  6. #6
    Join Date
    Sep 2011
    Posts
    57

    Cool Ask you again

    Hi Tonkuma, is any kind or raise original from db2 in stead of use PL/SQL? I mean I want write exception without use PL/SQL.

    Thanks
    Best Regards.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have not studied so much about exception handling other than not found condition in SQL procedure.

    But, if I need to write exception handling in SQL procedure,
    I will start from reading the topic.
    Compound SQL (compiled) - IBM DB2 9.7 for Linux, UNIX, and Windows
    Especialy, Syntax and Description of
    condition-declaration, return-codes-declaration, handler-declaration, specific-condition-value and general-condition-value
    and Examples at the bottom in the topic page.

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    A (simple) example sometimes says more than a 1000 words ;-)
    Here is a (simplified) example from the IBM manual which uses exception handling (both for SQLCODE=100 and SQLCODE<0) to (1) set a variable and (2) throw an exception to the calling application:

    Code:
    CREATE PROCEDURE sample (IN sal DEC(8,2), IN dept CHAR(3))
    LANGUAGE SQL   MODIFIES SQL DATA
    BEGIN
      DECLARE sqlstate   CHAR(5) DEFAULT '00000';
      DECLARE errorLabel CHAR(80) DEFAULT '';
      DECLARE v_name     VARCHAR(50);
      DECLARE v_salary   DEC(8,2);
      DECLARE at_end     CHAR(1) DEFAULT '0';
      DECLARE c1 CURSOR FOR
        SELECT name, salary FROM employee WHERE workdept = department  FOR UPDATE OF salary;
      DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET at_end = '1';
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE VALUE sqlstate SET MESSAGE_TEXT = errorLabel;
      SET errorLabel = 'OPEN CURSOR';   OPEN c1;
      SET errorLabel = 'FIRST FETCH';   FETCH c1 INTO v_name, v_salary;
      WHILE (at_end = '0') DO
        IF (v_salary < sal) THEN
          SET errorLabel = 'UPDATE FOR '||v_name|| ' FROM '||CAST(v_salary AS varchar(9))||' TO '||CAST(sal AS varchar(9));
          UPDATE employee SET salary = sal WHERE CURRENT OF c1;
        END IF;
        SET errorLabel = 'FETCH IN LOOP';   FETCH c1 INTO v_name, v_salary;
      END WHILE;
      SET errorLabel = 'CLOSE CURSOR';   CLOSE c1;
    END
    When an exception occurs inside any of the SQL statements (open, fetch, update, ...), an exception is thrown with the appropriate message text.
    When an SQLCODE equal to 100 is returned by a FETCH, the "at_end" variable is set such that the loop ends (or never starts).
    Last edited by Peter.Vanroose; 09-09-11 at 05:22.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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