问题：

如何使用CLOB调用REPLACE（不超过32K）

吕文林

2023-03-14

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.

只有当替换的第三个参数是具有超过32k个字符的CLOB时，才会发生这种情况。

（Oracle Database 11g Enterprise Edition 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，而不仅仅是那些碰巧是

共有3个答案

郏佐

2023-03-14

这将完成以下工作：

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

邵劲

2023-03-14

您可以创建一个函数来处理任意长度的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 |

羊舌阎宝

2023-03-14

以下是一个功能的初稿，该功能将在一定的限制下完成工作，它还没有经过很好的测试：

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

类似资料：

PHP/PDO调用过程，输出CLOB为json

当我在PHP中使用PDO驱动程序调用它时，如何从我的存储过程中输出CLOB？该过程在Oracle中是这样定义的： SCHE LOCT。ID %类型是数字(10) 如果我用TOAD调用它，它会工作: 输出结果: 因此，在PHP中，我尝试以下方法：但是甲骨文抛出：怎么了? 我尝试了许多与PDO:：PARAM_LOB|PDO:：PARAM_INPUT_OUTPUT的关联，但没有任何效果。感谢您的
Oracle CLOB不能插入超过4000个字符？

如何向CLOB类型列插入超过4000个字符？导致错误： ORA-01704:字符串太长。当我阅读Oracle参考时，可以最大节省4GB（千兆字节）？
如何使用Java反射调用超类方法

问题内容：我有两节课：我有一个实例B。如何调用A.method()从b？基本上，效果与super.method()从调用相同B。但是上述代码仍会调用。问题答案：如果使用的是JDK7，则可以使用MethodHandle实现此目的：
JavaScript如何使用超时调度Redux操作？

问题内容：我有一个操作可以更新应用程序的通知状态。通常，此通知将是错误或某种信息。然后，我需要在5秒钟后调度另一项操作，该操作会将通知状态恢复为初始状态，因此没有通知。其背后的主要原因是提供了5秒钟后通知自动消失的功能。我没有使用并返回其他动作的运气，也找不到在线完成的方法。因此，欢迎提出任何建议。问题答案：不要陷入[认为图书馆应该规定如何做每件事的陷阱。如果您想在JavaScript中执
如何使用Hibernate调用Oracle存储过程？

下面是我的Oracle存储过程，我得到以下异常：hibernate：{CALL REPORT_HIBERNATE（？）}org.hibernate.exception.GenericJDBcException：无法执行查询...并且由：java.sql.sqlException：无效的列索引引起 Plz让我知道在Spring中我将如何使用Hibernate注释调用这个存储过程？？
Hibernate@lob注释不再起作用：不能用几个字符串超过CLOB的总长度？

如何使用CLOB调用REPLACE（不超过32K）

共有3个答案

相关问答

相关文章

相关阅读

相关工具

相关文档