Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL and For Loop Question
Dynamic SQL and For Loop Question [message #184496] Wed, 26 July 2006 16:27 Go to next message
ericr
Messages: 22
Registered: January 2006
Junior Member
Is it possible to use Dynamic SQL in the initial for loop statement?

I am looping over an array of table names.

Example:

    for i IN tables.FIRST..tables.LAST
    loop
        dSQL := 'SELECT col1,col2 FROM '||tables(g);

        for g IN (execute immediate(dSQL))
        loop
            --Processing for table: tables(g)
        end loop;
    loop


I have not had any luck getting this work. Am I doing something wrong or is this not possible?

Thanks...
Re: Dynamic SQL and For Loop Question [message #184508 is a reply to message #184496] Wed, 26 July 2006 18:40 Go to previous message
lzulueta
Messages: 10
Registered: July 2006
Location: Philippines
Junior Member
Your outer loop is actually good if your using an array but for the inner loop you need to use something like this:


declare
TYPE cur_typ IS REF CURSOR;
m cur_typ;
v_col1 tablename.colname%type;
v_col2 tablename.colname%type;
begin
..part where you get the table name
string1 := 'SELECT col1, col2 from '|| p_tabname;

open m for string1;
LOOP
FETCH m INTO v_col1, v_col2;
EXIT WHEN m%NOTFOUND;
..do processing here
end loop;
end;

hope this helped

[Updated on: Wed, 26 July 2006 18:41]

Report message to a moderator

Previous Topic: Query to find machine name
Next Topic: HELP PLEASE WITH THE ALPHABET :)
Goto Forum:
  


Current Time: Thu Dec 08 00:13:29 CST 2016

Total time taken to generate the page: 0.12382 seconds