Using Volatile table in stored procedure

Database
Enthusiast

Using Volatile table in stored procedure

Hi I am trying to use volatile table inside of a stored procedure (for stored returned select statement and looping through it), but I am running into the issue of  Missing/Invalid SQL statement'E(3807):Object 'Temp_Table' does not exist.'.

From this http://stackoverflow.com/questions/24185966/store-the-result-of-a-select-statement-within-a-stored-p...

It seems that I will have to declare volatile table outside the Stored Procedure or use a global temp table outside of the stored procedure, but I don't really want to do this.

It seems that I could hide the use of the volatile table through dynamic SQL seen http://www.dwhpro.com/teradata-stored-procedures-dynamic-static-sql/

But I have too many references to this table in Stored Procedure and I think some references will be hard to replace with dynamic sql.

If anyone has any solutions to do this it would be great thanks.

3 REPLIES
Enthusiast

Re: Using Volatile table in stored procedure

Hi,

Even I need help on this topic. I have to create multiple volatile tables inside a stored procedure and use them for returing output to user in the form of cursor. But I am unable to compile the procedure , its showing error that " Object VT11 doesnt exist ".

Please help on this issue.

Teradata Employee

Re: Using Volatile table in stored procedure

There is no problem using volatile tables in a store procedure 

I used on a SP where after executing the SP i should review a volatile table, this table sometimes existed previous to the execution of the SP and sometimes no, so I used this piece of code to control this issue

  tv: BEGIN
DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
DECLARE CONTINUE HANDLER
FOR table_does_not_exist
--Tabla donde graba datos de cuadre de tablas
create multiset volatile table ComparaTabla(
col_nombre varchar(30),
col_valor varchar(300),
cruce char(5) compress('X', 'TNEW', 'TOLD', 'Error'),
qt_filas_old integer,
qt_filas_new integer
)primary index(col_nombre)
on commit preserve rows;/*raises exception 42000*/
delete from ComparaTabla where col_nombre=:ANA_COL;
END tv;
Junior Supporter

Re: Using Volatile table in stored procedure

Hi.

If you use a cursor as a resulset for returning rows to a user and this cursor selects rows from a volatile table, then you must declare it as a dynamic cursor, because the volatile table does not exist in the moment of the cursor declaration.

HTH.

Cheers.

Carlos.