Пакет DBMS OUTPUT 1689

SQL> set serveroutput on size 1000000

SQL> set serveroutput on size 1000001

SP2-0547: size option 1000001 out of range (2000 through 1000000)

Как видно из полученного сообщения об ошибке, однако, размер буфера должен быть в диапазоне от 20000 до 1000000 байт. Реально вы сможете поместить в буфер меньше данных, чем установленный предел. Пакет DBMS OUTPUT использует простой алгоритм упаковки данных при помещении в PL/SQL-таблицу. Он не помещает i-ю строку в i-ый элемент массива, он плотно упаковывает массив. В первом элементе массива может оказаться перв1х пять строк, выданных с помощью этого пакета. Для этого (чтобы поместить несколько строк в одну) необходимо расходовать дополнительные ресурсы. Эти дополнительные ресурсы для размещения сведений о том, где находятся данные пользователя и какого они размера, выделяются из того же ограниченного пространства. Поэтому, даже выполнив команду SET SERVEROUTPUT ON SIZE 1000000, вы сможете выдать меньше миллиона байтов.

Можно ли определить, сколько байтов можно будет в]дать? Иногда - да, а иногда - нет. При фиксированном размере выдаваемой строки, когда все строки одинаковой длины, это можно определить. Можно точно рассчитать, сколько байтов удастся выдать. Если же выдаются строки переменной длины, то вычислить заранее, сколько байтов удастся выдать, нельзя. Ниже я представлю алгоритм, используемый сервером Oracle для упаковки данных.

Мы знаем, что сервер Oracle сохраняет данные в массиве. Максимальное количество строк в этом массиве рассчитывается исходя из установки SET SERVEROUTPUT ON SIZE. Массив пакета DBMS OUTPUT никогда не будет длиннее IDXLIMIT строк, где IDXLIMIT рассчитывается как:

idxlimit := trunc((xxxxxx+499) / 500);

Итак, если выполнить SET SERVEROUTPUT ON SIZE 1000000, пакет DBMSOUTPUT будет использовать не более 2000 элементов массива. Пакет DBMSOUTPUT будет сохранять в каждом элементе массива не более 5 байт дан-н1х (обгчно - меньше). Пакет DBMSOUTPUT упаковывает данные в строку массива в следующем формате:

Буфер(1) = <sp>NNNваши данные<sp>NNNваши данные...; Буфер(2) = <sp>NNNваши данные<sр>NNNваши данные...;

Так что для каждой в]даваемой строки будет использоваться дополнительно 4 байта - для одного пробела и трехзначного числа. Каждая строка в буфере DBMS OUTPUT имеет длину не более 504 байт, и пакет DBMS OUTPUT не будет переносить данные с одной строки на другую. Поэтому, например, если использовать максимальную длину строки и всегда выдавать строки по 255 байт, пакет DBMS OUTPUT сможет упаковать в элемент массива только одну строку. Причина в том, что значение (255+4) * 2 = 518 больше, чем 504, а пакет DBMS OUTPUT не будет делить строку на два элемента своего массива. Две строки такого размера просто не помещаются в одну строку массива DBMS OUTPUT. Поэтому даже если затребовать буфер размером 1000000 байт, вы сможете поместить в него только 510000 байт данных - чуть больше половине! запрошенного. Значение 510000 получено, исходя из того, что длина выдаваемых строк - 255 байт;

1690

Приложение А

всего же строк может быть не более 2000 (вспомните представленное ранее вычисление значения IDXLIMIT); 255*2000 = 510000. С другой стороны, при использовании строк длиной 248 байт можно помещать по две строки в элемент массива, что позволит в1-дать 248 * 2 * 2000 = 992000 байт - чуть больше 99% запрошенного пространства. Фактически, это максимум того, на что можно рассчитывать при использовании пакета DBMS OUTPUT - 992000 байт данных. Больше выдать с помощью этого пакета нельзя.

Как уже б1ло сказано, при использовании строк фиксированной длины очень легко подсчитать выдаваемое количество строк. Если известна фиксированная длина строки, например 79, 80 или 81 байт, легко все рассчитать:

ops$tkyte@ORA8I.WORLD> select trunc(504/(79+4)) * 79 * 2000 from dual;

TRUNC(504/(79+4))*79*2000

948000

ops$tkyte@ORA8I.WORLD> select trunc(504/(80+4)) * 80 * 2000 from dual; TRUNC(504/(80+4))*80*2000

960000

ops$tkyte@ORA8I.WORLD> select trunc(504/(81+4)) * 81 * 2000 from dual; TRUNC(504/(81+4))*81*2000

810000

Как видите, максимальный объем выдаваемых данных сильно зависит от размера выдаваемой строки.

Проблема со строками переменной длины состоит в том, что максимальный объем результата предсказать нельзя. Он зависит от того, как идет в]дача, от последовательности строк, получаемых пакетом DBMS OUTPUT. Если выдавать одни и те же строки, но в другом порядке, их будет выдано больше или меньше. Это непосредственно связано с используемым алгоритмом упаковки.

Эта особенность пакета DBMS OUTPUT сбивает с толку. Вы можете выполнить процедуру один раз и успешно выдать отчет размером 700000 байт, а завтра та же процедура приведет к вгдаче сообщения об ошибке ORA-20000: ORU-10027: buffer overflow после получения 650000 байт. Это связано со способом упаковки данных в буфере пакета DBMS OUTPUT. Далее в этом разделе мы рассмотрим альтернативы пакету DBMS OUTPUT, позволяющие избежать этой неоднозначности.

Резонно задать вопрос: а зачем создатели пакета вообще делают эту упаковку? Причина в том, что, когда пакет DBMS OUTPUT появился в версии 7.0, выделение памяти для PL/SQL-таблиц выполнялось совсем не так, как сейчас. При выделении слота в PL/SQL-таблице сразу же выделялась память для элемента максимального размера. Это означает, что, поскольку DBMS OUTPUT использует элементы типа VARCHAR2(500), 500 байт будут в1делены при вызове DBMS OUTPUT.PUT LINE(hello wod), т.е. тот же объем, что и при выдаче длинной строки. Результат, состоящий из 2000 строк, занял бы 1000000 байт, даже если выдать 2000 раз строку hello world, что фактически требует только около 22 Кбайт. Так что подобная упаковка б1ла предусмотрена для предотвращения выделения лишней памяти в области PGA для буферного массива. В последних

Пакет DBMS OUTPUT

1691

версиях Oracle (начиная с 8.0) память в]деляется по-другому. Размер элементов массива меняется динамически, и упаковка больше не нужна. Поэтому ее можно считать унаследованной от старых версий.

Последнее, что хотелось бы сказать о работе пакета DBMS OUTPUT, - это то, что начальные пробелы в выдаваем1х строках удаляются. Ошибочно думать, что это свойство пакета DBMSOUTPUT. Фактически, это свойство SQL*Plus (хотя, я знаю многих, кто склонен считать это ошибкой). Небольшой тест позволит понять, что я имею в виду:

ops$tkyte@ORA8I.WORLD> exec dbms output.put line( helloworld), hello world

PL/SQL procedure successfully completed.

При передаче пакету DBMS OUTPUT строки hello world, начальные пробелы оказались удалены. Считается, что это делает пакет DBMS OUTPUT, но на самом деле это не так. Усекает начальные пробелы утилита SQL*Plus. Простое решение этой проблемы - использовать расширенный синтаксис команды SET SERVEROUTPUT. Вот полный синтаксис этой команды:

set serveroutput {ONIOFF} [SIZE n]

[FORMIAT {WRAPPEDTORD WRAPPEDTRUNCATED} ]

Значение конструкций формата в]дачи строк представлено ниже.

WRAPPED. Утилита SQL*Plus при необходимости переносит на новую строку выданные сервером результаты, начиная с позиции, задаваемой командой SET LINESIZE.

WORD WRAPPED. Переносится каждая строка выданных сервером результатов, начиная с позиции, задаваемой командой SET LINESIZE. Перенос выполняется по словам. Утилита SQL*Plus выравнивает каждую строку влево, удаляя все на-чальн1е пробел!. Это значение является стандартным.

TRUNCATED. Каждая строка результатов сервера усекается до длины, задаваемой командой SET LINESIZE.

Действие каждой опции форматирования проще всего понять на примере:

SQL>set linesize 20

SQL>set serveroutput on format wrapped

SQL>exec dbms output.put line( Hello World !!!!!);

Hello World

PL/SQL procedure successfully completed. SQL>set serveroutput on format word wrapped

SQL>exec dbms output.put line( Hello World !!!!!);

Hello World

!!!!!

PL/SQL procedure successfully completed. SQL>set serveroutput on format truncated

SQL>execdbms output.put line( Hello World !!!!!);