Передача списка значений в качестве значения переменной в процедуру PL/SQL из клиентского приложения

    Когда я только начинал писать на Java и C#, то всегда мучался с выдумыванием различных вариантов для передачи списков в процедуры PL/SQL из клиентского слоя. Понятное дело, что на уровне клиента всегда несоизмеримо проще сформировать некий документ в транспортабельном формате, чтобы потом обработать его в БД, для этого очевидно можно и нужно использовать готовые форматы XML или JSON, не выдумывая ненужных велосипедов, но принципы эффективного парсинга сформированных таким образом документов не всегда очевидны. 

    Со временем у меня сформировалась и устоялась некоторая технология, которая дает возможность неплохо унифицировать работу с различными наборами параметров при их формировании и при обработке на уровне PL/SQL. Собственно сложность в основном заключается в парсинге переданных значений на уровне PL/SQL. Именно поэтому я изначально и уделяю достаточно много времени тому, чтобы эффективно разбивать переданные строки на значения без выполнения сложных операций строковой обработки

Вот достаточно простой пример. Передача некоего списка значений в процедуру, где каждое из передаваемых значений является объектом (т. е. имеет свой собственный набор атрибутов помимо значения, например порядковый номер или что-то подобное). Задачу имеет смысл разбить на несколько этапов:

1 этап - сериализация списка в xml формат на уровне клиента, выполняется достаточно просто за счет использования базовых интерфейсов в используемом языке

2 этап - передача значения в параметр процедуры. Тут возникает только один вопрос, какой тип использовать для передачи параметра. Как правило я использую varchar2 параметры для передачи небольших списков и CLOB параметры, если длина списка непредсказуема либо точно превышает 4000 байт

3 этап - парсинг переданного списка в PL/SQL. Как вариант, можно использовать встроенную поддержку XML 

with q_xml as (

select 

        xmltype('<ordering>

            <person id = "1" checkout="1799-12-01">George Washington</person>

            <person id = "2" checkout="1826-12-01">John Adams</person>

            <person id = "3" checkout="1826-12-01">Tomas Jefferson</person>

            <person id = "4" checkout="1836-12-01">James Medisson</person>

            <person id = "5" checkout="1831-12-24">James Monroe</person>

        </ordering>') xml_data from dual

    )

select xt.id, xt.val, xt.checkout

from 

    q_xml x, 

    XMLTABLE('/ordering/person'

             PASSING x.xml_data

             COLUMNS 

               val     VARCHAR2(4000)  PATH '.',

               id     number  PATH '@id',

               checkout     date  PATH '@checkout'

             ) xt

Таким образом, на уровне SQL можно оперировать результирующим набором, сформированным на клиенте, если драйвер не имеет поддержки передачи массивов, например если вы работаете с драйвером ADODB.NET или любым другим драйвером с урезанным функционалом 

Из PL/SQL в Excel за деньги?! Ну уж неееет!!!....

Привет всем страждущим знаний! (хотя кого я обманываю - тут кроме меня и нету никого)

Ковырялся тут в интернетах за нуждой и случайно наткнулся на некий эстонский сайтик, расположенный по адресу https_//www_oraexcel_com/ по скандинавски аккуратно, но безлико сделанный в material дизайне и предлагающий всем желающим за €820 пакет для работы с xlsx файлами офисного пакета. Стало интересно,  чего там такого на €820 понаписано, попрыгал по примерам. Оказалось - ничего, оригинального там нет. Минуты через две, когда думал уже закрывать и забывать, вдруг вспомнилось, что у меня тоже есть очень похожий пакет для работы с xlsx из PL/SQL совершенно без СМС, рекламы и регистрации. 

Конечно, скажу сразу, этот пакетик не мой, а очень уважаемого мною нидерландского разработчика Anton Scheffler, с которым я когда-то давным-давно списывался и просил разрешения пользоваться этим пакетов в своих системах, на что он мне ответил согласием, да и пакет то, собственно доступен публично, но только почему-то мало известен, а если и известен, то под именем AS_XLSX.

Функционал AS_XLSX схож с функционалом вышеописанного пакета ORA_EXCEL от эстонцев, но, видимо, у коммерческого пакетика бантиков побольше, особенно не разбирался. Главное, что основную свою функцию AS_XLSX выполняет - генерирует полноценные *.XLSX файлы со стилями и прочими ништяками и способен сохранять их в виде файлов или BLOB объектов. Так как исходный код открыт, то нет никаких проблем с модификацией данного пакета не возникает.

Ссылка на пакет AS_XLSX от автора: https://technology.amis.nl/languages/oracle-plsql/create-an-excel-file-with-plsql/

Есть также альтернативное тоже совершенно бесплатное решение с исходным кодом Alexandria (сам не пользовался): https://github.com/mortenbra/alexandria-plsql-utils

ORACLE APEX также может использоваться для генерации pdf документов, но я не тестировал ни интерфейсы, ни производительность OA, так что ничего внятного сказать по этому поводу не могу.

Что же касается меня, то могу сказать, что я реализовал несколько вариантов работы с PDF документами в ORACLE:

1) я взял за основу пакет AS_XLSX и модифицировал и расширил его нужными мне методами для рисования схем. Меня не устроила конечная скорость пакета, т.к. мне приходится создавать десятки тысяч документов за сутки.

2) После первой попытки я взял за основу java библиотеку PDFClown, загрузил ее в БД и реализовал интерфейсы, позволяющие мне работать с документами на бекенде. Меня бы все устроило, если бы не одна неприятная особенность бекендной JVM, а именно то, что она деградирует многопоточную обработку, вытягивая все потоки в конвейер, т.е. сводит на нет все усилия по распараллеливанию процессов. Сначала я думал расстроиться, но потом вчитался в док и понял, что некая логика в этом есть, т.к. основная задача JVM в данном случае, не завалить сервер. Тут уж спору нет, пришлось от такой реализации также отказаться, потому что распараллеливать процессы на уровне сессий мне не очень удобно, т.к. я в многопоточном режиме создаю PDF документы, а в процессе работы каждый из потоков создает некий объект контроллер, который в свою очередь генерирует десятки потоков, подгружающих в документ изображения с сервера изображений.

3) Я выгрузил функционал из БД и реализовал daemon app, которое установлено на отдельном хосте и выполняет нужные мне операции, загружая сгенерированные документы в базу данных. Такая реализация на данный момент меня вполне устраивает, но я на всякий случай заложил возможность сборки таких хостов в кластер при нехватке производительности. В настоящее время я тестирую производительность различных open source или просто freeware библиотек, подбирая наиболее производительную и менее требовательную к памяти, т.к. тут к PDFClown появились некоторые вопросы. В основном по использованию кириллицы в документах, а также при работе с аннотациями в PDF документах

Вот такая незатейливая история длиной в 3 ковидных года. :)

Из неопубликованного. Забытые regexp этюды для работы со строковым представлением дробных чисел со степенью вида [+/-]NNN[.MMM[E[+/-]PPP]]

    Точно не помню, зачем это мне было нужно, но теперь, по прошествии нескольких лет, выглядит просто интересно. Насколько я помню, была у меня попытка написать регулярное выражение, которое могло бы обработать строку и определить, корректно ли написано число, чтобы не возникала исключительная ситуация Дополнительно к этому для сравнения написал функцию isNumber(), которая проверяла бы корректность написания числа с помощью вызова to_number() с гашением исключительных ситуаций.

with  

    function isNumber(p_String in varchar2) return number is 

      n number; 

    begin 

      n := to_number(p_String); 

      return 1; 

    exception when others then return 0; 

    end; 

 

select * from ( 

    with 

        q_pattern as ( 

            select '^'|| 

            '([-+]?)'|| 

            '('|| 

                '([[:digit:]]+[.,]?)(E[-+]?[[:digit:]]+)?|'|| -- целое число с научной нотацией 

                '([[:digit:]]*([.,][[:digit:]]+))(E[-+]?[[:digit:]]+)?'|| -- дробное число с научной нотацией 

            ')$' pattern from dual 

        ), 

        q_nums as (select '.1,1.,.,1.E1,E2,1,-1,.1E1,.1E-1,-.1E+1,1.,-1.,.E3,.0E3, -.0E3, 1.23847,-1.23847,22E2, 22E-2, 22F2, 22E2.2, 1L,0x12, .1112E-1,-.1E-10, 123.2344E1, 123.2344E11.1' num_list from dual), 

        q_tab as (select regexp_substr(num_list, '\s*([^,]+)\s*',1,level,'i',1) num from q_nums connect by regexp_substr(num_list, '[^,]+',1,level) is not null) 

    select 

        num, 

        case when regexp_like(num, pattern) then 'Ok' else 'Fail' end by_regexp_like, 

        case when isNumber(num) = 1 then 'Ok' else 'Fail' end by_to_number 

    from 

        q_tab 

        cross join q_pattern 

) t

Токенизация строки с помощью xmltable

    Посмотрел я свой бложик ретроспективно... Блин, столько лет работы, а в нем про одно разбиение по разделителю да работа с CSV. Уныло как-то стало. А потом задумался, - но ведь это же довольно значимая часть работы любого разработчика баз данных. Мы проводим часы и дни в поисках оптимальных решений для обработки данных, выискивая наилучшие подходы и поэтому выбор инструментов у нас по любой проблеме (даже такой, казалось бы, примитивной, как токенизация) должен быть достаточно широк. 
    Поэтому решил добавить еще один удобный вариант разбиения на токены, который используется у нас при работе с фронтендом. При данном подходе, разумеется, ограничена длина списка токенизируемых значений, поэтому применять нужно с оглядкой на возможную максимальную длину входной строки. 

 

select 

    xt.item 

from 

    xmltable(

        'for $text in tokenize($input, ",") return $text' 

        passing 'Северный Кавказ,Волга-Север,Северо-Запад' as "input" 

        columns item varchar2(4000) path '.'

    ) xt

Вывод:

ITEM

----------- 

Северный Кавказ

Волга-Север

Северо-Запад

PL/SQL парсер для CSV данных, находящихся в CLOB-переменной (в соотв. с RFC 4180)

Ну, вот и снова пришлось написать что-то набившее оскомину. На этот раз - очередной парсер для CLOB-переменной, содержащей CSV данные, сохраненные в формате Excel. Порылся в stackoverflow, ничего толкового не нашел, поэтому понял, что быстрее написать самому. Парсит достаточно большие файлы. У меня есть образец на 9Мб который достаточно быстро обрабатывается, разумеется без вставки в БД, просто с выводом в output. Потом, подумал, что надо бы сделать пример и потом самому к нему обращаться при необходимости, чтобы не рыться в каталогах. 

Итак, 

- входное значение указано в CLOB-переменной c. 

- в качестве разделителя значений используется ";"

- символ экранирования многострочных значений - двойная кавычка, 

- символ двойной кавычки - двойная двойная кавычка

- cимвол новой строки - 0x0A (10)

- результат выводится в output буфер

declare
    -- CLOB value for tests, separator is semicolon, has been copied from file saved as Windows-1251 CSV worksheet by Excel 2019
    c clob := '"1";"Serge;
George
Galina";3
""2"";Tatiana;3
3;"Nikolas
""Maxim""
Lisa";3
4;;3
5;"Daria;";3
6;""Leo"";3
7;Alexey;""3""
8;Ksenia;"3"';
    -- special characters
    separator       constant char(1) := ';';   -- semicolon separator
    quote_char      constant char(1) := '"';  -- quoting character
    new_row         constant char(1) := chr(10); -- new row character
    -- output cell value as a comment
    procedure cell_out(prow in number, pcol in number, pval in varchar2) is
    begin
        dbms_output.put_line('Cell ('||prow||', '||pcol || ') = '|| nvl(replace(pval,new_row,'<\n>'), '<null>'));
    end;
    -- CSV parser
    procedure parse_csv_clob (p_clob in out nocopy clob) is
        offset          number := 1;
        amount          number := 1;
        total           number := nvl(dbms_lob.getlength(p_clob),0);
        read_buffer     varchar2(1);
        read_buffer1    varchar2(1);
        cell_buffer     varchar2(32767);
        row_index       pls_integer := 1;
        col_index       pls_integer := 1;
        quoting         boolean := false;
    begin
        if ( dbms_lob.isopen(p_clob) != 1 ) then dbms_lob.open(p_clob, 0); end if;
        loop
            exit when ( offset > total );
            dbms_lob.read(p_clob, amount, offset, read_buffer); -- read the symbol
            if read_buffer = new_row and not quoting then -- new row
                cell_out(row_index, col_index, cell_buffer);
                row_index := row_index + 1;
                col_index := 1;
                cell_buffer := null;
            elsif read_buffer = separator and not quoting then -- new cell
                cell_out(row_index, col_index, cell_buffer);
                cell_buffer := null;
                col_index := col_index + 1;
            elsif read_buffer = quote_char then -- quoted value or a escaped double quote symbol (if next symbol is the same)
                if offset < total then dbms_lob.read(p_clob, amount, offset + 1, read_buffer1); else read_buffer1 := null; end if;
                if read_buffer1 is null or read_buffer1 != quote_char then quoting := not quoting;
                else 
                    cell_buffer := cell_buffer || quote_char;
                    offset := offset + 1;
                end if;
            else -- add any other symbols to a current cell value
                cell_buffer := cell_buffer || read_buffer;
            end if;
            offset := offset + 1;
            if (offset > total and not quoting) then
                if length(cell_buffer) > 0 then
                    cell_out(row_index, col_index, cell_buffer);
                end if;
            end if;
        end loop; 
        if ( dbms_lob.isopen(p_clob) = 1 ) then dbms_lob.close(p_clob); end if; 
        if quoting then raise_application_error(-20001, 'Closing quote character has not been found.'); end if;
    exception
        when others then
            dbms_output.put_line('CSV-PARSER-ERROR: '||sqlerrm);
    end;
begin
    parse_csv_clob(c);
end;

Нумерация групп при разбиении результирующего набора на части по значению в одном из столбцов

Полезный простенький запрос для разбиения результирующего набора на группы по значению в одном из столбцов. Для чего применяется - точно сказать не могу, т.к. к написанию этого запроса сподвиг мой коллега, но, очевидно, информация используется где-то на клиенте для удобного отображения таких групп.

На вход попадают следующие данные об адресах ADDRID и уникальных точках привязки к этим адресам IDX. Считаем, что сортировка данных проводится вне системы, но требуется сохранить естественный порядок поступления данных. Некоторые строки не имеют привязок адреса к точкам, такие строки считаем разделителями групп привязок точек к адресу:

ADDRID    IDX

999   1

999   2

999

999   4

999   5

999

999   7

          ... 

Таким образом, требуется сформировать столбец GROUPID, содержащий номера групп привязок точек к адресам, разбиваемых NULL значениями в столбце IDX, т.е. на выходе требуется получить примерно вот такие данные:

ADDRID    IDX    GROUPID

999   1      1

999   2      1

999

999   4      2

999   5      2

999

999   7      3

          ...  

Как вариант результата, NULL значения можно тоже относить к какой-либо группе, но это практически не меняет логику написания самого запроса. Как же написать такой запрос. 

q_data: Имитируем источник исходных данных, отдающий данные с учетом собственной сортировки (согласно начальным требованиям задания).

q_data_indexed: Подразумевается, что мы не можем управлять порядком следования данных в источнике, да в нем, очевидно, отсутствует тот столбец, по которому можно упорядочить данные. Поэтому, прежде всего нужно закрепить порядок поступаемых данных, для чего применяем либо ROW_NUMBER() OVER (ORDER BY <...выражение дополнительной сортировки>) RN, либо используем обычный ROWNUM. Также создаем столбец NULLC для выделения значением 1 тех строк, по которым происходит разбиение на группы, в нашем случае это те строки, в которых значение IDX IS NULL

q_data_grouped: В принципе, после выполненных выще действий остается только произвести суммирование единичек в столбце NULLC с помощью аналитической функции SUM() OVER(ORDER BY...) согласно закрепленному порядку по столбцу RN, чтобы получить нарастающий итог в столбце GROUPID. Каждое значение NULLC = 1 будет добавлять 1 в индекс группы GROUPID, а нули в NULLC не будут добавлять в него значение.

select: После формирования значения индекса группы можно убрать лишние значения индексов групп в строках, по которым производилось разбиение, но это безусловно диктуется общей логикой запроса - этого конечно можно не делать. В результате получаем следующий запрос:

with q_data as (

    select 999 addrid, 1 idx from dual union all 

    select 999 addrid, 2 idx from dual union all 

    select 999 addrid, null idx from dual union all 

    select 999 addrid, 4 idx from dual union all 

    select 999 addrid, 5 idx from dual union all 

    select 999 addrid, null idx from dual union all 

    select 999 addrid, 7 idx from dual),

q_data_indexed as (

    select addrid, idx, rownum rn, case when idx is null then 1 else 0 end nullc from q_data),

q_data_grouped as (

    select addrid, idx, sum(nullc) over(order by rn) + 1 groupid from  q_data_indexed)

select addrid, idx, case when idx is null then null else groupid end groupid from q_data_grouped

А вот, кстати, альтернативный вариант разбиения на такие же группы от моего коллеги. Пример имеет некоторые ограничения, например в нем все точки собираются в строку, которая имеет в Oracle максимальную длину в 4000 символов, а также в нем активно используются регулярные выражения, что здорово снижает производительность. Тем не менее, такой запрос также имеет место быть. Привожу для образца и самостоятельного разбора логики его работы:

with dat as (

    select 1 as ord, 99 adrid, 1 as id from dual union all

    select 2 as ord, 99 adrid, 2 as id from dual union all

    select 3 as ord, 99 adrid, null as id from dual union all

    select 4 as ord, 99 adrid, 3 as id from dual union all

    select 5 as ord, 99 adrid, 4 as id from dual union all

    select 6 as ord, 99 adrid, null as id from dual union all

    select 7 as ord, 99 adrid, 5 as id from dual union all

    select 8 as ord, 99 adrid, 6 as id from dual),

l as (

    select listagg(nvl(to_char(id),'*'),',') within group(order by ord) line from dat),

parts as (

    select level as gr ,trim(',' from regexp_substr(line, '[^*]+',1,level)) as p from l connect by regexp_substr(line, '[^*]+',1,level) is not null)

select 

    t.*, p.gr 

from 

    dat t

    left join parts p on t.id in (select regexp_substr(p.p, '[^,]+',1,level) from dual connect by regexp_substr(p.p, '[^,]+',1,level) is not null)

Работа со значениями типа LONG длиной до 4000 байт без использования PL/SQL

Как известно, со значениями типа LONG работа в ORACLE не особенно приветствуется. Точнее не приветствуется до той степени, что поддержка типа LONG в SQL практически отсутствует и значения для обработки могут быть обработаны только средствами клиентского ПО либо средствами PL/SQL.
Тем не менее, существует достаточно известный способ, с помощью которого все же можно воспользоваться значениями типа LONG непосредственно в SQL. Единственное ограничение этого способа - максимальная длина значений типа LONG должна быть не больше максимальной разрешенной длины SQL типа VARCHAR2, т.е. не более чем 4000 байт.
Приводимый мной пример в принципе дает понимание этого метода. Мне нужно было достать из системного словаря информацию о максимально возможных значениях ключа партиционирования таблицы по каждой из имеющихся партиций.

select partition_name, high_value from user_tab_partitions where table_name = '<ИМЯ_ТАБЛИЦЫ>'

Запрос крайне прост, но в SQL невозможно без определенных процедур воспользоваться значением HIGH_VALUE, так как оно имеет тип LONG. По "классике" нужно написать PL/SQL процедуру, которая преобразует тип LONG в VARCHAR2 по ROWID строки, но этот метод достаточно трудоемок и приводит к тому, что запрос при выполнении начинает переключать контекст выполнения с SQL в PL/SQL и обратно, что накладно.

Альтернативный метод таков:

with q_xml as (select dbms_xmlgen.getxmltype('select partition_name, high_value from user_tab_partitions where table_name = ''<ИМЯ_ТАБЛИЦЫ>''') xml from dual)
select
    z.*
from
    q_xml,
    xmltable('/ROWSET/ROW' passing q_xml.xml columns partition_name varchar2(64) path 'PARTITION_NAME', high_value varchar2(1024) path 'HIGH_VALUE' ) z

Запрос, конечно, становится более сложным, чем исходный, но тем не менее отпадает потребность в написании PL/SQL функции.

Разбиение CSV-значений в таблице на элементы

Много я ковырялся с разбиением строк на токены пока не натолкнулся на интересную задачку, связанную с разбиением CSV-значения на элементы и cross join полученных элементов с исходной записью. Для начала реализовал ее с помощью XMLTable, но такой способ требует подготовки исходного значения для использования в XMLTable, т.к. каждый элемент списка должен быть помещен в кавычки, например использование значения 'a,b,c,d,e' невозможно, но '"a","b","c","d","e"' вполне подходит. К сожалению, не всегда возможно изменять строку перед такой операцией, например из-за лимита длины буфера, как в моем случае. Я решил поискать альтернативные решения, которые позволяли бы выполнять cross join элементов без изменения исходной строки. В конце концов нашел на asktom.oracle.com прекрасный вариант, который меня полностью удовлетворил:

SQL> CREATE TABLE MY_TABLE(

  2  R_ID int,
  3  R_Site varchar(20),
  4  R_Name varchar(20),
  5  R_Role varchar(20)
  6  );

Table created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');

1 row created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');

1 row created.

SQL>
SQL>
SQL> select
  2    t.r_id,
  3    t.r_name,
  4    t.r_role,
  5    regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value)  as site
  6  from
  7    MY_TABLE t,
  8    table(cast(multiset(select level from dual connect by  level <= regexp_count(t.R_Site, '[^,]+')) as sys.OdciNumberList)) commas;

      R_ID R_NAME                                                       R_ROLE                                                       SITE
---------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------
         1 qwer                                                         Owner                                                        -456
         1 qwer                                                         Owner                                                        -789
         1 qwer                                                         Owner                                                        123
         2 qaz                                                          Manager                                                      -741
         2 qaz                                                          Manager                                                      -852
         2 qaz                                                          Manager                                                      56

6 rows selected.

Единственное, что я сделал с найденным примером, так это заменил невнятное выражение в CONNECT BY на приведенный в примере выше вызов regexp_count(...) и убрал секцию ORDER BY из-за зависания сессии.

Подсчет места, занятого объектами схем(ы) на дисках

with
    -- список схем через запятую и шаблон поиска
    q_schema_names_list as (select '&schema_owners' list, '\s*([^,]+)\s*' pattern from dual),
    -- таблица со схемами
    q_schema_names as (select upper(regexp_substr(list,pattern,1,level,'i',1)) schema_name from q_schema_names_list connect by regexp_substr(list,pattern,1,level,'i',1) is not null )
-- результат
select
    owner$ "Владелец",
    tablespace_name "Табличное пространство",
    segment_name "Сегмент",
    segment_type "Тип сегмента",
    master_object "Объект владедец",
    sum_size$ "Размер объекта"
from (
    -- приводим значения в удобочитаемый вид
    select
        case when grouping$type = '1111111' then 'ИТОГО по запросу' when grouping$type = '0111111' then 'Итого по схеме '|| owner else owner end owner$,
        tablespace_name,
        segment_name,
        segment_type,
        master_object,
        case
            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024 / 1024 ) || ' PB'

            when (tt.sum_size / 1024 / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024 / 1024) || ' TB'
            when (tt.sum_size / 1024 / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024 / 1024) || ' GB'
            when (tt.sum_size / 1024 / 1024) > 1 then to_char(tt.sum_size / 1024 / 1024) || ' MB'
            when (tt.sum_size / 1024) > 1 then to_char(tt.sum_size / 1024) || ' KB'
            else to_char(tt.sum_size) || ' B'
        end sum_size$,
        case when grouping$type = '1111111' then 10E6 when grouping$type = '0111111' then 10E6-1 else rn end rn$
    from (
        -- rollup запрос с подсчетом суммарных размеров
        select
            owner,
            tablespace_name,
            segment_name,
            segment_type,
            master_object,
            bytes,
            rn,
            grouping(owner)||
            grouping(tablespace_name)||
            grouping(segment_name)||
            grouping(segment_type)||
            grouping(master_object)||
            grouping(bytes) ||
            grouping(rn)grouping$type,
            sum(bytes) sum_size
        from (
            -- перечень интересующих нас сегментов по списку схем, сортируем список по убыванию занимаемого объектами размера
            select
                s.owner,
                s.tablespace_name,
                s.segment_name,
                s.segment_type,
                case
                    when s.segment_name like 'BIN$%' then '<Recyclebin> => ' || (select distinct original_name from dba_recyclebin r where r.owner = s.owner and r.object_name = s.segment_name)
                    when s.segment_type = 'TABLE' then s.segment_name
                    when s.segment_type in ('INDEX', 'LOBINDEX') then (select t.table_name from dba_indexes t where t.owner = s.owner and t.index_name = s.segment_name)
                    when s.segment_type = 'LOBSEGMENT' then (select t.table_name from dba_lobs t where t.owner = s.owner and t.segment_name = s.segment_name)
                    when s.segment_type = 'LOB PARTITION' then (select distinct t.table_name from dba_lob_partitions t where t.table_owner = s.owner and t.lob_name = s.segment_name)
                    when s.segment_type = 'TABLE PARTITION' then nvl((select distinct t.table_name from dba_tab_partitions t where t.table_owner = s.owner and t.partition_name = s.segment_name),s.segment_name)
                    when s.segment_type = 'INDEX PARTITION' then nvl((select distinct t.table_name from dba_indexes t where t.table_owner = s.owner and t.index_name = s.segment_name),s.segment_name)
                end master_object,
                s.bytes,
                row_number() over (order by owner, bytes desc) rn
            from
                dba_segments s
            where
                s.owner in (select schema_name  from q_schema_names)
        ) t group by rollup (owner, tablespace_name, segment_name, segment_type, master_object,bytes, rn)
    ) tt where grouping$type in ('0000000', '0111111', '1111111')
    order by
        tt.owner, rn$
) z

Разбиение строки на токены (JSON версия)

Мне очень нравится реализация встроенной поддержки JSON в ORACLE, что я даже уже не хочу вспоминать те времена, когда ее не было и каждый пытался реализовать JSON-парсеры по-своему. Некоторые так к ним привыкли (например на моей текущей работе), что пользуются ими даже на 12c.

Набившую оскомину задачу разбиения строки на токены можно решить и с помощью JSON, обрамив список элементов через запятую квадратными скобками и передав на вход JSON-парсеру:

-- можно не обрамлять элементы двойными кавычками, например для целых чисел

with q_json as (select '[1, 2, 3, 4, 0.24, .2, 5e4]' arrayList from dual)

SELECT arrayItem FROM json_table( (select arrayList from q_json) , '$[*]' COLUMNS (arrayItem PATH '$'))

 

-- со строковыми значениями надо обрамлять элементы списка двойными кавычками

with q_json as (select '["кактус", "утром", "на","окне", "вел", "старушку","на","ремне"]' arrayList from dual)

SELECT arrayItem FROM json_table( (select arrayList from q_json) , '$[*]' COLUMNS (arrayItem PATH '$'))