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;

String aggregation & inline views

In the SCOTT schema, we have this:

SQL> select dept.dname
  2        ,emp.ename
  3  from dept
  4  left join emp
  5  on dept.deptno=emp.deptno
  6  order by 1 asc
  7          ,2 asc
  8  ;

DNAME          ENAME
-------------- ----------
ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
OPERATIONS
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       JONES
RESEARCH       SCOTT
RESEARCH       SMITH
SALES          ALLEN
SALES          BLAKE
SALES          JAMES
SALES          MARTIN
SALES          TURNER
SALES          WARD

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

SQL>

And we would very much prefer to have something like this:

DNAME         ENAMES
---------------------------------------------------
ACCOUNTING    CLARK,KING,MILLER
OPERATIONS
RESEARCH      ADAMS,FORD,JONES,SCOTT,SMITH
SALES         ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Let's have a look at some common string aggregation techniques, how they compare to each other and how a left join affect them.

Queries:

-- 1
select dept.dname
      ,emp.ename
from dept
left join emp
on dept.deptno=emp.deptno
order by 1 asc
        ,2 asc
;

-- 2
select dpte.dname
      ,listagg(dpte.ename,',') within group (order by dpte.ename) enames
from (select dept.dname
            ,emp.ename
      from dept
      left join emp
      on dept.deptno=emp.deptno) dpte
group by dpte.dname
order by 1 asc
;        

-- 3
select dept.dname
      ,emps.enames
from dept
left join (select deptno
                 ,listagg(ename,',') within group (order by ename) enames
           from emp
           group by deptno) emps
on dept.deptno = emps.deptno
order by 1 asc
;

-- 4
select dlst.dname
      ,ltrim(max(sys_connect_by_path(dlst.ename,',')) keep (dense_rank last order by dlst.curr),',') enames
from (select dpte.dname
            ,dpte.ename
            ,row_number() over (partition by dpte.deptno order by dpte.ename) curr
            ,row_number() over (partition by dpte.deptno order by dpte.ename) -1 min1
      from (select dept.deptno
                  ,dept.dname
                  ,emp.ename
            from dept
            left join emp
            on dept.deptno=emp.deptno) dpte
      ) dlst
group by dlst.dname
connect by dlst.min1 = prior dlst.curr and dlst.dname = prior dlst.dname
start with dlst.curr = 1
order by 1 asc
;

-- 5
select dept.dname
      ,emps.enames
from dept
left join (select dpte.deptno
                 ,ltrim(max(sys_connect_by_path(dpte.ename,',')) keep (dense_rank last order by dpte.curr),',') enames
           from (select deptno
                       ,ename
                       ,row_number() over (partition by deptno order by ename) curr
                       ,row_number() over (partition by deptno order by ename) -1 min1
                 from emp) dpte
           group by dpte.deptno
           connect by dpte.min1 = prior dpte.curr and dpte.deptno = prior dpte.deptno
           start with dpte.curr = 1) emps
on dept.deptno = emps.deptno
order by 1 asc
;
  

Plans:

SQL> set linesize 130
SQL> set pagesize 1000
SQL> explain plan for select dept.dname
  2        ,emp.ename
  3  from dept
  4  left join emp
  5  on dept.deptno=emp.deptno
  6  order by 1 asc
  7          ,2 asc
  8  ;

ExplicitÚ.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 780249577

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |    14 |   308 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY                |         |    14 |   308 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN OUTER            |         |    14 |   308 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

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

SQL>
SQL> explain plan for select dpte.dname
  2        ,listagg(dpte.ename,',') within group (order by dpte.ename) enames
  3  from (select dept.dname
  4              ,emp.ename
  5        from dept
  6        left join emp
  7        on dept.deptno=emp.deptno) dpte
  8  group by dpte.dname
  9  order by 1 asc
 10  ;

ExplicitÚ.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4196575329

------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     4 |    88 |     8  (38)| 00:00:01 |
|   1 |  SORT ORDER BY                 |         |     4 |    88 |     8  (38)| 00:00:01 |
|   2 |   SORT GROUP BY                |         |     4 |    88 |     8  (38)| 00:00:01 |
|   3 |    MERGE JOIN OUTER            |         |    14 |   308 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |         |    14 |   126 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

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

SQL>
SQL> explain plan for select dept.dname
  2        ,emps.enames
  3  from dept
  4  left join (select deptno
  5                   ,listagg(ename,',') within group (order by ename) enames
  6             from emp
  7             group by deptno) emps
  8  on dept.deptno = emps.deptno
  9  order by 1 asc
 10  ;

ExplicitÚ.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 740866522

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     4 |  8072 |     8  (38)| 00:00:01 |
|   1 |  SORT ORDER BY                |         |     4 |  8072 |     8  (38)| 00:00:01 |
|   2 |   MERGE JOIN OUTER            |         |     4 |  8072 |     7  (29)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |         |     3 |  6015 |     5  (40)| 00:00:01 |
|   6 |     VIEW                      |         |     3 |  6015 |     4  (25)| 00:00:01 |
|   7 |      SORT GROUP BY            |         |     3 |    27 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL       | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMPS"."DEPTNO"(+))

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

SQL>
SQL> explain plan for select dlst.dname
  2        ,ltrim(max(sys_connect_by_path(dlst.ename,',')) keep (dense_rank last order by dlst.curr),',') enames
  3  from (select dpte.dname
  4              ,dpte.ename
  5              ,row_number() over (partition by dpte.deptno order by dpte.ename) curr
  6              ,row_number() over (partition by dpte.deptno order by dpte.ename) -1 min1
  7        from (select dept.deptno
  8                    ,dept.dname
  9                    ,emp.ename
 10              from dept
 11              left join emp
 12              on dept.deptno=emp.deptno) dpte
 13        ) dlst
 14  group by dlst.dname
 15  connect by dlst.min1 = prior dlst.curr and dlst.dname = prior dlst.dname
 16  start with dlst.curr = 1
 17  order by 1 asc
 18  ;

ExplicitÚ.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2397983189

-----------------------------------------------------------------------------------------------------
| Id  | Operation                                 | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |         |     4 |   168 |     9  (45)| 00:00:01 |
|   1 |  SORT ORDER BY                            |         |     4 |   168 |     9  (45)| 00:00:01 |
|   2 |   SORT GROUP BY                           |         |     4 |   168 |     9  (45)| 00:00:01 |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   4 |     VIEW                                  |         |    14 |   588 |     7  (29)| 00:00:01 |
|   5 |      WINDOW SORT                          |         |    14 |   308 |     7  (29)| 00:00:01 |
|   6 |       MERGE JOIN OUTER                    |         |    14 |   308 |     6  (17)| 00:00:01 |
|   7 |        TABLE ACCESS BY INDEX ROWID        | DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   8 |         INDEX FULL SCAN                   | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  9 |        SORT JOIN                          |         |    14 |   126 |     4  (25)| 00:00:01 |
|  10 |         TABLE ACCESS FULL                 | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DLST"."MIN1"=PRIOR "DLST"."CURR" AND "DLST"."DNAME"=PRIOR "DLST"."DNAME")
       filter("DLST"."CURR"=1)
   9 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))

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

SQL>
SQL> explain plan for select dept.dname
  2        ,emps.enames
  3  from dept
  4  left join (select dpte.deptno
  5                   ,ltrim(max(sys_connect_by_path(dpte.ename,',')) keep (dense_rank last order by dpte.curr),',') enames
  6             from (select deptno
  7                         ,ename
  8                         ,row_number() over (partition by deptno order by ename) curr
  9                         ,row_number() over (partition by deptno order by ename) -1 min1
 10                   from emp) dpte
 11             group by dpte.deptno
 12             connect by dpte.min1 = prior dpte.curr and dpte.deptno = prior dpte.deptno
 13             start with dpte.curr = 1) emps
 14  on dept.deptno = emps.deptno
 15  order by 1 asc
 16  ;

ExplicitÚ.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 4173785831

--------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |     4 |  8072 |     9  (45)| 00:00:01 |
|   1 |  SORT ORDER BY                               |         |     4 |  8072 |     9  (45)| 00:00:01 |
|   2 |   MERGE JOIN OUTER                           |         |     4 |  8072 |     8  (38)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID               | DEPT    |     4 |    52 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN                          | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                                 |         |     3 |  6015 |     6  (50)| 00:00:01 |
|   6 |     VIEW                                     |         |     3 |  6015 |     5  (40)| 00:00:01 |
|   7 |      SORT GROUP BY                           |         |     3 |   138 |     5  (40)| 00:00:01 |
|*  8 |       CONNECT BY NO FILTERING WITH START-WITH|         |       |       |            |          |
|   9 |        VIEW                                  |         |    14 |   644 |     4  (25)| 00:00:01 |
|  10 |         WINDOW SORT                          |         |    14 |   126 |     4  (25)| 00:00:01 |
|  11 |          TABLE ACCESS FULL                   | EMP     |    14 |   126 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPT"."DEPTNO"="EMPS"."DEPTNO"(+))
       filter("DEPT"."DEPTNO"="EMPS"."DEPTNO"(+))
   8 - access("DPTE"."MIN1"=PRIOR "DPTE"."CURR" AND "DPTE"."DEPTNO"=PRIOR "DPTE"."DEPTNO")
       filter("DPTE"."CURR"=1)

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

SQL>

Links: http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

SQL*Plus explain plan

Assuming that the PLAN_TABLE has been properly set up by running utlxplan.sql:

SQL> set linesize 130
SQL> explain plan for select * from dual where null is not null;

ExplicitÚ.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

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

SQL>