Stored Procedure doesn't recognize volatile table

Database
N/A

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?

5 REPLIES
Enthusiast

Re: Stored Procedure doesn't recognize volatile table

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

Enthusiast

Re: Stored Procedure doesn't recognize volatile table

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

Enthusiast

Re: Stored Procedure doesn't recognize volatile table

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.'.

Visitor

Re: Stored Procedure doesn't recognize volatile table

You need to create the volatile table before defining the procedure, then define the procedure, then drop the table.

/*
Step 1

Manually create the volatile table as it will be used in the procedure.
This needs to exist when you define/compile the procedure in step 2.

--*/
CREATE VOLATILE TABLE t1 (
	tdy DATE
	) 
	PRIMARY INDEX (tdy) 
	ON COMMIT PRESERVE ROWS ;

/*
Step 2

Define the procedure
*/
CREATE PROCEDURE test_sp ()
	DYNAMIC RESULT SETS 1
	BEGIN

	DECLARE q1 VARCHAR(50);
	DECLARE cur1 CURSOR WITH RETURN ONLY TO client FOR s1;
	-- create volatile
	CREATE VOLATILE TABLE t1 (
		tdy DATE
		) 
		PRIMARY INDEX (tdy) 
		ON COMMIT PRESERVE ROWS ;
	-- insert into volatile
	INSERT INTO t1
	SELECT DATE;
	-- prepare cursor as select from volatile table
	SET q1 = 'select * from t1';
	PREPARE s1 FROM q1;
	OPEN cur1;
	-- IMPORTANT! drop volatile table so procedure can be rerun
	DROP TABLE  t1;
	
	END;

/*
Step 3

Drop the volatile table after defining the procedure. The procedure is
going to try to create the table, so if the table already exists this
will produce a conflict/error.
*/
DROP TABLE t1;

/*
Step 4

Call the procedure, which will return the result set
*/
CALL test_sp();

-- clean up
DROP PROCEDURE test_sp;
Teradata Employee

Re: Stored Procedure doesn't recognize volatile table

Or better yet: Use a Global Temporary Table instead of a Volatile Table.