Объединение regexp_replace в listagg: слишком длинный результат (ошибка SQL: ORA-01489)

Я создал pl/sql procedure для пакета, который выполняет согласование между наборами таблиц, которые должны совпадать.

Я использую listagg для объединения имен столбцов текущего имени таблицы в цикле в строку, используемую в динамическом операторе SQL, который сравнивает две таблицы (34 набора, зацикленные для каждого имени таблицы).

Процедура сработала как положено, но результат неожиданно с минусом вернул. После исследования я определил, что некоторые поля содержат символ HEX (00), полученный в плоском файле, который заполняет данные только данными с одной стороны разведки. Чтобы учесть специальные символы, я добавил regexp_replace, соединенный в строке со listagg в выборе имени столбца, чтобы он выводил полные результаты listagg с каждым именем столбца, заключенным в regexp_replace.

Оно работает. Однако в некоторых таблицах более сотни столбцов, и listagg не работает, если результат превышает 4000 символов.

Есть ли лучший способ сделать все это?

Вот код:

Собирает имена столбцов в список, разделенный запятыми (символ запятой объединяется в саму строку для использования в качестве разделителя при выборе динамического SQL ниже)

execute immediate
'SELECT ' || q'{listagg('regexp_replace(' || column_name || ', ''[^A-Z0-9 ]'', '''')',  '||'', '' || ')}' || ' within group (order by rownum) "COLUMN_NAME"
FROM user_tab_cols
where table_name =''' || csrpubtable.table_name || ''''

into v_column_names;

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

--Insert data to RECON_PUB_TABLES where record exists in FILE but not PROD
execute immediate
'INSERT INTO RECON_PUB_TABLES
SELECT ''' || csrpubtable.table_name || ''', ''FILE'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '
minus
SELECT ''' || csrpubtable.table_name || ''', ''FILE'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '@pub_recon2prod where trunc(' || v_lastupdate_column || ') <= trunc(to_date(''' || v_compare_date || ''', ''dd-MON-yy''))';

--Insert data to RECON_PUB_TABLES where record exists in PROD but not FILE
execute immediate
'INSERT INTO RECON_PUB_TABLES
SELECT ''' || csrpubtable.table_name || ''', ''PROD'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name || '@pub_recon2prod where trunc(' || v_lastupdate_column || ') <= trunc(to_date(''' || v_compare_date || ''', ''dd-MON-yy''))
minus
SELECT ''' || csrpubtable.table_name || ''', ''PROD'' , ' || v_column_names || ', trunc(sysdate) from ' || csrpubtable.table_name ;

oracle dynamic-sql plsql regexp-replace listagg
person Spared Technology    schedule 10.10.2018    source источник

Ответы (1)


arrow_upward
1
arrow_downward

varchar2 ограничен 32 КБ внутри plsql, если 32 достаточно, вы можете попробовать что-то вроде этого

create or replace procedure conc_col_names(tableName IN varchar2)  as 
  collist varchar2(32767); 
begin
  for xx in (select * from user_tab_columns where table_name = tableName order by column_name asc) loop
    if ( length(collist) > 0) then 
      collist := collist||','; 
    end if; 
    collist := collist||'regexp_replace('||xx.column_name||',''[^A-Z0-9 ]'')';
  end loop;

  /* add the rest code for comparing rows in the two table here  */

end; 
/
person Kristian Saksen    schedule 10.10.2018
comment
Спасибо, Кристиан. Я не рассматривал цикл для подобного объединения имен столбцов. Общее количество символов превышает 4000 (при отсутствии ошибок listagg), но я считаю, что оно намного меньше 32k, что я могу проверить. Я посмотрю на это и посмотрю, смогу ли я интегрировать его вместо listagg. - person Spared Technology; 11.10.2018
comment
Работает неплохо. Еще раз спасибо, Кристиан. - person Spared Technology; 31.10.2018