Unanswered: Return cursor on temp table in stored procedure?
I have two stored procedures, one that initializes a temp table and another that calls it..
SPROC1 : TEMP TABLE SPROC has something like this --
SET tmpStatement = 'DECLARE GLOBAL TEMPORARY TABLE SESSION.tmp_readings LIKE TMPREADINGS NOT LOGGED';
EXECUTE IMMEDIATE tmpStatement;
SPROC 2 : SPROC THAT USES TEMP TABLE --
CALL SPROC1() --- to define the temp table
DECLARE C1 CURSOR WITH RETURN FOR SELECT * from SESSION.tmp_readings;
The problem is this -- when I create SPROC2, it complains in the cursor statement above that SESSION.tmp_readings hasn't been defined yet. Is there some keyword I can give the DECLARE GLOBAL TEMPORARY TABLE statement so that it recognizes the table when it comes to the cursor statement? Thanks!!