Using Volatile table in stored procedure


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

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

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.


Re: Using Volatile table in stored procedure


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

Re: Using Volatile table in stored procedure


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.