Oracle PLSQL: Help Required:解析字符串集合或连接字符串

Question

每当字符串l_long_string的长度超过4000个字符时，以下代码将抛出错误:

ORA-01460: unimplemented or unreasonable conversion requested

而不是嵌套的regexp_substr查询，当我试图使用

SELECT column_value 
 FROM TABLE(l_string_coll)

它抛出:

ORA-22905: cannot access rows from a non-nested table item

如何修改动态查询?

指出:- l_string_coll类型为DBMS_SQL.VARCHAR2S，并且作为我的过程的输入(在这里，我刚刚显示为一个匿名块)-我将不得不管理没有在数据库模式中创建用户定义的类型，所以我使用内置的DBMS_SQL.VARCHAR2S。-这不是实际的业务流程，但接近于此。(无法发布原文)-动态查询必须在那里，因为我使用它来构建实际查询会话，当前应用程序模式名称等。

/*
CREATE TABLE some_other_table
(word_id NUMBER(10), word_code VARCHAR2(30), word VARCHAR2(255));
INSERT INTO some_other_table VALUES (1, 'A', 'AB');
INSERT INTO some_other_table VALUES (2, 'B', 'BC');
INSERT INTO some_other_table VALUES (3, 'C', 'CD');
INSERT INTO some_other_table VALUES (4, 'D', 'DE');
COMMIT;
*/
DECLARE
    l_word_count  NUMBER(10)      := 0;
    l_counter     NUMBER(10)      := 0;
    l_long_string VARCHAR2(30000) := NULL;
    l_dyn_query   VARCHAR2(30000) := NULL;
    l_string_coll DBMS_SQL.VARCHAR2S;
BEGIN
    -- l_string_coll of type DBMS_SQL.VARCHAR2S comes as Input to the procedure
    FOR i IN 1 .. 4100
    LOOP
        l_counter := l_counter + 1;
        l_string_coll(l_counter) := 'AB';
    END LOOP;
    -- Above input collection is concatenated into CSV string 
    FOR i IN l_string_coll.FIRST .. l_string_coll.LAST
    LOOP
        l_long_string := l_long_string || l_string_coll(i) || ', ';
    END LOOP;
    l_long_string := TRIM(',' FROM TRIM(l_long_string));
    dbms_output.put_line('Length of l_long_string = ' || LENGTH(l_long_string));
    /*
    Some other tasks in PLSQL done successfully using the concatenated string l_long_string
    */
    l_dyn_query := ' SELECT COUNT(*)
                     FROM   some_other_table
                     WHERE  word IN ( SELECT TRIM(REGEXP_SUBSTR(str, ''[^,]+'', 1, LEVEL)) word
                                      FROM   ( SELECT :string str FROM SYS.DUAL )
                                      CONNECT BY TRIM(REGEXP_SUBSTR(str, ''[^,]+'', 1, LEVEL)) IS NOT NULL )';
                     --WHERE  word IN ( SELECT column_value FROM TABLE(l_string_coll) )';
    EXECUTE IMMEDIATE l_dyn_query INTO l_word_count USING l_long_string;
    dbms_output.put_line('Word Count = ' || l_word_count);
EXCEPTION
    WHEN OTHERS 
    THEN
      dbms_output.put_line('SQLERRM = ' || SQLERRM);
      dbms_output.put_line('FORMAT_ERROR_BAKCTRACE = ' || dbms_utility.format_error_backtrace);
END;
/

Answer 1

如何修改动态查询?

首先。根据您提供的代码，完全不需要使用动态、本机或DBMS_SQL动态SQL。

其次，SQL不能操作长度大于4K字节(Oracle版本12c之前)或大于32K字节(Oracle版本12cR1及以上，如果MAX_STRING_SIZE初始化参数设置为EXTENDED)的"字符串"。

另一方面，

PL/SQL允许您使用长度大于4K字节(最多32Kb)的varchar2()字符串。如果您只需要计算逗号分隔的字符串中的单词，则可以简单地使用regexp_count()正则表达式函数(Oracle 11gr1及以上)，如下所示:

set serveroutput on;
set feedback off;
clear screen;
declare
  l_str        varchar2(100) := 'aaa,bb,ccc,yyy';
  l_numOfWords number;
begin
  l_numOfWords := regexp_count(l_str, '[^,]+');
  dbms_output.put('Number of words: ');
  dbms_output.put_line(to_char(l_numOfWords));   
end;

结果:

Number of words: 4