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.'.
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.
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.
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
DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
DECLARE CONTINUE HANDLER
--Tabla donde graba datos de cuadre de tablas
create multiset volatile table ComparaTabla(
cruce char(5) compress('X', 'TNEW', 'TOLD', 'Error'),
on commit preserve rows;/*raises exception 42000*/
delete from ComparaTabla where col_nombre=:ANA_COL;
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.