How to pass column name as a variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to pass column name as a variable

  1. #1
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    15

    How to pass column name as a variable

    How can I pass a column name as a parameter to a SQL statement ?
    I have a table with columns named Log1,Log2,...,Log10.
    I want to run a loop and for each cicle to update the alue in LogX column.
    This is my code :
    <<
    DECLARE
    logX myTable.Log1%TYPE;
    BEGIN
    for i IN 1..11 LOOP
    logX := CONCAT('Log',TO_CHAR(i,'99'));
    UPDATE myTable set &logX='010100000000000';
    end loop;
    END;
    >>
    "logX" is the varaible that contais the current column name.
    Thanks,
    Richard

  2. #2
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    You can build a sql statement in a character string
    and then use the execute immediate command to run it.

    Here's an example. I'm using a bind variable to hold
    the column I want to update. It could also be passed
    as a parameter to a stored procedure. The approach would
    be basically the same.


    Make a sample table and load it.

    sys@AMSAA> create table testit
    as select * from tab;

    table created.

    Here's the test data.

    sys@AMSAA> select tname from testit;

    TNAME
    ------------------------------
    BONUS
    DEMO
    DEPT
    EMP
    PLAN_TABLE
    SALGRADE
    T1
    TESTIT

    8 rows selected.


    Now we'll define a bind variable for the field we want to update.

    sys@AMSAA> variable myfield varchar2(30);
    sys@AMSAA> exec :myfield := 'tname';

    PL/SQL procedure successfully completed.

    Now let's define a new value for that field

    sys@AMSAA> variable myval varchar2(30);
    sys@AMSAA> exec :myval := 'green';

    PL/SQL procedure successfully completed.

    sys@AMSAA> select :myfield from dual;

    :MYFIELD
    --------------------------------
    tname


    sys@AMSAA> select :myval from dual;

    :MYVAL
    --------------------------------
    green


    You would typically not want to use :myval the way I have.
    You'd want to use it as a bind variable in the execute immediate command so that your SQL is reusable. I left that out for clarity. I'm also going to update just the first row in this example.

    sys@AMSAA> declare
    2 l_sql varchar2(2000);
    3 begin
    4 l_sql := 'update testit set ' ||
    5 :myfield ||' = ''' || :myval || ''' ' ||
    6 'where rownum = 1';
    7 execute immediate (l_sql);
    8 end;
    9
    10 /

    PL/SQL procedure successfully completed.

    sys@AMSAA> select tname from testit;

    TNAME
    ------------------------------
    green
    DEMO
    DEPT
    EMP
    PLAN_TABLE
    SALGRADE
    T1
    TESTIT

    8 rows selected.

  3. #3
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    15
    Thanks

Posting Permissions

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



Click Here to Expand Forum to Full Width