Скучно...опять балуюсь

Теперь баловство с объектом varchar2_list(list sys.odcivarchar2list) с конструкторами из строки с разделителем... Зачем? Скууучно!

» Код «

create or replace type varchar2_list as object(
     list sys.odcivarchar2list,
     CONSTRUCTOR FUNCTION varchar2_list( p_list IN VARCHAR2)
                 RETURN self AS result,
     CONSTRUCTOR FUNCTION varchar2_list( p_list IN VARCHAR2,
                                         p_delim IN VARCHAR2)
                 RETURN self AS result
)
/
CREATE OR REPLACE TYPE BODY varchar2_list IS
     CONSTRUCTOR FUNCTION varchar2_list( p_list IN VARCHAR2)
     RETURN self AS RESULT IS
     BEGIN
         EXECUTE IMMEDIATE
             'select sys.odcivarchar2list('||p_list||') from dual'
             INTO list;
         RETURN;
     EXCEPTION
         WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid list');
     END;

     CONSTRUCTOR FUNCTION varchar2_list( p_list IN VARCHAR2,
                                         p_delim IN VARCHAR2)
     RETURN self AS RESULT IS
     BEGIN
         EXECUTE IMMEDIATE
             'select sys.odcivarchar2list('
                                         ||replace(p_list,p_delim,',')
                                        ||') from dual'
             INTO list;
         RETURN;
     EXCEPTION
         WHEN OTHERS THEN
              RAISE_APPLICATION_ERROR(-20001, 'Invalid list');
     END;
END;
/

* This source code was highlighted with Source Code Highlighter.

Проверка:

with t as(
            select '1|2|3|4' str, '|' delim from dual
 union all select '5,6,7,8' str, ',' delim from dual
)
select *
from t,
     table(varchar2_list(str,delim).list)

STR	DELIM	COLUMN_VALUE
1|2|3|4	|	1
1|2|3|4	|	2
1|2|3|4	|	3
1|2|3|4	|	4
5,6,7,8	,	5
5,6,7,8	,	6
5,6,7,8	,	7
5,6,7,8	,	8