Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time

Database
Enthusiast

Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time

Hi there

I have 2 objects

- a table 

- stored procedure which just does a cursor for loop over the contents of the table.

However, when I execute my stored procedure, I am presented with the following error:

"Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time "

This error can be prevented by:

- removing all columns with the 'date' datatype from the table

- creating the table under another name, and adjusting the SP to iterate over this new table.

Does anyone have any ideas what the root cause of this problem is?

Thanks,

Andrew. 

3 REPLIES
Enthusiast

Re: Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time

So

I've managed to create a test case:

does anyone know what is happening?

create table xxx
(
JLU_EFFECTIVE_DATE DATE NOT NULL
);

commit;

replace procedure px()
begin
declare x int;
for i as j cursor for
select * from xxx
do
set x = x + 1;
end for;
end;

commit;

call px();
*** Error 7691 PX:Table used in cursor select is restructured from compil
e-time to run-time.
*** Total elapsed time was 1 second.

Enthusiast

Re: Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time

For anyone interested Teradata support have suggested setting

SET SESSION DATEFORM=INTEGER

when procedures are compiled.

Enthusiast

Re: Error 7691 P1 :Table used in cursor select is restructured from compile-time to run-time

I ran into the same problem.  I tried:

SET SESSION DATEFORM=INTEGERDATE;

Replace Proc....

Call Proc...

And got another fun error:

CALL Failed: [100047] Unsupported Teradata data type.

The interesting thing is that I wrote and ran the same proc on a different Teradata system, and it worked just fine.  When I ported it over to this box is when I started getting the error that brought me to this thread.