Как вызвать REPLACE с CLOB (не превышая 32K)

Oracle 11g, безусловно, улучшил удобство использования CLOB, перегрузив большинство строковых функций, так что теперь они изначально работают с CLOB.

Однако коллега получил эту ошибку из своего кода:

ORA-22828: input pattern or replacement parameters exceed 32K size limit
22828. 00000 -  "input pattern or replacement parameters exceed 32K size limit"
*Cause:    Value provided for the pattern or replacement string in the form of
           VARCHAR2 or CLOB for LOB SQL functions exceeded the 32K size limit.
*Action:   Use a shorter pattern or process a long pattern string in multiple
           passes.

Это происходило только тогда, когда третьим параметром replace был CLOB с более чем 32 тыс. символов.

(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 — 64-битная версия)

Прецедент:

declare
  v2 varchar2(32767);
  cl_small clob;
  cl_big clob;
  cl_big2 clob;
begin
  v2 := rpad('x', 32767, 'x');
  dbms_output.put_line('v2:' || length(v2));
  cl_small := v2;
  dbms_output.put_line('cl_small:' || length(cl_small));
  cl_big := v2 || 'y' || v2;
  dbms_output.put_line('cl_big[1]:' || length(cl_big));
  cl_big2 := replace(cl_big, 'y', cl_small);
  dbms_output.put_line('cl_big[2]:' || length(cl_big2));
  cl_big2 := replace(cl_big, 'y', cl_big); 
  dbms_output.put_line('cl_big[3]:' || length(cl_big2));
end;
/

Полученные результаты:

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
ORA-22828: input pattern or replacement parameters exceed 32K size limit

Это кажется несовместимым с документами, которые подразумевают, что строка замены может быть CLOB - я бы подумал, что это должно подразумевать, что разрешены любые CLOB, а не только те, которые имеют размер ‹32K: http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions153.htm#SQLRF00697

oracle plsql oracle11gr2

Jeffrey Kemp 17.04.2014 источник

Ответы (6)

arrow_upward
8
arrow_downward

Вот грубый первый набросок функции, которая будет работать с некоторыми ограничениями, она еще не была хорошо протестирована:

function replace_with_clob
  (i_source in clob
  ,i_search in varchar2
  ,i_replace in clob
  ) return clob is
  l_pos pls_integer;
begin
  l_pos := instr(i_source, i_search);
  if l_pos > 0 then
    return substr(i_source, 1, l_pos-1)
        || i_replace
        || substr(i_source, l_pos+length(i_search));
  end if;
  return i_source;
end replace_with_clob;

Он выполняет только одну замену в первом экземпляре поискового запроса.

declare
  v2 varchar2(32767);
  cl_small clob;
  cl_big clob;
  cl_big2 clob;
begin
  v2 := rpad('x', 32767, 'x');
  dbms_output.put_line('v2:' || length(v2));
  cl_small := v2;
  dbms_output.put_line('cl_small:' || length(cl_small));
  cl_big := v2 || 'y' || v2;
  dbms_output.put_line('cl_big[1]:' || length(cl_big));
  cl_big2 := replace(cl_big, 'y', cl_small);
  dbms_output.put_line('cl_big[2]:' || length(cl_big2));
  cl_big2 := replace_with_clob(cl_big, 'y', cl_big); 
  dbms_output.put_line('cl_big[3]:' || length(cl_big2));
end;
/

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
cl_big[3]:131069

Jeffrey Kemp 17.04.2014

arrow_upward
1
arrow_downward

Вы можете создать функцию для обработки CLOB значений любой длины:

скрипт SQL

CREATE FUNCTION lob_replace(
  i_lob    IN clob, 
  i_what   IN varchar2, 
  i_with   IN clob,
  i_offset IN INTEGER DEFAULT 1,
  i_nth    IN INTEGER DEFAULT 1
) RETURN CLOB
AS
  o_lob  CLOB;
  n      PLS_INTEGER;
  l_lob  PLS_INTEGER;
  l_what PLS_INTEGER;
  l_with PLS_INTEGER;
BEGIN
  IF   i_lob IS NULL
    OR i_what IS NULL
    OR i_offset < 1
    OR i_offset > DBMS_LOB.LOBMAXSIZE
    OR i_nth < 1
    OR i_nth > DBMS_LOB.LOBMAXSIZE
  THEN
    RETURN NULL;
  END IF;

  n      := NVL( DBMS_LOB.INSTR( i_lob, i_what, i_offset, i_nth ), 0 );
  l_lob  := DBMS_LOB.GETLENGTH( i_lob );
  l_what := LENGTH( i_what );
  l_with := NVL( DBMS_LOB.GETLENGTH( i_with ), 0 );

  DBMS_LOB.CREATETEMPORARY( o_lob, FALSE );
  IF n > 0 THEN
    IF n > 1 THEN
      DBMS_LOB.COPY( o_lob, i_lob, n-1, 1, 1 );
    END IF;

    IF l_with > 0 THEN
      DBMS_LOB.APPEND( o_lob, i_with ); 
    END IF;

    IF n + l_what <= l_lob THEN
      DBMS_LOB.COPY( o_lob, i_lob, l_lob - n - l_what + 1, n + l_with, n + l_what );
    END IF;
  ELSE
    DBMS_LOB.APPEND( o_lob, i_lob );
  END IF;
  RETURN o_lob;
END; 
/

Настройка схемы Oracle 11g R2:

CREATE TABLE table_name ( value clob)
/

CREATE TABLE replacements ( str VARCHAR2(4000), repl CLOB )
/

DECLARE
  str VARCHAR2(4000) := 'value';
  r   CLOB;
  c1l CLOB;
  c1m CLOB;
  c1r CLOB;
  c2l CLOB;
  c2m CLOB;
  c2r CLOB;
  c3l CLOB;
  c3m CLOB;
  c3r CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY( r, FALSE );
  DBMS_LOB.CREATETEMPORARY( c1l, FALSE );
  DBMS_LOB.CREATETEMPORARY( c1m, FALSE );
  DBMS_LOB.CREATETEMPORARY( c1r, FALSE );
  DBMS_LOB.CREATETEMPORARY( c2l, FALSE );
  DBMS_LOB.CREATETEMPORARY( c2m, FALSE );
  DBMS_LOB.CREATETEMPORARY( c2r, FALSE );
  DBMS_LOB.CREATETEMPORARY( c3l, FALSE );
  DBMS_LOB.CREATETEMPORARY( c3m, FALSE );
  DBMS_LOB.CREATETEMPORARY( c3r, FALSE );
  FOR i IN 1 .. 10 LOOP
    DBMS_LOB.WRITEAPPEND( r, 4000, RPAD( 'y', 4000, 'y' ) );
    DBMS_LOB.WRITEAPPEND( C1m, 20, RPAD( 'x', 20, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C1r, 40, RPAD( 'x', 40, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C2m, 200, RPAD( 'x', 200, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C2r, 400, RPAD( 'x', 400, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C3m, 2000, RPAD( 'x', 2000, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C3r, 4000, RPAD( 'x', 4000, 'x' ) );
  END LOOP;
  DBMS_LOB.WRITEAPPEND( c1l, 5, str );
  DBMS_LOB.WRITEAPPEND( c1m, 5, str );
  DBMS_LOB.WRITEAPPEND( c1r, 5, str );
  DBMS_LOB.WRITEAPPEND( c2l, 5, str );
  DBMS_LOB.WRITEAPPEND( c2m, 5, str );
  DBMS_LOB.WRITEAPPEND( c2r, 5, str );
  DBMS_LOB.WRITEAPPEND( c3l, 5, str );
  DBMS_LOB.WRITEAPPEND( c3m, 5, str );
  DBMS_LOB.WRITEAPPEND( c3r, 5, str );
  FOR i IN 1 .. 10 LOOP
    DBMS_LOB.WRITEAPPEND( C1l, 40, RPAD( 'x', 40, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C1m, 20, RPAD( 'x', 20, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C2l, 400, RPAD( 'x', 400, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C2m, 200, RPAD( 'x', 200, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C3l, 4000, RPAD( 'x', 4000, 'x' ) );
    DBMS_LOB.WRITEAPPEND( C3m, 2000, RPAD( 'x', 2000, 'x' ) );
  END LOOP;
  INSERT INTO table_name VALUES ( NULL );
  INSERT INTO table_name VALUES ( EMPTY_CLOB() );
  INSERT INTO table_name VALUES ( '0123456789' );
  INSERT INTO table_name VALUES ( str );
  INSERT INTO table_name VALUES ( c1l );
  INSERT INTO table_name VALUES ( c1m );
  INSERT INTO table_name VALUES ( c1r );
  INSERT INTO table_name VALUES ( c2l );
  INSERT INTO table_name VALUES ( c2m );
  INSERT INTO table_name VALUES ( c2r );
  INSERT INTO table_name VALUES ( c3l );
  INSERT INTO table_name VALUES ( c3m );
  INSERT INTO table_name VALUES ( c3r );
  INSERT INTO replacements VALUES ( str, r );
  COMMIT;
END;
/

Запрос 1:

SELECT DBMS_LOB.GETLENGTH( value )
FROM   table_name

Результаты:

| DBMS_LOB.GETLENGTH(VALUE) |
|---------------------------|
|                    (null) |
|                         0 |
|                        10 |
|                         5 |
|                       405 |
|                       405 |
|                       405 |
|                      4005 |
|                      4005 |
|                      4005 |
|                     40005 |
|                     40005 |
|                     40005 |

Запрос 2:

UPDATE table_name
SET value = LOB_REPLACE(
              value,
              ( SELECT str FROM replacements ),
              ( SELECT repl FROM replacements )
            )

Запрос 3:

SELECT DBMS_LOB.GETLENGTH( value )
FROM   table_name

Результаты:

| DBMS_LOB.GETLENGTH(VALUE) |
|---------------------------|
|                    (null) |
|                         0 |
|                        10 |
|                     40000 |
|                     40400 |
|                     40400 |
|                     40400 |
|                     44000 |
|                     44000 |
|                     44000 |
|                     80000 |
|                     80000 |
|                     80000 |

MT0 31.10.2017

comment

Хорошая работа. Испытания успешно. В моих тестах на машине Exadata с 12.1.0.2 она немного медленнее. Чтобы выполнить все обновления 10 раз в цикле с lob_replace, потребовалось 1.161 1.278 1.298 секунд; replace_with_clob заняло 0.749 0.962 0.838 секунды. Было бы интересно сравнить производительность на 11g. - Jeffrey Kemp; 01.11.2017

arrow_upward
0
arrow_downward

Это сделает работу:

function CLOBREPLACE(
  AINPUT      CLOB,
  APATTERN    VARCHAR2,
  ASUBSTITUTE CLOB
) return CLOB is
FCLOB   CLOB := AINPUT;
FOFFSET INTEGER;
FCHUNK  CLOB;
begin
  if length(ASUBSTITUTE) > 32000 then
    FOFFSET := 1;
    FCLOB := replace(FCLOB, APATTERN, '###CLOBREPLACE###');
    while FOFFSET <= length(ASUBSTITUTE) loop
      FCHUNK := substr(ASUBSTITUTE, FOFFSET, 32000) || '###CLOBREPLACE###';
      FCLOB := regexp_replace(FCLOB, '###CLOBREPLACE###', FCHUNK);
      FOFFSET := FOFFSET + 32000;
    end loop;
    FCLOB := regexp_replace(FCLOB, '###CLOBREPLACE###', '');
  else 
    FCLOB := replace(FCLOB, APATTERN, ASUBSTITUTE);
  end if;
  return FCLOB;
end;

Результат тестового примера:

v2:32767
cl_small:32767
cl_big[1]:65535
cl_big[2]:98301
cl_big[3]:131069

idavid2013 12.04.2017

comment

Извините, не внимательно прочитал вопрос. Не полное решение. - idavid2013; 12.04.2017

comment

Одна проблема все еще остается (когда результат CLOB становится большим: около 1000000 байт в моем случае): ORA-22926: указанная длина обрезки больше, чем длина текущего значения LOB. Чтобы избежать этого, используется regexp_replace, но его нельзя применить к исходному шаблону, если только вы не уверены, что он не содержит символов regexp. - idavid2013; 14.04.2017

arrow_upward
-1
arrow_downward

Функция REPLACE может использоваться для CLOB

Документ: http://psoug.org/reference/translate_replace.html

Рабочий образец:

declare
  l_clob clob;
  l_parname clob;
  l_value  clob;
  l_par_id_obj        varchar2(200) := '${id_obj}';

  procedure setCLOBValue(p_CLOB in out CLOB, p_value string) as
  begin
    DBMS_LOB.createtemporary(p_CLOB, false);
    dbms_lob.open(p_CLOB, dbms_lob.lob_readwrite);
    dbms_lob.write(p_CLOB, length(p_value), 1, p_value);
    dbms_lob.close(p_CLOB);
  end;

begin
  select SOURCE_FILE into l_clob from st_static_source_clob;

  setCLOBValue(l_parname, l_par_id_obj);
  setCLOBValue(l_value, '200701000024');

  l_clob := replace(l_clob, l_parname, l_value);

end;

user2380723 02.04.2016

comment

Нет, ваша стоимость замены слишком мала, чтобы воспроизвести проблему, которая была изначально поднята. Этот вопрос касался значений замены, превышающих 32K. - Jeffrey Kemp; 02.04.2016

arrow_upward
-1
arrow_downward

Ниже функция решает проблему:

create or replace FUNCTION replace_clob
(
  in_source  IN CLOB,
  in_search  IN VARCHAR2,
  in_replace IN CLOB
) 
RETURN CLOB 
IS
  l_pos pls_integer;
  out_replace_clob CLOB := in_source;
BEGIN
  l_pos := instr(in_source, in_search);

  IF l_pos > 0 THEN
  WHILE l_pos > 0 LOOP
    out_replace_clob := substr(out_replace_clob, 1, l_pos-1)
        || in_replace
        || substr(out_replace_clob, l_pos+LENGTH(in_search));
  l_pos := instr(out_replace_clob, in_search);
  END LOOP;
  RETURN out_replace_clob;
  END IF;

  RETURN in_source;
END replace_clob;
/

Anjali Sharma 13.03.2020

comment

Хорошая попытка, хотя это страдает от возможности бесконечного цикла. - Jeffrey Kemp; 14.03.2020

arrow_upward
-1
arrow_downward

Эта функция ниже заменяет все функции для типа данных CLOB в Oracle PLSQL.

CREATE FUNCTION lob_replaceall
(
  i_lob    IN clob, 
  i_what   IN varchar2, 
  i_with   IN clob,
  i_offset IN INTEGER DEFAULT 1
) RETURN CLOB
AS
  o_lob  CLOB;
  n      PLS_INTEGER;
  l_lob  PLS_INTEGER;
  l_what PLS_INTEGER;
  l_with PLS_INTEGER;
  l_temp_lob CLOB;
BEGIN
  IF   i_lob IS NULL
    OR i_what IS NULL
    OR i_offset < 1
    OR i_offset > DBMS_LOB.LOBMAXSIZE
  THEN
    RETURN NULL;
  END IF;
  l_temp_lob:=i_lob;
  LOOP 
  n      := NVL( DBMS_LOB.INSTR( l_temp_lob, i_what, i_offset, 1 ), 0 );
  l_lob  := DBMS_LOB.GETLENGTH( l_temp_lob );
  l_what := LENGTH( i_what );
  l_with := NVL( DBMS_LOB.GETLENGTH( i_with ), 0 );

  DBMS_LOB.CREATETEMPORARY( o_lob, FALSE );
  IF n > 0 THEN
    IF n > 1 THEN
      DBMS_LOB.COPY( o_lob, l_temp_lob, n-1, 1, 1 );
    END IF;

    IF l_with > 0 THEN
      DBMS_LOB.APPEND( o_lob, i_with ); 
    END IF;

    IF n + l_what <= l_lob THEN
      DBMS_LOB.COPY( o_lob, l_temp_lob, l_lob - n - l_what + 1, n + l_with, n + l_what );
    END IF;
  ELSE
    DBMS_LOB.APPEND( o_lob, l_temp_lob );
  END IF;
  EXIT WHEN n=0;
  l_temp_lob:= o_lob;
  o_lob := NULL;
 END LOOP;
  RETURN o_lob;
END; 
/

Ramana 10.03.2021

comment

не круто копировать чужую работу - Jeffrey Kemp; 12.03.2021

comment

Привет @Jeffery Kemp ... Я согласен, что скопировал .. Я просто расширил функциональность, заменив все ... В моем требовании мне просто нужно было заменить все .. Я вставил сюда код, чтобы кто-то мог использовать без каких-либо изменений - Ramana; 13.03.2021

comment

Прошу прощения, я не заметил, что вы это сделали. Это не проблема взять чью-то работу, чтобы адаптировать ее, но это хороший тон, чтобы признать их работу, когда вы это делаете. В данном случае думаю не плохое решение. Возможно, если возможно, не помещайте createTemporary в цикл. Я не пробовал это, но я подозреваю, что проблема с производительностью может возникнуть, если нужно сделать много замен из-за всего необходимого копирования и временного пространства. Но это не всегда большая проблема в зависимости от требований. - Jeffrey Kemp; 14.03.2021

Как вызвать REPLACE с CLOB (не превышая 32K)

Ответы (6)

Вопросы по теме