Volatile Table in Stored Procedure

Database
Enthusiast

Volatile Table in Stored Procedure

Hi everyone, 

I've created a stored procedure as follows:

replace PROCEDURE mydb.sp_Insert_Values ( 
   IN  lastExecDate timestamp 
) 
SQL SECURITY CREATOR 
BEGIN 

CREATE MULTISET VOLATILE TABLE vt_ref_table_1
(
    Ref_Id integer,
    Ref_Unit_Type varchar(50)
) ON COMMIT PRESERVE ROWS;

insert into mydb.vt_ref_table_1
select Ref_Id, Ref_Unit_Type
from mydb.ref_table_1;  

INSERT INTO mydb.Time_Series_Table
select
  t1.TD_TIMECODE
, t1.Time_Series_Meas
, t1.Time_Series_Curve_Type_CD 
, t1.Ref_Unit_Type
, t1.Ref_Id 
, t1.created_on
from
        (
            select 
            meas_ts as TD_TIMECODE 
            , ref_table_1.Ref_Id as  Ref_Id
            , meas as Time_Series_Meas,
            , Time_Series_Curve_Type_CD
            , Ref_Unit_Type
            , current_timestamp as created_on
            from mydb_stg.Time_Series_Table_Stg as stg
            left join mydb.ref_table_1 as ref_table_1
            on ref_table_1.Ref_Id = stg.Ref_Id
            where stg.created_on >= :lastExecDate
        ) as t1
        left join mydb.Time_Series_Table as t2
        on t1.TD_TIMECODE=t2.TD_TIMECODE
        and t1.Time_Series_Curve_Type_CD = t2.Time_Series_Curve_Type_CD
        and t1.Ref_Id=t2.Ref_Id
        where t2.Ref_Id is null
;   
END;

It compiles but when I call it this error is thrown:

Only a COMMIT WORK or null statement is legal after a DDL Statement.

I know the error is related to the volatile table but I don't know how to correct it.

Why I need the volatile table:

The reference table has a row-level-security constraint. If I use it directly I get another error:

A multi-table operation is executed and the tables do not have the same security constraints.

I simplified and anonymized the query. I know I still need to include some error handling. 

Teradata Version: 16.20

Mode: ANSI

 

Any comment will be appreciated. 

 

Kind Regards, 

Paul

Kind regards,
Paul Hernandez

Accepted Solutions
Highlighted
Senior Apprentice

Re: Volatile Table in Stored Procedure

Hi Paul,

 

The solution is as per the error message. After your 'create table' you MUST issue a COMMIT WORK before continuing. These are ANSI transaction rules.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES
Highlighted
Senior Apprentice

Re: Volatile Table in Stored Procedure

Hi Paul,

 

The solution is as per the error message. After your 'create table' you MUST issue a COMMIT WORK before continuing. These are ANSI transaction rules.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Volatile Table in Stored Procedure

Or consider using a Global Temporary Table instead. A materialized instance of a GTT would be created automatically when referenced in the INSERT, and not require COMMIT.

Enthusiast

Re: Volatile Table in Stored Procedure

Hi Fred, 

thanks for your answer.

Could you share a working script or a link with an example with me?

I tried a GTT and I had to include a COMMIT to make the sp compile. After that I had problems to drop the table and need to use the ALL keyword.

I think I need to review the theory about temporary tables by Teradata Robot Embarassed

Best, 

Paul

Kind regards,
Paul Hernandez
Junior Contributor

Re: Volatile Table in Stored Procedure

You create the Global Temp Table once before you create the SP, you never drop it.

It's a template which is materialized as a session local table when you Insert rows, i.e. multiple session can use the same GTT with different data in it without CREATE VOLATILE TABLE.

 

Similar to a VT your session local version is automaticaly dropped when your session ends.

If you want to drop it, you must use DROP VOLATILE TABLE, DROP TABLE drops the definition.

But when you run the SP multiple times within your session you can simply DELETE it.

Teradata Employee

Re: Volatile Table in Stored Procedure

Correct. The GTT "template" is created outside the SP and persists.

If you execute DML (e.g. INSERT or even DELETE) that appears to be targeting the template, and you don't already have a "temporary" instance for your session, one is automatically materialized.

 

If you really want to DROP the materialized instance before the session disconnects, use DROP TEMPORARY TABLE.

DROP TABLE ALL waits for all the materialized instances (including any for other sessions) to be dropped, then drops the "template" also.