Relational data from XML (part 2)

XMLQuery allowed us, for instance, to present the department/employee relationship the other way around as an other XML document.

What if we want to build tables (or views) based on our XML document? Then, we use XMLTable.

Queries:

-- 1 - DEPARTMENTS
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select dep.dep_id
      ,dep.name
      ,dep.location
from xmldepemp 
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/DEPARTMENTS/DEPARTMENT'
              passing xmldepemp.depemp
              columns dep_id varchar2(6)  path '@id'
                     ,name   varchar2(10) path 'NAME'
                     ,location varchar2(10) path 'LOCATION'
             ) dep
order by dep_id asc;

-- 2 - EMPLOYEES per DEPARTMENTS
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select emp.emp_id
      ,dep.dep_id
      ,emp.manager_id
      ,emp.name
      ,emp.hire_date
      ,emp.job
      ,emp.salary
from xmldepemp 
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/DEPARTMENTS/DEPARTMENT'
              passing xmldepemp.depemp
              columns dep_id    varchar2(6) path '@id'
                     ,employees xmltype     path 'EMPLOYEES/EMPLOYEE'
             ) dep
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/EMPLOYEE'
              passing dep.employees
              columns emp_id     varchar2(8)  path '@id'
                     ,manager_id varchar2(8)  path '@manager_id'
                     ,name       varchar2(16) path 'NAME'
                     ,hire_date  date         path 'HIRE_DATE'
                     ,job        varchar2(16) path 'JOB'
                     ,salary     number(6,0)  path 'SALARY'
             ) emp
;

Execution:

SQL> set linesize 130
SQL> set pagesize 1000
SQL> -- 1 - DEPARTMENTS
SQL> with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xm
lns")
  2                                                    ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) a
s "id")
  3                                                                                   ,xmlelement("NAME",dep.dname)
  4                                                                                   ,xmlelement("LOCATION",dep.loc)
  5                                                                                   ,xmlelement("EMPLOYEES",(select xmlagg(xmlel
ement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
  6
                              ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
  7
                ,xmlelement("NAME",emp.ename)
  8
                ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
  9
                ,xmlelement("JOB",emp.job)
 10
                ,xmlelement("SALARY",emp.sal)
 11
                ) order by emp.ename asc
 12
     )
 13                                                                                                            from emp emp
 14                                                                                                            where dep.deptno=em
p.deptno)
 15                                                                                              )
 16                                                                      ) order by dep.dname asc
 17                                                           )) depemp
 18                     from dept dep)
 19  select dep.dep_id
 20        ,dep.name
 21        ,dep.location
 22  from xmldepemp
 23      ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
 24               ,'/DEPARTMENTS/DEPARTMENT'
 25                passing xmldepemp.depemp
 26                columns dep_id varchar2(6)  path '@id'
 27                       ,name   varchar2(10) path 'NAME'
 28                       ,location varchar2(10) path 'LOCATION'
 29               ) dep
 30  order by dep_id asc;

DEP_ID NAME       LOCATION
------ ---------- ----------
DEP_10 ACCOUNTING NEW YORK
DEP_20 RESEARCH   DALLAS
DEP_30 SALES      CHICAGO
DEP_40 OPERATIONS BOSTON

SQL>
SQL> -- 2 - EMPLOYEES per DEPARTMENTS
SQL> with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xm
lns")
  2                                                    ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) a
s "id")
  3                                                                                   ,xmlelement("NAME",dep.dname)
  4                                                                                   ,xmlelement("LOCATION",dep.loc)
  5                                                                                   ,xmlelement("EMPLOYEES",(select xmlagg(xmlel
ement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
  6
                              ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
  7
                ,xmlelement("NAME",emp.ename)
  8
                ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
  9
                ,xmlelement("JOB",emp.job)
 10
                ,xmlelement("SALARY",emp.sal)
 11
                ) order by emp.ename asc
 12
     )
 13                                                                                                            from emp emp
 14                                                                                                            where dep.deptno=em
p.deptno)
 15                                                                                              )
 16                                                                      ) order by dep.dname asc
 17                                                           )) depemp
 18                     from dept dep)
 19  select emp.emp_id
 20        ,dep.dep_id
 21        ,emp.manager_id
 22        ,emp.name
 23        ,emp.hire_date
 24        ,emp.job
 25        ,emp.salary
 26  from xmldepemp
 27      ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
 28               ,'/DEPARTMENTS/DEPARTMENT'
 29                passing xmldepemp.depemp
 30                columns dep_id    varchar2(6) path '@id'
 31                       ,employees xmltype     path 'EMPLOYEES/EMPLOYEE'
 32               ) dep
 33      ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
 34               ,'/EMPLOYEE'
 35                passing dep.employees
 36                columns emp_id     varchar2(8)  path '@id'
 37                       ,manager_id varchar2(8)  path '@manager_id'
 38                       ,name       varchar2(16) path 'NAME'
 39                       ,hire_date  date         path 'HIRE_DATE'
 40                       ,job        varchar2(16) path 'JOB'
 41                       ,salary     number(6,0)  path 'SALARY'
 42               ) emp
 43  ;

EMP_ID   DEP_ID MANAGER_ NAME             HIRE_DAT JOB                  SALARY
-------- ------ -------- ---------------- -------- ---------------- ----------
EMP_7782 DEP_10 EMP_7839 CLARK            09/06/81 MANAGER                2450
EMP_7839 DEP_10 EMP_7839 KING             17/11/81 PRESIDENT              5000
EMP_7934 DEP_10 EMP_7782 MILLER           23/01/82 CLERK                  1300
EMP_7876 DEP_20 EMP_7788 ADAMS            23/05/87 CLERK                  1100
EMP_7902 DEP_20 EMP_7566 FORD             03/12/81 ANALYST                3000
EMP_7566 DEP_20 EMP_7839 JONES            02/04/81 MANAGER                2975
EMP_7788 DEP_20 EMP_7566 SCOTT            19/04/87 ANALYST                3000
EMP_7369 DEP_20 EMP_7902 SMITH            17/12/80 CLERK                   800
EMP_7499 DEP_30 EMP_7698 ALLEN            20/02/81 SALESMAN               1600
EMP_7698 DEP_30 EMP_7839 BLAKE            01/05/81 MANAGER                2850
EMP_7900 DEP_30 EMP_7698 JAMES            03/12/81 CLERK                   950
EMP_7654 DEP_30 EMP_7698 MARTIN           28/09/81 SALESMAN               1250
EMP_7844 DEP_30 EMP_7698 TURNER           08/09/81 SALESMAN               1500
EMP_7521 DEP_30 EMP_7698 WARD             22/02/81 SALESMAN               1250

14 ligne(s) sÚlectionnÚe(s).

SQL>

Views:

-- 1 - WTDF_V_XML_DEP
create or replace view WTDF_V_XML_DEP as
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select to_number(substr(dep.dep_id,5)) dep_id
      ,dep.name
      ,dep.location
from xmldepemp 
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/DEPARTMENTS/DEPARTMENT'
              passing xmldepemp.depemp
              columns dep_id varchar2(6)  path '@id'
                     ,name   varchar2(10) path 'NAME'
                     ,location varchar2(10) path 'LOCATION'
             ) dep
order by dep_id asc;

-- 2 - WTDF_V_XML_EMP
create or replace view WTDF_V_XML_EMP as
with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select to_number(substr(emp.emp_id,5))                                            emp_id
      ,to_number(substr(dep.dep_id,5))                                            dep_id
      ,decode(emp.emp_id,emp.manager_id,null,to_number(substr(emp.manager_id,5))) manager_id
      ,emp.name
      ,emp.hire_date
      ,emp.job
      ,emp.salary
from xmldepemp 
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/DEPARTMENTS/DEPARTMENT'
              passing xmldepemp.depemp
              columns dep_id    varchar2(6) path '@id'
                     ,employees xmltype     path 'EMPLOYEES/EMPLOYEE'
             ) dep
    ,xmltable(xmlnamespaces(default 'http://http://dataflows.blogspot.com/SCOTT/depemp/1')
             ,'/EMPLOYEE'
              passing dep.employees
              columns emp_id     varchar2(8)  path '@id'
                     ,manager_id varchar2(8)  path '@manager_id'
                     ,name       varchar2(16) path 'NAME'
                     ,hire_date  date         path 'HIRE_DATE'
                     ,job        varchar2(16) path 'JOB'
                     ,salary     number(6,0)  path 'SALARY'
             ) emp
;

Relational data from XML (part 1)

Query:

with xmldepemp as (select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                                                  ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                                                 ,xmlelement("NAME",dep.dname)
                                                                                 ,xmlelement("LOCATION",dep.loc)
                                                                                 ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                                           ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                                             ,xmlelement("NAME",emp.ename)
                                                                                                                                             ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                                             ,xmlelement("JOB",emp.job)
                                                                                                                                             ,xmlelement("SALARY",emp.sal)
                                                                                                                                             ) order by emp.ename asc
                                                                                                                                  )
                                                                                                          from emp emp
                                                                                                          where dep.deptno=emp.deptno)
                                                                                            )
                                                                    ) order by dep.dname asc
                                                         )) depemp
                   from dept dep)
select xmlquery('declare default element namespace "http://http://dataflows.blogspot.com/SCOTT/depemp/1"; (: :)
                 declare namespace qres="http://http://dataflows.blogspot.com/SCOTT/tmp_query_result"; (: :)
                 for $all in /DEPARTMENTS
                 return {
                   for $dep in $all/DEPARTMENT
                     for $emp in $dep/EMPLOYEES/EMPLOYEE
                     where $emp/HIRE_DATE[. > xs:date("1981-12-31")]
                     return 
                              {$emp/NAME/text()}
                              {$dep/NAME/text()}
                            
                 }'
                passing xdem.depemp
                returning content) emps
from xmldepemp xdem
;

Result:

  
    MILLER
    ACCOUNTING
  
  
    ADAMS
    RESEARCH
  
  
    SCOTT
    RESEARCH
  

Relational data as XML

A few things to consider:

• Provide a namespace (see line 2). It will save you from a lot of troubles later on.
• Provide non-empty keys (see lines 3, 27 & 28). In an integration scenario, you will want to provide unique invariable integration keys, possibly a GUID.
• Never include an empty xsd:date tag (see line 30).

Queries:

-- 1 - All departments in a single document
select xmlelement("DEPARTMENTS",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns")
                               ,xmlagg(xmlelement("DEPARTMENT",xmlattributes('DEP_'||to_char(dep.deptno) as "id")
                                                              ,xmlelement("NAME",dep.dname)
                                                              ,xmlelement("LOCATION",dep.loc)
                                                              ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                                                        ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                                                          ,xmlelement("NAME",emp.ename)
                                                                                                                          ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                                                          ,xmlelement("JOB",emp.job)
                                                                                                                          ,xmlelement("SALARY",emp.sal)
                                                                                                                          ) order by emp.ename asc
                                                                                                               )
                                                                                       from emp emp
                                                                                       where dep.deptno=emp.deptno)
                                                                         )
                                                 ) order by dep.dname asc
                                      )) depemp
from dept dep
;

-- 2 - A document per department
select xmlelement("DEPARTMENT",xmlattributes('http://http://dataflows.blogspot.com/SCOTT/depemp/1' as "xmlns"
                                            ,'DEP_'||to_char(dep.deptno) as "id")
                              ,xmlelement("NAME",dep.dname)
                              ,xmlelement("LOCATION",dep.loc)
                              ,xmlelement("EMPLOYEES",(select xmlagg(xmlelement("EMPLOYEE",xmlattributes('EMP_'||empno as "id"
                                                                                                        ,decode (mgr,null,'EMP_'||empno,'EMP_'||mgr) as "manager_id")
                                                                                          ,xmlelement("NAME",emp.ename)
                                                                                          ,case when emp.hiredate is null then null else xmlelement("HIRE_DATE",emp.hiredate) end case
                                                                                          ,xmlelement("JOB",emp.job)
                                                                                          ,xmlelement("SALARY",emp.sal)
                                                                                          ) order by emp.ename asc
                                                                               )
                                                       from emp emp
                                                       where dep.deptno=emp.deptno)
                                         )
                 ) depemp
from dept dep
order by dep.dname asc
;

Results:

<-- data-blogger-escaped---="--" data-blogger-escaped--="-" data-blogger-escaped-1="1" data-blogger-escaped-a="a" data-blogger-escaped-department="department" data-blogger-escaped-document="document" data-blogger-escaped-per="per">

  
    ACCOUNTING
    NEW YORK
    
      
        CLARK
        1981-06-09
        MANAGER
        2450
      
      
        KING
        1981-11-17
        PRESIDENT
        5000
      
      
        MILLER
        1982-01-23
        CLERK
        1300
      
    
  
  
    OPERATIONS
    BOSTON
    
  
  
    RESEARCH
    DALLAS
    
      
        ADAMS
        1987-05-23
        CLERK
        1100
      
      
        FORD
        1981-12-03
        ANALYST
        3000
      
      
        JONES
        1981-04-02
        MANAGER
        2975
      
      
        SCOTT
        1987-04-19
        ANALYST
        3000
      
      
        SMITH
        1980-12-17
        CLERK
        800
      
    
  
  
    SALES
    CHICAGO
    
      
        ALLEN
        1981-02-20
        SALESMAN
        1600
      
      
        BLAKE
        1981-05-01
        MANAGER
        2850
      
      
        JAMES
        1981-12-03
        CLERK
        950
      
      
        MARTIN
        1981-09-28
        SALESMAN
        1250
      
      
        TURNER
        1981-09-08
        SALESMAN
        1500
      
      
        WARD
        1981-02-22
        SALESMAN
        1250
      
    
  


<-- data-blogger-escaped---="--" data-blogger-escaped--="-" data-blogger-escaped-2="2" data-blogger-escaped-a="a" data-blogger-escaped-department="department" data-blogger-escaped-document="document" data-blogger-escaped-per="per">

  ACCOUNTING
  NEW YORK
  
    
      CLARK
      1981-06-09
      MANAGER
      2450
    
    
      KING
      1981-11-17
      PRESIDENT
      5000
    
    
      MILLER
      1982-01-23
      CLERK
      1300
    
  

Accessing CDM Ruleframe error stack.

Accessing CDM Ruleframe's error stack is usually a pain. The "canonical" method, that is, the one I used, was a tricky construction involving autonomous_session and dbms_pipe. Then, one day, I found something vastly better by Lucas Jellema.

It uses a view on a table function to access the error stack. Very neat.

Here is the cut and paste:

create or replace type string_tbl_type is table of(4000);
/

create or replace function get_qms_errors
return string_tbl_type
is
  l_string_tbl string_tbl_type:= string_tbl_type();
  l_message_rectype_tbl hil_message.message_tabtype;
  l_message_count number := 0;
  l_error         varchar2(2000);
  l_raise_error   boolean := false;
    procedure add(p_text in varchar2)
    is
    begin
      l_string_tbl.extend;
      l_string_tbl(l_string_tbl.last):= p_text;
    end add;
begin
   add('CG$Error Stack:');
   add('---------------');
   cg$errors.get_error_messages
   ( l_message_rectype_tbl
   , l_message_count
   , l_raise_error
   );
   if l_message_count > 0
   then
      for i in 1..l_message_count loop
         l_error := cg$errors.get_display_string
                    ( p_msg_code => l_message_rectype_tbl(i).msg_code
                    , p_msg_text => l_message_rectype_tbl(i).msg_text
                    , p_msg_type => l_message_rectype_tbl(i).severity
                    );
         add( l_error);
      end loop;
   end if;
   return l_string_tbl;
end;
/

create or replace view qms_errors
as
select*
from table(get_qms_errors)
/

select * from qms_errors;

drop view qms_errors;
/

drop function get_qms_errors;
/

drop type string_tbl_type;
/

SQL string tokenizer

There are many ways to tokenize a string in Oracle, most of them involving PL/SQL.
If you wish to use the API, at first glance and based upon its name, DBMS_UTILITY.COMMA_TO_TABLE seems to be a good solution. Well, it is not, because, among others: "The procedure fails if the string between separators is longer than 30 bytes".
You will have to dig a little deeper to find APEX_UTIL.STRING_TO_TABLE, or create your own function.

A good solution, detailed by ALEX NUIJTEN is to use a hierarchical query that uses a regular expression to transform your string into a list:

SQL> select regexp_substr('this,is,a,list','[^,]+',1,rownum) ord_list_element
  2  from dual
  3  connect by level<=length(regexp_replace('this,is,a,list','[^,]+'))+1
  4  ;

ORD_LIST_ELEME
--------------
this
is
a
list

SQL>

From there, you may wish to write your own COMMA_TO_TABLE:

SQL> CREATE OR REPLACE PACKAGE MY_UTILITY
  2  IS
  3    /* To be used instead of dbms_utility.lname_array */
  4    TYPE LNAME_ARRAY IS TABLE OF VARCHAR2(32667) INDEX BY BINARY_INTEGER;
  5    /* Tokenizes a comma separated list. */
  6    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  7                            ,TABLEN OUT BINARY_INTEGER
  8                            ,TAB OUT MY_UTILITY.LNAME_ARRAY);
  9  END MY_UTILITY;
 10  /

Package crÚÚ.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY MY_UTILITY IS
  2    /* Tokenizes a comma separated list. */
  3    PROCEDURE COMMA_TO_TABLE(LIST IN VARCHAR2
  4                            ,TABLEN OUT BINARY_INTEGER
  5                            ,TAB OUT MY_UTILITY.LNAME_ARRAY)
  6    IS
  7    BEGIN
  8        select regexp_substr(LIST, '[^,]+',1,rownum) astring
  9        bulk collect into TAB
 10        from dual
 11        connect by level<= length(regexp_replace(LIST, '[^,]+'))+1;
 12        TABLEN := TAB.count;
 13    END COMMA_TO_TABLE;
 14  END MY_UTILITY;
 15  /

Corps de package crÚÚ.

SQL>

Cleanup:

drop package my_utility;