Oracle - BULK COLLECT INTO VARRAY используется с Bind Variables только для сбора заголовков столбцов

Краткий отказ от ответственности. Прежде всего, я знаю, что сейчас предпочтительным способом обработки динамического SQL в Oracle является DBMS_SQL, но, к сожалению, у моей команды приложений нет грантов для выполнения этих процедур в данный момент, и я надеюсь, что этот быстрый обходной путь будет сбит до того, как наша команда администраторов баз данных свяжется со мной. Кроме того, эта база данных находится на Oracle 12c.

Цель сценария. Недавно я разработал хранимую процедуру (назовем ее Исходная), которая использует значения в контрольной таблице для выполнения большого количества обновлений определенных столбцов в базе данных с много схем и таблиц. Этот скрипт, с которым я сейчас мучаюсь (назовем его Test), предназначен для быстрого просмотра тех столбцов, на которые влияет Original, чтобы убедиться, что все работает должным образом. В конечном счете, я хочу вывести 5 лучших результатов каждого измененного столбца и передать буферный файл моей группе тестирования для проверки.

control_table, используемый в обоих сценариях, имеет 4 столбца и выглядит следующим образом:

OWNER TABLE_NAME COLUMN_NAME ALGORITHM
Schema1 TableA ColumnA Method1
Schema1 TableB ColumnB Method1
Schema2 TableC ColumnC Method2

Примером одной из таблиц, которая обновляется с помощью Исходного (скажем, для TableA выше), может быть:

OtherCol1 OtherCol2 ColumnA OtherCol3
Ignored Ignored UpdatedData1 Ignored
Ignored Ignored UpdatedData2 Ignored
Ignored Ignored UpdatedData3 Ignored

Проблема со сценарием Test: у меня есть динамический SQL - я полагаю - работает так, как нужно, и я пытался выяснить, как лучше всего распечатать результаты команды EXECUTE IMMEDIATE для вывода. Почитав немного, я обнаружил, что МАССОВЫЙ СБОР В должен позволить мне сохранять результаты динамических запросов в COLLECTION, которую я затем могу распечатать с помощью dbms_output. Я пытался сделать это как с TABLE, так и с VARRAY, но в обоих случаях когда я печатаю, я обнаруживаю, что данные, хранящиеся в моей коллекции, являются заголовком столбца моего динамического запроса, а не значениями запроса! Единственное, что, как мне кажется, может быть проблемой, это объединение BULK COLLECT INTO с командой USING при запуске динамического оператора, но я не видел в документации ничего, что указывало бы на то, что эти две команды несовместимы, и мой Test приведенная ниже процедура компилируется без проблем (и даже вроде работает нормально).

Тестовый скрипт:

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
    l_script VARCHAR2(500);
    l_errm  VARCHAR2(64);
    TYPE results IS VARRAY(5) OF VARCHAR2(250);
    va_cols results;  --Defining here with a VARRAY but I have also tried with a table
BEGIN
    FOR c_col IN(
        SELECT owner, table_name, column_name, algorithm FROM control_list)
    LOOP
        l_errm := NULL;
        va_cols := NULL;
        BEGIN
            dbms_output.put_line('Column '|| c_col.column_name || ' of table ' || c_col.owner || 
                    '.' || c_col.table_name || ' used algorithm ' || c_col.algorithm);
            
            l_script := 'SELECT :1 FROM ' || c_col.owner || '.' || c_col.table_name || 
                ' WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            dbms_output.put_line('Script sent to Exec Immediate: ' || l_script); --Print l_script for debugging
            
            EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols USING c_col.column_name, c_col.column_name;
            dbms_output.put_line(va_cols(1));
            dbms_output.put_line(va_cols(2));
            dbms_output.put_line(va_cols(3));
            dbms_output.put_line(va_cols(4));
            dbms_output.put_line(va_cols(5));

        EXCEPTION         
         WHEN OTHERS THEN   
            l_errm := SUBSTR(SQLERRM, 1, 64);
            dbms_output.put_line(' ERROR: ' || l_errm || '. Skipping row');
            CONTINUE;
        END;
        
    END LOOP;
  END;
/

Итак, мой предполагаемый dbms_output сценария выше:

Column ColumnA of table Schema1.TableA used algorithm Method1
Script sent to Exec Immediate: SELECT :1 FROM SCHEMA1.TABLEA WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY    
UpdatedData1
UpdatedData2
UpdatedData3
UpdatedData4
UpdatedData5

Вместо этого, однако, странно, что я получаю, когда запускаю это:

Column ColumnA of table Schema1.TableA used algorithm Method1
Script sent to Exec Immediate: SELECT :1 FROM SCHEMA1.TABLEA WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY     
ColumnA
ColumnA
ColumnA
ColumnA
ColumnA

Кто-нибудь видел это раньше и знает, что я делаю неправильно? Заранее спасибо!!


sql oracle dynamic-sql plsql bind-variables
person DanK    schedule 16.03.2021    source источник
comment
Единственный вопрос, почему вы не используете динамический курсор? Эта ссылка на документацию содержит пример, точный для ваш вариант использования. Хорошо, вам понадобится дополнительный внутренний цикл над именами столбцов...   -  person Marmite Bomber    schedule 16.03.2021
comment
@MarmiteBomber Потому что я не великий разработчик PLSQL :-) Я очень ценю, что вы назвали эту функциональность. Я рассмотрю это для любых будущих задач, требующих динамического SQL. Спасибо!   -  person DanK    schedule 16.03.2021
comment
Добавлен фрагмент PL/SQL в качестве стартовой помощи;) Удачи!   -  person Marmite Bomber    schedule 16.03.2021
comment
Большинство разработчиков сказали бы, что собственный динамический SQL является предпочтительным способом работы с динамическим SQL и что мы должны избегать DBMS_SQL без необходимости. Тем не менее, пакет по-прежнему важен, и по умолчанию он должен быть общедоступным. Если у вас нет доступа к этому пакету, есть вероятность, что кто-то неправильно применил правило безопасности — см. мой ответ здесь для объяснение того, как часто возникают эти проблемы.   -  person Jon Heller    schedule 17.03.2021
comment
@JonHeller А. Хороший звонок. Теперь я вижу ту же рекомендацию в DBMS_SQL документация: Native Dynamic SQL is an alternative to DBMS_SQL that lets you place dynamic SQL statements directly into PL/SQL blocks. In most situations, Native Dynamic SQL is easier to use and performs better than DBMS_SQL. However, Native Dynamic SQL itself has certain limitations: There is no support for so-called Method 4 (for dynamic SQL statements with an unknown number of inputs or outputs). Спасибо, что назвали это!   -  person DanK    schedule 18.03.2021

Ответы (2)


arrow_upward
2
arrow_downward

Вы не можете использовать переменные привязки, чтобы изменить столбцы, на которые вы ссылаетесь. Вы используете переменные связывания для указания конкретных значений во время выполнения. Когда вы делаете

l_script := 'SELECT :1 FROM ' || c_col.owner || '.' || c_col.table_name || 
                ' WHERE :2 IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            
EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols USING c_col.column_name, c_col.column_name;

вы говорите Oracle, что хотите выбрать литеральную строку в переменной c_col.column_name. Не столбец в таблице с таким именем. Вот почему каждая строка возвращает это буквальное значение.

Вам нужно будет динамически собрать оператор SQL с именами столбцов, а не пытаться использовать их в качестве переменных связывания. Так что-то вроде

l_script := 'SELECT ' || c_col.column_name || 
            ' FROM ' || c_col.owner || '.' || c_col.table_name || 
            ' WHERE ' || c_col.column_name || ' IS NOT NULL FETCH FIRST 5 ROWS ONLY';
            
EXECUTE IMMEDIATE l_script BULK COLLECT INTO va_cols;
person Justin Cave    schedule 16.03.2021
comment
Спасибо, Джастин Кейв! Это была именно моя ошибка. И я смущен, как я должен был знать лучше. Я структурировал схему запроса и таблицу с учетом этого, но я думаю, что размещение объектов в качестве данных заставило меня измениться. Благодарю вас за раскрытие моей ошибки! - person DanK; 16.03.2021

arrow_upward
1
arrow_downward

Это примерно то, что вы хотите. Я использую внешний курсор над таблицами и столбцами для проверки, которые генерируют динамический SQL.

Внутренний цикл чтения значений столбца из предыдущего запроса

DECLARE
  TYPE CurTyp  IS REF CURSOR;
  v_cursor        CurTyp;
  v_value         VARCHAR2(200);
  v_stmt_str      VARCHAR2(200);
BEGIN
    FOR c  IN (
        SELECT   table_name, column_name FROM control_list)
    LOOP
       dbms_output.put_line('tab: '||c.table_name);
       v_stmt_str := 'SELECT '||c.column_name||' FROM '|| c.table_name;
 
       OPEN v_cursor FOR v_stmt_str;

  
       LOOP
         FETCH v_cursor  INTO v_value;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('col: '||c.column_name||' val: '||v_value);
      END LOOP;
    END LOOP;
  CLOSE v_cursor;
END;
/
person Marmite Bomber    schedule 16.03.2021
comment
В порядке. Я вижу, что ты сделал. Это гладко. Спасибо за идею. Я обязательно буду использовать эти динамические курсоры в будущем. - person DanK; 16.03.2021