DB2 procedure return tables

Question

Hi I have this procedure:

CREATE PROCEDURE TEST() 
LANGUAGE SQL
Specific TEST
BEGIN

DECLARE V_TEST VARCHAR(100);
DECLARE V_TEST_EXT VARCHAR(100);
DECLARE SQLCODE INTEGER DEFAULT -1;
DECLARE RET_CODE INTEGER DEFAULT -2;
DECLARE LIST_CMD VARCHAR(512);
DECLARE LIST_CMD_EXT VARCHAR(512);

DECLARE CUR_TEST CURSOR WITH RETURN FOR LIST_STMT;
DECLARE CUR_TEST_EXT CURSOR WITH RETURN FOR LIST_EXT_STMT;

DECLARE CONTINUE HANDLER FOR SQLEXECPTION SET RET_CODE = SQLCODE;

DECLARE global temporary table temptbl (testres varchar(512)) with replace not logged on commit preserve rows;

SET LIST_CMD = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT FROM LIST_CMD;

OPEN CUR_TEST;
FETCH CUR_TEST INTO V_TEST;
WHILE (RET_CODE <> 100) DO
insert into temptbl(testres) values V_TEST;
FETCH CUR_TEST INTO V_TEST;
END WHILE;
CLOSE CUR_TEST;

SET RET_CODE=-2;
SET LIST_CMD_EXT = 'SELECT TEST FROM TESTTAB';
PREPARE LIST_STMT_EXT FROM LIST_CMD_EXT;

OPEN CUR_TEST_EXT;
FETCH CUR_TEST_EXT INTO V_TEST_EXT;
WHILE (RET_CODE <> 100) DO
FETCH CUR_TEST_EXT INTO V_TEST_EXT;
END WHILE;
CLOSE CUR_TEST_EXT;

END;

It does compile but when I call it it results in an endless loop not doing anything. How can I get this to work and how can I do it for both cursors? (each in it's own temporary table)

Any ideas? Thank you for all your help.

I am running DB2 10.5 on Windows.

Answer 1

Try this:

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE TEST ()
BEGIN
  DECLARE L_TABNAME VARCHAR(128);
  DECLARE SQLSTATE CHAR(5);
  DECLARE C1 CURSOR FOR SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA=USER;

  DECLARE GLOBAL TEMPORARY TABLE SESSION.TABLES (TABNAME VARCHAR(128)) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
  OPEN C1;
  L1: LOOP
    FETCH C1 INTO L_TABNAME;
    IF (SQLSTATE='02000') THEN LEAVE L1; END IF;
    INSERT INTO SESSION.TABLES (TABNAME) VALUES L_TABNAME;
  END LOOP L1;
  CLOSE C1;

END@

CALL TEST@
SELECT COUNT(1) FROM SESSION.TABLES@