Hi,

Could any one tell that, how to handle the exceptions mainly after the
insert statement.

I am writing a PL SQL Procedure, it must capture the exception if any.
Also I need the error description for that errror.

Regards,
Ravi.

Hi,

Even I was trying to catch exceptions and this is what i was trying.
Hope others will chip in with some more EFFECTIVE examples::

CREATE PROCEDURE test ( IN cntry_cde CHAR(2))
SPECIFIC test
LANGUAGE SQL

se: BEGIN

-- Declare return codes

DECLARE ERRORFLAG INTEGER DEFAULT 0;
DECLARE insert_error CONDITION FOR SQLSTATE 'put the error code here';
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR insert_error
BEGIN
SET errorflag = 1;
END;

-- LOGIC

INSERT into test.t_emp VALUES('1,'tariq','IBM DB2 UDB');

IF ERRORFLAG=1 THEN
-- DO SOME THING
END IF;

END se


regards,
tariq

http://www.db2click.com/my_script_di...orHandler.html

shows one approach to handle exception and continue circumstances

HTH

Sathyaram

rAinDeEr wrote:[color=blue]
> Hi,
>
> Even I was trying to catch exceptions and this is what i was trying.
> Hope others will chip in with some more EFFECTIVE examples::
>
> CREATE PROCEDURE test ( IN cntry_cde CHAR(2))
> SPECIFIC test
> LANGUAGE SQL
>
> se: BEGIN
>
> -- Declare return codes
>
> DECLARE ERRORFLAG INTEGER DEFAULT 0;
> DECLARE insert_error CONDITION FOR SQLSTATE 'put the error code here';
> DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
> DECLARE SQLCODE INT DEFAULT 0;
>
> DECLARE CONTINUE HANDLER FOR insert_error
> BEGIN
> SET errorflag = 1;
> END;
>
> -- LOGIC
>
> INSERT into test.t_emp VALUES('1,'tariq','IBM DB2 UDB');
>
> IF ERRORFLAG=1 THEN
> -- DO SOME THING
> END IF;
>
> END se[/color]
Tariq,

Why don't you "do something" inside the handler iteslef (that's why it's
called a handler :-)

also note that handlers can (and typically should be specific to teh
statement:
BEGIN
DECLARE .. HANDLER FOR SQLSTATE ....
BEGIN
-- do something
END
INSERT ....
END

It's like Java try... catch
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab