Replace Stored procedure failed ,Volatile table doesn't exist

Question

This procedure already exists in database but when I tried to replace after making changes It's throwing error volatile table doesn't exist. Can you pls help identify the issue ? This failed while migrating to QA environment.

Statement 2: REPLACE PROCEDURE Failed. 
Output directed to Answer window

SPL5000:W(L78), E(3807):Object 'VT_SEG' does not exist. 
SPL1027:E(L165), Missing/Invalid SQL statement'E(3807):Object 'VT_SEG' does not exist.'.
SPL5000:W(L169), E(3807):Object 'VT_SEG' does not exist.

REPLACE PROCEDURE DB.SP_PNL ( IN P_EXTRACTSTARTDATE VARCHAR(35))
                
BEGIN

   
VT_SEG:BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
---- ERROR HANDLING HERE ----
END ;
DROP TABLE "VT_SEG";
END VT_SEG;

--LOAD THE TOPLINE DATA
CREATE MULTISET VOLATILE TABLE VT_SEG AS
( SELECT TIER
       , JOB_ROLE
       , REGION
       , LOC_ID
       , LOCATION
       , WK_NUM
       , YR_NUM+1 AS YR_NUM
       , cast(sum(coalesce(VOL_RAW_CASES,0) ) as decimal(38,9)) as VOL_RAW_CASES

 FROM DB1.VOL
GROUP BY 1,2,3,4,5,6,7
) WITH DATA
primary index (TIER,JOB_ROLE,LOC_ID,WK_NUM,YR_NUM)
ON COMMIT PRESERVE ROWS;

end;

Clearly this is not the portion of the stored procedure with the problem since the error references line 165. Generally speaking, Global Temporary tables are a better choice for stored procedure work than Volatile tables. Have you tried creating a volatile table by that name in the DDL session, before compiling the SP? — Fred, Apr 12 '21 at 17:09
I just modified the procedure since I don't want to post actual code because of employer restrictions. When I migrate to PROD ,we won't be able to create volatile table first and deploy SP..So I'm looking for any alternative here and what causes this error as I have deployed this SP many times before successfully without creating V tables first. — user9926789, Apr 12 '21 at 17:35
If you can't create the volatile table prior to compiling the stored proc, how are you going to create the volatile table in the stored proc? In any case, trying to re-use a volatile table within a session like this is less than ideal. — Andrew, Apr 12 '21 at 18:05
Are you suggesting to create volatile tables first and compile SP which has drop and create volatile tables again? — user9926789, Apr 12 '21 at 18:44
As you see from the messages, sometimes the parser will tolerate or merely warn about a reference to an object that does not exist at compile time; other times it is considered a fatal error. Did you perhaps add a DML statement referring to VT_SEG that is not within the scope of a condition handler? — Fred, Apr 12 '21 at 19:43
if you are referring to "SPL1027:E(L165), Missing/Invalid SQL statement'E(3807):Object 'VT_SEG' does not exist." yes I have a insert statement for VT_SEG after creating it.I'm not sure why it is throwing error to create V table in first place. — user9926789, Apr 12 '21 at 20:36
In SPs I prefer Global Temp Tables over Volatile Tables. Create it once before the SP and then simply Delete it in your code instead of Drop/Recreate a VT. — dnoeth, Apr 13 '21 at 16:32

Replace Stored procedure failed ,Volatile table doesn't exist

0 Answers0