Can not use volatile table inside the cursor

Database
Fan

Can not use volatile table inside the cursor

I need to get the query result in the form of dataset/resultset/tabular format, just like when we get the result executing a query. so that I can bind the result in my application.

Now I have created one SP. And inside that I have created one volatile table and inserted data in that table and from that volatile table. And trying to get the rows in the volatile table to a cursor. And from that cursor I wll get the result set.

But inside the cursor it is not able to find the volatile table.

Error message I got -- can not found table myTempTable.

Below is just the format that I am tryint to use.

replace procedure getTestData

(

  in  STRID VARCHAR(13)


dynamic result sets 1

BEGIN

DECLARE sql_stmt VARCHAR(1000);

DECLARE sql_stmt1 VARCHAR(1000);

begin

SET sql_stmt = 'CREATE VOLATILE TABLE myTempTbl ( STRID VARCHAR(20) ,STRNumber VARCHAR(20) ) 

ON COMMIT PRESERVE ROWS';

EXECUTE IMMEDIATE sql_stmt;

end;

begin

SET sql_stmt1 = 'insert into myTempTbl select top 10 STRID, STRNAME from tableName';

EXECUTE IMMEDIATE sql_stmt1;

end;

begin

DECLARE ResultCur CURSOR WITH RETURN ONLY TO CLIENT FOR

select *  from myTempTbl;

OPEN ResultCur;

end;

end;

Please help.

Thanks,

Arpan

Tags (1)
7 REPLIES

Re: Can not use volatile table inside the cursor

Hi I also have the same issue. How did it go? 

Hi Guys,

I am really struggling with this one. I am trying to convert SYBASE Stored Procedures to Teradata Stored Procedures and i am thinking of using Cursor or For Loop but I was getting compilation error in TD Assistant 14.0 that volatile table does not exists. Volatile tables are created at the beginning of the SP and the code in sybase for loop goes like this:

DECLARE Event_Cur CURSOR    FOR

SELECT  ATTRIBUTE,VALUE

FROM    #Event_Existing_Columns

OPEN  Event_Cur

FETCH Event_Cur INTO

         @ATTRIBUTE, @VALUE --X--  || @: Remove "@" and replace with prefix "v_"

WHILE (@@sqlstatus = 0) --X--  || @: Remove "@" and replace with prefix "v_"

BEGIN

IF @indicator=0 --X--  || @: Remove "@" and replace with prefix "v_"

BEGIN

SELECT @statement1=@statement1 + @ATTRIBUTE +', ' --X--  || @: Remove "@" and replace with prefix "v_"

IF @ATTRIBUTE IN ('trigger_event_dt','trigger_id','excptn_flg','rundate') AND LTRIM(@VALUE)IS NOT NULL --X--  || ltrim: TRIM (LEADING FROM string-expr) || @: Remove "@" and replace with prefix "v_"

BEGIN

SELECT @statement2=@statement2 +'"'+@VALUE+'", ' --X--  || @: Remove "@" and replace with prefix "v_"

END

END

END

FETCH Event_Cur INTO

@ATTRIBUTE, @VALUE --X--  || @: Remove "@" and replace with prefix "v_"

END //while

CLOSE Event_Cur

So to simplify, I developed a For Loop but seems not working, or any other looping (I tried my best before consulting you guys) Is there any way to handle the conversion without using Looping to scan through all data in the table? As you can see it is using the columns too set a variable declared inside my sp.

FOR loopvar AS  Event_Cur CURSOR FOR

    SELECT  CASEATTRIB,VAL

    INTO

    FROM VT_EVENT_EXISTING_COLUMNS 

DO

     IF V_INDICATOR = 0  THEN

         SET V_STATEMENT1 =   V_STATEMENT1 || V_ATTRIBUTE ||  ', ' ;

           IF V_ATTRIBUTE IN ('TRIGGER_EVENT_DT','TRIGGER_ID','EXCPTN_FLG','RUNDATE')  AND TRIM(TRAILING FROM V_VALUE)IS NOT NULL THEN

                SET V_STATEMENT2 = V_STATEMENT2 || '"' || V_VALUE || '", ' ;  

           ELSE

                SET V_STATEMENT2 = V_STATEMENT2 ||  COALESCE(TRIM(TRAILING FROM V_VALUE),'NULL')  || ', '; 

          END IF;

    END IF;

END FOR;

Please advise. Appreciate your immediate feedback.

Kind regards,

Abegail Bautista

Technical Consultant

Senior Apprentice

Re: Can not use volatile table inside the cursor

When you define a cursor the table you're selecting from must exist.

So there's a simple solution: create that table before you create the SP.

Another solution would involve a dynamic cursor using PREPARE:

begin
SET sql_stmt = 'select * from myTempTbl;';
DECLARE ResultCur CURSOR WITH RETURN ONLY FOR stmt;
PREPARE stmt FROM sql_stmt;
OPEN ResultCur;
end;

In both cases you have to include some logic to determine if the table already exists and then drop/delete it, otherwise it will fail when called a 2nd time within the same session. And when you REPLACE the SP you have to create the Volatile table again.

In your case (table definition is already known in advance) i would recommend a Global Temporary table, then it's just a simple "DELETE FROM gtt;".

You might also be able to avoid the temp table by simply declaring a cursor directly on the select statement used to populate the temp table.

Dieter

Re: Can not use volatile table inside the cursor

Hi Dieter, 

Thank you for your response, for my case, if you take a look at the original sybase code, it needs to read through records in the volatile table and get the column values and assigned it to local variables. HWhere  V_ATTRIBUTE And V_VALUE are local declared variables in my SP. 

At the beginning of the SP, I have a series of Create Volatile Table Statements before running a For Loop. I am getting a compilation error that volatile table does not exists. The requirement is to create a volatile table in SP. Is there any other way? We do not want to create GTT as per standard. I am an Oracle baby and is new to Teradata. Thanks Dieter. 

Senior Apprentice

Re: Can not use volatile table inside the cursor

I already wrote both possible solutions:

#1: create the volatile table once before you create the SP (and then drop it)

#2: do everything including the cursor with dynamic SQL

Btw, what is TRIM(TRAILING FROM V_VALUE)IS NOT NULL supposed to return?

Seems to be based on that strange Oracle behaviour treating an empty string as NULL. Better use V_VALUE <> ''.

Dieter

Re: Can not use volatile table inside the cursor

Hi Dieter, 

Thanks! I will let you know if I have any more concerns.  Thank you for pointing out that line in the code. Actually, I do not have any data set up yet on hand to test my conversion so It is like I am coding "blindly" for now until we get the Testing Environment ready. As long as I can compile it for now, I'm good. 

Enthusiast

Re: Can not use volatile table inside the cursor

Hi there...

I need to create cursor which makes changes for some group of logins.

Actuallty I know (more or less) how to create cursor with PL/SQL Oracle but I didn't have any oportunity to create one in Teradata.

Well the one I have to do have to run select's with dbuser login in it from pool of the logins and than run some sql functions with the result of this select.

Any help with proper cursor construction or cursor template in teradata?

Will try to create similar in default Oracle PL/SQL but I woulf like to hear some information from TD experienced developers like you.

Regards!

Enthusiast

Re: Can not use volatile table inside the cursor