Hi,

I have written 3 stored procedure

1>proc1() for retreiving and returning resultset to caller

2>proc2() for inserting the records retreived from proc1() into other tables.

3>proc3() for logging exception into error table


So essentially calling proc1() and proc3() from proc2()


code for retriving the resultset from proc1() is as follows.
************************************************** **********************************************


CALL proc1();

ASSOCIATE RESULT SET LOCATOR (LOC_RES1) WITH PROCEDURE proc1;
ALLOCATE RES1 CURSOR FOR RESULT SET LOC_RES1;

SET var_result_end =0;


WHILE(var_result_end = 0) DO

FETCH FROM RES1 INTO var_imcnumber,v_status,v_logonid,v_logonpassword,v _name,v_addressline1,v_addressline2,v_city;

P2:BEGIN
IF(var_result_end = 0)THEN
SELECT PK_VALUE INTO v_parent_pk FROM TEST.KEYS WHERE TABLE_NAME='TEST.TEST_PARENT_1';
SELECT PK_VALUE INTO v_child_pk FROM TEST.KEYS WHERE TABLE_NAME='TEST.TEST_CHILD_1';

INSERT INTO TEST.TEST_PARENT_1(USERS_ID,STATUS,LOGONID,LOGONPA SSWORD)
VALUES (v_parent_pk,v_status,v_logonid,v_logonpassword);

INSERT INTO TEST.TEST_CHILD_1(USERS_ID,NAME,ADDRESSLINE1,ADDRE SSLINE2,CITY)
VALUES (v_child_pk, v_name,v_addressline1,v_addressline2,v_city);

UPDATE TEST.KEYS SET PK_VALUE = v_parent_pk+1 WHERE TABLE_NAME='TEST.TEST_PARENT_1';
UPDATE TEST.KEYS SET PK_VALUE = v_child_pk+1 WHERE TABLE_NAME='TEST.TEST_CHILD_1';

COMMIT;
END IF;
END P2;

END WHILE;

************************************************** **********************************************

can anybody let me know how to handle any SQLException might occur inside P2:BLOCK.

it should meet the following requirement
a>if exception thrown during execution of P2 Block RollBack should happen
b>after handling exception while loop should conitnue with next record.


when i am trying it is throwing some Cursor fetch or close problem.
Thanking in advance.

Regards
Ramdas Nayak