Stored procedure error

ricci · #1 (**permalink**) 09-05-11, 11:39

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

stolze · #2 (**permalink**) 09-05-11, 18:43

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.

ricci · #3 (**permalink**) 09-05-11, 18:52

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.

tonkuma · #4 (**permalink**) 09-06-11, 02:13

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

Quote:

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

ricci · #5 (**permalink**) 09-06-11, 09:29

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

Best regards.

ricci · #6 (**permalink**) 09-07-11, 10:50

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.

tonkuma · #7 (**permalink**) 09-07-11, 21:07

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.

Peter.Vanroose · #8 (**permalink**) 09-09-11, 04:10

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech