All Forums Database
bcdjr1 1 post Joined 01/15
19 Mar 2015
Stored Procedure doesn't recognize volatile table

I’m trying to do a test stored procedure on using volatile tables.  This is what I have:
 
CREATE PROCEDURE "DLPMC_PMCOE_DEV_LAB"."test_sp" ()
                DYNAMIC RESULT SETS 1
BEGIN
 
declare q1 varchar(50);
declare cur1 cursor with return only to client for s1;
 
 
create volatile table t1 (tdy date) primary index (tdy) on commit preserve rows ;
 
insert into t1
select date;
 
set q1 = 'select * from t1';
 
prepare s1 from q1;
 
open cur1;
 
drop table  t1;
 
END;
 
 
When I try to submit this, though, it gives me an error saying “Object ‘t1’ does not exist.”
 
Can someone tell me what I'm doing wrong?

Rohan_Sawant 55 posts Joined 07/14
20 Mar 2015

Hi bcdjr1,
 
Set the DATABASE to default database of proc. i.e. in your case it must be DATABASE DLPMC_PMCOE_DEV_LAB. Compile and run the proc.
If the above solution doesnt work use the below code: (Just added SQL SECURITY OWNER). The below must surely work.
 

CREATE PROCEDURE "DLPMC_PMCOE_DEV_LAB"."test_sp" ()
                DYNAMIC RESULT SETS 1 SQL SECURITY OWNER
BEGIN
 
DECLARE q1 VARCHAR(50);
DECLARE cur1 CURSOR WITH RETURN ONLY TO client FOR s1;
 
 
CREATE VOLATILE TABLE t1 (tdy DATE) PRIMARY INDEX (tdy) ON COMMIT PRESERVE ROWS ;
 
INSERT INTO t1
SELECT DATE;
 
SET q1 = 'select * from t1';
 
PREPARE s1 FROM q1;
 
OPEN cur1;
 
DROP TABLE  t1;
 
END;

 
 
Thanks,
Rohan Sawant

jawadahmed 5 posts Joined 12/12
05 May 2016

The above solution is not working. Is there any other solution of this problem?

jawadahmed 5 posts Joined 12/12
05 May 2016

I tried the above mentioned solution and I got the following error message:
SPL1027:E(L6), Missing/Invalid SQL statement'E(3807):Object 'employee1' does not exist.'.

You must sign in to leave a comment.