ORA-20000

Часто бывает необходимо получить результат выборки в виде одной строки, разделенных каким-нибудь разделителем. Обычно всегда пишем для этого свои агрегирующие функции. Но бывает, что так, что запрещено создавать свои типы и пакеты, и результат необходимо получить чистым select’ом с использованием стандартных функций.

Вот пару способов это сделать. Примеры выполнены в схеме SCOTT.

SQL> select ltrim(max(sys_connect_by_path(ename, ',')), ',')
 2  from   (
 3          select emp.ename,
 4                 row_number() over (order by emp.ename) as rn
 5          from   emp
 6          where  emp.job = 'SALESMAN'
 7         )
 8  start with rn = 1
 9  connect by prior rn = rn-1;

LTRIM(MAX(SYS_CONNECT_BY_PATH(
--------------------------------------------------------------------------------
ALLEN,MARTIN,TURNER,WARD

SQL> 

Еще один способ.

SQL> select rtrim(extract(xmlagg(xmlelement(x, ename||',') order by emp.ename),'//X/text()').getstringval(), ',')
 2  from   emp
 3  where  emp.job = 'SALESMAN';

RTRIM(EXTRACT(XMLAGG(XMLELEMEN
--------------------------------------------------------------------------------
ALLEN,MARTIN,TURNER,WARD

SQL> 

Еще пару раз приходилось делать наоборот, есть строка, со значениями, и их надо превратить в набор строк со значениями. Ниже несколько способов, как это сделать.

SQL> select emp.empno, emp.ename
 2  from   emp
 3  where  emp.ename in
 4         (
 5          with t as (
 6                     select ltrim('ALLEN,MARTIN,TURNER,WARD'/*:p_list*/, ',') || ',' as s,
 7                            rownum as rn
 8                     from   emp /* или all_objects, здесь можно emp*/
 9                    )
10          select substr(t.s,
11                        lag(instr(t.s, ',', 1, rn), 1, 0) over (order by instr(t.s, ',', 1, rn)) + 1,
12                        instr(t.s, ',', 1, rn) - lag(instr(t.s, ',', 1, rn), 1, 0) over (order by instr(t.s, ',', 1, rn)) - 1
13                       ) as str
14          from   t
15          where  instr(t.s, ',', 1, rn) != 0
16        );

EMPNO ENAME
----- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7844 TURNER

SQL>

Еще один способ.

SQL> select emp.empno, emp.ename
 2  from   emp
 3  where  emp.ename in
 4         (
 5          with t as (
 6                     select '' || replace('ALLEN,MARTIN,TURNER,WARD', ',', '') || '' s
 7                     from   dual
 8                    )
 9          select extractvalue(value(row_data), '/X')
10          from   t,
11                 table(xmlsequence(extract(xmltype(t.s), '/Y/X'))) row_data
12         );

EMPNO ENAME
----- ----------
7499 ALLEN
7654 MARTIN
7844 TURNER
7521 WARD

SQL>