Oracle: обновить таблицу, используя динамические имена столбцов

Я использую Oracle 11g. Мои таблицы включают такие столбцы, как имя и l_name (строчные буквы столбца имени). Я пытаюсь перебрать все столбцы в моем табличном пространстве, чтобы установить столбцы l_ в нижний регистр соответствующих столбцов в верхнем регистре. Вот что я пробовал:

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;

Я сохраняю все имена столбцов в верхнем регистре в my_temp_storage и обновляю таблицу МНИЖНИМ значением столбцов в my_temp_storage. Это дало мне сообщение об ошибке:

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

Но вывод СУБД выглядел нормально:

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

Не могли бы вы помочь мне так, как я это сделал, или как-то иначе?


oracle oracle-sqldeveloper dynamic-columns oracle11g dynamicquery
person rav    schedule 10.11.2016    source источник
comment
Это единственная вещь на моем рабочем листе, а строка 8 содержит оператор «выполнить немедленно».   -  person rav    schedule 10.11.2016
comment
У вас нет begin и end вокруг этого? И заявление для my_temp_storage?   -  person Alex Poole    schedule 10.11.2016
comment
Есть ли у вас какие-либо таблицы или столбцы с зарезервированными словами или ключевыми словами и/или идентификаторами в кавычках? Что произойдет, если вы запустите это, закомментировав немедленное выполнение?   -  person Alex Poole    schedule 10.11.2016
comment
Мне жаль. Я не правильно понял ваш первый комментарий. Я думал, что вы говорите в контексте вышеприведенного кода. У меня есть все это, но я только что опубликовал свою логическую часть. Даже тогда ошибка отображается в строке с оператором выполнения!   -  person rav    schedule 10.11.2016
comment
Как я уже писал, вывод dbms работает нормально. Если я прокомментирую строку выполнения, все запросы будут напечатаны на выходе   -  person rav    schedule 10.11.2016
comment
Является ли показанный вами пример с name последним выводом, который вы получаете перед ошибкой? Если нет, то что? Пробуя ваш код, name не выдает эту ошибку.   -  person Alex Poole    schedule 10.11.2016
comment
Это единственная строка, которую я вижу в консоли. Итак, я подумал, что может произойти то, что он запускает первую строку вывода dbms и выдает ошибку в строке выполнения.   -  person rav    schedule 10.11.2016
comment
Подождите, результат точно такой же, как вы показали, с одинарными кавычками вокруг всего оператора обновления? Это получит ORA-00900, но опубликованный вами код не генерирует вывод или оператор с кавычками... Без кавычек все в порядке, как вы сказали в вопросе.   -  person Alex Poole    schedule 10.11.2016
comment
Правда без кавычек. Я думаю, что дважды выбрал код при публикации, который открыл эти кавычки.   -  person rav    schedule 10.11.2016

Ответы (1)


arrow_upward
2
arrow_downward

Программу, конечно, можно упростить:

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;

Однако это кажется странным дизайном базы данных. Рассматривали ли вы виртуальные столбцы и/или индексы на основе функций вместо столбцов, поддерживаемых вручную?

person Tony Andrews    schedule 10.11.2016
comment
Сначала пробовал что-то подобное. Но LOWER('||substr(i.columm_name,3)) возвращает подстроку из того же столбца (то есть столбца L_). - person rav; 10.11.2016
comment
Это подстрока имя столбца, а не его значение, так что это не имеет значения? - person Tony Andrews; 10.11.2016
comment
Это работает. Большое спасибо. То, как я пробовал раньше, это: LOWER(substr(' ||i.columm_name|| ',3) который фактически давал строчные значения того же столбца - person rav; 10.11.2016