Can anyone tell me why the following procedure gives me at runtime:
Exception occurred while debugging:
A database manager error occurred.[IBM][CLI Driver][DB2/6000] SQL0504N The cursor "C" is not defined. SQLSTATE=34000

I am trying to input a shema, table, and column, and have the procedure loop thru all records in that column and update those by replacing certain characters with ''. Any tips? Thanks

CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN

DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
DECLARE v_CURR_ROW INTEGER DEFAULT 0;
DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
DECLARE s1 VARCHAR(255) DEFAULT NULL;
DECLARE s2 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c CURSOR WITH HOLD FOR s1;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
SET v_SQL1 = 'SELECT ' || IN_COLUMN || ' FROM ' || v_IN_SCHEMA_IN_TABLE || ' FOR UPDATE';
SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR( ' || CHAR(v_CURR_ASCII)
|| ' ),'''') WHERE CURRENT OF c';

PREPARE s1 FROM v_SQL1;
WHILE (v_CURR_ASCII < 266) DO


OPEN c;

WHILE(at_end = 0) DO
FETCH c INTO v_CURR_PHONE_NUM;
PREPARE s2 FROM v_SQL2;
EXECUTE s2;
COMMIT;
SET v_CURR_ROW = v_CURR_ROW + 1;
END WHILE;
CLOSE c;
SET v_CURR_ASCII = v_CURR_ASCII + 1;
SET v_ROW_COUNT = 0;
SET at_end = 0;
END WHILE;
CLOSE c;
END

Are you getting the error when you build or run the SP? Also, please state which OS and db2 Version.

Andy

Error is at runtime. Proc builds with no problems. DB2 version 8.1 FP3 and O/S is AIX. Just so you don't have to dig thru the code, I am trying to execute a dynamic update statement within a dynamic cursor.

Thanks

By the way when I debug, the error occurs at this line:

PREPARE s2 FROM v_SQL2;

The code looks OK to me, but then again I have not tried doing what you are.
In looking at the code, you will update every row of the table anyway, so why use the cursor.

CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN

DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
DECLARE v_CURR_ROW INTEGER DEFAULT 0;
DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
DECLARE s1 VARCHAR(255) DEFAULT NULL;
DECLARE s2 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR( ' || CHAR(v_CURR_ASCII)
|| ' ),'''') ';

WHILE (v_CURR_ASCII < 266) DO

PREPARE s2 FROM v_SQL2;
EXECUTE s2;
COMMIT;
SET v_CURR_ASCII = v_CURR_ASCII + 1;
SET v_ROW_COUNT = 0;
SET at_end = 0;
END WHILE;
CLOSE c;
END

HTH

Andy

Andy,
good suggestion, I removed the cursor and now I am just doing blanket updates on the whole table. It now completes successfully, but it still doesn't update the column properly. (It should remove any ascii character 0-254 from that column). When I run the update statement by itself outside the procedure it works fine.

To be more specific, the update works fine as a standalone if i hardcode one ascii code instead of getting the variable v_CURR_ASCII and using that.

Example:
UPDATE SCHEMANAME.CONTACTS SET PHONENUMBER = REPLACE(PHONENUMBER,CHR(45),'');

You can try a variation on what you want:

CREATE PROCEDURE CRMVIEW.CLEANPHONENUMBERS (IN IN_SCHEMA VARCHAR(50), IN IN_TABLE VARCHAR(50), IN IN_COLUMN VARCHAR(50))
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN

DECLARE v_CURR_ASCII INTEGER DEFAULT 0;
DECLARE v_ROW_COUNT INTEGER DEFAULT 0;
DECLARE v_CURR_ROW INTEGER DEFAULT 0;
DECLARE v_CURR_PHONE_NUM VARCHAR(50) DEFAULT NULL;
DECLARE v_IN_SCHEMA_IN_TABLE VARCHAR(100) DEFAULT NULL;
DECLARE s1 VARCHAR(255) DEFAULT NULL;
DECLARE s2 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL1 VARCHAR(255) DEFAULT NULL;
DECLARE v_SQL2 VARCHAR(255) DEFAULT NULL;

DECLARE at_end INT DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;



SET v_IN_SCHEMA_IN_TABLE = IN_SCHEMA||'.'||IN_TABLE;
SET v_SQL2 = 'UPDATE ' || v_IN_SCHEMA_IN_TABLE || ' SET ' || IN_COLUMN || ' = REPLACE( ' || IN_COLUMN || ' ,CHR(?),'''') ';

WHILE (v_CURR_ASCII < 266) DO

PREPARE s2 FROM v_SQL2;
EXECUTE s2 using v_CURR_ASCII;
COMMIT;
SET v_CURR_ASCII = v_CURR_ASCII + 1;
SET v_ROW_COUNT = 0;
SET at_end = 0;
END WHILE;
CLOSE c;
END


Andy