Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    13

    Unanswered: For Loop variable

    There might be a better solution, but in my Unix pre-programing if I has a list of variables i wanted to run through a test, i normally did this in a For loop statement. for example
    for x in ('list1','list1') do
    echo $x
    done
    .....
    now,
    I have two variables i would like to reiterate in the system; however, I'm a little stumpt on how to go about this
    I have to sites that I want to ask the same question, the site name are variables...
    what i want it to do more or less is
    some for loop statement like
    for x in ('site1','site2')
    select * from dba_table@(x variable)

    any suggestions?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    write SQL to write SQL
    or
    EXECUTE IMMEDIATE

    SQL statements must be complete "at compile time".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2007
    Posts
    13
    I got a little further, but I'm still not able to make the table or site dynamic,
    Here is what I have thus far.
    declare
    output varchar(20);
    begin
    for i in select 'site1' as site from dual
    union select 'site2'as site from dual) loop
    select 'Hi from '||i.site into output from dual;
    dbms_output.put_line(output);
    end loop;
    end
    /
    this works...But i was to be able to have the table dynamic

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    --fewer characters are need to simply do:
    select * from dba_table@site1;
    select * from dba_table@site2;
    -- in your editor of choice
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I have some old code in my library. Might be useful, although I don't remembering ever verifying it

    Code:
    DECLARE
        sqlstring VARCHAR2(100) := 'SELECT dc FROM nadiv where dc = ';
        type refcurtype is ref cursor;
        csr refcurtype;
        divcode NADIV.DC%TYPE;
    BEGIN
        sqlstring := sqlstring || '''08''';
        open csr for sqlstring;
        LOOP
    	fetch csr into divcode;
    	exit when csr%NOTFOUND;
    	DBMS_OUTPUT.PUT_LINE(divcode);
        END LOOP;
    END;
    There might be some new "10g" way of doing things, though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •