Dynamic Cursor For

Analytics
Enthusiast

Dynamic Cursor For

[font=Verdana]
Hi,
I am cerating a procedure, for that I need to make a cursor's Select statment dynamic.
e.g.

replace PROCEDURE spSample(in P_TAB varchar(10))
begin
for cploop as cpcursor cursor for
select * from p_tab
do
processing...
end for;
end;

But it results in error SPL1027:E(L12), Missing/Invalid SQL statement'E(3807):Object 'p_tab' does not exist.'.

I have tried by creating volatile/global temporary tables also but it results in same error.
So is there any workaround to embed or dynamically replace the table name in FROM clause of cursor's SELECT ??

Regards
Harshad

[/font]
13 REPLIES
Teradata Employee

Re: Dynamic Cursor For

Hello,

Search the forum and documentation for dynamic sql or DBC.SysEXECSQL.

Regards,

Adeel
Enthusiast

Re: Dynamic Cursor For

Hi Adeel,

Thanks for your reply.
But we cannot write SELECT in CALL dbc.SYSEXECSQL.
And more again, I need the CURSOR's SELECT to be dynamic as showed in above example.

Regards
Harshad
Teradata Employee

Re: Dynamic Cursor For

Unfortunately, I do know SELECT cannot be used with CALL. They are the keywords to be searched for. Because dynamic SQL is to be used for that, and for dynamic sql you have to use a stored-procedure named DBC.SysEXECSQL.

Thanks & Regards,

Adeel

Enthusiast

Re: Dynamic Cursor For

So, Is there any work around for call dbc.sysexesql('SELECT .... ?
Enthusiast

Re: Dynamic Cursor For

Depending on the details of what you are trying to accomplish, you might be able to do a insert/select into an interim table or so and then run your queries against it. (will work if the columns you will be selecting for any possible tables of interest are of the same data type).

Enthusiast

Re: Dynamic Cursor For

Hi,

I am trying to accomplish the following procedure.

replace PROCEDURE spSample(in v_tab varchar(10))
BEGIN
DECLARE var varchar(20);
DECLARE CONTINUE HANDLER
FOR SQLEXCEPTION
BEGIN
INSERT INTO Proc_Error_Table (:SQLSTATE, var,'Invalid Date');
END;

FOR cploop AS cpcursor CURSOR FOR
select * from v_tab
do
somthing....
end for;
end;

Notice the select of cursor.
FOR cploop AS cpcursor CURSOR FOR
select * from v_tab

I need to make 'v_tab 'as dynamic, its values is coming from a paramter from proc.

Regards
Harshad

rgs
Enthusiast

Re: Dynamic Cursor For

If you can avoid them, cursors are usually a bad idea on Teradata, because you end up processing all the data in serial fashion and not using the parallel processing capabilities of the database. I would think very hard about looking at ways to do “processing…” in terms of the relational model. Build SQL statements to do the processing and you will find it works a lot faster. If you just want to use cursors it would be easier to write a host application instead.

But to answer your question you can’t make the select statement dynamic for a cursor in a stored procedure. The closest you can come is to define a dynamic result set which is available in TD12, which does allow a dynamic select statement. But that means you can only return the final result to the client using that technique. You cannot process it in a cursor for loop.

As was mentioned you can use the call dbc.sysexecsql to insert/select the original dynamic table into a temporary table and then have a cursor loop process that temporary table that has a fixed name.

I assume that the tables are all identical in their column layout and naming convention other than the name of the table, otherwise you will have further issues to deal with.
Enthusiast

Re: Dynamic Cursor For

Hi rgs,

Thanks for your reply.

Yeah, you are right that I can’t make the select statement dynamic for a cursor in a stored procedure.
I am using TD7.1 .
But one point to be noticed that the tables are not all identical in their column layout.The input paramter(i.e. table name) for the procedure may can be always different table name.
And, I need to process all records of this table in cursor for loop.Hence I need the table name to be embeded dynamically in that cursor's select statment.The hardcoded cursor select works well.
Now for this dynamic case, I am looking to build SQL statments for processing.
Your further suggestions are welcome.

Thanks again.

regards
Harshad
Enthusiast

Re: Dynamic Cursor For

Hi rgs,

I tried your suggestion regarding temporary table.but during compilation time that temp. table is not resolve its refernces. To coreect this, firstly I need to create table as a seperate unit and the select from this table.
But my requiremnt is not like that.

replace procedure test(in p_tab varchar(20))
begin
CALL USER01.SYSEXECSQL('CREATE GLOBAL TEMPORARY TABLE TMP as ' ||p_tab|| 'on commit preserve rows');
CALL USER01.SYSEXECSQL('INSERT INTO TMP SELECT * FROM '||p_tab);
for c1 as cur cursor for
select * from tmp
do
insert into a values ('harshad');
end for;
end;

error:
SPL1027:E(L7), Missing/Invalid SQL statement'E(3807):Object 'tmp' does not exist.'.

(as I am selecting duting compilation time, I am getting this error).

If you have any suggestions,please do let me know.

Regards
Harshad