Oracle: update table using dynamic column names

Question

I am using Oracle 11g. My tables include columns like name and l_name (lowercase of name column). I am trying to iterate through all the columns in my table space to set the l_ columns to lowercase of their respective uppercase columns. Here is what I tried:

for i in (select table_name from user_tables) loop
    SELECT SUBSTR(column_name,3) bulk collect into my_temp_storage FROM user_tab_columns WHERE table_name = i.table_name and column_name like 'L\_%' escape '\';
    for j in (select column_name from user_tab_columns where table_name = i.table_name) loop
        for k in 1..my_temp_storage.count
        loop
            if(j.column_name like 'L\_%' escape '\' and SUBSTR(j.column_name,3) = my_temp_storage(k)) then
                DBMS_OUTPUT.PUT_LINE( 'update ' || i.table_name || ' set ' || j.column_name || ' = LOWER(' ||my_temp_storage(k)|| ') where ' || j.column_name || ' is not null');
                execute immediate 'update ' || i.table_name || ' set ' || j.column_name || ' = LOWER(' ||my_temp_storage(k)|| ') where ' || j.column_name || ' is not null';
            end if;
        end loop;
    end loop;
end loop;

I am storing all the names of columns in uppercase in my_temp_storage and updating the table with the LOWER value of the columns in my_temp_storage. This gave me an error saying:

Error report -
ORA-00900: invalid SQL statement
ORA-06512: at line 8
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

But the DBMS output seemed to be fine:

`update EMPLOYEE set L_NAME = LOWER(NAME) where L_NAME is not null`

Could you help me with the way I did or any other way it can be done?

This is the only thing on my work sheet and line 8 contains the ' execute immediate' statement — rav
– rav, Commented Nov 10, 2016 at 17:21
You don't have begin and end around this? And a declaration for my_temp_storage? — Alex Poole
– Alex Poole, Commented Nov 10, 2016 at 17:22
Do you have any tables or columns named with reserved words or keywords, and/or quoted identifiers? What happens if you run this with the execute immediate commented out? — Alex Poole
– Alex Poole, Commented Nov 10, 2016 at 17:23
I am sorry. I did not properly understand your first comment. I thought you were speaking in context of above posted code. I do have all that stuff, but I just posted my logic part. Even then the error is being shown up at the line with the execute statement! — rav
– rav, Commented Nov 10, 2016 at 17:27
As I have posted, the dbms output is running fine. If I comment the execute line, all the queries are being printed in the output — rav
– rav, Commented Nov 10, 2016 at 17:29

Tony Andrews · Accepted Answer · 2016-11-10 17:40:04Z

2

The program could certainly be simplified:

begin
    for i in (select table_name, column_name from user_tab_columns 
              where column_name like 'L\_%' escape '\') 
    loop
        l_sql := 'update ' || i.table_name || ' set ' || i.column_name 
                  || ' = LOWER(' ||substr(i.columm_name,3)
                  || ') where ' || i.column_name || ' is not null';
        execute immediate l_sql;
    end loop;          
end;

It seems an odd database design though. Have you considered virtual columns, and/or function-based indexes, instead of manually maintained columns?

edited Nov 10, 2016 at 17:40

answered Nov 10, 2016 at 17:27

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rav Over a year ago

I tried something similar at first. But, the LOWER('||substr(i.columm_name,3)) returns the sub string from the same column (that is L_ column).

Tony Andrews Over a year ago

It is substringing the name of the column not its value, so it doesn't matter?

rav Over a year ago

This is working. Thank you so much. The way I tried before is : LOWER( substr(' ||i.columm_name|| ',3) which actually gave lowercase values of same column

Collectives™ on Stack Overflow

Oracle: update table using dynamic column names

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related