为 Oracle 函数定义 CLOB 输入参数(或使用 CLOB 输入参数定义函数)

Question

我将运行如下所示的代码块：

CREATE OR REPLACE FUNCTION func_name (START_DATE NUMBER,END_DATE NUMBER, NAME clob)
.
.
.
select * from table_name
where name_desc in NAME
.
.
.
END;

其目的是创建一个函数，其输入参数之一是 CLOB 数据类型。但是通过执行我的代码，我得到以下错误：

ORA-01704：字符串文本太长

我在互联网上搜索了堆栈溢出问题，但没有得到任何后果。

你能帮我解决这个问题吗？

Answer 1

我相信我明白你想实现的目标。根据评论，您对IN的使用是错误的。IN需要文本列表或来自 select 的值列表，但您的 CLOB 值都不是，它只是一个长字符串，需要先处理，然后才能像您在评论中提到的那样在SELECT中使用它。

要使用用,分隔的名称列表来处理您的 CLOB，您可以在 CLOB 中找到第一个逗号并从 CLOB 开头提取值，直到将第一个分隔符和找到的值放入集合中(分隔符被删除 ofc 并修剪值，这可能是可选的，因为我不确定您的输入到底是什么样子(。接下来，从 CLOB 的开头删除找到的值并重复，直到此 CLOB 中没有要处理的内容。在集合中拥有值列表后，您可以将其用作原始SELECT中的SELECT。

试试这个例子：

CREATE TABLE table_name (
name_desc VARCHAR2(250) NOT NULL
);
INSERT INTO table_name (name_desc)
VALUES ('Lorem');
INSERT INTO table_name (name_desc)
VALUES ('ipsum');
INSERT INTO table_name (name_desc)
VALUES ('test');
COMMIT;
CREATE OR REPLACE TYPE name_list_tabt IS TABLE OF VARCHAR2(4000);
/
CREATE OR REPLACE PROCEDURE func_name(p_start_date NUMBER,
p_end_date   NUMBER,
p_name       CLOB) IS
v_delimiter VARCHAR2(1) := ',';
v_name CLOB := p_name;
v_delimiter_pos NUMBER;
v_name_value VARCHAR2(4000);
v_name_list name_list_tabt := name_list_tabt();
BEGIN
-- OTHER CODE ABOVE ...
LOOP
-- Get position of delimiter
v_delimiter_pos := dbms_lob.instr(v_name, v_delimiter, 1, 1);
-- If no comma is found we get rest of the CLOB for last name, if there is no more CLOB to process this will return 0
IF v_delimiter_pos = 0
THEN
v_delimiter_pos := dbms_lob.getlength(v_name);
END IF;
-- Get next name based on delimiter position, can cause error if single name is over 4000 bytes long
v_name_value := dbms_lob.substr(v_name, v_delimiter_pos, 1);
-- Next code will remove found name from CLOB start
v_name := regexp_replace(v_name, v_name_value, NULL, 1, 1);
-- If we reached the end of CLOB, exit
IF v_delimiter_pos = 0
THEN
EXIT;
END IF;
-- Add new name to collection if it is not empty
IF TRIM(REPLACE(v_name_value, v_delimiter)) IS NOT NULL
THEN
v_name_list.extend();
v_name_list(v_name_list.count) := TRIM(REPLACE(v_name_value, v_delimiter));
END IF;
END LOOP;
-- Your select in for loop just to make this code working, adjust as you need
FOR i IN (SELECT *
FROM table_name
WHERE name_desc IN (SELECT /*+ dynamic_sampling(t 2) */
column_value
FROM TABLE(v_name_list) t))
LOOP
dbms_output.put_line(i.name_desc); -- Just some test output
END LOOP;
-- OTHER CODE BELOW ...
END;
/
BEGIN
-- Example with different, even possibly incorrect values
func_name(p_start_date => 1,
p_end_date   => 2,
p_name       => 'Lorem,ipsum,dolor,sit,amet,consectetur, 
,,adipiscing,elit,Etiam,interdum,ligula,    ,     ,');
-- Based on table values and CLOB here output should be "Lorem" and "ipsum"
END;
/