以CLOB作为输入参数并分割字符串的Oracle函数

Question

我创建了一个Oracle表函数，它可以删除和插入传入的值到我的表中。

是的，我知道函数不应该这样做，我必须使用存储过程，但不幸的是，我不能在另一个工具中使用存储过程，所以我误用了函数并添加了PRAGMA，所以函数也可以在主体中使用COMMIT。该函数按我想要的方式工作，但存在字符串字面值大于4000个字符的问题。我认为CLOB和DBMS_CLOB函数可以处理大字符串，但我仍然得到一个错误

ORA-01704:字符串字面值太长
01704。
*原因:字符串长度超过4000个字符。
*动作:使用最多4000个字符的字符串字面值。
较长的值只能通过绑定变量输入。

这是因为我正在传递超过4000个字符的非常大的字符串字面值，输入参数p_val被定义为CLOB并不重要，4000个字符总是传递字面值时的限制?

create or replace FUNCTION table_fn_test (p_val CLOB, p_action VARCHAR2, p_random NUMBER, p_user VARCHAR2)
RETURN my_t_tf_tab PIPELINED
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_count NUMBER :=0;
v_dup NUMBER :=0;
v_info  VARCHAR2(50) :='';
v_val CLOB;
v_index NUMBER := 1;
BEGIN
IF p_val IS NOT NULL THEN
v_val := p_val || ',';
WHILE DBMS_LOB.INSTR(v_val, ',', v_index) > 0
LOOP
IF p_action = 'insert' THEN
SELECT COUNT(*) INTO v_dup FROM my_table WHERE COL2 = TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)) AND COL1 =  RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' ');
IF v_dup = 0 THEN
INSERT INTO my_table(COL2,COL1) VALUES (TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)), RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' '));
v_count := v_count + SQL%ROWCOUNT;
v_info := v_count || ' row(s) inserted';
END IF;
ELSIF p_action = 'delete' THEN
DELETE FROM my_table WHERE COL2 = TRIM(DBMS_LOB.SUBSTR(v_val,DBMS_LOB.INSTR(v_val,'#',v_index)-v_index,v_index)) AND COL1 =  RPAD(' ' || TRIM(DBMS_LOB.SUBSTR( v_val,DBMS_LOB.INSTR(v_val,',',v_index)-DBMS_LOB.INSTR(v_val,'#',v_index)-1,DBMS_LOB.INSTR(v_val,'#',v_index)+1)),15,' ');
v_count := v_count + SQL%ROWCOUNT;
v_info := v_count || ' row(s) deleted';
END IF;
v_index := DBMS_LOB.INSTR(v_val, ',', v_index) + 1;
END LOOP;
COMMIT;
END IF;
FOR rec IN (SELECT COL1, COL2 from my_table )
LOOP
PIPE ROW(my_t_tf_tab(rec.COL2, rec.COL1 ));
END LOOP;
END table_fn_test;

Answer 1

4000个字符总是传递字面量的限制?

是的，除非您的DB配置为更大的32k限制。(从技术上讲，它是字节，而不是字符，这在这里可能很重要。)

问题出在你的呼叫，而不是功能。您将需要构造一个CLOB来传入——如何传入取决于您在哪里/如何调用它。

在SQL中，您可以将几个较短的字符串连接在一起:

select table_fn_test (
to_clob('less that 4000')
|| to_clob('less than 4000')
|| to_clob('less than 4000') /* etc */,
...)
from dual

这显然有点痛苦如果你有一个大字符串开始，你必须打破它，因为你复制和粘贴它。如果该值在文件中或从应用程序传递过来，那么您应该尽早读取并将其作为CLOB处理，因此它永远不会受到该限制。