Книги, научные публикации
Pages: | 1 | ... | 20 | 21 | 22 | 23 | 24 | Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва Х Санкт-Петербург Х Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с ...
-- [ Страница 22 ] --24 25 dbms_output.put_line('Encoded= ' || l_raw_enc);
26 dbms_output.put_line('Decoded= ' || 27 utl_raw.cast_tq_varchar2(l_raw_decoded));
28 end;
29 / Encoded In hex = 7004DB310AC6A8F210F8467278518CF988DF554B299B35EF Decoded = hello world Encoded = E3CC4E04EF3951178DEB9AFAE9C99096 Decoded = Goodbye PL/SQL procedure successfully completed. Этот пример демонстрирует базовые возможности функций ENCRYPT и DECRYPT. Здесь я вызывал их из PL/SQL, ниже мы будем вызывать их в SQL-операторах. Я протестировал работу со строками и данными типа RAW. B строке 11 кода я вызываю процедуру SETKEY для установки ключа шифрования, который будет использоваться при шифровании и дешифровании данных типа VARCHAR2, равным строке MAGICKEY.
Приложение А Это позволяет не передавать строку остальным функциям. Затем я шифрую строку и помещаю результат в переменную L_STR_ENC. После этого строка дешифруется, чтобы убедиться, что все работает, как предполагалось. В строках 16-18 выдаются результаты. Поскольку в зашифрованных данных могут содержаться символы, "сводящие с ума" эмуляторы терминалов, я выдаю зашифрованную строку на терминал, вызвав UTL_RAW.CAST_TO_RAW в строке 17. Тип данных меняется с VARCHAR2 на RAW. Сами данные при этом не меняются. Поскольку данные типа RAW неявно преобразуются в строку шестнадцатиричных цифр, этот прием можно использовать как удобный способ отображения на экране данных в шестнадцатиричном виде. В строках с 20 по 27 я делаю то же самое для данных типа RAW. Снова необходимо вызвать процедуру SETKEY, передав на этот раз 8 байт данных типа RAW. Для преобразования ключа типа VARCHAR2 в ключ типа RAW я использовал функцию UTL_RAW.CAST_TO_RAW. Можно было также воспользоваться функцией HEXTORAW и передать строку шестнадцатиричных цифр. Затем я шифрую данные и дешифрую результат. Зашифрованные данные выдаются в явном виде (они все равно будут отображаться в шестнадцатиричном представлении) и привожу тип расшифрованных данных снова к VARCHAR2, чтобы можно было проверить корректность дешифровки. Результат подтверждает, что пакет работает. Рассмотрим, как этот пакет использовать в языке SQL. Для этого протестируем процесс шифрования тройным DES в режиме с двойным ключом: tkyte@DEV817> drop table t;
Table dropped. tkyte@DEV817> create table t 2 (id int primary key, data varchar2(255));
Table created. tkyte@DEV817> insert into t values 2 (1, crypt_pkg.encryptString('This is row 1', 'MagicKeyIsLonger'));
1 row created. tkyte@DEV817> insert into t values 2 (2, crypt_pkg.encryptString( 'This is row2', 'MagicKeyIsLonger'));
1 row created. tkyte@DEV817> select utl_raw.cast_to_raw(data) encrypted_in_hex, 2 crypt_pkg.decryptString(data,'MagicKeyIsLonger') decrypted 3 from t 4/ ENCRYPTED_IN_HEX DECRYPTED 0B9A809515519FA6A34F15094lB318OA441FBB0C790E9481 This is row 1 0B9A809515519FA6A34F150941B318DA20A936F9848ADC13 This is row 2 Итак, задав 16-байтовый ключ процедуре CRYPT_PKG.ENCRYPTSTRING, мы автоматически переключились на использование процедуры DES3ENCRYPT пакета DBMS_OBFUSCATION_TOOLKIT. Этот пример показывает, насколько легко использовать средства пакета CRYPT_PKG в языке SQL. Все функции можно непосредствен Пакет DBMS_OBFUSCATION_TOOLKIT но вызывать в SQL-операторах в тех же конструкциях, что и, например, функцию SUBSTR. Средства пакета CRYPT_PKG можно использовать в конструкции SET оператора UPDATE, в конструкции VALUES оператора INSERT, в списке выбора оператора SELECT и даже в конструкции WHERE любого оператора. Теперь посмотрим, как этот пакет можно использовать для больших объектов, продемонстрировав по ходу использование функций MD5. Для проверки используем объект типа CLOB размером 50 Кбайт. Сначала загружаем большой объект в базу данных: tkyte@DEV817> create table demo (id int, theClob clob);
Table created. tkyte@DEV817> create or replace directory my_files as 2 '/d01/home/tkyte';
Directory created. tkyte@DEV817> declare 2 l_clob clob;
3 l_bfile bfile;
4 begin 5 insert into demo values (1, erapty_clob()) 6 returning theclob into l_clob;
7 8 l_bfile :=bfilename('MY_FILES', 'htp.sql');
9 dbms_lob.fileopen(l_bfile);
10 11 dbms_lob.loadfromfile(l_clob, l_bfile, 12 dbms_lob.getlength(l_bfile));
13 14 dbms_lob.fileclose(l_bfile);
15 end;
16 / PL/SQL procedure successfully completed. Процедуры загрузили данные в объект типа CLOB. Теперь мы хотели бы выполнить с ним какие-нибудь действия. Снова используем язык SQL, поскольку это наиболее оптимальный способ работы с данными. Начнем с вычисления контрольной суммы по первым 32 Кбайтам данных объекта типа CLOB: tkyte@DEV817> select dbms_lob.getlength(theclob) lob_len, 2 utl_raw.cast_to_raw(crypt_pkg.md51ob(theclob)) md5_checksum 3 from demo;
LOB_LEN MD5_CHECKSUM 50601 307D19748889C2DEAD879F89AD45DlBA Для того чтобы преобразовать перед выводом данные типа VARCHAR2, возвращаемые функциями MD5, в шестнадцатиричную строку, мы воспользовались функцией UTL_RAW.CAST_TO_RAW. Строка типа VARCHAR2 скорее всего будет содержать встроенные символы новой строки, табуляции или другие управляющие символы терминала. Представленный выше код показывает, насколько легко использовать функции MD5: достаточно передать им данные, и контрольная сумма будет вычислена.
Приложение А Далее я продемонстрирую, как шифровать и дешифровать большой объект. Для этого используется простой оператор UPDATE. Обратите внимание, что на этот раз применяется ключ шифрования длиной 24 байта. Мы будем использовать подпрограмму DES3ENCRYPT, поскольку установили необязательный параметр which => ThreeKeyMode. В результате будет выполнено шифрование тройным алгоритмом DES с тройным ключом: tkyte@DEV817> update demo 2 set theClob = crypt_pkg.encryptLob(theClob, 3 'MagicKeyIsLongerEvenMore') 4 where id = 1;
1 row updated. tkyte@DEV817> select dbms_lob.getlength(theclob) lob_len, 2 utl_raw.cast_to_raw(crypt_pkg.md51ob(theclob)) md5_checksum 3 from demo;
LOB_LEN MD5_CHECKSUM 50624 FCBD33DA2336C83685B1A62956CA2D16 Длина объекта увеличилась с 50601 до 50624 байт, и рассчитанная по алгоритму MD5 контрольная сумма отличается от прежней Ч т.е. данные изменены. Если вспомнить представленное ранее описание алгоритма, мы взяли первые 32000 байт объекта типа CLOB, добавили в начале 8 байт при кодировании строки и зашифровали результат. Затем мы выбрали оставшиеся 18601 байт данных и дополнили их до 18608 байт (чтобы длина была кратна 8), и добавили еще 8 байт для сохранения исходной длины. Это и дало в результате увеличение длины до 50624 байт. Теперь рассмотрим, как выбрать из базы данных зашифрованный объект типа CLOB: tkyte@DEV817> select dbms_lob.substr( 2 crypt_pkg.decryptLob(theClob), 100, 1) data 3 from demo 4 where id = 1;
DATA set define off create or replace package htp as /* STRUCTURE tags */ procedure htmlOpen;
procedure Интересно отметить, что я не передавал ключ дешифрования. Поскольку он сохранен в переменной пакета, то передавать его необязательно. Значение переменной пакета сохраняется между вызовами, но только на время сеанса. Ключ хранится в глобальной переменной в теле пакета и недоступен другим сеансам.
Проблемы Данные, зашифрованные средствами пакета DBMS_OBFUSCATION_TOOLKIT в системе с обратным порядком байтов ("little endian" system) не могут быть дешифрова Пакет DBMS_OBFUSCATION_TOOLKIT ны с помощью того же ключа в системе с прямым порядком байтов ("big endian" system). Речь идет о порядке байтов в многобайтовом числе. На Intel-платформах (NT, многие дистрибутивы Linux и Solaris x86) принят обратный порядок байтов. Системы на базе процессоров Sparc и Risc обычно имеют прямой порядок байтов. Данные, зашифрованные на Windows NT с помощью ключа "12345678", нельзя расшифровать на Sparc Solaris с помощью этого же ключа. Следующий пример демонстрирует проблему (и способ ее избежать). На платформе Windows NT выполним: tkyte@TKYTE816> create table anothert (encrypted_data varchar2(25));
Table created. tkyte@TKYTE816> insert into anothert values 2 (crypt_pkg.encryptString( 'helloworld', '12345678'));
1 row created. tkyte@TKYTE816> select crypt_pkg.decryptstring(encrypted_data) from anothert;
CRYPT_PKG.DECRYPTSTRING(ENCRYPTED_DATA) hello world tkyte@TKYTE816> host exp userid=tom/kyte tables=anothert Соответствующий файл EXPDAT.DMP передан по FTP на машину Sparc Solaris и загружены находящиеся в нем данные. При попытке выбрать данные я получил: ops$tkyte@DEV816> s e l e c t 2 crypt_pkg.decryptstring(encrypted_data, '12345678') 3 from t;
crypt_pkg.decryptstring(encrypted_data, '12345678') * ERROR at line 2: ORA-06502: PL/SQL: numeric or value error: character to number convers ORA-06512: at "OPS$TKYTE.CRYPT_PKG", line 84 ORA-06512: at "OPS$TKYTE.CRYPT_PKG", line 215 ORA-06512: at line 1 ops$tkyte@DEV816> select 2 crypt_pkg.decryptstring(encrypted_data, '43218765') 3 from t;
CRYPT_PKG.DECRYPTSTRING(ENCRYPTED_DATA,'43218765') hello world Представленное выше сообщение об ошибке выдается пакетом-оболочкой. Я предполагаю, что первые 8 байт данных в строке Ч это число. Поскольку с помощью переданного ключа нельзя успешно дешифровать данные, первые 8 байт не содержат значение длины Ч это произвольный набор символов. Оказывается, 8 байтовый (или 16-, или 24-байтовый ключ) внутренне хранится как набор 4-байтовых целых чисел. Мы должны изменить порядок байтов в каждой 4-байтовой группе символов ключа, чтобы расшифровать данные в системе с другим поряд Приложение А ком байтов. Поэтому, если использовался ключ '12345678' на платформе Windows NT (Intel), я должен использовать ключ '43218765' на Sparc Solaris. Задаем первые 4 байта в обратном порядке, затем задаем в обратном порядке следующее 4 байта (и так далее Ч для ключей большего размера). Это важно помнить при переносе данных, например, с NT на Sparc Solaris и при запросе данных из удаленной базы. Вы должны быть готовы физически переупорядочить байты, чтобы успешно дешифровать данные. Эта проблема была решена в версиях, начиная с Oracle 8.1.7.1, так что теперь переставлять байты уже не нужно.
Управление ключами Я бы хотел кратко рассмотреть проблемы управления ключами. Шифрование Ч лишь часть действий, обеспечивающих защиту данных. Данных в базе шифруются для того, чтобы администратор базы данных, выполнив запрос к таблице, не смог понять, какие данные в ней находятся. Например, вы поддерживаете Web-сайт, где принимаете заказы клиентов. Клиенты передают номера кредитных карточек, которые сохраняются в базе данных. Необходимо гарантировать, что ни администратор базы данных, у которого есть возможность выполнять ее резервное копирование, ни злонамеренный хакер, взломавший базу данных, не смогли бы прочитать эту строго конфиденциальную информацию. Если хранить данные в явном виде, их легко сможет прочитать любой, получивший привилегии доступа администратора к базе данных. Если же данные хранятся в зашифрованном виде, этого не произойдет. Зашифрованные данные защищены настолько, насколько защищен ключ, использовавшийся для шифрования. Тут все дело в ключе. Если ключ известен, данные с таким же успехом можно вовсе не шифровать (при наличии ключа данные можно расшифровать оператором SELECT). Поэтому проблему генерации и защиты ключей следует хорошо продумать. Можно использовать различные подходы. Далее представлено несколько подходов, которые можно использовать, но с каждым из них связаны специфические проблемы.
Генерация и хранение ключей в клиентском приложении Можно вообще не хранить ключей в базе данных, вынеся их на другую машину (главное, не потерять их Ч потребуются сотни лет процессорного времени, чтобы их подобрать). При этом клиентское приложение, будь то сервер приложений или клиент в приложении с клиент-серверной архитектурой, сохраняет ключи в своей системе. Клиентское ПО определяет, имеет ли право обратившийся пользователь дешифровать данные, и посылает соответствующие ключи серверу базы данных. При использовании этого метода, связанного с передачей ключей по сети, необходимо добавить еще один уровень шифрования Ч шифрование потока данных протокола Net8. Связываемые переменные и строковые литералы по умолчанию передаются в явном виде. В этом случае, поскольку защита ключей принципиально важна, придется использовать технологии типа ASO (Advanced Security Option Ч расширенная защита).
Пакет DBMS_OBFUSCATION_TOOLKIT Эта возможность протокола Net8 обеспечивает шифрование всего потока данных, так что никто не сможет перехватить ключи при передаче по сети. Если ключ безопасно хранится в клиентском приложении (это должны обеспечить вы сами) и используется ASO, это решение будет вполне надежным.
Хранение ключей в той же базе данных Предполагается хранение ключей вместе с данными в базе. Это решение Ч не идеально, поскольку есть вероятность, что при наличии достаточного времени администратор базы данных (или хакер, получивший привилегии учетной записи администратора) сможет найти ключи и получить зашифрованные с их помощью данные. В подобных случаях следует максимально затруднить поиск ключей, соответствующих данным. Сделать это сложно, поскольку и ключи, и данные хранятся в одной базе. Приложение не должно напрямую связывать таблицу ключей с таблицей данных. Предположим, имеется таблица со столбцами CUSTOMER_ID, CREDIT_CARD и другими данными. Столбец CUSTOMER_ID неизменен Ч это первичный ключ (а мы знаем, что изменять первичный ключ не стоит). Можно создать другую таблицу: ID number primary key, DATA varchar2(255) В этой таблице будут храниться ключи для всех идентификаторов клиентов. Создадим функцию в пакете, которая будет возвращать ключ, только при условии, что ее вызывает соответствующий пользователь и в соответствующей среде (аналогично тому, как при использовании средств тщательного контроля доступа данные можно получить только в соответствующем контексте приложения). Пакет будет предоставлять две основные функции. Х Функция 1: добавление нового клиента. В данном случае функция будет выполнять некоторые действия с идентификатором клиента, чтобы скрыть его (преобразовать в другую строку). Функция должна быть детерминированной, чтобы по заданному идентификатору клиента всегда выдавалась одна и та же строка. Чуть позже мы поговорим о том, что делать с идентификатором клиента или любой другой строкой. Для клиента также генерируется случайный ключ. Ниже представлен ряд способов генерации этого ключа. Затем с помощью динамического SQL строка вставляется в таблицу ключей. (Не стоит называть таблицу KEY_TABLE или еще как-нибудь, чтобы по имени было понятно ее назначение.) Х Функция 2: получение ключа для клиента. Функция принимает идентификатор клиента, пропускает его через ту же детерминированную функцию, что и предыдущая, а затем с помощью динамического SQL находит и возвращает ключ для клиента. Все это выполняется, только если текущий пользователь работает в соответствующей среде. Динамический SQL используется для того, чтобы нельзя было понять, что пакет используется для управления ключами. Пользователь может обратиться к представлению ALL_DEPENDENCIES и выяснить, на какие таблицы статически ссылается пакет. При использовании динамического SQL не будет никакой связи между пакетом и таблицей Приложение А ключей. Это не позволит скрыть таблицу ключей от очень умного человека, но максимально затруднит ее поиск. Теперь о том, как скрыть идентификатор клиента или любой набор неизменных данных в строке. (Первичный ключ для этого подходит только при условии, что всегда остается неизменным.) Для этого имеется множество алгоритмов. Если бы я использовал Oracle 8.1.7, то мог бы послать результат конкатенации этих данных с некоторым постоянным значением (его часто называют "затравкой" Ч "salt") функциям резюмирования по алгоритму MD5 для получения 16-байтовой контрольной суммы. Именно ее я бы использовал в качестве ключа. В Oracle 8.1.6 я мог бы использовать такое же действие, но передавал бы значение функции DBMS_UTILITY.GET_HASH_VALUE с очень большим размером хеш-таблицы. Можно было бы применить операцию XOR после изменения порядка байтов в CUSTOMER_ID. Подойдет любой алгоритм, который сложно угадать по полученному результату. Вы можете возразить, что администратор базы данных может прочитать код, увидеть алгоритм и разобраться во всем этом. Нет, если скрыть (wrap) код. Скрыть PL/SQL-код очень просто (см. описание утилиты WRAP в руководстве PL/SQL User's Guide and Reference). Она берет исходных код и "шифрует" его. В базу данных загружается "зашифрованная" версия кода. Теперь код никто прочитать не сможет. Средств "дешифрации" для wrap нет. Сохраните только в каком-либо безопасном месте текст алгоритма. Восстановить текст после применения утилиты wrap и получить исходный код из базы данных не удастся. Для генерации ключа необходим своего рода генератор случайных строк. Его можно создать разными способами. Можно использовать те же приемы, что и для сокрытия значения CUSTOMER_ID. Можно использовать реальный генератор случайных чисел (например, пакет DBMS_RANDOM или собственной разработки). Задача в том, чтобы генерировать значение, которое будет сложно "угадать" на основе имеющейся информации. Лично я предпочел бы хранить ключи именно в базе данных. Если ключи находятся в клиентском приложении, всегда остается риск их потери вследствие сбоя носителя или другой катастрофы в системе. При хранении ключей в файловой системе риск остается. Только хранение ключей в базе данных гарантирует, что зашифрованные данные всегда удастся расшифровать: база данных всегда синхронизирована, а процедуры резервного копирования и восстановления Ч надежны.
Хранение ключей в файловой системе сервера базы данных Ключи шифрования данных можно также хранить в файловой системе сервера и обращаться к ним с помощью внешней процедуры на языке С. Я рекомендую использовать внешнюю процедуру на языке С, поскольку цель состоит в том, чтобы спрятать ключи от администратора базы данных, который обычно имеет доступ к учетной записи владельца программного обеспечения Oracle. Пакет UTL_FILE, работа с объектами типа BFILE и вызов хранимых процедур на языке Java, выполняющих ввод-вывод, осуществляется с правами пользователя-владельца программного обеспечения Oracle. Если Пакет DBMS_OBFUSCATION_TOOLKIT администратор базы данных работает с правами владельца программного обеспечения Oracle, он может прочитать файлы. А прочитав, сможет найти в них ключи. При использовании внешней процедуры, написанной на языке С, можно запустить службу EXTPROC (и соответствующий процесс прослушивания для службы EXTPROC) от имени другого пользователя. В этом случае пользователь Oracle не увидит ключи. К ним можно получить доступ только через процесс прослушивания EXTPROC. Это добавляет еще один уровень защиты. Подробнее о реализации этого подхода см. в главе 18.
Резюме Мы достаточно подробно рассмотрели пакет DBMS_OBFUSCATION_TOOLKIT. Я научил вас создать для него пакет-оболочку, предоставляющий те же функциональные возможности нужным образом (если моя реализация вам не подходит, напишите другую оболочку). Вы научились использовать динамический SQL для создания пакетов, которые можно устанавливать на серверах с разными возможностями (речь шла о возможностях шифрования в версиях 8.1.6 и 8.1.7). Мы обсудили проблему переноса данных на другую платформу при использовании пакета DMBS_OBFUSCATION_TOOLKIT, связанную с изменением порядка байтов в ключах. Вы узнали о возможности решать эту проблему переупорядочением байтов ключа. Интересным расширением пакета CRYPT_PKG было бы автоматическое определение порядка байтов в системе и перестановка байтов в ключе, чтобы избавить пользователя от необходимости учитывать это различие. Эта идея становится еще более привлекательной, если учесть, что в версиях начиная с 8.1.7.1 менять порядок следования байтов больше не нужно Ч соответствующий код в этой версии можно не выполнять, что обеспечивает одинаковые функциональные возможности во всех версиях сервера. Наконец, мы рассмотрели важную проблему управления ключами. Я потратил немало времени на разработку удобного пакета-оболочки, упрощающего шифрование и дешифрование данных. Вам для самостоятельного решения, однако, я оставляю самую сложную проблему Ч защиту ключей. Следует помнить, что при подозрении взлома ключей необходимо создать новый их набор, расшифровать и снова зашифровать все имеющиеся данные. Если все продумать заранее, подобных ситуаций можно избежать.
Пакет DBMS_OUTPUT Пакет DBMS_OUTPUT пользователи часто понимают неправильно. Они не понимают, что и как он делает и с какими ограничениями связано его использование. В этом разделе я попытаюсь объяснить все это. Я также предложу альтернативные пакеты с возможностями, аналогичными тем, что предоставляются пакетом DBMS_OUTPUT, но без упомянутых ограничений. Пакет DBMS_OUTPUT предназначен для эмуляции простых действий вывода на экран в языке PL/SQL. Он позволяет эмулировать выдачу на экран строки Hello World из PL/SQL-кода. Вы уже видели сотни примеров использования этого пакета. Вот типичный пример: ops$tkyte@DEV816> exec dbms_output.put_line('Hello World');
Hello World PL/SQL procedure successfully completed. Однако вы не видите команды SQL*Plus (или SVRMGRL), которая необходима, чтобы этот пример сработал. Вывод на экран можно включать и отключать следующим образом: ops$tkyte@DEV816> set serveroutput off ops$tkyte@DEV816> exec dbms_output.put_line('Hello World');
PL/SQL procedure successfully completed. ops$tkyte@DEV816> set serveroutput on ops$tkyte@DEV816> exec dbms_output.put_line('Hello World');
Hello World PL/SQL procedure successfully completed.
Пакет DBMS OUTPUT На самом деле язык PL/SQL не позволяет выполнять ввод-вывод на экран (вот почему я написал, что пакет предназначен для эмуляции такой возможности). На самом деле ввод-вывод на экран выполняет утилита SQL*Plus Ч из языка PL/SQL ничего нельзя выдать на терминал. PL/SQL-код выполняется другим процессом, обычно работающим на другой машине в сети. Утилиты SQL*Plus, SVRMGRL и другие инструментальные средства, однако, могут выдавать результаты на экран весьма просто. Это легко обнаружить при использовании пакета DBMS_OUTPUT в Java-коде или в программе на Pro*C (или в любой другой программе) Ч выданные пакетом DBMS_OUTPUT результаты никогда не выдаются на экран. Дело в том, что приложение само отвечает за выдачу этих результатов.
Как работает пакет DBMS_OUTPUT В пакете DBMS_OUTPUT подпрограмм немного. Чаще всего используются следующие. Х PUT. Выдает строку, данные типа NUMBER или DATE в буфер вывода, не добавляя символ новой строки. Х PUT_LINE. Выдает строку, данные типа NUMBER или DATE в буфер вывода и добавляет символ новой строки. Х NEW_LINE. Выдает в буфер вывода символ новой строки. Х ENABLE/DISABLE. Включает и отключает выдачу в буфер, используя DBMS_OUTPUT. Эти процедуры выдают данные во внутренний буфер;
PL/SQL-таблицу в теле пакета DBMS_OUTPUT. Общая длина выдаваемой строки (сумма всех байтов, помещенных в буфер пользователем до вызова процедуры PUT_LINE или NEW_LINE, завершающей эту строку) должна быть не более 255 байт. Выданные результаты буферизуются в этой таблице и не будут видны в среде SQL*Plus, пока не завершится выполнение соответствующего PL/SQL-кода. Язык PL/SQL не позволяет ничего выдавать на терминал, данные просто помещаются в PL/SQL-таблицу. При вызове процедуры DBMS_OUTPUT.PUT_LINE пакет DBMS_OUTPUT сохраняет соответствующие данные в массиве (PL/SQL-таблице) и возвращает управление. Пока работа вызывающей процедуры не завершится, результаты на экран не выдаются. Даже после этого результаты можно увидеть, только если используемый клиент "знает" о пакете DBMS_OUTPUT и сам позаботится о выдаче накопленных результатов. Утилита SQL*Plus, например, вызывает DBMS_OUTPUT.GET_LINES для получения части содержимого буфера пакета DBMS_OUTPUT и выдачи его на экран. Если вызвать хранимую процедуру из приложения на языке Java/JDBC, предположение о том, что результаты DBMS_OUTPUT окажутся там же, где и данные, выданные с помощью функции System.out.println, не оправдается. Если клиентское приложение не позаботится о выборке и выдаче данных, они просто исчезнут. Как сделать это в программе на языке Java/JDBC будет продемонстрировано в этом разделе. При использовании пакета DBMS_OUTPUT больше всего сбивает с толку то, что результат буферизуется и не выдается до завершения процедуры. Пользователи зна Приложение А ют о пакете DBMS_OUTPUT и пытаются использовать его для контроля продолжительного процесса. Другими словами, они повсеместно вставляют в код вызовы DBMS_OUTPUT.PUT_LINE и запускают процедуру в среде SQL*Plus. Они ждут, что результаты начнут выдаваться на экран, и очень расстраиваются, когда этого не происходит (потому что и не может произойти). Не зная особенностей реализации, трудно понять, почему результаты не выдаются сразу. Если понять, что процедуры на языке PL/SQL (а также внешние процедуры на языках Java и С), работающие на сервере, не выполняют ввод-вывод на экран и что пакет DBMS_OUTPUT просто накапливает данные в большом массиве, ситуация проясняется. Вот когда имеет смысл вернуться к разделу, посвященному пакету DBMS_APPLICATION_INFO, и почитать о способе контроля работы продолжительных действий. Для контроля продолжительно работающих процессов надо использовать пакет DBMS_APPLICATION_INFO, а не DBMS_OUTPUT. Для чего же тогда пакет DBMS_OUTPUT? Он прекрасно подходит для выдачи простых отчетов и создания утилит. В главе 23 была представлена процедура PRINT_TABLE, использующая средства пакета DBMS_OUTPUT для генерации результатов следующего вида: SQL> exec print_table('select * from all_users where username = user') ;
USERNAME : OPS$TKYTE USER_ID : 334 CREATED : 02-oct-2000 10:02:12 PL/SQL procedure successfully completed. Она выдает данные по одному столбцу в строке, а не одной, разбитой на части, строкой. Прекрасно подходит для выдачи длинных строк таблиц, которые переносятся по границе экрана, что затрудняет чтение. Теперь, зная, что пакет DBMS_OUTPUT работает путем помещения данных в PL/ SQL-таблицу, можно изучать его реализацию. При включении DBMS_OUTPUT (вызовом DBMS_OUTPUT.ENABLE либо с помощью команды SET SERVEROUTPUT ON) мы не только позволяем накапливать данные, но и задаем максимальный объем данных, которые сможем накопить. По умолчанию, если выполнить: SQL> set serveroutput on создается буфер DBMS_OUTPUT размером 20000 байт. При достижении этого предела будет выдано: begin * ERROR at line 1: ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 106 ORA-06512: at "SYS.DBMS_OUTPUT", line 65 ORA-06512: at line 3 Этот предел можно увеличить, выполнив команду SET SERVEROUTPUT (или вызвав процедуру DBMS_OUTPUT.ENABLE):
Пакет DBMS_OUTPUT 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-ый элемент массива, он плотно упаковывает массив. В первом элементе массива может оказаться первых пять строк, выданных с помощью этого пакета. Для этого (чтобы поместить несколько строк в одну) необходимо расходовать дополнительные ресурсы. Эти дополнительные ресурсы для размещения сведений о том, где находятся данные пользователя и какого они размера, выделяются из того же ограниченного пространства. Поэтому, даже выполнив команду 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, пакет DBMS_OUTPUT будет использовать не более 2000 элементов массива. Пакет DBMS_OUTPUT будет сохранять в каждом элементе массива не более 504 байт данных (обычно Ч меньше). Пакет DBMS_OUTPUT упаковывает данные в строку массива в следующем формате: Буфер(1) = '
Буфер(2) = '
Так что для каждой выдаваемой строки будет использоваться дополнительно 4 байта Ч для одного пробела и трехзначного числа. Каждая строка в буфере DBMS_OUTPUT имеет длину не более 504 байт, и пакет DBMS_OUTPUT не будет переносить данные с одной строки на другую. Поэтому, например, если использовать максимальную длину строки и всегда выдавать строки по 255 байт, пакет DBMS_OUTPUT сможет упаковать в элемент массива только одну строку. Причина в том, что значение (255+4) * 2 = 518 больше, чем 504, а пакет DBMS_OUTPUT не будет делить строку на два элемента своего массива. Две строки такого размера просто не помещаются в одну строку массива DBMS_OUTPUT. Поэтому даже если затребовать буфер размером 1000000 байт, вы сможете поместить в него только 510000 байт данных Ч чуть больше половины запрошенного. Значение 510000 получено, исходя из того, что длина выдаваемых строк Ч 255 байт;
Приложение А всего же строк может быть не более 2000 (вспомните представленное ранее вычисление значения IDXLIMIT);
255*2000 = 510000. С другой стороны, при использовании строк длиной 248 байт можно помещать по две строки в элемент массива, что позволит выдать 248 * 2 * 2000 = 992000 байт Ч чуть больше 99% запрошенного пространства. Фактически, это максимум того, на что можно рассчитывать при использовании пакета DBMS_OUTPUT Ч 992000 байт данных. Больше выдать с помощью этого пакета нельзя. Как уже было сказано, при использовании строк фиксированной длины очень легко подсчитать выдаваемое количество строк. Если известна фиксированная длина строки, например 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 байт будут выделены при вызове DBMS_OUTPUT.PUT_LINE('hello world'), т.е. тот же объем, что и при выдаче длинной строки. Результат, состоящий из 2000 строк, занял бы 1000000 байт, даже если выдать 2000 раз строку hello world, что фактически требует только около 22 Кбайт. Так что подобная упаковка была предусмотрена для предотвращения выделения лишней памяти в области PGA для буферного массива. В последних Пакет DBMS_OUTPUT версиях Oracle (начиная с 8.0) память выделяется по-другому. Размер элементов массива меняется динамически, и упаковка больше не нужна. Поэтому ее можно считать унаследованной от старых версий. Последнее, что хотелось бы сказать о работе пакета DBMS_OUTPUT, Ч это то, что начальные пробелы в выдаваемых строках удаляются. Ошибочно думать, что это "свойство" пакета DBMS_OUTPUT. Фактически, это "свойство" SQL*Plus (хотя, я знаю многих, кто склонен считать это ошибкой). Небольшой тест позволит понять, что я имею в виду: ops$tkyte@ORA8I.WORLD> exec dbms_output.put_line(' hello world'), hello world PL/SQL procedure successfully completed. При передаче пакету DBMS_OUTPUT строки ' hello world', начальные пробелы оказались удалены. Считается, что это делает пакет DBMS_OUTPUT, но на самом деле это не так. Усекает начальные пробелы утилита SQL*Plus. Простое решение этой проблемы Ч использовать расширенный синтаксис команды SET SERVEROUTPUT. Вот полный синтаксис этой команды: set serveroutput {ON|OFF} [SIZE n] [FORMAT {WRAPPED|TORD_WRAPPED|TRUNCATED}] Значение конструкций формата выдачи строк представлено ниже. Х WRAPPED. Утилита SQL*Plus при необходимости переносит на новую строку выданные сервером результаты, начиная с позиции, задаваемой командой SET LINESIZE. WORD_WRAPPED. Переносится каждая строка выданных сервером результатов, начиная с позиции, задаваемой командой SET LINESIZE. Перенос выполняется по словам. Утилита SQL*Plus выравнивает каждую строку влево, удаляя все начальные пробелы. Это значение является стандартным. TRUNCATED. Каждая строка результатов сервера усекается до длины, задаваемой командой SET LINESIZE.
Х Х Действие каждой опции форматирования проще всего понять на примере: SQL>set linesize 20 SQL>set serveroutput on format wrapped SQL>exec dbms_output.put_line(' Hello World !!!! ! PL/SQL procedure successfully completed. SQL>set serveroutput on format word_wrapped SQL>exec dbms_output.put_line(' Hello Hello World !!!!!
Hello World !!!!!');
World !!!!!');
PL/SQL procedure successfully completed. SQL>set serveroutput on format truncated SQL>execdbms_output.put_line(' Hello World !!!!!');
Приложение А Hello World PL/SQL procedure successfully completed.
Пакет DBMS_OUTPUT в других средах Стандартные средства, такие как SQL*Plus и SVRMGRL, учитывают особенности работы пакета DBMS_OUTPUT. Большинство остальных сред Ч нет. Например, обычная программа на языке Java/JDBC о пакете DBMS_OUTPUT ничего "не знает". В этом подразделе мы рассмотрим, как учесть в такой программе особенности работы пакета DBMS_OUTPUT. Такой же подход можно применить в любой среде программирования. Методы, использованные мной для языка Java, можно применить в среде Pro*C, OCI, VB и в любой другой среде. Начнем с небольшой PL/SQL-процедуры, генерирующей данные для вывода: scott@TKYTE816> create or replace 2 procedure emp_report 3 as 4 begin 5 dbms_output.put_line 6 (rpad('Empno', 7) || 7 rpad('Ename',12) || 8 rpad('Job',ll));
9 10 dbms_output.put_line 11 (rpad('-', 5, '-') || 12 rpad('-',12,'-') || 13 rpad(' -',11,'-'));
14 15 for x in (select * from emp) 16 loop 17 dbms_output.put_line 18 (to_char(x.empno, '9999') || ' 19 rpad(x.ename, 12) || 20 rpad(x.job, 11));
21 end loop;
22 end;
23 / Procedure created. scott@TKYTE816> set serveroutput on format wrapped scott@TKYTE816> exec emp_report Empno Ename Job 7369 7499 7934 SMITH ALLEN MILLER CLERK SALESMAN CLERK ' || PL/SQL procedure successfully completed.
Пакет DBMS_OUTPUT Теперь создадим класс, позволяющий работать с буфером пакета DBMS_OUTPUT в среде Java/JDBC: import j a v a. s q l. * ;
>
private CallableStatement disable_stmt;
private CallableStatement show_stmt;
/* * * * * * * * * * * * * * Конструктор готовит три оператора, которые предполагается выполнить. Оператор, который мы готовим для SHOW, Ч это блок кода для возврата строки результатов DBMS_OUTPUT. Обычно можно использовать тип PL/SQL-таблицы, но JDBC-драйверы не поддерживают типы PL/SQL-таблиц. Поэтому мы получаем результат и конкатенируем его в строку. Будем выбирать не более одной строки результата, так что можем превзойти значение параметра MAXBYTES. Если установить MAXBYTES равным 10, а первая строка имеет длину 100 байт, вы получите 100 байт. Параметр MAXBYTES не даст получить следующую строку, но разбиения строки не произойдет.
*/ public DbmsOutput(Connection conn) throws SQLException { enable_stmt = conn.prepareCall ("begin dbms_output.enable (: 1);
end;
");
disable_stmt = conn.prepareCall("begin dbms_output.disable;
end;
");
show_stmt = conn.prepareCall( "declare " + l_line varchar2(255);
" + " l_done number;
" + " l_buffer long;
" + "begin " + " loop " + " exit when length(l_buffer)+255 > :maxbytes OR l_done = 1;
" + Приложение А dbms_output.get_line(l_line, l_done);
" + l_buffer := l_buffer || l_line || chr(10);
" + " end loop;
" + " :done := l_done;
" + " :buffer := l_buffer;
" + "end;
");
" > /* * ENABLE задает размер и выполняет * вызов DBMS_OOTPOT.ENABLE * */ public void enable(int size) throws SQLException { enable_stmt.setInt(l, size);
enable_stmt.executeUpdate();
) /* * DISABLE просто вызывает DBMS_OUTPUT.DISABLE */ public void disable() throws SQLException { disable_stmt.executeUpdate() ;
) /* * Функция SHOW выполняет основную работу. Она циклически * выбирает данные DBMS_OUTPUT, * по 32000 байт за раз (плюс-минус 255 байт). * По умолчанию она выдает результат в стандартный * выходной поток (для перенаправления результатов * достаточно перенаправить System.out). */ public void show() throws SQLException { int done = 0;
show_stmt.registerOutParameter(2, java.sql.Types.INTEGER);
show_stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
for(;
;
) { show_stmt.setInt(l, 32000);
show_stmt.executeUpdate();
System.out.print(show_stmt.getString(3));
if ((done = show_stmt.getInt(2)) = 1) break;
) } /* * Функция CLOSE закрывает все выполняемые операторы, связанные * с классом DbmsOutput. Ее надо вызывать, если вы создали оператор Пакет DBMS OUTPUT * DbmsOutput и он выходит из области действия, как и для любого * вызываемого оператора, результирующего множества и т.п. */ public void close() throws SQLException { enable_stmt.close();
disable_stmt.close();
show_stmt.close();
} } Чтобы продемонстрировать использование этого класса, я создал небольшую тестовую программу на языке Java/JDBC. Здесь dbserver Ч имя сервера базы данных, а ora8i Ч имя службы, соответствующей экземпляру: import java.sql.*;
>
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:6dbserver:1521:ora8i", "scott", "tiger");
conn.setAutoCommit (false);
Statement stmt = conn.createStatement();
DbmsOutput dbmsOutput = new DbmsOutput(conn);
dbmsOutput.enable(1000000);
stmt.execute ("begin emp_report;
end;
");
stmt.close();
dbmsOutput.show();
dbmsOutput.close();
conn.close();
} } Теперь протестируем программу, скомпилировав и выполнив ее: $ javac test.java $ java test Empno Ename 7369 SMITH 7499 ALLEN 7521 WARD Job CLERK SALESMAN SALESMAN 1696 Приложение А Итак, это показывает, как в языке Java использовать средства пакета DBMS_OUTPUT. Как и в случае утилиты SQL*Plus, необходимо вызывать DbmsOutput.show() после выполнения оператора, выдающего какие-либо результаты. После выполнения оператора INSERT, UPDATE, DELETE или вызова хранимой процедуры утилита SQL*Plus вызывает подпрограмму DBMS_OUTPUT.GET_LINES для получения результата. Приложение на языке Java (или С, или VB) должно вызывать функцию SHOW для выдачи результатов.
Обход ограничений Я обнаружил два основных ограничения пакета DBMS_OUTPUT: Х Длина "строки" ограничена 255 байтами. Символ новой строки надо вставлять не реже, чем через 255 байт. Х Общий объем выдаваемых результатов ограничен и находится в пределах от 200000 байт (если выдавать по одному байту в строке) до 992000 байт (если выдавать по 248 байт в строке). Для некоторых действий этого хватает, для других недостаточно, особенно если учесть, что общий объем результатов, которые можно выдать, зависит от длины и порядка выдачи строк. Итак, что можно сделать? В следующих подразделах я предложу три способа обойти эти ограничения.
Использование небольшой функции-оболочки или другого пакета Иногда 255 байт не хватает. Необходимо выдать отладочную информацию, и получается строка длиной 500 символов. Ее надо выдать, при этом не так важен формат, как возможность получить результаты. В этом случае можно написать небольшую подпрограмму-оболочку. Во всех моих базах данных установлена такая подпрограмма, позволяющая обойти ограничение длины строки и заодно сократить вызов, поскольку строка DBMS_OUTPUT.PUT_LINE состоит из 20 символов, что многовато для постоянного набора. Я часто использую процедуру Р. Вот эта процедура: procedure p(p_string in varchar2) is l_string long default p_string;
begin loop exit when l_string is null;
dbms_output.put_line(substr(l_string, 1, 248));
l_string := substr(l_string, 251);
end loop;
end;
Она не переносит переданную строку по словам и вообще ничего особенного не делает. Она принимает строку размером до 32 Кбайт и выдает ее. Длинную строку она разобьет на ряд строк размером 248 байт каждая (248 Ч оптимальное значение, кото Пакет DBMS OUTPUT рое мы вычислили ранее;
оно позволяет выдать максимальный объем результатов). Процедура меняет данные (поэтому она не подходит для увеличения длины строки в процедуре, создающей текстовый файл), выдавая переданную строку в виде нескольких строк. Процедура решает простую проблему. Она позволяет избавиться от сообщения об ошибке: ops$tkyte@ORA8l.WORLD> exec dbms_output.put_line(rpad('*',256,'*')) BEGIN dbms_output.put_line(rpad('*', 256,'*'));
END;
* ERROR at line 1: ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line ORA-06512: at "SYS.DBMS_OUTPUT", line 99 ORA-06512: at "SYS.DBMS_OUTPUT", line 65 ORA-06512: at line 1 получаемого при выдаче отладочной информации или печати отчета. Более надежный способ обойти это ограничение, особенно при сбросе данных в текстовый файл, Ч использовать вместо пакета DBMS_OUTPUT средства пакета UTL_FILE для записи непосредственно в файл. Пакет UTL_FILE ограничивает размер выдаваемой строки 32 Кбайтами и не ограничивает размер файла. С помощью пакета UTL_FILE можно создавать файлы только на сервере, так что это решение не подойдет, если предполагается использование утилиты SQL*Plus на клиенте, подключенном по сети, с выдачей результатов в локальный файл на клиенте. Если же необходимо создать текстовый файл данных для загрузки и его создание на сервере допустимо, пакет UTL_FILE вполне можно использовать. Итак, мы рассмотрели две из трех возможностей. Переходим к последней.
Создание аналога пакета DBMS_OUTPUT Это универсальное решение, хорошо работающее во всех средах. Мы собираемся заново изобрести велосипед, но велосипед более совершенный. Создадим пакет-аналог DBMS_OUTPUT, который: Х ограничивает длину строки 4000 байтами (это, к сожалению, ограничение языка SQL, а не PL/SQL);
Х не ограничивает количество выдаваемых строк;
Х позволяет получать результаты на клиенте, как и пакет DBMS_OUTPUT;
Х не позволяет утилите SQL*Plus удалять начальные пробелы в строке, независимо от режима;
Х позволяет выбирать результаты как результирующее множество на клиенте с помощью курсора (к результату можно будет делать запросы). Начнем с создания SQL-типа. Этот тип будет использоваться для буфера, аналогичного тому, что применяется в пакете DBMS_OUTPUT. Поскольку это SQL-тип, можно применять к данным операторы SELECT *. Поскольку практически в любой среде можно выполнить оператор SELECT *, выдать результаты не представит сложности.
Приложение А ops$tkyte@ORA8I.WORLD> create or replace type my_dbms_output_type 2 as table of varchar2(4000) 3/ Type created. Теперь переходим к спецификации пакета-аналога DBMS_OUTPUT. Этот пакет устроен подобно DBMS_OUTPUT. В нем нет только подпрограмм GET_LINE и GET_LINES, поскольку в нашей реализации они не нужны. Процедуры PUT, PUT_UNE и NEW_LINE работают точно так же, как их аналоги в пакете DBMS_OUTPUT. Функции GET, FLUSH и GET_AND_FLUSH Ч новые. Аналогов для них в пакете DBMS_OUTPUT нет. Эти функции используются для получения результата после выполнения хранимой процедуры. Функция GET будет просто возвращать данные из буфера, но не "стирать" их. Можно вызывать функцию GET повторно для получения одного и того же содержимого буфера (пакет DBMS_OUTPUT всегда сбрасывает буфер). Функция FLUSH позволяет сбросить буфер, другими словами, очистить его. Функция GET_AND_FLUSH, как можно догадаться, возвращает содержимое буфера и очищает его;
следующие вызванные подпрограммы пакета будут работать с пустым буфером: tkyte@TKYTE816> create or replace package my_dbms_output 2 as 3 procedure enable;
4 procedure disable;
5 6 procedure put(s in varchar2);
7 procedure put_line(s in varchar2);
8 procedure new_line;
9 10 function get return my_dbms_output_type;
11 procedure flush;
12 function get_and_flush return my_dbms_output_type;
13 end;
14 / Package created. Используем некоторые из методов, которые рассмотрены в главе 20, посвященной использованию объектно-реляционных средств. В частности, используем возможность выполнять операторы SELECT * from PLSQL_FUNCTION Ч именно так и будет работать аналог пакета DBMS_OUTPUT. Наибольший интерес представляют подпрограммы ENABLE, DISABLE, PUT, PUT_LINE и NEW_LINE. Они работают более-менее похоже на одноименные подпрограммы пакета DBMS_OUTPUT. Основное различие в том, что процедура ENABLE не имеет параметров, а пакет MY_DBMS_OUTPUT по умолчанию выдает результаты (тогда как пакет DBMS_OUTPUT по умолчанию их не выдает). Выдаваемые результаты ограничены объемом оперативной памяти, который вы можете выделить в системе (учтите это!). Рассмотрим тело пакета. Реализация этого пакета очень проста. Имеется глобальная переменная пакета, используемая в качестве буфера для результатов. Мы добавляем строки текста в буфер.выделяя при необходимости дополнительную память. Чтобы сбросить буфер, присваиваем ему пустую таблицу. Поскольку все так просто, я представлю реализацию без комментариев:
Пакет DBMS OUTPUT tkyte@TKYTE816> create or replace package body my_dbms_output 2 as 3 4 g_data my_dbms_output_type := my_dbms_output_type();
5 g_enabled boolean default TRUE;
6 7 procedure enable 8 is 9 begin 10 g_enabled := TRUE;
11 end;
12 13 procedure disable 14 is 15 begin 16 g_enabled := FALSE;
17 end;
18 19 procedure put(s in varchar2) 20 is 21 begin 22 if (NOT g_enabled) then return;
end if;
23 if (g_data.count <> 0) then 24 g_data(g_data.last) :=g_data(g_data.last) || s;
25 else 26 g_data.extend;
27 g_data(l) := s;
28 end if;
29 end;
30 31 procedure put_line(s in varchar2) 32 is 33 begin 34 if (NOT g_enabled) then return;
end if;
35 put(s);
36 g_data.extend;
37 end;
38 39 procedure new_line 40 is 41 begin 42 if (NOT g_enabled) then return;
end if;
43 put(null);
44 g_data.extend;
45 end;
46 47 48 procedure flush 49 is 50 l_empty my_dbms_output_type := my_dbms_output_type();
51 begin 52 g_data := l_empty;
1700 Приложение А 53 end;
54 55 function get return my_dbms_output_type 56 is 57 begin 58 return g_data;
59 end;
60 61 function get_and_flush return my_dbms_output_type 62 is 63 l_data my_dbms_output_type := g_data;
64 l_empty my_dbms_output_type := my_dbms_output_type();
65 begin 66 g_data := l_empty;
67 return l_data;
68 end;
69 end;
70 / Package body created. Теперь, чтобы сделать пакет действительно полезным, необходим простой метод получения содержимого буфера. Можно вызывать функции MY_DBMS_OUTPUT.GET или GET_AND_FLUSH и выбирать содержимое переменной объектного типа самостоятельно или использовать одно из созданных ниже представлений. Первое представление, MY_DBMS_OUTPUT_PEEK, обеспечивает SQL-интерфейс к функции GET. Оно позволяет многократно запрашивать данные из буфера результатов, фактически обеспечивая просмотр буфера без сброса результатов. Второе представление, MY_DBMS_OUTPUT_VIEW, позволяет выполнить запрос к буферу один раз любые последующие вызовы подпрограмм PUT, PUT_LINE, NEW_LINE, GET или GET_AND_FLUSH будут работать с пустым буфером результатов. Оператор SELECT * FROM MY_DBMS_OUTPUT_VIEW аналогичен вызову функции DBMS_OUTPUT.GET_LINES. Буфер сбрасывается: tkyte@TKYTE816> create or replace 2 view my_dbms_output_peek (text) 3 as 4 select * 5 from TABLE (cast(my_dbms_output.get() 6 as my_dbms_output_type)) 7/ View created. tkyte@TKYTE816> create or replace 2 view my_dbms_output_view (text) 3 as 4 select * 5 from TABLE (cast(my_dbms_output.get_and_flush() 6 as my_dbms_output_type)) 7/ View created.
Пакет DBMS_OUTPUT Теперь все готово для демонстрации работы этого решения. Выполним процедуру, генерирующую данные в буфер, а затем посмотрим, как их выдать и что с ними можно делать: tkyte@TKYTE816> begin 2 my_dbms_output.put_line('hello');
3 my_dbms_output.put('Hey ' ) ;
4 my_dbms_output.put('there ' ) ;
5 my_dbms_output.new_line;
6 7 for i in 1.. 20 8 loop 9 my_dbms_output.put_line(rpad(' ', i, ' ') || i ) ;
10 end loop;
11 end;
12 / PL/SQL procedure successfully completed. tkyte@TKYTE816> select * 2 from my_dbms_output_peek 3/ TEXT hello Hey there 1 2 19 20 23 rows selected. Интересно, что утилита SQL*Plus, создатели которой ничего не знали о пакете MY_DBMS_OUTPUT, не выдает результаты автоматически. Надо ей помочь, выполнив запрос, выдающий результаты. Поскольку для получения результатов используются SQL-операторы, вы легко сможете написать собственный класс DbmsOutput на языке Java/JDBC. Это будет простой объект ResultSet Ч ничего больше. В качестве последнего комментария к этому коду скажу, что результаты ожидают выборки в буфере: tkyte@TKYTE816> s e l e c t * 2 from my_dbms_output_peek 3/ TEXT hello Hey there 1 Приложение А 19 20 23 rows selected. Более того, при выборке можно задавать конструкцию WHERE, сортировать результаты, соединять их с другими таблицами и т.д. (как и для данных любой таблицы): tkyte@TKYTE816> select * 2 from my_dbms_output_peek 3 where text like '%1%' 4/ TEXT 1 10 11 18 19 11 rows selected. Если же повторное обращение к данным нежелательно, можно выбрать результаты с помощью оператора SELECT из представления MY_DBMS_OUTPUT_VIEW: tkyte@TKYTE816> select * 2 from my_dbms_output_view 3/ TEXT hello Hey there 1 19 20 23 rows selected. tkyte@TKYTE816> select * 2 from my_dbms_output_view 3/ no rows selected В этом случае данные можно получить только один раз. Эта новая реализация пакета DBMS_OUTPUT увеличивает допустимую длину строки с 255 байт до 4000 и фактически снимает ограничение на общий объем выдаваемых результатов (вы, однако, ограничены объемом оперативной памяти сервера). Она также предоставляет ряд новых возможностей (можно делать запросы к результатам, сортиро Пакет DBMS_OUTPUT вать их и т.д). Она позволяет избавиться от стандартного удаления начальных пробелов в среде SQL*Plus. Наконец, в отличие от пакета UTL_FILE результаты MY_DBMS_OUTPUT можно сбросить в файл на клиенте точно так же, как и результаты пакета DBMS_OUTPUT, что делает пакет MY_DBMS_OUTPUT достойной заменой DBMS_OUTPUT для удаленного клиента. Вы можете спросить, почему я использовал при реализации объектный тип, а не временную таблицу. Причина в объеме кода и дополнительном расходе ресурсов. Объем кода для управления временной таблицей, связанного с добавлением столбца для запоминания порядка данных, по сравнению с этой простой реализацией окажется существенно больше. Кроме того, работа с временной таблицей требует ввода-вывода и дополнительного расхода ресурсов. Наконец, сложно реализовать "представление со сбросом", когда буфер результатов автоматически очищается при выборке данных. Короче, использование объектного типа облегчает реализацию. Если бы я собирался использовать этот пакет для выдачи десятков мегабайт результатов, то пересмотрел бы способ буферизации и использовал временную таблицу. Для средних же объемов данных эта реализация вполне подходит.
Резюме В этом разделе мы рассмотрели пакет DBMS_OUTPUT. Теперь, зная, как он работает, вы не пострадаете от побочных эффектов этой реализации. Вы будете готовы к тому, что между запрошенным размером буфера и суммарным объемом результатов, которые можно в него выдать, иногда нет очевидной зависимости. Вы будете знать, что выдать строку результатов длиной более 255 байт нельзя. Результаты DBMS_OUTPUT не выдаются, пока не завершится выполнение процедуры или оператора, но и тогда они будут выданы только при условии, что среда, из которой выполняются запросы, поддерживает пакет DBMS_OUTPUT. Помимо анализа особенностей пакета DBMS_OUTPUT мы рассмотрели способы обойти ограничения, связанные с его применением: для этого рекомендуется использование других средств. Можно использовать пакет UTL_FILE для создания текстовых файлов с результатами или процедуры типа P, не только уменьшающие количество набираемых символов, но и обеспечивающие выдачу длинных строк. Можно реализовать и собственный пакет с аналогичными функциями, не имеющий подобных ограничений. Пакет DBMS_OUTPUT Ч удачный пример того, как тривиальный на первый взгляд компонент может оказаться сложной программой с неожиданными побочными эффектами. Когда читаешь описание пакета DBMS_OUTPUT в руководстве Oracle Supplied PL/SQL Packages Reference, все кажется простым и понятным. А потом неожиданно возникают проблемы с суммарным объемом выдаваемых результатов и т.п. Знание особенностей реализации пакета помогает избежать этих проблем.
Пакет DBMS_PROFILER Появления стандартного средства профилирования ждали давно (по крайней мере, я). Пакет DBMS_PROFILER представляет собой профилировщик исходного кода для PL/SQL-приложений. Раньше приходилось настраивать производительность PL/SQLприложений с помощью средств SQL_TRACE и TKPROF. Они помогали выявить долго выполняющиеся SQL-операторы, но определить узкие места в в PL/SQL-коде из 5000 строк (особенно, если он написан кем-то другим) было практически невозможно. Чтобы определить проблемные фрагменты кода, приходилось вставлять в него множество вызовов функции DBMS_UTILITY.GET_TIME для измерения времени выполнения. Теперь этого делать не нужно: можно воспользоваться возможностями пакета DBMS_PROFILER. Я собираюсь продемонстрировать, как его обычно используют. Лично я использую небольшую часть функциональных возможностей этого пакета: нахожу с его помощью проблемные фрагменты и занимаюсь ими непосредственно. Я использую пакет DBMS_PROFILER очень примитивным способом. Он позволяет, однако, делать намного больше, чем представлено в этом разделе. Статистическая информация собирается в таблицы базы данных. Они позволяют сохранять статистическую информацию за несколько "прогонов" кода. Некоторых это устраивает, но я предпочитаю сохранять результаты одного-двух последних прогонов. Дополнительная информация лишь сбивает с толку. Иногда информации бывает слишком много. Администратору базы данных, возможно, придется установить профилировщик в базе данных. Процедура установки этого пакета проста: Х cd [ORACLE_HOME]/rdbms/admin;
Пакет DBMS_PROFILER Х с помощью SVRMGRL (или SQL*Plus Ч прим. научн. ред.) подключиться как SYS или INTERNAL;
Х выполнить сценарий profload.sql. После этого надо установить таблицы статистической информации. Их можно установить в базе данных в одном экземпляре, но я рекомендую каждому разработчику создать свой набор этих таблиц. К счастью, пакет DBMS_PROFILER создан с правами вызывающего и использует неуточненные имена таблиц, так что таблицы статистической информации можно установить в каждой схеме, и они будут корректно использоваться пакетом-профилировщиком. При использовании собственных таблиц каждый разработчик будет видеть только свои результаты профилирования, а не результаты коллег по работе. Чтобы создать таблицы статистической информации в своей схеме, надо выполнить сценарий [ORACLE_HOME]\rdbms\admin\proftab.sql в SQL*Plus. После выполнения сценария proftab.sql надо выполнить сценарий profrep.sql. Этот сценарий создает представления и пакеты для создания отчетов по таблицам профилировщика. Сценарий profrep.sql находится в файле [ORACLE_HOME]\plsql\demo\profrep.sql. Этот сценарий надо выполнить в своей схеме после создания таблиц. Я обычно создаю небольшой сценарий для очистки таблиц профилировщика и выполняю эту очистку постоянно. После одного-двух тестовых прогонов и анализа результатов я выполняю этот сценарий. В сценарии, который я назвал profreset.sql, выполняется следующее: Ч используются операторы d e l e t e, поскольку таблицы связаны требованием внешнего ключа delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler runs;
Теперь можно начинать профилирование. Я собираюсь продемонстрировать использование этого пакета путем запуска двух разных реализаций алгоритма вычисления факториала. Один алгоритм Ч рекурсивный, а другой Ч итеративный. Для того чтобы определить, какой из них работает быстрее и какие фрагменты кода наиболее медленные в каждой реализации, используется профилировщик. Тестирование выполняется следующим образом: tkyte@TKYTE816> @profreset tkyte@TKYTE816> create or replace 2 function fact_recursive(n int) return number 3 as 4 begin 5 if (n = 1) 6 then 7 return 1;
8 else 9 return n * fact_recursive(n-l);
10 end if;
11 end;
12 / Function created.
Приложение А tkyte@TKYTE816> create or replace 2 function fact_iterative(n int) return number 3 as 4 l_result number default 1;
5 begin 6 for i in 2.. n 7 loop 8 l_result := l_result * i;
9 end loop;
10 return l_result;
11 end;
12 / Function created. tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> exec dbms_profiler.start_profiler('factorial recursive') PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 for i in 1.. 50 loop 3 dbms_output.put_line(fact_recursive(50));
4 end loop;
5 end;
6/ 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dbms_profiler.stop_profiler PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dbms_profiler.start_profiler('factorial iterative') PL/SQL procedure successfully completed. tkyte@TKYTE816> begin 2 for i in 1.. 50 loop 3 dbms_output.put_line(fact_iterative(50));
4 end loop;
5 end;
6/ 30414093201713378043612608166064768844300000000000000000000000000 30414093201713378043612608166064768844300000000000000000000000000 PL/SQL procedure successfully completed. tkyte@TKYTE816> exec dbms_profiler.stop_profiler PL/SQL procedure successfully completed. Для сбора статистической информации очередного прогона профилировщика необходимо вызвать процедуру START_PROFILER. Каждому прогону мы даем определен Пакет DBMS_PROFILER ное имя, а затем выполняем соответствующий код. Я вызывал каждую из функций вычисления факториала 50 раз, прежде чем завершить сбор статистической информации для прогона. Теперь все готово для анализа результатов. В каталоге [ORACLE_HOME]/plsql/demo есть сценарий profsum.sql. Не запускайте его: некоторые запросы этого сценария выполняются очень долго (иногда Ч несколько часов), и он выдает очень много данных. (Ниже представлен измененный сценарий profsum.sql, который использую я;
он выдает почти ту же информацию, но запросы выполняются очень быстро, а многие отчеты с избыточной детализацией просто не создаются.) Кроме того, одни запросы учитывают время выполнения вызова STOP_PROFILER, другие Ч нет. Это затрудняет сравнение результатов запросов. Я изменил все запросы так, чтобы время выполнения вызовов пакета профилировщика нигде не учитывалось. Мой сценарий profsum.sql представлен ниже. Кроме того, он доступен для загрузки на сайте set set set set set echo off linesize 5000 trimspool on serveroutput on termout off owner format all unit_name format al4 text format a21 word_wrapped runid format 9999 secs format 999.99 hsecs format 999.99 grand_total format 9999.99 run_comment format all word_wrapped line# format 99999 pct format 999.9 unit_owner format all column column column column column column column column column column column spool profsum.out /* Очистка и пересоздание итоговых результатов. */ update plsql_profiler_units set total_time = 0;
execute prof_report_utilities.rollup_all_runs;
prompt = prompt = prompt ======================== prompt Суммарное время select grand_total/1000000000 as grand_total from plsql_profiler_grand_total;
prompt = prompt = prompt ===================== prompt Суммарное время каждого прогона select runid, substr(run_comment,l, 30) as run_comment, Приложение А run_total_time/1000000000 as secs from (select a.runid, sum(a.total_time) run_total_time, b.run_comment from plsql_profiler_units a, plsql_profiler_runs b where a.runid = b.runid group by a.runid, b.run_comment) where run_total_time > 0 order by runid asc;
prompt = prompt = prompt ================== prompt Процент времени, приходящийся на каждый модуль, отдельно для -> каждого прогона select pl.runid, substr(p2.run_comment, 1, 20) as run_comment, pl.unit_owner, decode(pl.unit_name, '', '', substr(pl.unit_name,l, 20)) as unit_name, pl.total_time/1000000000 as secs, TO_CHAR(100*pl.total_time/p2.run_total_time, '999.9') as percentage from plsql_profiler_units p1, (select a.runid, sum(a.total_time) run_total_time, b.run_comment from plsql_profiler_units a, plsql_profiler_runs b where a.runid - b.runid group by a.runid, b.run_comment) p2 where pl.runid=p2.runid and pl.total_time > 0 and p2.run_total_time > 0 and (pl.total_time/p2.run_total_time) >=.01 order by pl.runid asc, pl.total_time desc;
column secs form 9.99 prompt = prompt = prompt prompt Процент времени, приходящийся на каждый модуль, суммарно по всем -> прогонам select pl.unit_owner, decode(pl.unit_name, '', '', substr(pl.unit_name,l, 25)) as unit_name, pl.total_time/1000000000 as secs, TO_CHAR(100*pl.total_time/p2.grand_total, '99999.99') as percentage from plsql_profiler_units_cross_run p1, plsql_profiler_grand_total p2 order by pl.total_time DESC;
prompt = prompt = prompt ======================== prompt Строки, потребовавшие более 1% суммарного времени, отдельно по -> каждому прогону select pl.runid as runid, pl.total_time/10000000 as Hsecs, pl.total_time/p4.grand_total*100 as pct, Пакет DBMS_PROFILER substr(p2.unit_ovmer, 1, 20) as owner, decode(p2.unit_name, '', '', substr(p2.unit_name,l, 20)) as unit_name, pl.line#, (select p3.text from all_source p3 where p3.owner = p2.unit_owner and p3.line = pl.line# and p3.name=p2.unit_name and p3.type not in ('PACKAGE', 'TYPE')) text from plsql_profiler_data p1, plsql_profiler_units p2, plsql_profiler_grand_total p4 where (pl.total_time >= p4.grand_total/100) AND pl.runID = p2.runid and p2.unit_number=p1.unit_number order by pl.total_time desc;
prompt = prompt = p r o m p t= = = = = = = = = = = = = = = = = = = = = = = = prompt Наиболее популярные строки (более 1%), суммарно по всем прогонам select pl.total_time/10000000 as hsecs, pl.total_time/p4.grand_total*100 as pct, substr(pl.unit_owner, 1, 20) as unit_owner, decode(pl.unit_name, '', '', substr(pl.unit_name,l, 20)) as unit_name, pl.line#, (select p3.text from all_source рЗ where (p3.1ine = p1.line#) and (p3.owner = pl.unit_owner) AND (p3.name = pl.unit_name) and (p3.type not in ('PACKAGE', 'TYPE'))) text from plsql_profiler_lines_cross_run p1, plsql_profiler_grand_total p4 where (pl.total_time >= p4.grand_total/100) order by pl.total_time desc;
execute prof_report_utilities.rollup_all_runs;
prompt = prompt = prompt = = = = = = = = = prompt Количество реально выполненных строк в программных единицах (с -> группировкой по unit_name) select pl.unit_owner, pl.unit_name, count(decode( pl.total_occur, 0, null, 0)) as lines_executed, count(pl.line#) as lines_present, count(decode( pl.total_occur, 0, null, 0))/count(pl.line#) *100 as pct from plsql_profiler_lines_cross_run p1 where (pl.unit_type in ('PACKAGE BODY', 'TYPE BODY', Приложение А 'PROCEDURE', 'FUNCTION')) group by pl.unit_owner, pl.unit_name;
prompt = prompt = prompt ==================== prompt Количество реально выполненных строк для всех программных единиц select count(pl.line#) as lines_executed from plsql_profiler_lines_cross_run p1 where (pl.unit_type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'FUNCTION')) AND pl.total_occur > 0;
prompt prompt prompt prompt select from where = = Ч Общее количество строк во всех программных единицах count(pl.line#) as lines_present plsql_profiler_lines_cross_run p1 (pl.unit_type in ('PACKAGE BODY', 'TYPE BODY', 'PROCEDURE', 'FUNCTION'));
spool off set termout on edit profsum.out set linesize 131 Я постарался поместить отчет в стандартное окно терминала шириной 80 символов. Вы можете изменить формат некоторых столбцов, если не так часто используете программу Telnet. Рассмотрим результаты, которые получены при тестировании функций, вычисляющих факториал, т.е. результаты работы представленного выше сценария profsum.sql. Суммарное время GRAND_TOTAL 5.57 Суммарное время выполнения двух тестов составило 5,S7 секунды. Теперь посмотрим, сколько выполнялся каждый тест. Суммарное время каждого прогона RONID RUN_COMMENT 17 factorial recursive 18 factorial iterative SECS 3.26 2. Рекурсивная версия уступает по производительности Ч она выполнялась почти в полтора раза дольше. Теперь посмотрим, сколько выполнялся каждый модуль (пакет или процедура) в тесте и какой процент это составляет от общего времени выполнения.
Пакет DBMS_PROFILER Процент времени, приходящийся на каждый модуль (отдельно для каждого прогона): RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME 17 factorial recursive 17 factorial recursive 17 factorial recursive 17 factorial recursive 18 factorial iterative 18 factorial iterative 18 factorial iterative 18 factorial iterative 8 rows selected. По этим данным видно, что в рекурсивной реализации 57% времени приходится на выполнение нашей функции, 37% Ч на выполнение процедуры DBMS_OUTPUT, а остальное время выполняются прочие подпрограммы. Во втором тесте результаты существенно отличаются. На выполнение нашего кода пришлось лишь 38% суммарного времени, причем это проценты от существенно меньшего времени! Это убедительно показывает, что вторая реализация эффективней первой. Столбец SECS содержит еще более показательные результаты. Как видите, рекурсивная функция выполнялась 1,87 секунды, а итеративная Ч 0,89. Если проигнорировать выполнение операторов DBMS_OUTPUT, окажется, что итеративная функция работает вдвое быстрее, чем рекурсивная. Учтите, что результаты в вашей системе могут отличаться. Если не выполнить команду SERVEROUTPUT ON в SQL*Plus, например, вызовы DBMS_OUTPUT могут даже не попасть в отчет. Если выполнять тесты на других машинах, значения будут существенно отличаться. Например, при выполнении тестов на машине Sparc Solaris, суммарное время (GRAND_TOTAL) составило около 1,0 секунды, а время выполнения каждого раздела кода отличалось. В процентах, тем не менее, конечные результаты практически совпали. Теперь рассмотрим, сколько времени суммарно в тестах выполнялся каждый модуль. Это покажет, какой фрагмент кода выполнялся дольше всего. TKYTE SYS FACT_RECURSIVE DBMS_OUTPUT SECS PERCEN 1.87 1.20.08.06 1.24.89.08.06 57.5 36.9 2.5 1.9 53.6 38.5 3.4 2. SYS TKYTE DBMS_OUTPUT FACT_ITERATIVE Приложение А Процент времени, приходящийся на каждый модуль, суммарно по всем прогонам:
UNIT_OWNER SYS TKYTE TKYTE SYS UNIT_NAME DBMS_OUTPUT FACT_RECURSIVE FACT_ITERATIVE DBMS_PROFILER SECS PERCENTAG 2.44 1.87.89.33.04 43.82 33.61 16.00 5.88. Очевидно, что время выполнения можно уменьшить почти вдвое, убрав один вызов DBMS_OUTPUT. Если просто выполнить SET SERVEROUTPUT OFF, отключив выполнение DBMS_OUTPUT, и повторно выполнить тесты, окажется, что на эту процедуру приходится менее 3% общего времени выполнения. Сейчас, однако, именно эта процедура выполнялась дольше всего. Что еще интереснее Ч 33% времени заняло выполнение рекурсивной функции и 16% Ч итеративной. Итеративная функция работает намного быстрее. Теперь рассмотрим более детальную информацию. Строки, для выполнения которых потребовалось более 1% суммарного времени, -> отдельно по каждому прогону: RUNID 17 18 17 17 18 18 17 18 18 17 18 17 17 18 18 17 17 18 18 18 17 18 HSECS 142.47 68.00 43.29 19.58 19.29 17.66 14.76 14.49 13.41 13.22 10.62 10.46 8.11 8.09 8.02 8.00 7.52 7.22 6.65 6.21 6.13 5.77 PCT OWNER UNIT_NAME 25.6 12.2 7.8 3.5 3.5 3.2 2.7 2.6 2.4 2.4 1.9 1.9 1.5 1.5 1.4 1.4 1.4 1.3 1.2 1.1 1.1 1.0 TKYTE TKYTE TKYTE SYS TKYTE SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS FACT_RECURSIVE: FACT_ITERATIVE FACT_RECURSIVE DBMS_OUTPUT FACT_ITERATIVE DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT DBMS_OUTPUT LINE TEXT 8 return n*fact_recursive(n-l);
7 l_result := l_result * i;
4 if ( n = 1 ) 116 a3 a0 51 a5 lc 6e 81 b0 5 for i in 2.. n 116 a3 a0 51 a5 lc 6e 81 b0 118 lc 51 81 b0 a3 a0 lc 51 118 lc 51 81 b0 a3 a0 lc 51 142 :2 a0 a5 b b4 2e d b7 19 142 :2 a0 a5 b b4 2e d b7 19 166 6e Ы 2e d :2 a0 7e 51 b4 166 6e b4 2e d :2 a0 7e 51 b4 72 1TO_CHAR: 144 8f a0 b0 3d b4 55 6a :3 a0 72 lTO_CHAR: 144 8f a0 b0 3d b4 55 6a :3 a0 3 3 141 a0 b0 3d b4 55 6a :3 a0 7e 1 1 81 lORU-10028:: line length 22 rows selected. Здесь выдается время выполнения (в сотых долях секунды) и процент от общего времени выполнения. В этих результатах нет ничего удивительного: можно было предположить, что дольше всего будет выполняться строка 8 рекурсивной и строка 7 итеративной функции. Это предположение подтверждается. В этой части кода показываются Пакет DBMS PROFILER конкретные строки кода, на которые надо обратить внимание. Обратите внимание на странного вида строки кода, начинающиеся с DBMS_OUTPUT. Так выглядит скрытый PL/SQL-код в базе данных. Это просто последовательность байтов, представляющая исходный код и скрывающая его от любопытных глаз. В следующей части отчета представлены суммарные результаты по всем тестам, тогда как в предыдущей части проценты вычислялись для каждого теста отдельно.
Наиболее "популярные" строки (более 1%), суммарно по всем прогонам:
HSECS 142.47 68.00 43.29 37.24 29.26 26.63 21.08 19.29 16.88 16.13 16.09 14.74 11.28 10.17 9.52 8.54 7.36 6.25 6.19 5.77 PCT OWNER UNIT_NAME 25.6 12.2 7.8 6.7 5.3 4.8 3.8 3.5 3.0 2.9 2.9 2.6 2.0 1.8 1.7 1.5 1.3 1.1 1.1 1.0 TKYTE TKYTE TKYTE SYS SYS SYS SYS TKYTE SYS SYS SYS SYS SYS SYS SYS SYS SYS SYS LINE TEXT FACT_RECURSIVE 8 return n * fact_recursive(n-l);
7 1 result := 1 result * i;
FACT_ITERATIVE FACT_RECURSIVE 4 if ( n = 1 ) DBMS OUTPUT 116 a3 a0 51 a5 lc 6e 81 b0 118 lc 51 81 b0 a3 a0 lc 51 DBMS OUTPUT DBMS_OUTPUT 142 :2 a0 a5 b b4 2e d b7 19 166 6e b4 2e d :2 a0 7e 51 b4 DBMS_OUTPUT FACT_ITERATIVE 5 for i in 2.. n 1 DBMS_OUTPUT 72 1TO CHAR: DBMS_OUTPUT 144 8f a0 b0 3d b4 55 6a :3 a0.3 DBMS OUTPUT 81 1ORU-10028:: line length overflow, 147 4f 9a 8f a0 b0 3d b4 55 DBMS_OUTPUT DBMS_OUTPUT 73 1DATE: 117 a3 a0 lc 51 81 b0 a3 a0 DBMS_OUTPUT 141 a0 b0 3d b4 55 6a :3 a0 7e DBMS_OUTPUT DBMS_OUTPUT 96 1WHILE: DBMS_OUTPUT 65 1499: DBMS_OUTPUT 145 7e a0 b4 2e d a0 57 b 20 rows selected. Наконец, рассмотрим статистическую информацию о частоте выполнения отдельных строк кода. Она пригодится не только при профилировании и настройке производительности, но и при тестировании. Эта часть отчета показывает, какие операторы в коде выполнялись и какой процент кода "покрыт" в ходе тестирования: Количество реально выполненных строк в программных единицах (с К группировкой по unit_name) UNIT_OWNER SYS SYS TKYTE TKYTE UNIT_NAME DBMS_OUTPUT DBMS_PROFILER FACT_ITERATIVE FACT_RECURSIVE LINES_EXECUTED LINES_PRESENT 51 9 4 3 88 62 4 3 PCT 58.0 14.5 100.0 100. == == ======================================== Количество реально выполненных строк для всех программных единиц Приложение А LINES_EXECUTED Общее количество строк во всех программных единицах LINES_PRESENT 157 Из 88 операторов пакета DBMS_OUTPUT выполнены 51. Интересно, как пакет DBMS_PROFILER подсчитывает строки или операторы. Утверждается, что функция FACT_ITERATIVE содержит 4 строки кода, но если обратиться к исходному коду: function fact_iterative(n int) return number as l_result number default 1;
begin for i in 2.. n loop l_result := l_result * i;
end loop;
return l_result;
end;
О каких четырех строках идет речь Ч непонятно. Пакет DBMS_PROFILER считает операторы, а не строки кода. Речь идет о следующих четырех операторах: l_result number default 1;
for i in 2.. n l_result := l_result * i;
return l_result;
Остальные строки, хотя и необходимы для компиляции и выполнения кода, к выполняемому коду не относятся и поэтому операторами не считаются. Пакет DBMS_PROFILER можно использовать для того, чтобы определить количество операторов, выполняемых в коде и в тестах.
Проблемы Единственная проблема, с которой я сталкивался при использовании пакета DBMS_PROFILER, связана с большим объемом генерируемых им данных и временем анализа этих данных.
Пакет DBMS_PROFILER Небольшой тест, который мы выполнили, сгенерировал более 500 строк статистической информации в таблице PLSQL_PROFILER_DATA. Эта таблица содержит одиннадцать числовых столбцов, так что она не очень "широкая", но растет быстро. При выполнении каждого оператора в таблицу добавляется строка. Надо контролировать пространство, занимаемое таблицей, периодически удаляя из нее строки. Обычно эта проблема несущественна, но я видел, как при тестировании сложных PL/SQL-процедур в таблицу записывались тысячи (и даже сотни тысяч) строк. Время анализа результатов Ч более серьезная проблема. Сколько бы вы не настраивали производительность, всегда найдется строка кода, выполняющаяся дольше всего. Если удалить эту строку кода, ее место займет другая. Вы никогда не получите отчет DBMS_PROFILER, изучив который придете к выводу, что все работает прекрасно и настраивать больше нечего. Чтобы эффективно использовать это инструментальное средство, надо определить для себя, когда можно закончить настройку. Задайте либо определенное время настройки (например, эта процедура будет настраиваться в течение двух часов), либо критерий производительности (если процедура будет выполняться за N единиц времени, можно прекратить настройку). В противном случае вы будете (как и я иногда) тратить огромное время на настройку процедуры, которая просто не может работать быстрее. Пакет DBMS_PROFILER Ч замечательное средство, которое может выдать массу подробной информации. Старайтесь не погрязнуть в изучении всех этих деталей.
Резюме В этом разделе мы рассмотрели использование пакета DBMS_PROFILER. Он используется в основном для решения двух задач. При профилировании исходного кода можно найти строки, выполняющиеся дольше всего, или сравнить скорость работы двух алгоритмов. Можно также понять, какая часть кода охвачена при тестировании приложения. Хотя 100-процентный охват кода при тестировании не гарантирует его безошибочности, но приближает к ней. Мы также разработали отчет, построенный на базе предлагаемого корпорацией Oracle примера отчета профилировщика. Этот отчет выдает основную информацию, необходимую для успешного использования пакета DBMS_PROFILER. Он избавляет от необходимости изучать лишние детали, предоставляя итоговую информацию о том, что происходило в приложении, и подробно описывая наиболее неэффективные части. Этого отчета может оказаться вполне достаточно для выявления узких мест и настройки приложения.
Пакет DBMS_UTILITY Пакет DBMS_UTILITY Ч это набор процедур различного назначения. В него помещено несколько отдельных, не связанных между собой процедур. Пакет DBMS_UTILITY стандартно устанавливается в базе данных, и привилегия EXECUTE для него предоставляется роли PUBLIC. Процедуры в этом пакете не взаимосвязаны, как в большинстве остальных пакетов. Например, все подпрограммы пакета UTL_FILE имеют общее назначение Ч выполнение ввода-вывода в файл. Подпрограммы в пакете DBMS_UTILITY практически независимы. Мы рассмотрим некоторые из этих подпрограмм, уделив особое внимание потенциальным проблемам при их использовании.
Процедура COMPILE_SCHEMA Процедура COMPILE_SCHEMA предназначена для перекомпиляции недействительных (invalid) процедур, пакетов, триггеров, представлений, типов и других объектов схемы. Эта процедура работает в версии Oracle 8.1.6 на базе представления SYS.ORDER_OBJECT_BY_DEPENDENCY. Представление возвращает объекты в порядке зависимостей. Начиная с версии Oracle 8.1.7, это представление больше не используется (почему, будет показано далее). Если компилировать объекты в порядке, задаваемом этим представлением, объекты, которые можно успешно перекомпилировать, окажутся действительными (valid). Эта процедура выполняет оператор ALTER COMPILE от имени пользователя, который вызвал процедуру COMPILE_SCHEMA (т.е. она работает с правами вызывающего).
Пакет DBMS_UTILITY Процедура COMPILE_SCHEMA тpeбyeт передавать имена пользователей в верхнем регистре. Если вызвать: scott@TKYTE816> exec DBMS_UTILITY.compile_schema('scott');
скорее всего, ничего не произойдет, если при создании учетной записи имя пользователя scott не задано в нижнем регистре (как идентификатор в кавычках Ч прим. научн.ред.). Необходимо передать имя схемы как SCOTT. При использовании процедуры COMPILE_SCHEMA в версиях 8.1 сервера до 8.1.6.2 (т.е. во всех версиях 8.1.5, 8.1.6.0 и 8.1.6.1) возникает еще одна проблема. Если сервер поддерживает использование Java, в системе возникают рекурсивные зависимости. При вызове COMPILE_SCHEMA выдается сообщение об ошибке: scott@TKYTE816> exec dbms_utility.compile_schema(user);
BEGIN dbms_utility.compile_schema(user);
END;
* ERROR at l i n e 1: ORA-01436: CONNECT BY loop in user data ORA-06512: at "SYS.DBMS_UTILITY", line 195 ORA-06512: at line 1 Проблема связана с представлением SYS.ORDER_OBJECT_BY_DEPENDENCY, поэтому в версиях, начиная с Oracle 8.1.7, оно не используется. Если вы столкнетесь с этим сообщением об ошибке, можно создать собственную процедуру COMPILE_SCHEMA, работающую аналогично стандартной процедуре COMPILE_SCHEMA. В этой процедуре можно компилировать объекты в любом порядке. Типичное заблуждение состоит как раз в том, что объекты надо компилировать в строго определенном порядке. На самом деле компилировать объекты можно в произвольном порядке и получить тот же результат, что и при компиляции в порядке, определяемом зависимостями. Алгоритм следующий: 1. Выбираем недействительный объект схемы, который мы еще не пытались перекомпилировать. 2. Компилируем его. 3. Возвращаемся к первому шагу, пока есть недействительные объекты, которые мы еще не пытались перекомпилировать. Определенного порядка придерживаться не обязательно. Причина Ч в побочном эффекте компиляции недействительного объекта. При этом все недействительные объекты, от которых он зависит, тоже будут скомпилированы. Надо только продолжать компилировать объекты, пока недействительных не останется. (На самом деле недействительные объекты могут остаться, но лишь потому, что скомпилировать их невозможно вообще.) Может оказаться, что при компиляции всего одной процедуры перекомпилированными окажутся 10 или 20 других объектов. Если не пытаться перекомпилировать эти 10 или 20 объектов вручную (при этом исходный объект снова станет недействительным), все будет в порядке. Поскольку реализация этой процедуры представляет определенный интерес, я продемонстрирую ее. Для выполнения оператора ALTER COMPILE необходимо исполь Приложение А зовать процедуру с правами вызывающего. Необходим также доступ к представлению DBA_OBJECTS для поиска следующего недействительного объекта и проверки состояния скомпилированного объекта. Не хотелось бы требовать обязательного доступа к представлению DBA_OBJECTS от пользователя, выполняющего процедуру. Для этого придется использовать подпрограммы как с правами вызывающего, так и с правами создателя. Необходимо, однако, сделать так, чтобы вызываемая пользователем основная процедура работала с правами вызывающего, Ч это обеспечит использование ролей. Ниже представлена моя реализация процедуры COMPILE_SCHEMA. Пользователь, выполняющий этот сценарий, должен получить привилегию SELECT на представление SYS.DBA_OBJECTS непосредственно (подробнее об этом можно прочитать в главе 23). Поскольку это сценарий для утилиты SQL*Plus, включающий ряд директив SQL*Plus, я представлю только сам сценарий, а не результаты его выполнения. Для указания имени схемы при компиляции объектов я использую подставляемую переменную SQL*Plus. Это делается потому, что процедура выполняется с правами вызывающего (и если необходимо всегда обращаться к одной и той же таблице, независимо от того, кто выполняет процедуру, имя таблицы необходимо уточнять), а я лично предпочитаю не полагаться на общедоступные синонимы. Я представлю сценарий по частям, комментируя каждую часть: column u new_val uname select user u from dual;
drop table compile_schema_tmp / create global temporary table compile_schema_tmp (object_name varchar2(30), object_type varchar2(30), constraint compile_schema_tmp_pk primary key(object_name,object_type) ) on commit preserve rows / grant all on compile_schema_tmp to public / Сценарий начинается с получения имени текущего зарегистрировавшегося пользователя в подставляемую переменную SQL*Plus. Она будет использоваться в операторах CREATE OR REPLACE PROCEDURE. Это приходится делать, поскольку процедура должна выполняться с правами вызывающего и обращаться к созданной выше таблице. В главе 23 было описано, как разрешаются ссылки на таблицы с использованием стандартной схемы для пользователя, выполняющего процедуру. Используется одна временная таблица для всех сеансов, которая будет принадлежать пользователю, выполнившему этот сценарий. Поэтому необходимо явно указать имя пользователя в PL/SQL-процедуре. Временная таблица используется процедурами для запоминания Пакет DBMS_UTILITY того, какие объекты мы пытались перекомпилировать. Необходимо использовать конструкцию ON COMMIT PRESERVE ROWS, поскольку предполагается выполнять в процедурах операторы ЯОД (оператор ALTER COMPILE относится к операторам ЯОД), а после выполнения каждого такого оператора транзакция фиксируется. Теперь можно переходить к процедурам: create or replace procedure get_next_object_to_compile(p_username in varchar2, p_cmd out varchar2, p_obj out varchar2, p_typ out varchar2) as begin select 'alter ' || object_type || ' ' || p_username || '.' || object_name || decode(object_type, 'PACKAGE BODY', ' compile body', ' compile'), object_name, object_type into p_cmd, p_obj, p_typ from dba_objects a where owner = upper(p_username) and status = 'INVALID' and object_type <> 'UNDEFINED' and not exists (select null from compile_schema_tmp b where a.object_name = b.object_name and a.object_type = b.object_type ) and rownum = 1;
insert into compile_schema_tmp (object_name, object_type) values (p_obj, p_typ);
end;
/ Это процедура с правами создателя, с помощью которой мы будем обращаться к представлению DBA_OBJECTS. Она будет возвращать некий недействительный объект для перекомпиляции, если мы еще не пытались его компилировать. Процедура просто находит первый такой объект. По мере выбора мы запоминаем эти объекты во временной таблице. Процедура возбуждает исключительную ситуацию NO_DATA_FOUND, когда в запрошенной схеме не остается объектов, требующих перекомпиляции. Этот факт будет использоваться в следующей процедуре для прекращения обработки. Затем мы создадим процедуру с правами вызывающего, которая будет фактически выполнять компиляцию. Это объясняет, зачем в представленном выше коде понадобилась директива COLUMN U NEW_VAL UNAME Ч необходимо вставить имя владельца временной таблицы, чтобы избежать использования синонима. Поскольку мы делаем это динамически при компиляции процедуры, это решение лучше, чем использование синонима: create or replace procedure compile_schema(p_username in varchar2) authid current_user 1720 Приложение А as l_and varchar2(512);
l_obj dba_objects.object_name%type;
l_typ dba_objects.object_type%type;
begin delete from &uname..compile_schema_tmp;
loop get_next_object_to_compile(p_username, l_cmd, l_obj, l_typ);
dbms_output.put_line(l_cmd);
begin execute immediate l_cmd;
dbms_output.put_line('Успешно');
exception when others then dbms_output.put_line(sqlerrm);
end;
dbms_output.put_line(chr(9));
end loop;
exception Ч процедура get_next_object_to_compile возбуждает эту Ч исключительную ситуацию, когда завершает работу when no_data_found then NULL;
end;
/ grant execute on compile_schema to public / Вот и все. Теперь можно переходить в любую схему, где есть компилируемые объекты, и выполнять: scott@TKYTE816> exec tkyte.compile_schema('scott') alter PROCEDURE scott.ANALYZE_MY_TABLES compile Успешно alter PROCEDURE scott.CUST_LIST compile ORA-24344: success with compilation error alter TYPE scott.EMP_MASTER compile ORA-24344: success with compilation error alter PROCEDURE scott.FOO compile Успешно alter PACKAGE scott.LOADLOBS compile Успешно alter PROCEDURE scott.P compile Успешно alter PROCEDURE scott.RUN_BY_JOBS compile Успешно PL/SQL procedure successfully completed.
Пакет DBMS_UTILITY Итак, процедура выдает информацию о том, какие объекты она пытается компилировать, и результат компиляции. Судя по полученному результату, компилировалось семь объектов: при компиляции двух произошла ошибка, остальные пять успешно скомпилированы. Объекты компилировались в произвольном порядке Ч порядок просто не имеет значения. Эта процедура работает во всех версиях сервера.
Процедура ANALYZE_SCHEMA Процедура ANALYZE_SCHEMA делает именно то, что можно предположить по ее названию, Ч выполняет операторы ANALYZE для сбора статистической информации об объектах в пользовательской схеме. Не рекомендуется применять ее для схем SYS или SYSTEM. В особенности не надо этого делать для схемы SYS, поскольку рекурсивные SQL-операторы, которые СУБД Oracle генерирует уже многие годы, оптимизированы для обработки оптимизатором, основанным на правилах. При наличии статистической информации о таблицах в схеме SYS сервер будет работать медленнее, чем мог бы. Эту процедуру можно использовать для анализа созданных пользователями прикладных схем. Процедура ANALYZE_SCHEMA принимает пять аргументов. Х SCHEMA. Схема, которую необходимо проанализировать. Х METHOD. ESTIMATE, COMPUTE или DELETE. Если передано значение ESTIMATE, то одно из значений: ESTIMATE_ROWS, ESTIMATE_PERCENT должно быть ненулевым. ESTIMATE_ROWS. Количество оцениваемых строк. ESTIMATE_PERCENT. Процент оцениваемых строк. Если передано ненулевое значение параметра ESTIMATE_ROWS, этот параметр игнорируется.
Х Х Х METHOD_OPT [FOR TABLE] [FOR ALL [INDEXED] COLUMNS] [SIZE n] [FORALL INDEXES]. Это те же опции, что используются в операторе ANALYZE. Они подробно описаны в руководстве Oracle8i SQL Reference, в разделе, посвященном конструкции FOR оператора ANALYZE. Итак, например, все объекты в пользовательской схеме SCOTT можно проанализировать следующим образом. Начнем с удаления статистической информации, а затем соберем ее снова: scott@TKYTE816> exec dbms_utility.analyze_schema(user, 'delete');
PL/SQL procedure successfully completed. scott@TKYTE816> select table_name, num_rows, last_analyzed 2 from user_tables;
TABLE_NAME BONUS CREATE$JAVA$LOB$TABLE DEPT NUM_ROWS LAST_ANAL Приложение А 12 rows selected. scott@TKYTE816> exec dbms_utility.analyze_schema(user, 'compute');
PL/SQL procedure successfully completed. scott@TKYTE816> select table_name, num_rows, last_analyzed 2 from user_tables;
TABLE_NAME BONUS CREATE$JAVA$LOB$TABLE DEPT 12 rows selected. Этот простой пример показывает, что оператор ANALYZE COMPUTE выполняется Ч столбцы NUM_ROWS и LAST_ANALYZED получили значения. Процедура ANALYZE_SCHEMA работает в соответствии со своим названием. Если необходимо анализировать объекты с разной степенью детализации, она не поможет. Процедура применяет один и тот же метод анализа ко всем типам объектов. Например, при эксплуатации большого хранилища данных, если необходимо использовать гистограммы по определенным столбцам или наборам столбцов только в некоторых таблицах, процедуру ANALYZE_SCHEMA применять нельзя. С помощью процедуры ANALYZE_SCHEMA можно либо получить гистограммы для всех столбцов, либо не получить их вообще Ч избирательно обрабатывать столбцы нельзя. Если анализ объектов выходит за рамки элементарного, процедура ANALYZE_SCHEMA не позволит его выполнить. Она подходит для небольших и средних (по объему обрабатываемых данных) приложений. Если необходимо обрабатывать большие объемы данных, имеет смысл распараллелить анализ или использовать разные опции анализа для различных таблиц. Этого процедура ANALYZE_SCHEMA не обеспечивает. При использовании процедуры ANALYZE_SCHEMA следует учитывать следующие особенности. Первая связана с применением процедуры ANALYZE_SCHEMA к изменяющейся схеме. Вторая Ч с тем, какие объекты процедура ANALYZE_SCHEMA не анализирует. Рассмотрим их последовательно. NUM_ROWS LAST_ANAL 0 03-FEB-01 58 03-FEB-01 4 03-FEB- Применение процедуры ANALYZE_SCHEMA к изменяющейся схеме Предположим, процедура ANALYZE_SCHEMA выполняется в схеме SCOTT. В эту схему добавлено несколько больших таблиц, поэтому их анализ требует определенного времени. В другом сеансе вы удаляете или добавляете ряд объектов в схеме SCOTT. Удаленный объект не был обработан процедурой ANALYZE_SCHEMA. Когда процедура попытается его проанализировать, будет выдано сбивающее с толку сообщение: scott@TKYTE816> exec dbms_utility.analyze_schema(user, 'compute');
BEGIN dbms_utility.analyze_schema(user, 'compute');
END;
Пакет DBMS_UTILITY * ERROR at line 1: ORA-20000: You have insufficient privileges for an object in this schema. ORA-06512: at "SYS.DBMS_UTILITY", line 258 ORA-06512: at line 1 Очевидно, что все необходимые привилегии есть Ч объект принадлежит вашей схеме. Ошибка связана с тем, что таблицы, которую процедура пытается анализировать, больше нет. Вместо того чтобы определить отсутствие таблицы, процедура предполагает, что таблица существует и пользователю просто не хватает привилегий, чтобы ее проанализировать. В этом случае можно сделать только следующее: Х Х повторно выполнить процедуру ANALYZE_SCHEMA;
не удалять объекты по ходу выполнения процедуры ANALYZE_SCHEMA.
Следует также помнить, что, если объект добавлен в схему после начала выполнения процедуры ANALYZE_SCHEMA, он не будет проанализирован Ч процедура его не увидит. Это Ч не большая проблема, поскольку процедура ANALYZE_SCHEMA выполнится успешно.
Процедура ANALYZE_SCHEMA анализирует не все В процедуре ANALYZE_SCHEMA есть нерешенная проблема. Она не анализирует таблицы, организованные по индексу, если в них используется дополнительный сегмент (подробнее о таблицах, организованных по индексу, и дополнительных сегментах см. в главе 6). Например, если выполнить следующий код: scott@TKYTE816> drop table t;
Table dropped. scott@TKYTE816> create table t (x int primary key, y date) 2 organization index 3 OVERFLOW TABLESPACE TOOLS 4/ Table created. scott@TKYTE816> execute dbms_utility.analyze_schema('SCOTT', 'COMPOTE') PL/SQL procedure successfully completed. scott@TKYTE816> select table_name, num_rows, last_analyzed 2 from user_tables 3 where table_name = 'T';
TABLE_NAME T таблица Т не будет проанализирована. Однако если не указывать конструкцию OVERFLOW: NUM_ROWS LAST_ANAL Приложение А scott@TKYTE816> drop table t;
Table dropped. scott@TKYTE816> create table t (x int primary key, y date) 2 organization index 3 / Table created. scott@TKYTE816> execute dbms_utility.analyze_schema('SCOTT', 'COMPUTE') PL/SQL procedure successfully completed. scott@TKYTE816> select table_name, num_rows, last_analyzed 2 from user_tables 3 where table_name = 'T';
TABLE_NAME NUM_ROWS LAST_ANAL T 0 03-FEB- таблица анализируется. Это не означает, что конструкцию OVERFLOW для таблиц, организованных по индексу, задавать не надо Ч просто такие таблицы анализируются отдельно, вручную.
Процедура ANALYZE_DATABASE Ей я посвящаю очень короткий раздел. Не используйте эту процедуру. Ее не имеет смысла использовать в базе данных любого размера. Кроме того, она имеет неприятный побочный эффект: анализирует словарь данных (объекты, принадлежащие пользователю SYS, никогда не нужно анализировать). Не используйте эту процедуру. Просто забудьте о ее существовании.
Функция FORMAT_ERROR_STACK На первый взгляд эта функция кажется очень полезной, но на самом деле она бесполезна. Фактически FORMAT_ERROR_STACK Ч это просто менее функциональная реализация функции SQLERRM (SQL ERRor Message). Простой пример поможет вам понять, что я имею в виду: scott@TKYTE816> create or replace procedure p1 2 as 3 begin 4 raise program_error;
5 end;
6/ Procedure created. scott@TKYTE816> create or replace procedure p2 2 as 3 begin 4 p1;
5 end;
Пакет DBMS_UTILITY / Procedure created. scott8TKYTE816> create or replace procedure p3 2 as 3 begin 4 p2;
5 end;
6/ Procedure created. scott@TKYTE816> exec p3 BEGIN p3;
END;
* EBROR at line 1: ORA-06501: PL/SQL: program error ORA-06512: at "SCOTT.P1", line 4 ORA-06512: at "SCOTT.P2", line 4 ORA-06512: at "SCOTT.P3", line 4 ORA-06512: at line 1 В случае возникновения ошибки, если она не перехвачена обработчиком, выдается весь стек ошибок, который можно будет использовать в программе, использующей интерфейсы Pro*C, OCI, JDBC и т.п. Можно ожидать, что функция DBMS_UTILITY.FORMAT_ERROR_STACK будет возвращать подобную информацию. Оказывается, однако, что эту важную информацию она теряет: scottQTKYTE816> create or replace procedure p3 2 as 3 begin 4 p2;
5 exception 6 when others then 7 dbms_output.put_line(dbms_utility.format_error_stack);
8 end;
9/ Procedure created. scott@TKYTE816> exec p3 ORA-06501: PL/SQL: program error PL/SQL procedure successfully completed. Как видите, при вызове функции FORMAT_ERROR_STACK информация стека ошибок потеряна! Функция возвращает ту же информацию, что и SQLERRM: scott@TKYTE816> create or replace procedure p3 2 as 3 begin 4 p2;
5 exception 6 when others then 7 dbms_output.put_line(sqlerrm);
Приложение А 8 end;
9/ Procedure created. scott@TKYTE816> exec p3 ORA-06501: PL/SQL: program error PL/SQL procedure successfully completed. Я утверждал, что функция FORMAT_ERROR_STACK обеспечивает меньше возможностей, чем SQLERRM. Дело в том, что функция SQLERRM может не только возвращать сообщение о текущей ошибке, но и сообщение о любой ошибке, код которой передан как параметр: scott@TKYTE816> exec dbms_output.put_line(sqlerrm(-l));
ORA-00001: unique constraint (.) violated PL/SQL procedure successfully completed. К сожалению, сейчас нет способа получить весь стек ошибок в языке PL/SQL. Чтобы получить фактический номер строки кода, при выполнении которой произошла ошибка, приходится пропускать фатальные ошибки до вызывающей процедуры в клиентском приложении.
Функция FORMAT_CALL_STACK К счастью, эта функция действительно очень полезна Ч не то, что функция FORMAT_ERROR_STACK. Она возвращает текущий стек вызовов. С ее помощью можно написать ряд полезных утилит, например MY_CALLER и WHO_AM_I. (Они выдают информацию о том, откуда вызвана текущая подпрограмма и как она называется Ч прим. научн. ред.) Эти утилиты вызывают рассматриваемую функцию чтобы определить, из какой строки какой подпрограммы вызваны. Такие сведения могут пригодиться при отладке и регистрации. Кроме того, процедуры могут работать по-разному, в зависимости от того, кто их вызвал и в какой среде. Прежде чем представить код моих утилит MY_CALLER и WHO_AM_I, давайте посмотрим, какую информацию можно получить в стеке вызовов и какие результаты должны выдавать эти утилиты. Если использовать процедуры P1, P2, P3 из представленного ранее примера и переписать процедуру P1 следующим образом: scott@TKYTE816> create or replace procedure p1 2 as 3 l_owner varchar2(30);
4 l_name varchar2(30);
5 l_lineno number;
6 l_type varchar2(30);
7 begin 8 dbms_output.put_line (' ') ;
9 dbms_output.put_line(dbms_utility.format_call_stack) ;
10 dbms_output.put_line (' ') ;
11 who_called_me(l_owner, l_name, l_lineno, l_type);
12 dbms_output.put_line(l_type || ' ' || 13 l_owner || '.' || l_name || Пакет DBMS_UTILITY 14 '(' || l_lineno || ')');
15 dbms_output.put_line (' ') ;
16 dbms_output.put_line(who_am_i);
17 dbms_output.put_line(' ') ;
18 raise program_error;
19 end;
20 / Procedure created, мы получим следующий результат: scott@TKYTE816> exec p3 PL/SQL Call Stack object line object handle number name 2fl91e0 9 procedure SCOTT.P1 39f0a9c 4 procedure SCOTT.P2 3aae318 4 procedure SCOTT.P3 3a3461c 1 anonymous block PROCEDURE SCOTT.P2(4) SCOTT.Pl(16) BEGIN p3;
END;
* ERROR at line 1: ORA-06501: PL/SQL: program error ORA-06512: at "SCOTT.P2", line 8 ORA-06512: at "SCOTT.P3", line 4 ORA-06512: at line 1 Итак, мы видим весь стек вызовов для процедуры P1. Показано, что процедура P1 была вызвана процедурой P2, процедура P2 была вызвана процедурой P3, которая, в свою очередь, была вызвана из анонимного блока. Кроме того, в программном коде можно выяснить, что процедура P1 была вызвана в строке 4 процедуры SCOTT.P2. Наконец, можно выяснить, что выполняется сейчас процедура SCOTT.P1. Теперь, разобравшись, как выглядит стек вызовов и что мы хотим получить, можно представить код, позволяющий это сделать: tkyte@TKYTE816> create or replace function my_caller return varchar2 2 3 as 4 owner varchar2(30);
5 name varchar2(30);
6 lineno number;
7 caller_t varchar2(30);
8 call_stack varchar2(4096) default dbms_utility.format_call_stack;
9 n number;
10 found_stack BOOLEAN default FALSE;
Приложение А 11 line varchar2(255);
12 cnt number := 0;
13 begin 14 15 loop 16 n := instr(call_stack, chr(10));
17 exit when (cnt = 3 or n is NULL or n = 0);
18 19 line := substr(call_stack, 1, n-1);
20 call_stack := substr(call_stack, n+1);
21 22 if (NOT found_stack) then 23 if (line like ' %handle%number%name%') then 24 found_stack := TRUE;
25 end if;
26 else 27 cnt := cnt + 1;
28 Ч cnt = 1 Ч это я 29 Ч cnt = 2 Ч это подпрограмма, которая меня вызвала 30 Ч cnt = 3 Ч это подпрограмма, которая вызвала -> подпрограмму, вызвавшую меня 31 if (cnt = 3) then 32 lineno := to_number(substr(line, 13, 6));
33 line := substr(line, 21);
34 if (line like 'pr%') then 35 n := length('procedure ' ) ;
36 elsif (line like 'fun%') then 37 n := length('function ' ) ;
38 elsif (line like 'package body%') then 39 n := length('package body ' ) ;
40 elsif (line like 'pack%') then 41 n := length('package ' ) ;
42 elsif (line like 'anonymous block%') then 43 n := length('anonymous block ' ) ;
44 else Ч must be a trigger 45 n := 0;
46 end if;
47 if (n <> 0) then 48 caller_t := ltrim(rtrim(upper(substr(line,l, -> n-1))));
49 line := substr(line, n ) ;
50 else 51 caller_t := 'TRIGGER';
52 line := ltrim(line);
53 end if;
54 n := instr(line, '. ' ) ;
55 owner := ltrim(rtrim(substr(line, 1, n-1)));
56 name := ltrim(rtrim(substr(line, n+1)));
57 end if;
58 end if;
59 end loop;
60 return owner || '.' || name;
Пакет DBMS_UTILITY 61 end;
62 / Function created. tkyte@TKYTE816> create or replace function who_am_i return varchar2 2 as 3 begin 4 return my_caller;
5 end;
6/ Function created. При наличии этих процедур можно реализовать интересные решения. В частности, они могут использоваться в следующих случаях. Х Для проверки. Процедуры проверки могут регистрировать не только пользователя, выполнившего определенное действие, но и код, выполнивший это действие. Х Для отладки. Например, если снабдить код вызовами DBMS_APPLICATION_INFO.SET_CLIENT_INFO(WHO_AM_I), в другом сеансе можно выполнить запросы к представлению V$SESSION, чтобы определить, какой фрагмент кода сейчас выполняется. Подробнее о пакете DBMS_APPLICATION_INFO см. в начале приложения А.
Функция GET_TIME Эта функция возвращает время, прошедшее с определенного момента, с точностью до сотых долей секунды. Функция GET_TIME не возвращает значение текущего времени, как можно было предположить по ее названию. Ее можно использовать для измерения периода времени между событиями. Обычно эта функция используется следующим образом: scott@TKYTE816> declare 2 l_start number;
3 n number := 0;
4 begin 5 6 l_start := dbms_utility.get_time;
7 8 for x in 1.. 100000 9 loop 10 n := n+1;
11 end loop;
12 13 dbms_output.put_line(' it took ' || 14 round((dbms_utility.get_time-l_start)/100, 15 ' seconds...');
16 end;
17 / it took.12 seconds... PL/SQL procedure successfully completed.
2) || Приложение А Итак, функция GET_TIME используется для измерения времени с точностью до сотых долей секунды. Нужно, однако, учитывать, что счетчик GET_TIME может переполниться, сбросится в ноль и начать отсчет сначала, если сервер работает достаточно долго. Сейчас на большинстве платформ сброс произойдет не раньше, чем через год. Для счетчика используется 32-битовое целое число, что позволяет хранить сотые доли секунды примерно для 497 дней. После этого произойдет переполнение счетчика, и отсчет начнется с нуля. На некоторых платформах операционная система увеличивает значение счетчика чаще, чем раз в одну сотую секунды. На этих платформах сброс счетчика может произойти раньше, чем через 497 дней. Например, на платформе Sequent таймер обнуляется за 71,58 минуты, поскольку в этой операционной системе счетчик отсчитывает микросекунды, поэтому 32-битовое целое переполняется существенно быстрее. На 64-битовых платформах счетчик не переполнится и за тысячи лет. Последнее замечание о функции GET_TIME. Значение, возвращаемое функцией GET_TIME, может быть получено и с помощью оператора SELECT * FROM V$TIMER. Это динамическое представление и функция GET_TIME возвращают одно и то же: tkyte@TKYTE816> select hsecs, dbms_utility.get_time 2 from v$timer;
HSECS 7944822 GET_TIME Функция GET_PARAMETER_VALUE Эта функция позволяет пользователю получить значение указанного параметра конфигурации. Даже при отсутствии доступа к представлению V$PARAMETER и невозможности выполнить команду SHOW PARAMETER, c помощью этой функции можно получить значение параметра инициализации сервера. Функция используется следующим образом: scott@TKYTE816> show parameter utl_file_dir ORA-00942: table or view does not exist scott@TKYTE816> select * from v$parameter where name = 'utl_file_dir' 2/ select * from v$parameter where name = 'utl_file_dir' * ERROR at line 1: ORA-00942: table or view does not exist scott@TKYTE816> declare 2 intval number;
3 strval varchar2(512);
4 begin 5 if (dbms_utility.get_parameter_value('utl_file_dir', 6 intval, 7 strval) = 0) 8 then 9 dbms_output.put_line('Значение= ' || intval);
Пакет DBMS_UTILITY 10 else 11 dbms_output.put_line( 'Значение = ' || strval);
12 end if;
13 end;
14 / Значение = c:\temp\ PL/SQL procedure successfully completed. Как видите, хотя пользователь SCOTT не может обратиться к представлению V$PARAMETER и выполнить команду SHOW PARAMETER, он может получить требуемое значение. Следует отметить, что параметры инициализации со значениями True/ False в файле init.ora будут выданы как числа: значение 1 обозначает ИСТИНУ (True), а значение 0 Ч ЛОЖЬ (False). Для параметров с несколькими значениями, например UTL_FILE_DIR, функция выдает только первое значение. Если использовать учетную запись, имеющую право для этой же базы данных выполнять команду SHOW PARAMETER: tkyte@TKYTE816> show parameter utl_file_dir NAME utl_file_dir можно получить все значения. TYPE VALUE string c:\temp, c:\oracle Процедура NAME_RESOLVE Этой процедуре можно передать имя: Х процедуры верхнего уровня;
Х функции верхнего уровня;
Х пакета;
Х синонима пакета, процедуры или функции верхнего уровня. Она выдает полное имя соответствующего объекта. Процедура позволяет узнать, является ли объект с указанным именем процедурой, функцией или пакетом и какой схеме он принадлежит. Рассмотрим простой пример: scott@TKYTE816> declare 2 type vcArray is table of varchar2(30);
3 l_types vcArray := vcArray(null, null, null, null, 'synonym', 4 null, 'procedure', 'function', 5 'package');
6 7 l_schema varchar2(30);
8 l_part1 varchar2(30);
9 l_part2 varchar2(30);
10 l_dblink varchar2(30);
11 l_type number;
12 l_obj# number;
13 begin 1732 Приложение А 14 dbms_utility.name_resolve(name => 'DBMS_UTILITY', 15 context => 1, 16 schema => l_schema, 17 part1 => l_part1, 18 part2 => l_part2, 19 dblink => l_dblink, 20 part1_type => l_type, 21 object_number => l_obj#);
22 if l_obj# IS NULL 23 then 24 dbms_output.put_line('Object not found or not valid.');
25 else 26 dbms_output.put(l_schema || '.' || nvl(l_partl,l_part2));
27 if l_part2 is not null and l_partl is not null 28 then 29 dbms_output.put('.' || l_part2);
30 end if;
31 32 dbms_output.put_line(' is a ' || l_types(l_type) || 33 ' with object id ' || l_obj# || 34 ' and dblink "' || l_dblink || '"');
35 end if;
36 end;
37 / SYS.DBMS_UTILITY is a package with object id 2408 and dblink "" PL/SQL procedure successfully completed. Процедура NAME_RESOLVE по синониму DBMS_UTILITY определила, что речь идет о пакете, принадлежащем пользователю SYS. Следует отметить, что процедура NAME_RESOLVE работает только для процедур, функций, пакетов и синонимов, ссылающихся на один из этих трех типов объектов. В частности, она не работает для таблиц. При попытке передать имя таблицы ЕМР в схеме пользователя SCOTT, например, вы получите следующее сообщение об ошибке: declare * ERROR at line 1: ORA-06564: object emp does not exist ORA-06512: at "SYS.DBMS_UTILITY", line 68 ORA-06512: at line 9 Помимо того, что процедура NAME_RESOLVE не работает с таблицами, индексами и другими объектами, она работает не так, как указано в документации, при разрешении синонимов, ссылающихся на удаленные объекты по связи базы данных. В документации написано, что если передать процедуре NAME_RESOLVE синоним удаленного пакета или удаленной процедуры, то в качестве типа объекта будет возвращен синоним, а в качестве имени Ч имя связи базы данных. Проблема Ч в коде процедуры NAME_RESOLVE (документация описывает предполагаемые результаты, но процедура не работает должным образом). В текущей реализации процедура NAME_RESOLVE никогда не возвращает SYNONYM в качестве типа объекта. Вместо этого она уточняет Пакет DBMS_UTILITY имя удаленного объекта и возвращает его имя и идентификатор -1. Например, я настроил связь базы данных и создал синоним X для пакета DBMS_UTILITY@ora8i.world. При попытке уточнения имени этого синонима я получаю:
SYS.DBMS_UTILITY is a package with object id -1 and dblink "" PL/SQL procedure successfully completed. В выходном параметре DBLINK я должен был бы получить сообщение о том, что объект X Ч синоним и соответствующую информацию о связи. Как видите, однако, в переменной DBLINK мы получили значение Null, и единственный признак того, что пакет не является локальным, Ч значение -1 для идентификатора объекта. Не стоит предполагать, что эта особенность останется в следующих версиях СУБД Oracle. Было доказано, что проблема Ч в реализации процедуры NAME_RESOLVE, а не в документации. Документация правильно задает требования, но они некорректно реализованы. После исправления ошибки процедура NAME_RESOLVE будет работать для удаленных объектов иначе. Поэтому следует либо избегать использования процедуры NAME_RESOLVE для удаленных объектов, либо поместить вызов NAME_RESOLVE в подпрограмму, решающую эту проблему. Когда реализация этой процедуры изменится, можно будет реализовать прежние особенности работы для фрагментов кода, которые от них зависят. Последнее замечание относительно процедуры NAME_RESOLVE. Параметр CONTEXT плохо описан, а параметр OBJECT_NUMBER не описан в документации вовсе. Про параметр CONTEXT написано только, что:... должен быть целым числом от 0 до 8 На самом деле, его значение должно быть целым числом от 1 до 7, или будет получено сообщение об ошибке: declare * ERROR at line 1: ORA-20005: ORU-10034: context argument must be 1 or 2 or 3 or 4 or 5 or 6 or 7 ORA-06512: at "SYS.DBMS_UTILITY", line 66 ORA-06512: at line 14 А если задать любое значение из этого диапазона, кроме 1, будет выдано одно из следующих сообщений об ошибке: ORA-04047: object specified is incompatible with the flag specified ORA-06564: object OBJECT-NAME does not exist Так что единственно допустимым значением контекста является 1. Параметр OBJECT_NUMBER вообще не описан. Это идентификатор объекта (значение столбца OBJECT_ID), который можно найти в представлениях DBA_OBJECTS, ALL_OBJECTS и USER_OBJECTS. Например, возвращаясь к первому примеру, где было выдано значение OBJECT_ID 2048, можно выполнить запрос: scott@TKYTE816> select owner, object_name 2 from all_objects 3 where object_id = 2408;
SYS Приложение А OWNER OBJECT_NAME DBMS_UTILITY Процедура NAME_TOKENIZE Эта процедура разбивает строку, представляющую имя объекта, на компоненты. Для ссылок на объекты используется следующий синтаксис: [схема].[имя_объекта].[процедура|функция]@[связь_6аэы_данных] Процедура NAME_TOKENIZE в строке такого вида выделяет три начальных компонента и связь базы данных. Кроме того, она сообщает, на каком байте от начала строки ею завершен анализ имени объекта. Ниже представлен простой пример, демонстрирующий, что можно получить при передаче процедуре имени объекта. Учтите, что имена реальных объектов передавать необязательно (соответствующие таблицы и процедуры могут и не существовать), но обязательно передавать допустимые имена объектов. Если передан недопустимый (синтаксически) идентификатор, процедура NAME_TOKENIZE возбудит исключительную ситуацию. Это позволяет проверить с помощью процедуры NAME_TOKENIZE, является ли строка символов допустимым идентификатором: scott@TKYTE816> declare 2 l_a varchar2(30);
3 l_b varchar2(30);
4 l_c varchar2(30);
5 l_dblink varchar2(30);
6 l_next number;
7 8 type vcArray is table of varchar2(255);
9 l_names vcArray := 10 vcArray('owner.pkg.proc@database_link', 11 'owner.tbl@database_link', 12 'tbl', 13 '"Owner".tbl', 14 'pkg.proc', 15 'owner.pkg.proc', 16 'proc', 17 'owner.pkg.proc@dblink with junk', 18 '123');
19 begin 20 for i in 1.. l_names.count 21 loop 22 begin 23 dbms_utility.name_tokenize(name => l_names(i), 24 a => l_a, 25 b => l_b, 26 c => l_c, 27 dblink => l_dblink, 28 nextpos=> l_next);
29 30 dbms_output.put_line('name ' || l_names(i));
Пакет DBMS_UTILITY 31 dbms_output.put_line('A ' II l_a);
32 dbms_output.put_line('B ' II l_b);
33 dbms_output.put_line('C ' II l_c);
34 dbms_output.put_line('dblink ' || l_dblink);
35 dbms_output.put_line('next ' || l_next || ' ' || 36 length(l_names(i)));
37 dbms_output.put_line (' ') ;
38 exception 39 when others then 40 dbms_output.put_line('name ' || l_names(i));
41 dbms_output.put_line (sqlerrm) ;
42 end;
43 end loop;
44 end;
45 / name owner.pkg.prod@database_link A OWNER B PKG C PROC dblink DATABASE_LINK next 28 28 Как видите, процедура позволяет выделить компоненты имени объекта. Параметр NEXT получил значение длины строки Ч в данном случае анализ закончился, когда был достигнут конец строки. Поскольку мы задали полное имя объекта, все четыре компонента имени получили значения. Теперь рассмотрим другие примеры: name owner.tbl@database_link A OWNER B TBL C dblink DATABASE_LINK next 23 23 name A B tbl TBL c dblink next 33 Обратите внимание, что параметры В и С остались пустыми. Хотя идентификатор объекта строится по принципу СХЕМА.ОБЪЕКТ.ПРОЦЕДУРА, процедура NAME_TOKENIZE не поместила значение TBL в выходной параметр В. Она просто помещает первую часть имени в параметр А, следующую часть Ч в В и т.д. Параметры А, В и С не содержат логический компонент имени Ч они содержат первую найденную часть, вторую и т.д.
name A B "Owner Owner TBL ". t b l с Приложение А dblink next 11 11 Этот результат представляет определенный интерес. В предыдущих примерах процедура NAME_TOKENIZE выдавала все имена в верхнем регистре. Дело в том, что все идентификаторы сервер переводит в верхний регистр, если только они не взяты в кавычки. Мы же передали идентификатор в кавычках. В таком идентификаторе процедура NAME_TOKENIZE сохранит регистр символов, но кавычки удалит. name pkg.proc A PKG B PROC C dblink next 88 ---------------------------name owner.pkg.proc A OWNER B PKG C PROC dblink next 14 14 ---------------------------name proc A PROC B C dblink next 44 ---------------------------name owner.pkg.proc@dblink with junk A OWNER B PKG C PROC dblink DBLINK next 22 31 ---------------------------В этом примере анализ прекратился раньше, чем был достигнут конец строки. Процедура NAME_TOKENIZE сообщает, что прекратила анализ на 22-ом байте из 31. Это пробел перед строкой with junk. Процедура проигнорировала остаток строки после имени. name 123 ORA-00931: missing identifier PL/SQL procedure successfully completed. Последний пример показывает, что при получении недопустимого идентификатора процедура NAME_TOKENIZE возбуждает исключительную ситуацию. Она проверяет, является ли каждая возвращаемая лексема допустимым идентификатором. Это позво Пакет DBMS_UTILITY ляет использовать ее для проверки допустимости имен в приложениях, создающих объекты в базе данных Oracle. Например, если создается средство построения модели данных и необходимо проверить допустимость имени, которое пользователь хочет задать для таблицы или столбца, можно применить процедуру NAME_TOKENIZE.
Процедуры СОММА_TO_TABLE, TABLE_TO_COMMA Эти две процедуры преобразуют, соответственно, список идентификаторов через запятую в PL/SQL-таблицу (COMMA_TO_TABLE) и PL/SQL-таблицу произвольных строк в строку-список через запятую (TABLE_TO_COMMA). Я выделил слово "идентификаторов", потому что процедура COMMA_TO_TABLE использует для анализа строк процедуру NAME_TOKENIZE. Поэтому необходимо задавать допустимые идентификаторы Oracle (или идентификаторы в кавычках). При этом, однако, элемент списка все равно не может быть длиннее 30 символов. Эта утилита наиболее полезна для приложений, которые хранят список, например, имен таблиц в одной строке, и преобразуют их по ходу работы в PL/SQL-таблицу. Применить эту таблицу для других целей не удастся. Универсальная процедура COMMA_TO_TABLE, работающая со строками данных, перечисленных через запятую, была представлена в главе 20. Там я демонстрирую на ее примере, как выбирать данные с помощью SELECT из PL/SQL-функции. Вот пример использования стандартной процедуры COMMA_TO_TABLE, показывающий, как она обрабатывает длинные и недопустимые идентификаторы: scott@TKYTE816> declare 2 type vcArray is table of varchar2(4000);
3 4 l_names vcArray :=vcArray('emp,dept,bonus', 5 'a, b, c', 6 423, 456, 789', 7 '"123", "456", "789"', 8 "'This is a long string, longer then 32 characters","b",c');
9 l_tablen number;
10 l_tab dbms_utility.uncl_array;
11 begin 12 for i in 1.. l_names.count 13 loop 14 dbms_output.put_line(chr(10) || 15 '[' || l_names(i) || ']');
16 begin 17 18 dbms_utility.comma_to_table(l_names(i), 19 l_tablen, l_tab);
20 21 for j in l..l_tablen 22 loop 23 dbms_output.put_line('[' || l_tab(j) || ']');
24 end loop;
Приложение А 25 26 l_names(i) := null;
27 dbms_utility.table_to_comma(l_tab, 28 l_tablen, l_names(i));
29 dbms_output.put_line(l_names(i));
30 exception 31 when others then 32 dbms_output.put_line(sqlerrm) ;
33 end;
34 end loop;
35 end;
36 / [emp,dept,bonus] [emp] [dept] [bonus] emp,dept,bonus Этот пример демонстрирует как по строке emp,dept,bonus строится таблица идентификаторов, которая затем снова преобразуется в строку. [a, b, [a] [b] [ c] a, b, c] c Этот пример показывает, что пробелы в списке сохраняются. Для удаления начальных и конечных пробелов, если они не нужны, надо использовать встроенную функцию TRIM. [123, 456, 789] ORA-00931: missing identifier Это показывает, что для применения процедуры к строке чисел необходимо изменить формат строки, как показано ниже: t"123", "456", "789"] ["123"] [ "456"] [ "789"] "123", "456", "789" Числа в кавычках удалось извлечь из строки. Обратите внимание, однако, что в элементах таблицы сохранен не только начальный пробел, но и кавычки. Если они не нужны, удалять их придется отдельно. ["This is a long string, longer than 32 characters","b",c] ORA-00972: identifier is too long PL/SQL procedure successfully completed. Последний пример показывает, что, если передан слишком длинный идентификатор (длиннее 30 символов), процедура возбуждает исключительную ситуацию. Рассматриваемые процедуры подходят только для строк длиной до 30 символов. Хотя процеду Пакет DBMS_UTILITY pa TABLE_TO_COMMA и позволяет формировать строку из элементов таблицы длиннее 30 символов, процедура COMMA_TO_TABLE не позволит преобразовать результат обратно в таблицу.
Процедура DB_VERSION и функция PORT_STRING Процедура DB_VERSION появилась в версии Oracle 8.0 для определения версии сервера в приложениях. Ее можно было бы использовать, например, в пакете CRYPT_PKG (см. раздел приложения А, посвященный пакету DBMS_OBFUSCATION_TOOLKIT), чтобы предупредить пользователей, пытающихся использовать для шифрования процедуры DES3 на сервере Oracle 8.1.5, что это не сработает, до того, как они начнут получать сообщения об ошибках. Интерфейс этой процедуры предельно прост: scott@TKYTE816> declare 2 l_version varchar2(255);
3 l_compatibility varchar2(255);
4 begin 5 dbms_utility.db_version(l_version, l_compatibility);
6 dbms_output.put_line(l_version);
7 dbms_output.put_line(l_compatibility);
8 end;
9/ 8.1.6.0.0 8.1. PL/SQL procedure successfully completed.
При этом она выдает более детальную информацию, чем более старая функция PORT_STRING: scott@TKYTE816> PORT_STRING ------------------------------------IBMPC/WIN_NT-8.1.0 Функция PORT_STRING не только вынуждает анализировать полученную строку, но и не позволяет определить, работаем ли мы с сервером версии 8.1.5, 8.1.6 или 8.1.7. Процедура DB_VERSION для получения такой информации подходит больше. С другой стороны, функция PORT_STRING позволяет определить, на какой операционной системе работает сервер. s e l e c t dbms_utility.port_string from dual;
Функция GET_HASH_VALUE Эта функция возвращает для переданной строки числовое хеш-значение. Ее можно использования для создания собственной "PL/SQL-таблицы" со строковыми индексами или, как было показано в разделе, посвященном пакету DBMS_LOCK, для реализации других алгоритмов. Учтите, что алгоритм, используемый для реализации функции GET_HASH_VALUE, менялся при переходе к следующей версии сервера, так что не надо использовать эту 1740 Приложение А функцию для генерации суррогатных ключей. Если сохранять возвращаемые этой функцией значения в таблице, при переносе приложения в следующую версию сервера возможны проблемы, поскольку по тем же входным данным будут выдаваться другие значения хеш-функции! Функция принимает три параметра. Х Строку, которую необходимо хешировать. Х "Базовое число" для возвращаемых значений. Если необходимо получить числа в диапазоне от 0 до некоторого числа, надо указать базовое значение 0. Х Размер хеш-таблицы. Лучше, если это число будет степенью двойки. Чтобы продемонстрировать использование функции GET_HASH_VALUE, я реализую новый тип, HASHTABLETYPE, для поддержки хешей в языке PL/SQL. Он очень похож на PL/SQL-таблицу, проиндексированную не числами, а строками типа VARCHAR2. Обычно элементы PL/SQL-таблицы индексируются целыми числами. Новый же тип PL/SQL-таблицы позволяет индексировать элементы строками. Можно объявлять переменные типа HASHTABLETYPE, помещать (GET) и выбирать (PUT) из них данные. Такого рода таблиц можно создать сколько угодно. Вот спецификация соответствующих типов: tkyte@TKYTE816> create or replace type myScalarType 2 as object 3 (key varchar2(4000), 4 val varchar2(4000) 5 );
6/ Type created. tkyte@TKYTE816> create or replace type myArrayType 2 as varray(10000) of myScalarType;
3/ Type created. tkyte@TKYTE816> create or replace type hashTableType 2 as object 3( 4 g_hash_size number, 5 g_hash_table myArrayType, 6 g_collision_cnt number, 7 8 static function new(p_hash_size in number) 9 return hashTableType, 10 11 member procedure put(p_key in varchar2, 12 p_val in varchar2), 13 14 member function get(p_key in varchar2) 15 return varchar2, 16 17 member procedure print_stats Пакет DBMS_UTILITY 18 );
/ Type c r e a t e d. Интересная особенность реализации состоит в добавлении статической функциичлена NEW. Это позволит создать собственный конструктор. Специального значения имя NEW не имеет. Это не ключевое слово. Функция NEW просто позволяет объявлять данные типа HASHTABLETYPE следующим образом: declare l_hashTable hashTableType := hashTableType.new(1024);
a не как обычно:
declare l_hashTable hashTableType := hashTableType(1024, myArrayType(), 0);
Я уверен, что первый вариант надежнее и понятнее второго. Второй вариант вызова раскрывает многие детали реализации (например, то, что используется тип массива, имеется переменная G_COLLISION_CNT, которой надо задавать значение 0, и т.п.). Пользователям знать об этом не обязательно. Теперь рассмотрим тело объектного типа: scott@TKYTE816> create or replace type body hashTableType 2 as 3 4 Ч Наш более "дружественный" конструктор. 5 6 static function new(p_hash_size in number) 7 return hashTableType 8 is 9 begin 10 return hashTableType(p_hash_size, myArrayType(), 0 ) ;
11 end;
12 13 member procedure put(p_key in varchar2, p_val in varchar2) 14 is 15 l_hash number := 16 dbms_utility.get_hash_value(p_key, 1, g_hash_size);
17 begin 18 19 if (p_key is null) 20 then 21 raise_application_error(-20001, 'Пустой ключ не допускается');
22 end if;
23 Следующий фрагмент кода определяет, надо ли увеличить таблицу для размещения нового хешированного значения. Если Ч да, таблица увеличивается до необходимого для добавления индекса размера:
27 28 if (l_hash > nvl( g _ h a s h _ t a b l e. c o u n t, then 0)) 1742 Приложение А 29 30 31 g_hash_table.extend(l_hash-nvl(g_hash_table.count,0)+l);
end if;
Нет никакой гарантии, что запись с индексом, полученным при хешировании соответствующего ключа, пуста. При выявлении конфликта мы пытаемся поместить значение в следующий элемент набора. Выполняется до 1000 попыток поместить значение в таблицу. Если все 1000 попыток завершатся неудачно, попытка добавления считается неудачной. Это значит, что размер таблицы недостаточен: 35 36 37 38 39 40 41 42 43 for i in 0.. 1000 loop Ч Если мы собираемся выйти за пределы таблицы, Ч сначала надо добавить новый слот. if (g_hash_table.count <= l_hash+i) then g_hash_table.extend;
end if;
Следующий фрагмент реализует действие: если слот не используется или ключ уже находится в этом слоте, использовать и вернуть его. Странной кажется проверка того, пуст ли элемент G_HASH_TABLE или значение G_HASH_TABLE(L_HASH+I).KEY. Это показывает, что элемент набора может быть пустым (Null) или может содержать объект с пустыми атрибутами: 46 if (g_hash_table(l_hash+i) is null OR 47 nvl(g_hash_table(l_hash+i).key,p_key) = p_key) 48 then 49 g_hash_table(l_hash+i) := myScalarType(p_key,p_val);
50 return;
51 end if;
52 53 Ч Иначе увеличить счетчик количества конфликтов 54 Ч и перейти к следующему слоту. 55 g_collision_cnt := g_collision_cnt+l;
56 end loop;
57 58 -- Если мы оказались здесь, значит, таблица слишком мала. 59 -Увеличьте ее. 60 raise_application_error(-20001, 'слишком много хеш-значений в -> таблице');
61 end;
62 63 64 member function get(p_key in varchar2) return varchar2 65 is 66 l_hash number := 67 dbms_utility.get_hash_value(p_key, 1, g_hash_size);
68 begin Если необходимо получить значение, мы обращаемся к элементу индекса, в котором это значение хранится, а в случае возникновения конфликта, просматриваем до Пакет DBMS_UTILITY следующих элементов. Поиск прекращается раньше, если обнаружится пустой слот (известно, что нужной записи после этого значения быть не может): 71 for i in l_hash.. least(l_hash+1000, nvl(g_hash_table.count,0)) 72 loop 73 Ч Если обнаружили ПУСТОЙ слот, мы ЗНАЕМ, что искомого 74 Ч значения в таблице нет, поскольку он должен был быть -> помещен в этот слот. 75 if (g_hash_table(i) is NULL) 76 then 77 return NULL;
78 end if;
79 80 Ч Если ключ найден, вернуть соответствующее значение. 81 if(g_hash_table(i).key = p_key) 82 then 83 return g_hash_table(i).val;
84 end if;
85 end loop;
86 87 Ч Ключа в таблице нет. Завершаем работу. 88 return null;
89 end;
90 Последняя процедура используется для выдачи полезной информации, например, о количестве выделенных и использованных слотов, а также о количестве конфликтов. Учтите, что количество конфликтов может превосходить размер таблицы! 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 member procedure print_stats is l_used number default 0;
begin for i in 1.. nvl(g_hash_table.count,0) loop if (g_hash_table(i) is not null) then l_used := l_used + 1;
end if;
end loop;
dbms_output.put_line('Таблица увеличена до ' || g_hash_table.count);
dbms_output.put_line( 'Мы используем ' || l_used);
dbms_output.put_line('Количество конфликтов...' || g_collision_cnt);
end;
end;
/ Type body created.
Приложение А Как видите, мы использовали функцию GET_HASH_VALUE для получения по строке числа, которое можно использовать для индексации табличного типа и выборки значения. Теперь можно рассмотреть, как используется этот новый тип: tkyte@TKYTE816> declare 2 l_hashTblhashTableType :=hashTableType.new(power(2,7));
3 begin 4 for x in (select username, created from all_users) 5 loop 6 l_hashTbl.put(x.username, x.created);
7 end loop;
8 9 for x in (select username, to_char(created) created, 10 l_hashTbl.get(username) hash 11 from all_users) 12 loop 13 if (nvl(x.created, 'x') onvl(x.hash,'x')) 14 then 15 raise program_error;
16 end if;
17 end loop;
18 19 l_hashTbl.print_stats;
20 end;
21 / Таблица увеличена до 120 Используется 17 Количество конфликтов....1 PL/SQL procedure successfully completed. Вот и все. Мы расширили возможности языка PL/SQL, добавив хеш-таблицу на базе стандартных пакетов.
Резюме Мы завершаем обзор основных подпрограмм пакета DBMS_UTILITY. Многие из них, в частности GET_TIME, GET_PARAMETER_VALUE, GET_HASH_VALUE и FORMAT_CALL_STACK, входят в мой список "часто даваемых ответов". Это означает, что именно они необходимы для решения многих проблем. Пользователи просто не знают о существовании этих подпрограмм.
Pages: | 1 | ... | 20 | 21 | 22 | 23 | 24 | Книги, научные публикации