All Forums Analytics
HaRsHaD 8 posts Joined 11/08
11 Nov 2008
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 ??RegardsHarshad [/font]

Adeel Chaudhry 773 posts Joined 04/08
11 Nov 2008

Hello,Search the forum and documentation for dynamic sql or DBC.SysEXECSQL.Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

HaRsHaD 8 posts Joined 11/08
11 Nov 2008

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.RegardsHarshad

Adeel Chaudhry 773 posts Joined 04/08
11 Nov 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

HaRsHaD 8 posts Joined 11/08
11 Nov 2008

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

joedsilva 505 posts Joined 07/05
11 Nov 2008

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).

HaRsHaD 8 posts Joined 11/08
12 Nov 2008

Hi,I am trying to accomplish the following procedure.replace PROCEDURE spSample(in v_tab varchar(10))BEGINDECLARE var varchar(20);DECLARE CONTINUE HANDLERFOR SQLEXCEPTIONBEGININSERT INTO Proc_Error_Table (:SQLSTATE, var,'Invalid Date');END;FOR cploop AS cpcursor CURSOR FOR select * from v_tabdosomthing....end for;end;Notice the select of cursor.FOR cploop AS cpcursor CURSOR FOR select * from v_tabI need to make 'v_tab 'as dynamic, its values is coming from a paramter from proc.RegardsHarshad

rgs 106 posts Joined 02/07
12 Nov 2008

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.

HaRsHaD 8 posts Joined 11/08
12 Nov 2008

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.regardsHarshad

HaRsHaD 8 posts Joined 11/08
13 Nov 2008

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.RegardsHarshad

Fred 1096 posts Joined 08/04
13 Nov 2008

Using "embedded SQL" in an external routine, you can DECLARE a cursor for a dynamic SQL statement that you PREPARE and DESCRIBE. Search for examples; details would be too lengthy to post here.It may be possible to use that approach in an "external Stored Procedure" that CONTAINS SQL in TD12 or later. But you cannot do so within a SQL Stored Procedure.

rgs 106 posts Joined 02/07
13 Nov 2008

There is no USER01.sysexecsql. It is always dbc.sysexecsql (it is a built in call that tells the SP compiler that this is a dynamic SQL statement and to generate appropriate code for it. I can’t imagine having a requirement to process any table in your cursor processing loop. There must be a finite set of table formats that would satisfy your processing loop .At a minimum it would have to know the column names and what the columns represent. You can’t find that information out in a stored procedure. It is a goal you cannot achieve given the stored procedure architecture. I assume what you really mean is there is a certain set of tables which have the information in some of the columns that you want to process in your cursor loop. Here is an example of a way to get around that. Make your own cursor processing loop. I would still think of a relational SQL logic solution to solve your problem rather than a slow single thread processing loop. The following is not a complete example but there should be enough there for you to work with.PROCEDURE mine(tblename VARCHAR(30))BEGIN DECLARE loop_counter INTEGER;--- create my fixed name table to use with the fake cursor loop CREATE MULTISET VOLATILE TABLE mylist, NO FALLBACK, CHECKSUM = DEFAULT, LOG (Sequence Integer, other columns…) UNIQUE PRIMARY INDEX (Sequence) ON COMMIT PRESERVE ROWS;--- now populate the volatile table from the table name specified Call dbc.sysexecsql(‘insert into mylist SELECT CSUM(1, c1), c1 … FROM’ || tblname ‘;’);SET loop_counter = 1;Myloop: LOOP BEGIN Select … into …. From mylist where loop_counter= Sequence; If (SQLSTATE = ‘02000’) THEN LEAVE Myloop; END IF; …. Do my processing; SET loop_counter = loop_counter+1; END END LOOP;

HaRsHaD 8 posts Joined 11/08
17 Nov 2008

Hi rgs & Fred,Thanks for your nice inputs.For this time I have managed to resolve this issue by creating one database table.And selecting from this table is SELECT's.But in future I will keep your inputs to resolve such issue while working with TD12. :-)Thanks & regardsHarshad

prashanth.bose 4 posts Joined 03/10
15 Mar 2010

what if i am passing different queries at different times and i am not sure about the column no's, how can i know the column no's while fetching the records from the dynamic cursor

Prashanth Bose

You must sign in to leave a comment.