All Forums Database
jhangilp 7 posts Joined 10/15
27 Oct 2015
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- procedure
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.

MeenakshiM 11 posts Joined 10/15
19 Jul 2016

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.

AtardecerR0j0 71 posts Joined 09/12
19 Jul 2016

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;

 

Be More!!

CarlosAL 512 posts Joined 04/08
19 Jul 2016

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.

You must sign in to leave a comment.