ORA-20000

Transform the set of values in a table:

SQL> 
SQL> select column_value
  2  from   table ( sys.odcinumberlist(20, 30) );
 
COLUMN_VALUE
------------
          20
          30
 
SQL> 
SQL> select column_value
  2  from   table ( sys.odcivarchar2list('First', 'Second') );
 
COLUMN_VALUE
--------------------------------------------------------------------------------
First
Second
 
SQL> 
SQL> select column_value
  2  from   table ( sys.odcidatelist(sysdate, sysdate + 1) );
 
COLUMN_VALUE
------------
24.10.2013 1
25.10.2013 1
 
SQL>

Читать полностью

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

Читать полностью

Очередной раз при работе с коллекциями наткнулся на несколько возможностей для их заполнения и решил их рассмотреть.

Сначала создадим коллекции, основанные на на простых типах varchar2, integer и на основе записи (record). Тестирование выполняем в схеме SCOTT.

SQL> create or replace type array_varchar as table of varchar2(4000);
2  /

Type created

SQL> create or replace type array_integer as table of integer;
2  /

Type created

SQL>
SQL> create or replace type param as object
2  (
3    param_varchar  varchar2(4000),
4    param_integer  integer
5  );
6  /

Type created

SQL> create or replace type array_param as table of param;
2  /

Type created

SQL> 

Рассмотрим сначала самый простой, но некрасивый и медленный способ.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- декларируем переменные для коллекции
3    --   и сразу же их инициалзируем
4    v_array_varchar array_varchar := array_varchar();
5    v_array_integer array_integer := array_integer();
6    v_array_param array_param := array_param();
7  begin
8    for vCur in (
9                 select emp.empno, emp.ename
10                 from   emp
11                ) loop
12      -- заполняем коллекцию строк
13      v_array_varchar.extend();
14      v_array_varchar(v_array_varchar.count) := vCur.ename;
15      -- заполняем коллекцию чисел
16      v_array_integer.extend();
17      v_array_integer(v_array_integer.count) := vCur.empno;
18      -- заполняем коллекцию записей
19      v_array_param.extend();
20      v_array_param(v_array_param.count) := param(vCur.ename, vCur.empno);
21    end loop;
22    -- посмотрим количесто записей
23    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
24    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
25    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
26  end;
27  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

Правильный способ – это использовать BULK COLLECT INTO.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select emp.ename, emp.empno , param(emp.ename, emp.empno)
8    bulk collect into v_array_varchar, v_array_integer, v_array_param
9    from   emp;
10    -- посмотрим количесто записей
11    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
12    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
13    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
14  end;
15  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

Еще один способ использовать функцию COLLECT.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select cast(collect(emp.ename) as array_varchar),
8           cast(collect(emp.empno) as array_integer),
9           cast(collect(param(emp.ename, emp.empno)) as array_param)
10    into   v_array_varchar, v_array_integer, v_array_param
11    from   emp;
12    -- посмотрим количесто записей
13    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
14    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
15    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
16  end;
17  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

И еще один способ использовать функцию MULTISET.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select cast(multiset(select emp.ename from emp) as array_varchar),
8           cast(multiset(select emp.empno from emp) as array_integer),
9           cast(multiset(select param(emp.ename, emp.empno) from emp) as array_param)
10    into   v_array_varchar, v_array_integer, v_array_param
11    from   dual;
12
13    -- посмотрим количесто записей
14    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
15    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
16    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
17  end;
18  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL>

Читать полностью