How to insert cursor/resultset that produced by stored procedure to temporary table in db2

Question

hi im trying to insert the resultset to temporary table using this method,

this was asked before How i can put a result set from a stored procedure in a temporary table in DB2 but no one actually answer it so.

CREATE OR REPLACE PROCEDURE SCHEMANAME.SP_CALLER()
DYNAMIC RESULT SETS 1

BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_table(
useraccountid SMALLINT,
roleid SMALLINT,
userid varchar(20),
username varchar(50),
lastlogindate TIMESTAMP,
deleted SMALLINT
) ON COMMIT PRESERVE ROWS;

P1:BEGIN

DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;

CALL SCHEMANAME.PR_USERACCOUNTGETALL();


ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE SCHEMANAME.PR_USERACCOUNTGETALL;
ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;

--INSERT INTO #TEMPTABLE (cur);
--FETCH cur INTO TRANSACTIONCODE;
--CLOSE CUR;

Insert into session.temp_table(cur);

END P1;

END

UPDATED CODE:

I tried the snippet code that you suggest, It runs no error, But i need to return the resultset i queried from the SESSION.temp_table,

DECLARE ret CURSOR WITH RETURN FOR SELECT * FROM SESSION.temp_table where userid='JOHNDOE'; open ret;

how will I return the values from here now??

CREATE OR REPLACE PROCEDURE ITRS.SP_CALLER()
DYNAMIC RESULT SETS 1

BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_table(
USERACCOUNTID SMALLINT,
ROLEID SMALLINT,
USERID VARCHAR(20),
USERNAME VARCHAR(50),
LASTLOGINDATE TIMESTAMP,
DELETED SMALLINT
);

P1:BEGIN

DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;
DECLARE a CHAR(5) DEFAULT '00000';
DECLARE v_useraccountid SMALLINT;
DECLARE v_roleid SMALLINT;
DECLARE v_userid VARCHAR(20);
DECLARE v_username VARCHAR(50);
DECLARE v_lastlogindate TIMESTAMP;
DECLARE v_deleted SMALLINT;

DECLARE ret CURSOR WITH RETURN FOR
SELECT * FROM SESSION.temp_table where userid='JOHNDOE';
open ret;


CALL ITRS.PR_USERACCOUNTGETALL();

ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE ITRS.PR_USERACCOUNTGETALL;
ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;

ins_loop: LOOP
FETCH cur INTO v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted;
IF a <> '00000' THEN LEAVE ins_loop; END IF;
INSERT INTO SESSION.temp_table(USERACCOUNTID,ROLEID,USERID,USERNAME,LASTLOGINDATE,DELETED)
    VALUES(v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted);

END LOOP ins_loop;

CLOSE cur;
END P1;

END

UPDATED:

Here i tried to just insert the data on table with normal insertion via SP, so i removed the declaration of temporary table and just directly put the dumbtable name to replace the temporary table

--#SET TERMINATOR @
CREATE OR REPLACE PROCEDURE ITRS.SP_CALLER()
DYNAMIC RESULT SETS 1

BEGIN

DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_useraccountid SMALLINT;
DECLARE v_roleid SMALLINT;
DECLARE v_userid VARCHAR(20);
DECLARE v_username VARCHAR(50);
DECLARE v_lastlogindate TIMESTAMP;
DECLARE v_deleted SMALLINT;

DECLARE ret CURSOR WITH RETURN FOR
SELECT * FROM ITRS.DUMBTABLE where USERID='JOHNDOE';


CALL ITRS.PR_USERACCOUNTGETALL();

ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE ITRS.PR_USERACCOUNTGETALL;
ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;

ins_loop: 
LOOP
  FETCH cur INTO v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted;
  IF SQLSTATE <> '00000' THEN LEAVE ins_loop; END IF;
  INSERT INTO ITRS.DUMBTABLE(USERACCOUNTID,ROLEID,USERID,USERNAME,LASTLOGINDATE,DELETED)
  VALUES(v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted);
  COMMIT;
END LOOP ins_loop;
CLOSE cur;
open ret;
END@

Answer 1

You should fetch data from the cursor in a loop as described in LOOP statement in SQL procedures, for example.

--#SET TERMINATOR @
DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_table(
USERACCOUNTID SMALLINT,
ROLEID SMALLINT,
USERID VARCHAR(20),
USERNAME VARCHAR(50),
LASTLOGINDATE TIMESTAMP,
DELETED SMALLINT
) WITH REPLACE NOT LOGGED@

CREATE OR REPLACE PROCEDURE ITRS.SP_CALLER()
DYNAMIC RESULT SETS 1

BEGIN

DECLARE loc_cursor RESULT_SET_LOCATOR VARYING;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE v_useraccountid SMALLINT;
DECLARE v_roleid SMALLINT;
DECLARE v_userid VARCHAR(20);
DECLARE v_username VARCHAR(50);
DECLARE v_lastlogindate TIMESTAMP;
DECLARE v_deleted SMALLINT;

DECLARE ret CURSOR WITH RETURN FOR
SELECT * FROM SESSION.temp_table where userid='JOHNDOE';

DECLARE GLOBAL TEMPORARY TABLE SESSION.temp_table(
USERACCOUNTID SMALLINT,
ROLEID SMALLINT,
USERID VARCHAR(20),
USERNAME VARCHAR(50),
LASTLOGINDATE TIMESTAMP,
DELETED SMALLINT
) WITH REPLACE NOT LOGGED;

CALL ITRS.PR_USERACCOUNTGETALL();

ASSOCIATE RESULT SET LOCATOR (loc_cursor) WITH PROCEDURE ITRS.PR_USERACCOUNTGETALL;
ALLOCATE cur CURSOR FOR RESULT SET loc_cursor;

ins_loop: 
LOOP
  FETCH cur INTO v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted;
  IF SQLSTATE <> '00000' THEN LEAVE ins_loop; END IF;
  INSERT INTO SESSION.temp_table(USERACCOUNTID,ROLEID,USERID,USERNAME,LASTLOGINDATE,DELETED)
  VALUES(v_useraccountid,v_roleid,v_userid,v_username,v_lastlogindate,v_deleted);
END LOOP ins_loop;

CLOSE cur;
open ret;

END@

Note, that you must use some different statement separator like @ instead of the default one ;, if you use a COMPOUND SQL statement.
Do not change the SQLSTATE variable name. It's the special one. It's set almost after each statement executed automatically.
The example should work in the Db2 Command Line Processor (it understands such a directive as in the 1-st line), if you run a file containing this text.
If you use some different tool to run the statement, you must find there a place, where the default statement terminator can be changed.