Hi all,

I am trying to create a procedure in db2 its giving an error,could you kindly help me with this


DROP PROCEDURE BOUNCEINC_COUNT1
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "DEVTEAM.BOUNCEINC_COUNT1" is an undefined name. SQLSTATE=42704

CREATE PROCEDURE BOUNCEINC_COUNT1
(
IN starting TIMESTAMP ,
IN ending TIMESTAMP )
LANGUAGE SQL
p1: BEGIN

DECLARE c1_OPEN_FLAG INTEGER DEFAULT 0;

DECLARE c1_FOUND INTEGER DEFAULT NULL;

DECLARE SQLCODE INTEGER DEFAULT 0;
BEGIN

DECLARE err_value CHAR(1) ;

DECLARE var_reason1 VARCHAR(26) ;

DECLARE var_reason2 VARCHAR(26) ;

DECLARE vBounce_reason VARCHAR(2000) ;
DECLARE at_end INTeger DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE flag_status FLOAT DEFAULT 0;

DECLARE c1rec_* VARCHAR(19) /****** adventnet-swissql table used in cursor is not found in metadata or metadata not updated ***********/ ;

DECLARE c1 CURSOR WITH HOLD FOR SELECT *
FROM bounceback_inc
WHERE made between starting and ending
;
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42710'
BEGIN
END;
DECLARE GLOBAL TEMPORARY TABLE hard_bounce( tempIdx INTEGER, hard_bounce VARCHAR(500)) ON COMMIT PRESERVE ROWS;

END;

BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42710'
BEGIN
END;
DECLARE GLOBAL TEMPORARY TABLE soft_bounce( tempIdx INTEGER, soft_bounce VARCHAR(500)) ON COMMIT PRESERVE ROWS;

END;

SET var_reason1 = null;

SET var_reason2 = null;

SET vBounce_reason = null;

SET c1_OPEN_FLAG = 1;


OPEN c1 ;

IF EXISTS(SELECT * FROM SESSION.hard_bounce WHERE tempIdx = 1)
THEN
UPDATE SESSION.hard_bounce SET hard_bounce = '%invalid local real name%' WHERE tempIdx = 1;
ELSE
INSERT INTO SESSION.hard_bounce ( tempIdx , hard_bounce) VALUES(1, '%invalid local real name%' );
END IF;


IF EXISTS(SELECT * FROM SESSION.hard_bounce WHERE tempIdx = 2)
THEN
UPDATE SESSION.hard_bounce SET hard_bounce = '%no user%' WHERE tempIdx = 2;
ELSE
INSERT INTO SESSION.hard_bounce ( tempIdx , hard_bounce) VALUES(2, '%no user%' );
END IF;






IF EXISTS(SELECT * FROM SESSION.soft_bounce WHERE tempIdx = 1)
THEN
UPDATE SESSION.soft_bounce SET soft_bounce = '%blocked server%' WHERE tempIdx = 1;
ELSE
INSERT INTO SESSION.soft_bounce ( tempIdx , soft_bounce) VALUES(1, '%blocked server%' );
END IF;


IF EXISTS(SELECT * FROM SESSION.soft_bounce WHERE tempIdx = 2)
THEN
UPDATE SESSION.soft_bounce SET soft_bounce = '%mailbox full%' WHERE tempIdx = 2;
ELSE
INSERT INTO SESSION.soft_bounce ( tempIdx , soft_bounce) VALUES(2, '%mailbox full%' );
END IF;





-- ADVENTNET SWISSQL : ORACLE_SUBSTR IS SwisSQL-OracleToDB2 Library Function. Contact swissql-support@adventnet.com for Implementation
LOOP_LABEL1: LOOP
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET c1_FOUND = SQLCODE ;
SET c1_FOUND = 0;
FETCH FROM c1 INTO c1rec_* ;
END ;

IF c1_FOUND = 100 THEN
LEAVE LOOP_LABEL1;
END IF;

BEGIN

DECLARE j INTEGER ;

SET j = 1;

---- Checking for Hard Bounces---

LOOP_LABEL2:
WHILE j <= 13 DO


IF ( LOWER(c1rec_subject)like (SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ) or LOWER(c1rec_msg)like (SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ) ) THEN

SET vBounce_reason = VARCHAR(RTRIM(CHAR(REPLACE((SELECT hard_bounce FROM SESSION.hard_bounce WHERE tempIdx = j ), '%' , '' ))));

SET flag_status = 1;

LEAVE LOOP_LABEL2;
ELSEIF ( LOWER(c1rec_subject)like (SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ) or LOWER(c1rec_msg)like (SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ) ) THEN

SET flag_status = 2;

SET vBounce_reason = VARCHAR(RTRIM(CHAR(REPLACE((SELECT soft_bounce FROM SESSION.soft_bounce WHERE tempIdx = j ), '%' , '' ))));

LEAVE LOOP_LABEL2;
ELSE

SET flag_status = 0;

SET vBounce_reason = ORACLE_SUBSTR(VARCHAR(RTRIM(CHAR(c1rec_subject))), 1, 100);

LEAVE LOOP_LABEL2;
END IF ;

SET j = j + 1 ;

END WHILE ;

END;
IF ( flag_status = 1 ) THEN


BEGIN

DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.HardBounce = HardBounce + 1,
bounceback_count.updat = c1rec_updat,
bounceback_count.Bounce_reason = vBounce_reason
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

IF (at_end=1 ) THEN

INSERT INTO bounceback_count
( email ,
vemail ,
campaign ,
REASON ,
REASON_COUNT ,
HardBounce ,
SoftBounce ,
Bounce_Reason )
VALUES ( c1rec_email ,
LCASE(c1rec_email) ,
c1rec_campaign ,
'HardBounce' ,
1 ,
1 ,
0 ,
vBounce_reason )
;
END IF ;

SELECT REASON
INTO VAR_REASON1 FROM BOUNCEBACK_COUNT
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;

IF VAR_REASON1 = 'HardBounce' THEN

SET var_reason1 = 'HardBounce' ;
ELSEIF VAR_REASON1 = 'SoftBounce' THEN

SET var_reason1 = 'HardBounce,SoftBounce' ;
END IF ;


BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
bounceback_count.REASON = var_reason1
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

ELSEIF ( flag_status = 2 ) THEN


BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.SoftBounce = SoftBounce + 1,
bounceback_count.updat = c1rec_updat,
bounceback_count.bounce_reason = vBounce_reason
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

IF (at_end=1) THEN

INSERT INTO bounceback_count
( email ,
vemail ,
campaign ,
reason ,
reason_count ,
HardBounce ,
SoftBounce ,
Bounce_Reason )
VALUES ( c1rec_email ,
LCASE(c1rec_email) ,
c1rec_campaign ,
'SoftBounce' ,
1 ,
0 ,
1 ,
vBounce_reason )
;
END IF ;

SELECT REASON
INTO VAR_REASON2 FROM BOUNCEBACK_COUNT
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;

IF VAR_REASON2 = 'HardBounce' THEN

SET var_reason2 = 'HardBounce,SoftBounce' ;
ELSEIF VAR_REASON2 = 'SoftBounce' THEN

SET var_reason2 = 'SoftBounce' ;
END IF ;


BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
bounceback_count.REASON = var_reason2
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

ELSEIF ( flag_status = 0 ) THEN


BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.SoftBounce = SoftBounce + 1,
bounceback_count.updat = c1rec_updat,
bounceback_count.bounce_reason = vBounce_reason
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

IF (at_end=1) THEN

INSERT INTO bounceback_count
( email ,
vemail ,
campaign ,
reason ,
reason_count ,
HardBounce ,
SoftBounce ,
Bounce_Reason )
VALUES ( c1rec_email ,
LCASE(c1rec_email) ,
c1rec_campaign ,
'SoftBounce' ,
1 ,
0 ,
1 ,
vBounce_reason )
;
END IF ;
END IF ;

SELECT REASON
INTO VAR_REASON2 FROM BOUNCEBACK_COUNT
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;

IF VAR_REASON2 = 'HardBounce' THEN

SET var_reason2 = 'HardBounce,SoftBounce' ;
ELSEIF VAR_REASON2 = 'SoftBounce' THEN

SET var_reason2 = 'SoftBounce' ;
END IF ;


BEGIN
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
set at_end=0;
UPDATE bounceback_count
SET bounceback_count.REASON_COUNT = REASON_COUNT + 1,
bounceback_count.REASON = var_reason2
WHERE email = c1rec_email
AND CAMPAIGN = c1rec_CAMPAIGN
;
END;

END LOOP LOOP_LABEL1 ;
SET c1_OPEN_FLAG = 0;
CLOSE c1 ;

END ;
END p1


DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "*" was found following "0; DECLARE c1rec_".
Expected tokens may include: "<space>". LINE NUMBER=27. SQLSTATE=42601

You cannot use * in a variable name.

Andy