Работа со значениями типа 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 '$'))

Разбиение строки на токены

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

with

    q_item_list as (

        select 'a,b,cv,d,r,e,,df,f,' list, ',' delimiter, '''' quotation 

        from dual),

    q_item_list_prepared as (

        select quotation||replace(list, delimiter, quotation||delimiter||quotation)||quotation list, delimiter, quotation 

        from q_item_list)

select

    regexp_substr(list, '['||quotation||']([^'||delimiter||']*)['||quotation||']+',1,level,'mi',1)

from

    q_item_list_prepared

connect by

    regexp_substr(list, '[^'||delimiter||']+',1,level) is not null

В данном случае строкой, имитирующей список является строка  'a,b,cv,d,r,e,,df,f,' (см q_item_list), разделителем я выбрал запятую, а обрамляющим символом принимается одиночный апостроф.
Первым делом квотируем все элементы списка апострофами с обеих сторон, чтобы не потерять при разбиении пустые элементы (см. q_item_list_prepared). После этого спокойно нарезаем все элементы по разделителю и после этого удаляем обрамляющие символы.

Если перед нами стоит задача получить из этой строки только непустые элементы, то все резко упрощается:

with

    q_item_list as (

        select 

            'a,b,cv,d,r,e,,df,f,' list, ',' delimiter, '''' quotation 

        from 

            dual)

    select 

        regexp_substr(list, '[^'||delimiter||']+',1,level) 

    from 

        q_item_list 

    connect by 

        regexp_substr(list, '[^'||delimiter||']+',1,level) is not null

В данном запросе мы ничего не подготавливаем к нарезке, а просто режем строку по разделителю.

Таинственная ошибка "ORA-04023: Object could not be validated or authorized" и что с ней можно сделать

Как известно, согласно законам Мерфи "если есть вероятность того, что какая-нибудь неприятность может случиться, то она обязательно произойдёт". Так случилось и у меня. На фазе внедрения, перед показом системы Заказчику прибежал ко мне аналитик, ответственный за демонстрацию и рассказал, что при переходе на одно из закладок в клиентском ПО стабильно возникает ошибка ORA-04023. Согласно этой ошибке, object could not be validated or authorized. Сперва не нашел ничего лучше, чем пересоздать проблемное view. Помогло ровно на сутки, после чего аналитик пришел вновь и убитым голосом подтвердил, что проблема вновь присутствует и стабильно повторяется. 

Я начал разбираться и обнаружил, что описания этой ошибки, собственно, не существует. Иначе, как еще можно понять следующий пассаж  в документации:

ORA-04023: Object could not be validated or authorized

Cause: A number of attempts were made to validate or authorize an object but failed.

Action: Please report this error to Oracle Support Services.

Но это ведь не наш метод. Я подключил своего sysdba и мы вместе начали курить инет, но ровным счетом ничего интересного, решавшего нашу проблему не нашли. Например у Бурлесона был приведен следующий текст

The ORA-04023 error may indicate that the shared pool has a RAM corruption.  One emergency workaround is to bounce the database or to flush the shared pool:

alter system flush shared pool;

If this does not stop the ORA-04023 error, then open a service request on MOSC.

У нас при сбросе разделяемого пула ошибка вновь воспроизводилась без проблем. Так в поисках прошло 4 дня. Временно помогала пересборка проблемного view, которая откладывала появление ошибки до следующего дня.

Наконец, на одном из форумов нашел ссылку на нижеприведенный SQL запрос и предположение, что данная ошибка на 12.1 может возникать из-за рассинхронизации времени между датой создания объекта и датой жестких привязок (hard dependencies) между самим объектом и его зависимыми объектами, т.е., например, табличный индекс имеет дату привязке к таблице раньше, чем дата создания самой таблицы. ORACLE, пытаясь получить данные из view (а в моем случае это и было view), для начала проверяет эту информацию в системном словаре для пользователей, не входящих в SYSDBA. Для SYSDBA рабочим является режим ограниченных/отключенных проверок, таким образом данная проверка не выполняется и данные из проблемной view достаются без проблем. Это объяснение идеально подходило под описание нашего случая.

Вот сам SQL запрос

select

    du.name d_owner, d.name d_name, d.defining_edition d_edition,

    pu.name p_owner, p.name p_name, p.defining_edition p_edition,

   case

      when p.status not in (1, 2, 4) then 'P Status: ' || to_char(p.status)

   else 'TS mismatch:      ' ||

      to_char(dep.p_timestamp, 'DD-MON-YY HH24:MI:SS') || ' -> ' ||

      to_char(p.stime, 'DD-MON-YY HH24:MI:SS')

   end reason

from

    sys."_ACTUAL_EDITION_OBJ" d,

    sys.user$ du,

    sys.dependency$ dep,

    sys."_ACTUAL_EDITION_OBJ" p, sys.user$ pu

where

    d.obj# = dep.d_obj# and p.obj# = dep.p_obj#

     and d.owner# = du.user# and p.owner# = pu.user#

     and d.status = 1                                    -- Valid dependent

     and bitand(dep.property, 1) = 1                     -- Hard dependency

     and d.subname is null                               -- !Old type version

     and not(p.type# = 32 and d.type# = 1)               -- Index to indextype

     and not(p.type# = 29 and d.type# = 5)               -- Synonym to Java

     and not(p.type# in(5, 13) and d.type# in (2, 55))   -- TABL/XDBS to TYPE

     and (p.status not in (1, 2, 4) or p.stime != dep.p_timestamp);

Для моего случая этот запрос вернул некоторое количество проблемных объектов базы данных (причем проблемная view в их состав не входила, но были, например, таблицы, которые в проблемном view использовались ), которые были мной пересобраны, т.е. были выполнены команды 

DROP <theObjectFromResultSet>;

и

CREATE <theObjectFromResultSet>;

После того как список, полученный с помощью вышеуказанного запроса опустел, но ошибка при обращении к проблемному view так и не исчезла, мной был сброшен shared pool

alter system flush shared_pool;

На двух из трех серверов ошибка исчезла сразу и к проблемному view вновь стало можно обращаться, не получая ошибки. На третьем сервере ошибка исчезла только после переподключения к серверу базы данных. Обращаю внимание, что пересборка самого проблемного view не производилась. Надеюсь, что приведенное мной описание решения проблемы ошибки ORA-04023 когда-нибудь поможет и вам.

Получение названия таблиц из произвольного SQL запроса

Проблема: Понадобилось сделать генератор SQL скриптов для переноса данных по таблицам. Простой путь: Нашел в сети топорный шаблон под названием fn_gen_inserts(updates) и вознамерился было попользоваться им, но понял, что код не удовлетворяет моим и без того скромным требованиям к генерации скриптов. У меня SQL скриптами осуществляется перенос данных с сервера разработки на сервер тестирования и промышленный сервер. Поэтому задавать имена целевых таблиц и их владельцев, как того требуют авторы исходных кодов вышеназванных функций, как-то несуразно. Как известно, пакет DBMS_SQL имеет несколько простых и удобных методов описания столбцов заданного динамически SQL запроса, но описания таблиц - нет. Как мне кажется, в случае анализа простых SQL запросов, вполне можно попытаться определить название таблицы и ее владельца в автоматическом режиме.
Задача: По заданному строкой SQL запросу определять название опорной таблицы и ее владельца. Итак, кусочек PL/SQL кода, где переменная p_sql - передаваемый SQL запрос

     ...

    -- 1) Evaluate execution plan for SQL query

    begin

        execute immediate 'explain plan for '||p_sql;

    exception when others then

        raise_application_error(-20001, 'ERROR: SQL query cannot be parsed ('||SQLERRM||')');

    end;

    -- 2) Getting the name of the first table in access order list (99,99%)

    begin

        select table_name into l_base_table_name from (

            select regexp_substr(acc, '^[|]\s*(\w+)\s*[|]',1,1,'i',1) idx, regexp_substr(acc, '[|]\s*((\w|[$#_])+)\s*[|]$',1,1,'i',1) table_name 

            from (

                        select plan_table_output acc from table(dbms_xplan.display(null, null, 'BASIC -ROWS -COST -PREDICATE -PARALLEL -PARTITION -PROJECTION -ALIAS -NOTE -REMOTE'))

            ) tabaccinfo

            where tabaccinfo.acc like '%TABLE ACCESS%' order by idx

        ) maintabacc where rownum = 1;

    exception when others then

        raise_application_error(-20002, 'ERROR: Cannot get the name of the source table ('||SQLERRM||')');

    end;

    -- 3) Get primary table owner from SQL query as a first name from the list of possible owners

    begin

        select owner into l_base_table_owner from (

            select owner from all_tables t where t.table_name = l_base_table_name order by decode(t.owner, user, 1, 2)

        ) t where rownum = 1;

    exception when others then

        raise_application_error(-20003, 'ERROR: Cannot get the owner of the source table ('||SQLERRM||')');

    end;

     ...

    после этого можно обработать переданный запрос в DBMS_SQL, чтобы обработать описания столбцов запроса. 

Разумеется, есть несколько допущений, которые значительно ограничивают применимость данного подхода. Вот они:

1) Совершенно необязательно, что основная таблица запроса будет идти первой в порядке доступа в плане выполнения SQL запроса или в случае выгрузки из пайплайновой функции такой таблицы вообще может не быть. В таких случаях я явно задаю название целевой таблицы

2) Совершенно не факт, что владельца таблицы можно получить просто получив отсортированный список владельцев одноименного объекта. В случае, если владелец объекта не может быть определен таким образом, я задаю его вручную, явно.

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