Книги, научные публикации
Pages: | 1 | ... | 18 | 19 | 20 | 21 | 22 | ... | 24 | Oracle для профессионалов Том Кайт DiaSoft 2003 торгово-издательский дом Москва Х Санкт-Петербург Х Киев УДК 681.3. 06(075) Б Б К 32.973.2 К 91 КАЙТ ТОМ К 91 Oracle для профессионалов. Пер. с ...
-- [ Страница 20 ] --Возможные ошибки Других специфических ошибок, кроме тех, что обсуждалось в разделе "Проблемы", при использовании прав вызывающего или создателя не будет. Используя права вызывающего, важно четко понимать, как в PL/SQL обрабатываются встроенные SQL-операторы, чтобы избежать проблем с операторами SELECT * и изменяющимся порядком столбцов, "скрытыми" столбцами при выполнении и так далее. Кроме того, работавший без проблем PL/SQL-код при использовании прав вызывающего может выдавать различные ошибки для каждого из пользователей. Причина в том, что ссылки на объекты разрешаются по-разному. В каких-то схемах может не хватать привилегий, использоваться другие типы данных и т.д. При использовании прав вызывающего необходимо больше внимания уделять надежности кода- и учитывать возможность возникновения ошибок там, где их обычно не бывает. Статические ссылки больше не гарантируют безошибочность работы кода. Ситуация напоминает скорее создание ODBC- или JDBC-программы со встроенными непосредственными вызовами SQL-операторов. Вы контролируете "компоновку" программы (вам известно, какие подпрограммы в клиентском приложении будут вызываться), но вы не контролируете работу SQL-операторов до тех пор, пока они не выполнятся. SQL-оператор в PL/SQL-подпрограмме с правами вызывающего работает также, как в клиентском приложении, использующем интерфейс JDBC. Не проверив все варианты выполнения для каждого пользователя, вы не можете быть на 100 процентов уверены, что в производственной среде все будет работать без ошибок. Поэтому в коде надо больше заботиться об обработке ошибок, чем в традиционной хранимой процедуре.
Резюме В этой главе мы детально рассмотрели особенности процедур с правами создателя и вызывающего. Вы убедились, как легко обеспечить работу с правами вызывающего, но узнали и то, какой ценой это достигается с точки зрения: Х выявления и обработки ошибок;
Х трудно выявляемых ошибок, вызванных иной структурой таблицы при выполнении;
Х дополнительного расхода пространства в разделяемой области SQL;
Х дополнительных затрат времени на анализ операторов.
Права вызывающего и создателя Во многих случаях эта цена оказывается неприемлемой. В других же случаях (например, при создании универсальной подпрограммы для выдачи в виде набора строк со значениями столбцов через запятую результатов запроса или при форматировании результатов запроса по вертикали, а не по горизонтали) возможность использовать права вызывающего Ч бесценна. Без нее просто не удалось бы получить желаемый результат. Подпрограммы с правами вызывающего имеет смысл применять в таких случаях: Х когда необходимо использовать динамические SQL-операторы (как в упомянутых выше примерах);
Х когда выполняемые SQL-операторы обеспечивают защиту на базе идентификатора схемы (как в случае работы со словарем данных или при реализации собственных средств тщательного контроля доступа в приложении);
Х когда необходим учет ролей Ч только подпрограммы с правами вызывающего позволяют этого добиться. Права вызывающего можно использовать для обеспечения доступа к различным схемам на основе текущей схемы, возвращаемой вызовом SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'), но при этом надо позаботиться о согласованности схем и наличии необходимых привилегий (или учитывать возможные проблемы с этим в коде). Надо также быть готовым к более интенсивному использованию разделяемого пула и дополнительному расходу ресурсов сервера на анализ операторов. Работа с правами создателя идеально подходит почти для всех хранимых процедур. Процедуры с правами вызывающего Ч мощное средство, но использовать его надо только по назначению.
A Основные стандартные пакеты В этом разделе книги будут рассмотрены стандартные пакеты в базе данных, которых, по моему мнению, должен знать каждый. Все эти пакеты описаны в руководстве Oracle8i Supplied PL/SQL Packages Reference. B фирменной документации обычно описаны точки входа (общедоступные процедуры и функции) стандартных пакетов и использование каждой функции/процедуры. Я же рассмотрю подробно, когда имеет смысл использовать тот или иной пакет. Не вдаваясь глубоко в работу каждой процедуры, я уделю внимание наиболее часто используемым точкам входа и продемонстрирую, как они используются. Исчерпывающий список содержащихся в каждом пакете процедур и подробное описание параметров вы сможете найти в упомянутом документе. Освоив это приложение, вы будете хорошо ориентироваться в назначении стандартных пакетов. Мы изучим не все пакеты. Это не означает, что остальные пакеты менее полезны, просто при разработке они используются редко. Мы рассмотрим пакеты, описанные в приложении. Х DBMS_ALERT и DBMS_PIPE. Средства межпроцессного взаимодействия в базе данных. Пакет DBMS_ALERT можно использовать для уведомления всех заинтересованных сеансов об определенном событии. Пакет DBMS_PIPE позволяет двум сеансам взаимодействовать, аналогично тому, как это происходит через сокет TCP/IP. Х DBMS_APPLICATION_INFO. Позволяет приложению записать полезную информацию в представления V$. Незаменим в случае контроля действий хранимой процедуры и записи другой информации.
Х DBMS_JAVA. PL/SQL-пакет, используемый для работы с хранимыми процедурами на языке Java.
Приложение А Х DBMS_JOB. Планировщик заданий в базе данных. Используется, если необходимо выполнять хранимую процедуру, например, ежесуточно в 2 часа ночи или просто для выполнения какого-либо действия в фоновом режиме. Х DBMS_LOB. Пакет для работы с большими объектами (Large OBjects Ч LOB) в базе данных. Х DBMS_LOCK. Пакет для создания пользовательских блокировок, независимых от блокировок уровня строки или таблицы, устанавливаемых сервером Oracle. Х DBMS_LOGMNR. Пакет для просмотра и анализа содержимого активных журналов повторного выполнения. Х DBMS_OBFUSCATION_TOOLKIT. Обеспечивает шифрование данных в базе. Х DBMS_OUTPUT. Обеспечивает простые средства вывода информации на экран из PL/SQL для среды SQL*Plus и SVRMGRL. Х DBMS_PROFILER. Профилировщик исходного кода PL/SQL, встроенный в базу данных. Х DBMS_UTILITY. "Сборная солянка" полезных процедур. Х UTL_FILE. Обеспечивает средства ввода-вывода текстовых (а в Oracle 9.2.x и двоичных Ч прим. научн.ред.) файлов в PL/SQL. Позволяет читать и записывать текстовые файлы на сервере с помощью PL/SQL. Х UTL_HTTP. Обеспечивает работу по протоколу HTTP (Hyper Text Transfer Protocol Ч протокол передачи гипертекста) из среды PL/SQL. Позволяет загружать Web-страницы в PL/SQL. Х UTL_RAW. Обеспечивает преобразование данных типа RAW в VARCHAR2, и наоборот. Используется для работы с протоколом TCP/IP, при обработке больших объектов типа BLOB и BFILE, а также для шифрования. Х UTL_SMTP. Обеспечивает работу по протоколу SMTP (Simple Mail Transfer Protocol Ч простой протокол передачи электронной почты) из среды PL/SQL. В частности, позволяет послать сообщение по электронной почте из PL/SQL-подпрограммы. Х UTL_TCP. Предоставляет средства работы с сокетами TCP/IP в языке PL/SQL. Позволяет из PL/SQL-кода подключиться к любой службе TCP/IP.
Когда используются стандартные пакеты Причина использования стандартных пакетов проста: гораздо проще разрабатывать и сопровождать код, использующий стандартные средства, чем создавать их самому. Если корпорация Oracle поставляет пакет для определенных целей (например, шифрования данных), не имеет смысла писать такой пакет самому. Часто я сталкиваюсь с тем, что разработчики по незнанию создают средства, уже существующие в базе данных. Знание того, какие готовые инструментальные средства имеются, существенно упрощает разработку.
Основные стандартные пакеты O стандартных пакетах Имена всех стандартных пакетов Oracle начинаются с префиксов DBMS_ или UTL_. Имена пакетов, созданных разработчиками серверных технологий (Server Technologies Ч теми, кто писал ядро базы данных), обычно начинаются с префикса DBMS_. Пакеты с именами, начинающимися с UTL_, происходят из других источников. В качестве примера можно назвать UTL_HTTP Ч пакет для выполнения HTTP-запросов из PL/SQL (для получения Web-страниц и т.п.). Подразделение по разработке сервера приложений (Application Server Division) корпорации Oracle создало этот пакет для поддержки механизма ICX (Inter-Cartridge eXchange Ч обмен данными между картриджами) в сервере OAS (Oracle Application Server Ч сервер приложений Oracle), который сейчас заменен сервером iAS, (internet Application Server Ч сервер приложений Internet). Это различие имен для разработчиков практического значения не имеет Ч просто интересно отметить. Большинство этих пакетов хранится в базе данных в скомпилированном, скрытом (wrapped) формате. Благодаря этому код защищен от любопытных глаз. Можно увидеть спецификацию пакета, но не реализацию. Если выбрать из базы данных код тела пакета DBMS_OUTPUT, будет получено примерно следующее: tkyte@TKYTE816> s e l e c t t e x t 2 from all_source 3 where name = 'DBMS_OUTPUT' 4 and type = 'PACKAGE BODY' 5 and line < 10 6 order by line 7/ TEXT package body dbms_output wrapped 0 abcd abcd abcd abcd abcd abcd abcd 9 rows s e l e c t e d. Как видите, пользы от этого мало. Можно, однако, выбрать спецификацию пакета.* tkyte@TKYTE816> s e l e c t t e x t 2 from all_source 3 where name = 'DBMS_OUTPUT' 4 and type = 'PACKAGE' 5 and line < * В базе данных, естественно, все комментарии в спецификации - на английском. Оригинал можно получить, выполнив представленный ниже оператор SELECT. - Прим. научн. ред.
6 7 TEXT Приложение А order by line / package dbms_output as -Ч Ч Ч Ч Ч Ч Ч Ч Ч Ч Ч Ч ОБЗОР Эти процедуры накапливают информацию в буфере (с помощью "put" и "put_line") так, что ее можно выбрать в дальнейшем (с помощью "get_line" или "get_lines"). Если пакет отключен, то все вызовы просто игнорируются. Таким образом, эти подпрограммы активны, только если клиент способен обработать получаемую информацию. Это хорошо подходит для отладки или написания хранимых процедур, с помощью которых выдаются сообщения или отчеты в среде sql*dba или plus (например, "описательных процедур" и т.п.). Стандартный размер буфера Ч 20000. Минимальный Ч 2000, а максимальный -1000000. ПРИМЕР Предположим, из триггера необходимо выдать отладочную информацию. Для этого в триггере можно вызвать dbms_output.put_line('Мы получили: '||:new.col|| ' Ч новое значение');
Если клиент включил пакет dbms_output, строка-аргумент put_line будет помещена в буфер и клиент, выполнив оператор (предположительно, оператор вставки, удаления или изменения, вызвавшего срабатывание триггера), сможет выполнить Ч Ч Ч 25 rows selected. В базе данных скрыт оперативный источник документации. Спецификация каждого из рассматриваемых пакетов содержит достаточно полное описание назначения пакета, действий каждой функции и процедуры, а также их использования. Это очень удобно при отсутствии документации, но и при ее наличии тоже пригодится, поскольку спецификация иногда содержит данные, отсутствующие в документации, или полезные примеры. Далее мы рассмотрим пакеты, являющиеся большим подспорьем при постоянной работе с СУБД Oracle. Эти пакеты часто используются всеми разработчиками. Мы также рассмотрим новые пакеты и способы обхода некоторых ограничений этих встроенных пакетов, с которыми, как я знаю по своему опыту, часто сталкиваются разработчики.
Пакеты DBMS_ALERT и DBMS_PIPE Пакеты DBMS_ALERT и DBMS_PIPE Ч очень мощные средства межпроцессного взаимодействия. Оба они обеспечивают возможность взаимодействия сеансов базы данных. Пакет DBMS_ALERT по функциональности во многом аналогичен сигналам операционной системы UNIX, a DBMS_PIPE очень похож на именованный канал UNIX. Поскольку разработчики приложений часто сомневаются, какой пакет в каких случаях использовать, я решил описать их вместе. Пакет DBMS_ALERT создавался для того, чтобы сеанс мог сигнализировать об определенном событии в базе данных. Другие сеансы, которых касается это событие, получают уведомление о том, что оно произошло. Уведомления эти посылаются в рамках транзакций, т.е. можно сигнализировать о событии в триггере или хранимой процедуре, но пока соответствующая транзакция не зафиксирована, уведомление ожидающим сеансам не посылается. Если транзакция отменена, уведомление не будет послано. Важно понимать, что сеанс, которому необходимо получить уведомление о событии в базе данных, должен либо периодически опрашивать базу данных, не поступил ли соответствующий сигнал, либо просто ждать в заблокированном состоянии возникновения соответствующего события. Пакет DBMS_PIPE более универсален. Он позволяет одному или нескольким сеансам читать сообщения с одной стороны именованного канала и при этом записывать сообщения в этот канал с другой стороны. Только один из читающих сеансов может получить сообщение, причем адресовать сообщение конкретному сеансу по одному именованному каналу нельзя. Если читающих сеансов больше одного, прочитает записанное в канал сообщение любой из них. Каналы не поддерживают транзакции: если сообщение послано, оно будет доступным другим сеансам. Фиксировать транзакцию не Приложение А надо, а фиксация или откат соответствующей транзакции не повлияет на результат передачи сообщения по каналу.
Когда использовать сигналы и каналы Основное отличие между сигналами и каналами Ч это поддержка (или отсутствие поддержки) транзакций. С помощью сигналов можно передать сообщение одному или нескольким сеансам после того, как некоторое действие успешно зафиксировано в базе данных. Каналы позволяют немедленно передать сообщение одному сеансу. Сигналы имеет смысл использовать, например, в следующих случаях. Х На экране в виде графической диаграммы отображаются данные о курсе акций. Когда информация о курсе изменяется в базе данных, необходимо уведомить приложение, чтобы изменить соответственно экран. Х При добавлении новой записи в таблицу необходимо выдать в приложении диалоговое окно, уведомляющее пользователя о новом задании. Каналы базы данных имеет смысл использовать, например, в следующих случаях. Х На другой машине в сети работает процесс, выполняющий определенные действия. Надо послать процессу сообщение с требованием выполнить необходимое действие. В этом случае канал базы данных используется аналогично сокету TCP/IP. Х Необходимо поставить в очередь в области SGA данные, которые должны быть прочитаны и обработаны другим процессом. При этом канал базы данных используется как непостоянная очередь (FIFO), доступная для чтения нескольким различным сеансам. Можно привести и другие примеры использования пакетов, но это Ч наиболее типичные варианты использования сигналов и каналов, позволяющие понять, когда именно надо применять каждый из пакетов. Сигналы используются для уведомления множества пользователей о произошедшем событии (после фиксации). Каналы используются для немедленной передачи сообщения другому сеансу (и, как правило, ожидания от него ответа). Теперь, когда назначение сигналов и каналов понятно, рассмотрим детали реализации каждого из этих механизмов.
Настройка Пакеты DBMS_ALERT и DBMS_PIPE стандартно устанавливаются в базе данных. В отличие от многих стандартных пакетов, привилегия EXECUTE для этих пакетов роли PUBLIC не предоставляется. В Oracle 8.0 и выше привилегия ЕХЕСUТЕ для этих пакетов предоставляется роли EXECUTE_CATALOG_ROLE. В предыдущих версиях никакие привилегии на эти пакеты по умолчанию не предоставлялись. Поскольку привилегия EXECUTE предоставлена роли, причем Ч не PUBLIC, вы не сможете создать хранимую процедуру, зависящую от этих пакетов, поскольку при компиляции хранимого кода роли никогда не действуют. Необходимо явно предоставить привилегию EXECUTE соответствующей учетной записи.
Пакеты DBMS_ALERT и DBMS_PIPE Пакет DBMS_ALERT Пакет DBMS_ALERT Ч очень небольшой и содержит всего семь точек входа. Я опишу здесь шесть наиболее интересных. Если для приложения требуется получить уведомление, то наиболее существенными окажутся следующие подпрограммы. Х REGISTER. Зарегистрироваться на получение указанного сигнала. В сеансе можно многократно вызывать подпрограмму REGISTER c разными именами сигналов, чтобы получать уведомления при наступлении одного из нескольких событий. Х REMOVE. Снять регистрацию на получение сигнала, чтобы сервер не пытался уведомить о наступлении события. Х REMOVEALL. Снять для сеанса регистрацию на получение всех сигналов. Х WAITANY. Ожидать уведомления о сигналах, на получение которых сеанс зарегистрирован. Эта подпрограмма выдает имя поступившего сигнала и обеспечивает доступ к сопровождающему его короткому сообщению. Ждать можно либо заданное время, либо вообще не ждать (что позволяет из приложения эпизодически опрашивать систему, чтобы узнать, не произошло ли событие, не блокируя при этом дальнейшую обработку ожиданием события). Х WAITONE. Ожидать уведомления об указанном сигнале. Как и в случае WAITANY, ждать можно определенное время или вообще не ждать. Приложение, желающие послать сигнал, или уведомить о событии, может сделать это с помощью следующей подпрограммы. Х SIGNAL. Послать сигнал о наступлении события при фиксации текущей транзакции. При откате посылка сигнала отменяется. Итак, пакет DBMS_ALERT очень просто использовать. Клиентское приложение, для которого требуется получение уведомления о событии, может содержать код вида: tkyte@TKYTE816> begin 2 dbms_alert. r e g i s t e r (' MyMert') ;
3 end;
4/ PL/SQL procedure successfully completed. tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> declare 2 l_status number;
3 l_msg varchar2(1800);
4 begin 5 dbms_alert.waitone(name => 'MyMert', 6 message => l_msg, 7 status => l_status, 8 timeout => dbms_alert.maxwait);
9 10 if (l_status = 0) 11 then 12 'dbms_output.put_line('Сообщение события: ' || l_msg);
13 14 Приложение А end if;
end;
/ Мы зарегистрировались на получение сигнала MyAlert, а затем вызвали процедуру DBMS_ALERT.WAITONE, ожидая поступление этого сигнала. Обратите внимание, что, поскольку использована константа DBMS_ALERT.MAXWAIT из пакета DBMS_ALERT, сеанс при выполнении этого вызова начнет ждать бесконечно. Сеанс блокируется в ожидании соответствующего события. Можно задать для клиентского приложения период ожидания 0 секунд, что исключит ожидание, и опрашивать сервер о наступлении события. Например, приложение Oracle Forms может использовать ежеминутно срабатывающий таймер, вызывающий процедуру DBMS_ALERT.WAITONE, чтобы узнать, не произошло ли некоторое событие. Если событие произошло, экран приложения изменяется. Можно с такой же частотой активизировать поток Java, проверяющий, не произошло ли событие, и обновляющий совместно используемую структуру данных, и т.д. Чтобы послать этот сигнал, достаточно выполнить следующее: tkyte@TKYTE816> exec dbms_alert.signal('MyMert', 'Hello World');
PL/SQL procedure successfully completed. tkyte@TKYTE816> Commit complete. в другом сеансе. В сеансе, заблокированном в ожидании события, вы должны немедленно увидеть: 15 / commit;
Сообщение события: Hello World PL/SQL procedure successfully completed. То есть сеанс больше не заблокирован. Я продемонстрировал наиболее типичный вариант использования пакета DBMS_ALERT. Сеансы ждут сигнала с определенным именем. Пока в пославшем сигнал сеансе транзакция не зафиксирована, уведомление о сигнале не посылается. В этом легко убедиться с помощью двух сеансов SQL*Plus. Работа с сигналами становится более интересной, если задаться следующими вопросами. Х Что происходит, если несколько сигналов более-менее одновременно отправляются разными сеансами? Х Что происходит, если посыпать сигнал несколько раз: сколько сигналов будет сгенерировано в конечном итоге? Х Что происходит, если более одного сеанса пошлет сигнал, после того как я зарегистрировался на его получение, но до вызова одной из процедур ожидания? А что произойдет, если несколько сеансов пошлют сигнал в промежутке между вызовами процедур ожидания? Ответы на эти вопросы позволят выявить побочные эффекты использования сигналов, которые необходимо учитывать. Я также предложу способы избежать некоторых проблем, связанных со всем изложенным выше.
Пакеты DBMS_ALERT и DBMS_PIPE Одновременные сигналы нескольких сеансов Если повторно выполнить рассмотренный пример, зарегистрировавшись на получение сигнала MyAlert и ожидая его в одном сеансе, а затем запустить два дополнительных сеанса, можно будет увидеть, что произойдет при одновременной передаче сигналов из нескольких сеансов. На этот раз в обоих сеансах мы выполним: tkyte@TKYTE816> exec dbms_alert.signal('MyMert', 'Hello World');
(транзакции не фиксируются). Окажется, что сеанс, пославший сигнал вторым, заблокирован. Это показывает, что если N сеансов одновременно пытаются послать один и тот же сигнал, N-1 из них будут заблокированы при вызове DBMS_ALERT.SIGNAL. Продолжит работу только один из сеансов. Сигналы должны посылаться последовательно, и следует позаботиться о предотвращении подобных проблем. Должна обеспечиваться возможность одновременного доступа к базе данных множества сеансов. Пакет DBMS_ALERT Ч одно из тех средств, которое существенно снижает масштабируемость по этому показателю. Если создать для таблицы триггер на событие INSERT, а в коде этого триггера использовать вызов DBMS_ALERT.SIGNAL, при выполнении множества операторов INSERT все они выстроятся в очередь, если хоть один сеанс зарегистрировался на получение соответствующего сигнала. Поэтому имеет смысл ограничить количество сеансов, посылающих сигналы. Например, при получении оперативных данных из внешнего источника пакет DBMS_ALERT вполне можно использовать, если данные в таблицу вставляет только один сеанс. Если же речь идет о таблице проверки, в которую часто вставляют данные все сеансы, средства пакета DBMS_ALERT лучше не использовать. Один из способов избежать выстраивания сеансов в очередь Ч использовать пакет DBMS_JOB (которому посвящен специальный раздел в этом приложении). Можно написать процедуру, действия которой будут сводиться к передаче сигнала и фиксации транзакции: tkyte@TKYTE816> create table alert_messages 2 (job_id int primary key, 3 alert_name varchar2(30), 4 message varchar2(2000) 5) 6/ Table created. tkyte@TKYTE816> create or replace procedure background_alert(p_job in int) 2 as 3 l_rec alert_messages%rowtype;
4 begin 5 select * into l_rec from alert_messages where job_id = p_job;
6 7 dbms_alert.signal(l_rec.alert_name, l_rec.message);
8 delete from alert_messages where job_id = p_job;
9 commit;
10 end;
11 / Procedure created.
Приложение А Тогда соответствующий триггер будет иметь вид: tkyte@TKYTE816> create table t (x int);
Table created. tkyte@TKYTE816> create or replace trigger t_trigger 2 after insert or update of x on t for each row 3 declare 4 l_job number;
5 begin 6 dbms_job.submit(l_job, 'background_alert(JOB);
');
7 insert into alert_messages 8 (job_id, alert_name, message) 9 values 10 (l_job, 'MyAlert', 'X в T имеет значение ' || :new.x);
11 end;
12 / Trigger created. И будет обеспечивать передачу сигнала фоновым процессом после фиксации. При этом: Х сигналы посылаются в рамках транзакций;
Х приоритетные процессы (интерактивные приложения) в очередь не выстраиваются. Недостаток этого подхода в том, что задания могут выполняться не сразу;
может пройти некоторое время, прежде чем сигнал будет послан. Во многих случаях это приемлемо (важно уведомить ожидающие процессы о произошедшем событии, даже если и с небольшим опозданием). Средства расширенной поддержки очередей (advanced queues Ч AQ) также обеспечивают хорошо масштабируемый способ уведомления о событиях в базе данных. Использовать их сложнее, чем средства пакета DBMS_ALERT, но при этом обеспечивается большая гибкость.
Неоднократная передача сигнала в сеансе Теперь попытаемся ответить на вопрос, что произойдет, если послать одноименный сигнал в приложении несколько раз, а затем зафиксировать транзакцию? Сколько сигналов фактически будет послано? В данном случае ответ простой: один. Работа пакета DBMS_ALERT аналогична механизму передачи сигналов в ОС UNlX. В UNIX сигналы посылаются для уведомления процессов о событиях в операционной системе. Пример такого события Ч 'I/O is ready' (готовность к вводу-выводу), которое означает, что один из открытых файлов (или сокетов и т.п.) готов для продолжения операций вводавывода. Этот сигнал можно, например, использовать при создании сервера на основе протоколов TCP/IP. Операционная система уведомит вас, когда в одном из открытых сокетов появятся данные, ожидающие чтения;
то есть не придется постоянно опрашивать состояние каждого сокета, проверяя, нет ли в нем данных для чтения. Если ОС пять раз определила, что в сокете есть данные для чтения, но у нее не было возможности уведомить об этом приложение, она не будет повторять сообщение пять раз. Вы полу Пакеты DBMS_ALERT и DBMS_PIPE чите уведомление о событии "из сокета X можно читать", но не всю хронологию предыдущих событий по этому сокету. Пакет DBMS_ALERT работает точно так же. Возвращаясь к рассматриваемому примеру, можно выполнить фрагмент кода, регистрирующий сеанс на уведомление о событии и вызывающий процедуру WAITONE в ожидании этого события. В другом сеансе выполняем: tkyte@TKYTE816> begin 2 for i in 1.. 10 loop 3 dbms_alert.signal('MyAlert', 4 end loop;
5 end;
6/ 'Сообщение ' || i ) ;
PL/SQL procedure successfully completed. tkyte@TKYTE816> commit;
Commit complete. И в первом окне получаем результат: Сообщение события: сообщение 10 PL/SQL procedure successfully completed. Послано будет только последнее сообщение, о котором мы сигнализировали. Промежуточных сообщений никто никогда не увидит. Следует учитывать, что пакет DBMS_ALERT будет, как и задумано создателями, отбрасывать все предыдущие сообщения сеанса. С помощью этого пакета нельзя отправить в транзакции последовательность сообщений Ч это только механизм сигнализации. Он позволяет уведомить клиентское приложение, что "нечто произошло". Если вы предполагаете, что каждое событие, о котором вы уведомляли с помощью сигнала, будет получено всеми заинтересованными сеансами, Ч вас ждет разочарование (написанный на основе этого предположения код будет скорее всего ошибочен). Для решения этой проблемы можно использовать пакет DBMS_JOB, если для уведомления о каждом событии применять его средства. Однако можно использовать и другую технологию. С помощью средств расширенной поддержки очередей (которые в этой книге не рассматриваются) справиться с этой задачей намного проще.
Передача многочисленных сигналов несколькими сеансами до вызова процедуры ожидания Это последний вопрос: что произойдет, если сигнал будет послан несколькими сеансами после того, как на него поступил запрос, но прежде, чем вызвана процедура ожидания? Аналогичный вопрос: что произойдет, если между вызовами процедур ожидания несколько сеансов пошлют сигнал? Как и в случае многократного вызова DBMS_ALERT.SIGNAL в одном сеансе, запоминается только последний сигнал, и именно о нем получат уведомление сеансы. В этом можно убедиться, добавив команду PAUSE к используемому в примерах сценарию SQL*Plus:
Приложение А begin dbms_alert.register('MyAlert');
end;
/ pause Затем в других сеансах вызовите процедуры DBMS_ALERT.SIGNAL с уникальными сообщениями (чтобы их можно было различать) и зафиксируйте каждое сообщение. Например, измените представленный ранее простой цикл следующим образом: tkyte@TKYTE816> begin 2 for i in 1.. 10 loop 3 4 5 6 7 dbms_alert.signal('MyMert', commit;
end loop;
end;
/ 'Сообщение ' || i ) ;
PL/SQL procedure successfully completed. После этого в исходном сеансе просто нажмите клавишу Enter, и блок кода, вызывающий процедуру WAITONE, будет выполнен. Поскольку ожидаемый сигнал уже послан, этот блок кода немедленно завершит работу и выдаст строку, свидетельствующую о получении последнего сообщения (о чем оповестил сигнал). Все промежуточные сообщения других сеансов потеряны, как и было задумано создателями пакета. Итак, пакет DBMS_ALERT подходит для тех случаев, когда необходимо уведомить о событиях в базе данных множество клиентов. Об этих именованных событиях должно сообщать как можно меньше сеансов, из-за существенных проблем с очередностью доступа к процедурам пакета DBMS_ALERT. Поскольку неоднократные сообщения теряются, пакет DBMS_ALERT подходит в качестве средства уведомления о событии. Его можно использовать для уведомления клиента, например, об изменении данных в таблице T, но попытка использовать его для уведомления об изменениях в отдельных строках таблицы T закончится неудачей (поскольку сохраняется только последнее сообщение). Пакет DBMS_ALERT очень прост в использовании и практически не требует настройки.
Пакет DBMS_PIPE DBMS_PIPE Ч это стандартный пакет, обеспечивающий обмен данными между двумя сеансами. Это средство межпроцессного взаимодействия. Один сеанс может записывать сообщение в программный канал, а другой Ч читать это сообщение. В ОС UNIX аналогичный механизм реализован в виде именованного канала операционной системы. С помощью именованных каналов можно обеспечить запись данных одним процессом для другого. Пакет DBMS_PIPE, в отличие от DBMS_ALERT, Ч это пакет, работающий в режиме реального времени. При вызове функции SEND_MESSAGE немедленно посылается сообщение. Сервер не ждет выполнения оператора COMMIT;
передача сообщения выполняется вне транзакции. Это позволяет использовать пакет DBMS_PIPE в тех случаях, когда DBMS_ALERT не подходит (и наоборот). С помощью пакета Пакеты DBMS_ALERT и DBMS_PIPE DBMS_PIPE можно обеспечить диалоговое взаимодействие двух сеансов (что с помощью DBMS_ALERT сделать невозможно). Один сеанс может "попросить" другой выполнить некоторое действие. Выполнив его, второй сеанс возвращает первому результат. Предположим, второй сеанс Ч это С-программа, которая снимает показания термометра, подключенного к последовательному порту компьютера, и возвращает значение температуры первому сеансу. Первому сеансу надо записать текущую температуру в базу данных. Он может послать сообщение "дай мне значение температуры" второму сеансу, который определяет это значение и выдает ответ первому сеансу. Первый и второй сеансы могут работать на разных компьютерах, главное, оба они подключены к одной базе данных. Я использую базу данных примерно так же, как сеть TCP/IP Ч для обеспечения взаимодействия между двумя процессами. Однако при использовании пакета DBMS_PIPE мне не нужно знать имя хоста и номер порта для подключения Ч достаточно имени канала базы данных, в который надо отправить запрос. В базе данных есть два типа каналов Ч общедоступные и пользовательские. Общедоступный канал можно создать явно, вызвав CREATE_PIPE, либо неявно, послав в него сообщение. Основное отличие между явно и неявно созданными каналами состоит в том, что канал, созданный явным вызовом CREATE_PIPE, удаляется приложением по завершении работы, тогда как неявно созданный канал удаляется из области SGA как устаревший после определенного промежутка времени. Общедоступный канал устроен так, что любой сеанс, имеющий доступ к пакету DBMS_PIPE, может читать и записывать в него сообщения. Поэтому общедоступные каналы не подходят для передачи секретных или просто важных данных. Поскольку каналы обычно используются для диалога, а общедоступные каналы позволяют перехватывать или вмешиваться в этот диалог любому, злонамеренный пользователь может удалять сообщения из канала либо добавлять свои, "мусорные". Любое из этих действий нарушает диалог или протокол обмена данными между сеансами. Поэтому в большинстве приложений применяются пользовательские каналы. К данным в пользовательских каналах можно обращаться только сеансам, работающим с эффективным идентификатором пользователя-владельца канала, или от имени специальных пользователей SYS и INTERNAL. Это означает, что только с помощью хранимых процедур с правами создателя (см. главу 23, посвященную правам создателя и вызывающего), принадлежащих владельцу канала, либо в сеансах от имени владельца канала, пользователя SYS или INTERNAL можно читать или записывать данные в этот канал. Это существенно увеличивает надежность каналов, поскольку ни один другой сеанс или код не может вмешаться в протокол или перехватить данные. Канал Ч это объект в области SGA экземпляра Oracle. Этот механизм вообще не связан с диском. Данные в каналах теряются при остановке и перезапуске сервера. Чаще всего каналы используют для создания специализированных служб или серверов. До появления внешних процедур в Oracle 8.0 они были единственным способом реализовать хранимые процедуры на языке, отличном от PL/SQL. Для этого создавался "канальный" сервер. Компонент ConText (предшественник компонента interMedia Text) был реализован в Oracle 7.3 с помощью каналов базы данных и с тех пор так и работает. Со временем часть его функций была реализована с помощью внешних процедур, но большая часть механизмов индексирования по-прежнему реализуется с помощью каналов.
Приложение А Поскольку делать попытку читать данные из канала и писать их туда может любое количество сеансов, необходимо реализовать алгоритм, гарантирующий доставку сообщений нужному сеансу. Если предполагается создание специализированной службы (например, представленного ранее сервера температуры) и ее добавление в базу данных, необходимо гарантировать получение ответа, предназначенного сеансу А, именно сеансом А, а не сеансом В. Для удовлетворения этого стандартного требования обычно запрос выдается в один канал с общеизвестным именем, а вместе с обращением передается уникальное имя канала, из которого мы хотим прочитать ответ. Это можно проиллюстрировать следующей схемой:
Шаг 1. Сеанс А пишет свое обращение Ч "Какая сейчас температура? Ответить канал А" Ч в известный всем сеансам "канал температуры". Одновременно это могут делать и другие сеансы. Каждое сообщение помещается в канал по принципу очереди Ч "первым пришел, первым обслужен". Шаг 2. Сервер температуры читает одно сообщение из канала и запрашивает соответствующую службу, к которой он обеспечивает доступ. Шаг 3. Сервер температуры использует уникальное имя канала, которое запрашивающий сеанс (в данном случае канал А) указал в сообщении, для записи ответа. Для ответа используется неявный канал (так что канал ответа исчезает сразу после того, как работа с ним завершена). Если подобных вызовов предполагается много, имеет смысл использовать явно созданный канал, чтобы он сохранялся в области SGA i течение всего сеанса (не забудьте удалить его перед завершением сеанса!). Шаг 4. Сеанс А читает ответ из канала, который он указал серверу температуры для записи. Такая же последовательность событий произойдет и для сеанса В. Сервер температуры будет читать обращение, запрашивать температуру, определять по запросу канал, в который надо выдать ответ, и записывать ответ в него. Одна из интересных особенностей каналов базы данных Ч возможность читать из канала несколькими сеансами. Помещенное в канал сообщение будет прочитано только Пакеты DBMS_ALERT и DBMS_PIPE одним сеансом, но читать сообщения из канала может несколько сеансов одновременно. Это позволяет масштабировать представленную выше схему. По ней понятно, что запрашивать данные у сервера температуры может несколько сеансов, и он будет последовательно, по одному, обрабатывать эти запросы. Но ничего не мешает запустить несколько серверов температуры:
Теперь можно обрабатывать два запроса одновременно. Если запустить пять экземпляров сервера, обрабатывать можно одновременно пять запросов. Это похоже на пул подключений или на работу многопотокового сервера Oracle. Имеется пул процессов, готовых к работе, и максимальный объем одновременно выполняемых действий в каждый момент времени определяется количеством запущенных процессов. Эта особенность каналов базы данных позволяет легко масштабировать систему.
Серверы каналов или внешние процедуры?
В Oracle8 версии 8.0 впервые появилась возможность реализовать хранимые процедуры непосредственно на языке С, а сервер Oracle8i позволил создавать их также на языке Java. С учетом этого, нужны ли теперь пакет DBMS_PIPE и серверы каналов? Однозначный ответ: да. В главе, посвященной использованию внешних процедур, была описана соответствующая архитектура. Внешние процедуры на языке С, например, выполняются в отдельном адресном пространстве по отношению к хранимой процедуре на PL/SQL. Имеется однозначное соответствие между количеством сеансов, одновременно использующих внешние процедуры, и количеством созданных отдельных адресных пространств. Т.е., если 50 сеансов одновременно вызовут внешнюю процедуру, будет запущено 50 процессов или, по крайней мере, потоков EXTPROC. Механизм поддержки внешних процедур на языке С похож на механизм работы выделенного сервера Oracle. Сервер Oracle запускает отдельный серверный процесс для каждого из одновременно работающих сеансов;
точно так же он запускает экземпляр EXTPROC для каждого из одновременных вызовов внешних подпрограмм. Внешние процедуры на Java выполняются аналогично: с соответствием один к одному. Для каждого сеанса, использующего внешнюю процедуру на Java, запускается отдельный экземпляр JVM, с собственной информацией о состоянии и специально выделенными ресурсами.
1572 Приложение А Сервер каналов, с другой стороны, подобен многопотоковому серверу (MTS) в Oracle. Вы создаете пул разделяемых ресурсов (запускаете N серверов каналов), и они обслуживают обращения. Если одновременно поступает больше обращений, чем можно обработать, они выстраиваются в очередь. Это очень похоже на многопотоковый режим работы сервера Oracle, когда обращения поступают в очередь в области SGA и выбираются из очереди разделяемым сервером после обработки им предыдущего обращения. Это хорошо демонстрирует ранее рассмотренный пример сервера температуры. На первой схеме показан один сервер канала;
он определяет и выдает температуру клиентам по одному. На второй схеме представлены два сервера канала, обслуживающие все поступающие обращения. Одновременно к термометру будут обращаться не более двух клиентов. Это важно потому, что позволяет ограничить одновременный доступ к этому разделяемому ресурсу. Если бы использовались внешние процедуры и температуру запросили бы одновременно 50 сеансов, они могли бы "разрушить" программное обеспечение термометра, если оно не способно поддерживать такое количество одновременных обращений. Замените термометр любым другим разделяемым ресурсом, и возникнут такие же проблемы. Можно допустить несколько одновременных обращений, но если их будет много, то либо произойдет сбой, либо производительность снизится настолько, что практически ресурс работать перестанет. Еще одна причина использования сервера каналов может быть связана с тем, что для подключения к разделяемому ресурсу требуется много времени. Например, пару лет назад я работал над проектом в большом университете. Требовалось выполнять транзакции на мэйнфрейме (необходимо было обращаться к мэйнфрейму для получения информации о студентах). Подключение к мэйнфрейму могло потребовать от 30 до 60 секунд, но после этого информация выдавалась очень быстро (если только мы не перегружали мэйнфрейм огромным количеством одновременных обращений). Используя сервер каналов, мы смогли подключаться к серверу один раз, при запуске сервера каналов. Сервер каналов работал много дней, используя первоначальное подключение. Если бы использовалась внешняя процедура, пришлось бы инициировать подключение для каждого сеанса. Реализация на основе внешних процедур в этой среде просто не работала бы из-за продолжительности подключения к мэйнфрейму. Сервер каналов не только позволил ограничить количество одновременных обращений к мэйнфрейму, но и выполнять продолжительный процесс подключения один раз, а затем использовать это подключение сотни тысяч раз. Если вы знакомы с обоснованием использования программного обеспечения для организации пула подключений в трехкомпонентной среде, вам и так понятно, зачем могут понадобиться каналы. Они позволяют повторно использовать результаты выполнения продолжительной операции (подключения к базе данных, в случае ПО для организации пула подключений) и ограничить объем потребляемых одновременно ресурсов (размер пула подключений). Последнее отличие сервера каналов от внешних процедур связано с тем, где может работать сервер каналов. Предположим, в случае сервера температуры сервер баз данных работает на платформе Windows. Контроль температуры осуществляется на UNIX-машине. При этом все доступные серверу библиотеки тоже находятся в ОС UNIX. Поскольку сервер каналов Ч всего лишь клиент базы данных, его можно запрограммировать, Пакеты DBMS_ALERT и DBMS_PIPE скомпилировать и запустить в среде UNIX. Сервер каналов необязательно должен работать на той же машине и даже аппаратной платформе, что и сервер баз данных. Внешняя же процедура должна выполняться на той же машине, что и сервер базы данных, Ч такие процедуры нельзя выполнять на удаленной машине. Поэтому сервер каналов можно использовать в ситуациях, когда внешнюю процедуру использовать невозможно.
Пример в сети Internet На Web-сайте издательства Wrox ( можно найти пример небольшого сервера каналов. Он отвечает на часто задаваемый вопрос: как выполнить команду базовой операционной системы из PL/SQL? После добавления поддержки Java и внешних процедур на языке С, такую функцию выполнения команд можно легко реализовать с помощью любой из этих технологий. А если компилятора языка С просто нет или поддержка Java в базе данных не установлена Ч что тогда? Этот пример показывает, как создать небольшой "сервер каналов", способный выполнять команды операционной системы, используя только утилиту SQL*Plus и командный интерпретатор csh. Сервер получился сравнительно простым, содержащим лишь несколько строк кода командного интерпретатора csh и еще меньше текста на языке PL/SQL. Однако он показывает основные возможности каналов базы данных и должен натолкнуть вас на идеи по созданию других полезных приложений.
Резюме Каналы базы данных Ч мощное средство сервера Oracle, позволяющее двум сеансам вести диалог. Созданные по аналогии с именованными каналами в ОС UNIX, они позволяют разработать собственный протокол пересылки и получения сообщений. Небольшой пример, представленный на сайте издательства Wrox, демонстрирует, как создать "сервер каналов" Ч внешний процесс, получающий обращения от сеансов базы данных и выполняющий для них кое-какие специальные действия. Каналы базы данных работают вне транзакций, что отличает их от сигналов, но именно это делает их во многих случаях незаменимыми. Я использовал каналы базы данных, в частности, для реализации следующих возможностей: Х передачи сообщений электронной почты;
Х распечатывания файлов;
Х интеграции с другими источниками данных, находящимися вне баз данных Oracle и не поддерживающими язык SQL;
Х реализации аналога процедуры DBMS_LOB.LOADFROMFILE для типов данных LONG и LONG RAW.
Пакет DBMS_APPLICATION_INFO Это Ч один из наименее используемых стандартных пакетов, хотя его функциональные возможности пригодятся в любом приложении. Интересовались ли вы когда-нибудь следующими вопросами: Х Что делает сеанс, какая форма обрабатывается, какой код выполняется в модуле? Х Насколько близко к завершению выполнение хранимой процедуры? Х Насколько близко к завершению выполнение пакетного задания? Х Какие значения связываемых переменных использованы в запросе? Пакет DBMS_APPLICATION_INFO можно использовать для получения ответов на эти и многие другие вопросы. Он позволяет устанавливать значения трех столбцов Ч CLIENT_INFO, ACTION и MODULE Ч соответствующей сеансу строки в представлении V$SESSION. Пакет предоставляет функции не только для установки этих значений, но и для их получения. Кроме того, один из параметров встроенных функций USERENV и SYS_CONTEXT позволяет получить значения столбца CLIENT_INFO в запросе. Можно, например, выполнить SELECT USERENV('CLIENT_INFO') FROM DUAL или использовать конструкцию WHERE SOME_COLUMN SYS_CONTEXT('USERENV','CLIENT_INFO') в запросах. Значения, устанавливаемые в представлениях V$, сразу же доступны в других сеансах. Фиксировать их не нужно, что позволяет эффективно использовать эти представления для взаимодействия с "внешним миром". Наконец, пакет DBMS_APPLICATION_INFO позволяет задать значения в представлении динамической производительности V$SESSION_LONGOPS (LONG OPerationS). Это удобно для записи сделанного в продолжительных заданиях.
Пакет DBMS_APPLICATION_INFO Многие инструментальные средства Oracle, например SQL*Plus, уже используют возможности этого пакета. Я создал сценарий SHOWSQL.SQL, позволяющий узнать, какие SQL-операторы пользователи выполняют в настоящий момент в базе данных (этот сценарий можно найти на Web-сайте издательства Wrox, Часть этого сценария выбирает данные из представления V$SESSION, в которых значения столбцов CLIENT_INFO, MODULE или ACTION непустые (NOT NULL). При запуске этого сценария я получаю, например, следующие результаты:
USERNAME OPS$TKYTE(107,19225) OPS$TKYTE(22,50901) MODULE 01@ s h o w s q l. s q l SQL*Plus ACTION CLIENT_INFO Первая строка показывает, что текущий сеанс выполняет сценарий SHOWSQL.SQL на уровне 01. Это означает, что сценарий вызван непосредственно, а не из другого сценария. Если создать сценарий TEST.SQL с единственной строкой @SHOWSQL, то для представления этого вложенного вызова утилита SQL*Plus установит для сценария SHOWSQL уровень вызова 02. Вторая строка показывает другой сеанс SQL*Plus. В нем сейчас никакие сценарии не выполняются (возможно, в нем выполняется команда, введенная непосредственно в командной строке). Если добавить соответствующие вызовы процедур пакета DBMS_APPLICATION_INFO в приложение, можно добиться такого же результата, расширив возможности контроля работы приложения для его создателя и администратора базы данных. Для установки соответствующих значений в представлении V$SESSION используются следующие вызовы. Х SET_MODULE. Эта процедура позволяет установить в представлении V$SESSION значения столбцов MODULE и ACTION. Имя модуля должно быть не длиннее 48 байт, а значение поля, описывающего действие, не должно быть длиннее 32 байт. В качестве имени модуля обычно указывается имя приложения. Первым действием можно указать STARTUP или INITIALIZING, чтобы отметить начало работы приложения. Х SET_ACTION. Эта процедура позволяет установить в представлении V$SESSION значение столбца ACTION (действие). Это значение должно быть таким, чтобы можно было понять, какая часть кода программы выполняется. В качестве действия можно указывать, например, имя текущей активной экранной формы, имя функции в программе на Pro*C или имя PL/SQL-подпрограммы. Х SET_CLIENT_INFO. Эта процедура позволяет сохранить до 64 байт специфической информации о приложении, которая может понадобиться. Обычно (см. далее) так сохраняются параметры представления или запроса.
Имеются процедуры и для чтения соответствующей информации из представлений. Помимо установки значений в представлении V$SESSION этот пакет позволяет устанавливать значения в представлении динамической производительности V$SESSION_LONGOPS. Это представление позволяет сохранять несколько строк информации в различных столбцах. Вскоре мы более подробно рассмотрим эту возможность.
Приложение А Использование информации о клиенте Процедура SET_CLIENT_INFO позволяет установить не только значения в столбцах представления V$SESSION, но и значение переменной CLIENT_INFO, которое можно получить с помощью встроенных функций userenv (в версиях Oracle 7.3 и выше) или sys_context (именно ее лучше использовать в версиях Oracle 8i и выше). Например, можно создать параметризованное представление, результаты выборки данных из которого зависят от значения переменной CLIENT_INFO. Эту идею можно проиллюстрировать следующим примером: scott@TKYTE816> exec dbms_application_info.set_client_info('KING');
PL/SQL procedure successfully completed. scott@TKYTE816> select userenv('CLIENT_INFO') from dual;
USERENV('CLIENT_INFO') KING scottTKYTE816> select sys_context('userenv','client_info')from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO') KING scott6TKYTE816> create or replace view 2 emp_view 3 as 4 select ename, empno 5 from emp 6 where ename = sys_context('userenv', 'client_info');
View created. scott@TKYTE816> select * from emp_view;
ENAME KING EMPNO scott@TKYTE816> exec dbms_application_info.set_client_info('BLAKE');
PL/SQL procedure successfully completed. scotteTKYTE816> select * from emp_view;
ENAME BLAKE EMPNO Как видите, можно установить соответствующее значение и легко использовать его в запросах вместо константы. Это позволяет создавать сложные представления с условиями, значения которых уточняются при выполнении. При использовании представлений могут возникать проблемы, связанные с включением условия (predicate merging). Если оптимизатор включает условие в определение представления, запрос из представления выполняется очень быстро. В противном случае запрос выполняется медленно. Используя значение переменной CLIENT INFO, можно включить условие заранее, если Пакет DBMS_APPLICATION_INFO оптимизатор не может этого сделать. Разработчик приложения должен установить переменной соответствующее значение и выполнить SELECT * из представления. В результате он получит нужные данные. Средства пакета DBMS_APPLICATION_INFO я применяю также для записи значений связываемых переменных, используемых в запросе и другой необходимой информации, позволяющей легко понять, что именно делают процедуры. Например, если включить в долго выполняющийся процесс следующие вызовы: tkyte@TKYTE816> declare 2 l_owner varchar2(30) default 'SYS';
3 l_cnt number default 0;
4 begin 5 dbms_application_info.set_client_info('owner-'||l_owner);
6 7 for x in (select * from all_objects where owner Ч l_owner) 8 loop 9 l_cnt := l_cnt+l;
10 dbms_application_info.set_action('processingrow ' || l_cnt);
11 end loop;
12 end;
13 / то с помощью сценария SHOWSQL.SQL можно получить такую информацию: tkyte@TKYTE816> @showsql USERNAME TKYTE TKYTE SID 8 11 SERIAL# PROCESS STATUS 206 780:716 ACTIVE 635 1004:1144 ACTIVE TKYTE(ll,635) ospid = 1004:1144 program = SQLPLUS.EXE Saturday 15:59 Saturday 16:15 SELECT * FROM ALL_OBJECTS WHERE OWNER = :bl USERNAME MODULE ACTION CLIENT_INFO TKYTE(8,206) 01@showsql.sql TKYTE(ll,635) SQL*Plus processingrow owner=SYS 5393 Сеанс (11,635) выполняет запрос SELECT * FROM ALL_OBJECTS WHERE OWNER = :B1. Отчет также показывает, что запрос выполнен пользователем SYS (owner=SYS) и что в момент вызова сценария showsql он уже обработал S393 строки. В следующем разделе мы увидим, как с помощью представления V$SESSION_LONGOPS получить еще более точную информацию, если заранее известно, сколько действий или шагов будет выполнять процедура.
Использование представления V$SESSION_LONGOPS Многие действия в базе данных могут выполняться достаточно долго. К таким действиям относятся, в частности, восстановление с помощью Recovery Manager, сортировка Приложение А или загрузка больших объемов данных,выполнение сложных запросов, требующих распараллеливания. При выполнении этих продолжительных действий информация о ходе работы записывается в представление динамической производительности V$SESSION_LONGOPS, что позволяет оценить объем сделанного. Это представление можно использовать и в приложениях. В представлении отражается состояние действий, выполняющихся в базе данных более шести секунд. Другими словами, в функции сервера Oracle, которые, по предположению разработчиков, обычно выполняются дольше шести секунд, включены вызовы процедуры, вставляющей данные в представление V$SESSION_LONGOPS. Это не означает, что при выполнении действия продолжительностью более шести секунд в это представление автоматически будет что-то записываться. К таким действиям сейчас относятся многие функции резервного копирования и восстановления, сбора статистической информации и выполнение запросов. В каждой новой версии Oracle появляются новые действия. Изменения в этом представлении сразу же доступны для других сеансов, т.е. транзакцию фиксировать не нужно. Можно контролировать ход процесса, изменяющего это представление, из другого сеанса с помощью запросов к представлению V$SESSION_LONGOPS. Разработчики могут добавлять строки в это представление. Обычно приложение вставляет и изменяет одну строку, но при необходимости можно вставлять и несколько. Процедура для установки значений в этом представлении имеет следующие параметры: PROCEDURE SET_SESSION_LONGOPS Argument Name RINDEX SLNO OP_NAME TARGET CONTEXT SOFAR TOTALWORK TARGET_DESC UNITS Эти параметры описаны ниже. Х RINDEX. Указывает серверу, какую строку в представлении V$SESSION_LONGOPS необходимо изменить. Если в качестве значения этого параметра указать DBMS_APPICATION_INFO.SET_SESSION_LONGOPS_NOHINT, в это представление будет автоматически вставлена новая строка, индекс которой будет записан в RINDEX. При указании в последующих вызовах процедуры SET_SESSION_LONGOPS этого индекса в качестве значения параметра RINDEX будет изменяться добавленная строка. Х SLNO. Служебное значение. Первоначально надо передать значение NULL, а полученное в результате выполнения значение Ч игнорировать. При каждом вызове надо передавать одно и то же значение. Type BINARY_INTEGER BINARY_INTEGER VARCHAR2 BINARY_INTEGER BINARY_INTEGER NUMBER NUMBER VARCHAR2 VARCHAR2 In/Out Default? IN/OUT IN/OUT IN DEFAULT IN DEFAULT IN DEFAULT IN DEFAULT IN DEFAULT IN DEFAULT IN DEFAULT Пакет DBMS_APPLICATION_INFO Х OP_NAME. Имя продолжительно выполняющегося процесса. Его длина не должна превышать 64 байт, а в качестве значения необходимо задавать строку, по которой легко определить, что именно выполняется. Х TARGET. Обычно используется для передачи идентификатора объекта, с которым выполняется продолжительное действие (например, идентификатор таблицы, в которую загружаются данные). Можно передать любое значение, в том числе NULL. Х CONTEXT. Число, задаваемое пользователем. Это число должно быть информативным для пользователя. Передать можно любое число. Х SOFAR. В качестве значения можно передавать любое число, но если это число будет представлять собой процент или другую количественную характеристику уже выполненной части действия, сервер сможет автоматически оценить, сколько времени осталось до завершения действия. Например, если необходимо обработать 25 объектов и на обработку каждого из них уходит примерно одинаковое время, можно задать в качестве значения параметра SOFAR количество обработанных объектов, а общее их количество передать как следующий параметр, TOTALWORK. Сервер определит, сколько времени потребовалось для выполнения уже сделанной части, и оценит время, необходимое для завершения действия. Х TOTALWORK. В качестве значения можно передавать любое число, но имеет смысл сопоставлять его со значением параметра SOFAR. Если SOFAR представляет собой процент от TOTALWORK, отражающий ход выполнения действия, сервер сможет вычислить, сколько времени осталось до завершения действия. Х TARGET_DESC. Этот параметр описывает значение TARGET, представленное выше. Если в качестве параметра TARGET передан идентификатор объекта, параметр может содержать имя объекта с этим идентификатором. Х UNITS. Описательный параметр, задающий единицу измерения для значений параметров SOFAR и TOTALWORK. Это могут быть, например, файлы, итерации или вызовы. Перечисленные выше значения может устанавливать пользователь. Если обратиться к представлению V$SESSION_LONGOPS, то в нем можно обнаружить намного больше столбцов, чем описано выше: ops$tkyte@ORA8I.WORLD> desc v$session_longops Name Null? Type SID SERIAL# OPNAME TARGET TARGET_DESC SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING NUMBER NUMBER VARCHAR2(64) VARCHAR2(64) VARCHAR2(32) NUMBER ** NUMBER ** VARCHAR2(32) DATE DATE NUMBER ** ** ** ** 1580 Приложение А ELAPSED_SECONDS CONTEXT MESSAGE USERNAME SQL_ADDRESS SQL_HASH_VALUE QCSID NUMBER NUMBER ** VARCHAR2(512) VARCHAR2(30) RAW(4) NUMBER NUMBER Значения столбцов, помеченных двумя звездочками (**), может устанавливать пользователь. Остальные столбцы имеют следующие значения. Х Столбцы SID и SERIAL# используются при соединении с представлением V$SESSION для получения информации о сеансе. Х Столбец START_TIME содержит время создания записи (обычно это время первого вызова процедуры DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS, с помощью которого и была создана строка). Х Х Столбец LAST_UPDATE_TIME представляет время последнего вызова процедуры SET_SESSION_LONGOPS. Столбец TIME_REMAINING содержит предполагаемое время, оставшееся до завершения действия. Его значение вычисляется как: ROUND(ELAPSED_SECONDS*((TOTALWORK/SOFAR)-1)). Х Столбец ELAPSED_SECONDS содержит количество секунд, прошедших с начала выполнения продолжительного действия до последнего изменения строки.
Х Столбец MESSAGE Ч производный. Он представляет собой конкатенацию значений столбцов OPNAME, TARGET_DESC, TARGET, SOFAR, TOTALWORK и UNITS, описывающую выполняемое действие. Х Значение в столбце USERNAME Ч имя пользователя, выполняющего действие. Х Значения столбцов SQL_ADDRESS и SQL_HASH_VALUE можно использовать для поиска в представлении V$SQLAREA последнего SQL-оператора, выполненного процессом. Х Значение столбца QCSID используется при распараллеливании запроса. Это идентификатор сеанса-координатора параллельного запроса. Итак, что же можно получить с помощью данного представления? Небольшой пример поможет прояснить это. Выполним в одном сеансе следующий блок кода: tkyte@TKYTE816> declare 2 l_nohint number default dbms_application_info.set_session_longops_nohint;
3 l_rindex number default l_nohint;
4 l_slno number;
5 begin 6 for i in 1.. 25 7 loop 8 dbms_lock.sleep(2);
9 dbms_application_info.set_session_longops Пакет DBMS_APPLICATION_INFO 10 (rindex => l_rindex, 11 slno => l_slno, 12 op_name => 'my long running operation', 13 target => 1234, 14 target_desc => '1234 is my target', 15 context => 0, 16 sofar => i, 17 totalwork => 25, 18 units => 'loops' 19 );
20 end loop;
21 end;
22 / Этот блок кода представляет собой продолжительное действие, выполняющееся в течение 50 секунд (вызов DBMS_LOCK.SLEEP просто приостанавливает выполнение на две секунды). В другом сеансе можно следить за ходом данного сеанса с помощью представленного ниже запроса (описание использованной в нем утилиты PRINT_TABLE см. в главе 23): tkyte@TKYTE816> begin 2 print_table('select b.* 3 from v$session a, v$session_longops b 4 where a.sid = b.sid 5 and a.serial# = b.serial#');
6 end;
7/ SID : 11 SERIAL : 635 OPNAME : my long running operation TARGET : 1234 TARGET_DESC : 1234 is my target SOFAR :2 TOTALWORK : 25 UNITS : loops START_TIME : 28-apr-2001 16:02:46 LAST_UPDATE_TIME : 28-apr-2001 16:02:46 TIME_REMAINING :0 ELAPSED_SECONDS :0 CONTEXT :0 MESSAGE : my long running operation: 1234 is my target 1234: 2 out of 25 loops done USERNAME : TKYTE SQL_ADDRESS : 036C3758 SQL_HASH_VALUE : 1723303299 QCSID :0 PL/SQL procedure successfully completed. ops$tkyteeORA8I.WORLD> / SID SERLAL# : 11 : Приложение А : my long running operation : 1234 : 1234 is my target :6 : 25 : loops : 28-apr-2001 16:02:46 : 28-apr-2001 16:02:55 : 29 :9 :0 : my long running operation: 1234 is my target 1234: 6 out of 25 loops done : TKYTE : 036C3758 : 1723303299 : OPNAME TARGET TARGET_DESC SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS CONTEXT MESSAGE USERNAME SQL_ADDRESS SQL_HASH_VALUE QCSID PL/SQL procedure successfully completed. ops$tkyte@ORA8I.WORLD> / SID SERIAL# OPNAME TARGET TARGET_DESC SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING ELAPSED_SECONDS CONTEXT MESSAGE USERNAME SQL_ADDRESS SQL_HASH_VALUE QCSID : 11 : 635 : my long running operation : 1234 : 1234 is my target : 10 : 25 : loops : 28-apr-2001 16:02:46 : 28-apr-2001 16:03:04 : 27 : 18 :0 : my long running operation: 1234 is my target 1234: 10 out of 25 loops done : TKYTE : 036C3758 : 1723303299 : PL/SQL procedure successfully completed. Может возникнуть вопрос: зачем представление V$SESSION_LONGOPS соединяется с представлением V$SESSION, если из V$SESSION не выбирается информация? Дело в том, что представление V$SESSION_LONGOPS содержит строки как для текущего, так и для прежних сеансов. По завершении сеанса это представление не очищается. Данные остаются, пока какой-нибудь другой сеанс не использует повторно соответствующий слот. Поэтому, чтобы получить информацию только для текущих сеансов, необходимо использовать соединение или подзапрос.
Пакет DBMS_APPLICATION_INFO Этот достаточно простой пример демонстрирует, что из представления V$SESSION_LONGOPS можно получить информацию, весьма ценную для пользователей и администраторов базы данных, которым необходимо контролировать ход выполнения продолжительных хранимых процедур, пакетных заданий, отчетов и т.п. Добавив лишь несколько вызовов, можно получить точную информацию в производственной среде. Вместо того чтобы гадать, что именно происходит в задании и сколько оно будет выполняться, можно получить точную информацию о том, что сделано, и обоснованную оценку времени, необходимого для завершения работы.
Резюме В этом разделе мы рассмотрели пакет DBMS_APPLICATION_INFO, о котором мало кто знает и использует его. Пакет можно и нужно использовать в любом приложении для регистрации в базе данных, что позволит администратору базы данных или пользователю, отвечающему за сервер, определить, какие приложения с ним работают. Очень важно использовать представление V$SESSION_LONGOPS в приложениях, выполняющихся дольше нескольких секунд. Только это позволит показать, что процесс не "висит", а выполняет необходимые действия. Oracle Enterprise Manager (OEM) и многие инструментальные средства независимых производителей обращаются к этому представлению и автоматически отображают информацию, которую приложения в нем устанавливают.
Пакет DBMS_JAVA Пакет DBMS_JAVA весьма загадочен. Это PL/SQL-пакет, но он не описан в справочном руководстве Supplied PL/SQL Packages Reference. Пакет создавался для поддержки Java на сервере, так что можно предположить, что он описан в справочном руководстве Supplied Java Packages Reference (но там о нем тоже ничего нет). Описан этот пакет в руководстве Oracle8i Java Developer's Guide. Я уже многократно упоминал и использовал его в примерах, не вникая в детали. Пришло время рассмотреть процедуры этого пакета и описать их назначение и особенности использования. В пакет DBMS_JAVA входит почти 60 процедур и функций, но лишь некоторые из них действительно полезны для разработчиков ПО. Основная часть пакета предназначена для отладчиков (точнее, для тех, кто создает программы-отладчики). Кроме того, пакет включает ряд служебных подпрограмм и утилит экспорта/импорта. Все эти функции и процедуры мы рассматривать не будем.
Функции LONGNAME и SHORTNAME Это служебные функции для преобразования коротких, 30-символьных идентификаторов (все идентификаторы Oracle Ч не длиннее 30 символов) в длинные имена Java, и наоборот. В словаре данных обычно находятся хешированные имена Java-классов, загруженных в базу данных. Причина в том, что их исходные имена Ч слишком длинные, а сервер такие имена не поддерживает. Эти две функции позволяют получить реальное имя по короткому (значению столбца OBJECT_NAME в представлении USER_OBJECTS), а также короткое имя для полного имени. Вот пример использования этих функций пользователем SYS (которому принадлежит много фрагментов Java-кода, если в базе данных установлена поддержка Java):
Пакет DBMS_JAVA sys@TKYTE816> column long_nm format a30 word_wrapped sys@TKYTE816> column short_nm format a30 sys@TKYTE816> select dbms_java.longname(object_name) long_run, 2 dbms_java.shortname(dbms_java.longname(object_name)) short_nm 3 from user_objects where object_type = 'JAVA>
Установка опций компилятора Для компилятора Java в базе данных можно задавать большинство опций, причем двумя способами. Опции можно задавать в командной строке при использовании процедуры loadjava или в таблице JAVA$OPTIONS. Опция, установленная в командной строке, всегда используется вместо соответствующего значения из таблицы JAVA$OPTIONS. Это, конечно же, происходит только при использовании компилятора Java в базе данных Oracle. Если используется отдельный компилятор Java вне базы данных (например, в среде JDeveloper), опции надо устанавливать в соответствующей среде. Можно устанавливать три опции компилятора, каждая из которых связана с прекомпилятором SQLJ (это Java-прекомпилятор, преобразующий встроенные операторы SQL в вызовы интерфейса JDBC), встроенным в базы данных. Эти опции представлены в следующей таблице. Опция ONLINE DEBUG Назначение Выполнять ли проверку типов при компиляции (online) или при выполнении Возможные значения True/False Компилировать ли Java-код с включенной True/False отладкой. Аналог вызова javac -g из командной строки Стандартно используется кодировка Latin ENCODING Кодировка исходного файла для компилятора Стандартные значения опций выделены полужирным.
Приложение А Я продемонстрирую использование средств пакета DBMS_JAVA для установки опций компилятора на примере опции online прекомпилятора SQLJ. Обычно эта опция имеет стандартное значение True, что требует от прекомпилятора SQLJ проверять семантику SQLJ-кода. Это означает, что прекомпилятор SQLJ обычно проверяет существование всех упомянутых в коде объектов базы данных, соответствие типов хост-переменных и т.п. Если необходимо делать эти проверки при выполнении (например, потому, что таблицы, на которые ссылается SQLJ-код, еще не созданы, но хотелось бы загрузить код в базу без ошибок), можно отключить проверку семантики с помощью процедуры DBMS_JAVA.SET_COMPILER_OPTION. Для иллюстрации используем следующий фрагмент кода. В нем выполняется попытка вставить данные в таблицу, которой в базе данных еще нет: tkyte@TKYTE816> create or replace and compile 2 java source named "bad_code" 3 as 4 import java.sql.SQLException;
5 6 public>
13 } 14 } 15 / Java created. tkyte@TKYTE816> show errors java source "bad_code" Errors for JAVA SOURCE bad_code: LINE/COL ERROR 0/0 0/ 0/ bad_code:7: Warning: Database issued an error: PLS-00201: identifier 'NON_EXISTENT_TABLE' must be declared insert into non_existent_table values ( 1 ) ^^^^^^^^^^^^^^^^^^^^^^^^^ 0/0 0/0 0/0 0/ #sql { Info: 1 warnings Теперь установим опции компилятора ONLINE значение FALSE. Для этого необходимо отключиться от сервера и подключиться снова. Проблема связана с тем, что среда времени выполнения языка Java проверяет существование таблицы JAVA$OPTIONS только при запуске. Если таблицы нет, повторных попыток прочитать из нее данные в сеансе не делается. Процедура DBMS_JAVA.SET_COMPILER_OPTION создаст эту таблицу автоматически, но только если она вызвана до запуска среды времени выполнения языка Java. Так что сеанс придется начинать заново.
Пакет DBMS_JAVA В следующем примере мы организуем новый сеанс и убедимся, что таблицы JAVA$OPTIONS нет. Мы установим опцию компилятора и увидим, что таблица автоматически создана. Наконец, мы создадим такую же Java-функцию, как в примере выше, и увидим, что теперь она компилируется без предупреждений благодаря установленной опции компилятора: tkyte@TKYTE816> disconnect Disconnected from Oracle8i Enterprise Edition Release 8.1.6.0.0 Ч -> Production With t h e P a r t i t i o n i n g option JServer Release 8.1.6.0.0 - Production tkyte@TKYTE816> connect t k y t e / t k y t e Connected. tkyte@TKYTE816> column value format al0 tkyte@TKYTE816> column what format al0 tkyte@TKYTE816> select * from java$options;
select * from java$options * ERROR at line 1: ORA-00942: table or view does not exist tkyte@TKYTE816> begin 2 dbms_java.set_compiler_option 3 (what => 'bad_code', 4 optionName => 'online', 5 value => 'false') ;
6 end;
7/ PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from java$options;
WHAT bad_code OPT online VALUE false tkyte@TKYTE816> create or replace and compile 2 java source named "bad_code" 3 as 4 import java.sql.SQLException;
5 6 public>
13 } 14 } 15 / Java created.
Приложение А tkyte@TKYTE816> show errors java source "bad_code" No errors.
В данном случае процедура SET_COMPILER_OPTION вызывается с тремя параметрами. Х WHAT. Шаблон, с которым надо сопоставлять код. Обычно Java-программы используют пакеты, поэтому полное имя будет иметь вид a.b.c.bad_code, а не просто bad_code. Если необходимо установить опцию для пакета a.b.c, это можно сделать. В результате для любого кода, имя которого соответствует шаблону a.b.c, будет использоваться соответствующая опция, если только нет более точной спецификации, тоже соответствующей данному пакету. Если опции заданы для значений WHAT, равных a.b.c и a.b.c.bad_code, будет использоваться опция для a.b.c.bad_code, поскольку она соответствует большей части имени. Х Х Х Х OPTIONNAME. Одно из трех значений: ONLINE, DEBUG или ENCODING. VALUE. Значение соответствующей опции. GET_COMPILER_OPTION. Эта функция возвращает значение указанной опции компилятора, даже если оно не отличается от стандартного. RESET_COMPILER_OPTION. Эта процедура удаляет из таблицы JAVA$OPTIONS строки, соответствующие шаблону WHAT и имеющие указанное значение в столбце OPTIONNAME.
С процедурой SET_COMPILER_OPTION связаны еще две подпрограммы.
Вот примеры использования обеих подпрограмм. Начнем с использования GET_COMPILER_OPTION, чтобы узнать значение опции online: tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> begin 2 dbms_output.put_line 3 (dbms_java.get_compiler_option(what => 'bad_code', 4 optionName => 'online'));
5 end;
6/ false PL/SQL procedure successfully completed. A затем сбросим ее с помощью процедуры RESET_COMPILER_OPTION: tkyte@TKYTE816> begin 2 dbms_java.reset_compiler_option(what 3 optionName 4 end;
5/ PL/SQL procedure successfully completed. Теперь убедимся, что функция GET_COMPILER_OPTION всегда возвращает значение указанной опции компилятора, даже если таблица JAVA$OPTIONS пуста (при вызове RESET_COMPILER_OPTION соответствующая строка была удалена): => 'bad_code', => 'online');
Пакет DBMS_JAVA tkyte@TKYTE816> begin 2 dbms_output.put_line 3 (dbms_java.get_compiler_option(what => 'bad_code', 4 optionName => ' o n l i n e ' ) ) ;
5 end;
6 true PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from java$options;
no rows selected / Процедура SET_OUTPUT Эта процедура аналогична команде SET SERVEROUTPUT ON в SQL*Plus. Точно так же, как выполнение этой команды включает вывод строк с помощью пакета DBMS_OUTPUT, вызов DBMS_JAVA.SET_OUTPUT обеспечивает вывод результатов функций System.out.println и System.err.print на экран в SQL*Plus. Если не выполнить вызовы: SQL> s e t serveroutput on s i z e 1000000 SQL> exec dbms_java.set_output(1000000) перед выполнением хранимой процедуры на языке Java из среды SQL*Plus, то все сообщения, выдаваемые с помощью вызовов System.out.println, будут записываться в трассировочный файл в каталоге на сервере, задаваемом параметром инициализации USER_DUMP_DEST. Эта процедура используется при отладке хранимых процедур на языке Java, поскольку позволяет помещать в код выдачу отладочной информации в виде вызовов System.out.println во многом аналогично вызовам DBMS_OUTPUT.PUT_LINE в PL/SQL-коде. В дальнейшем можно отключить выдачу отладочной информации в Javaкоде, перенаправив ее в "корзину". Так что, если вас интересовало, куда делись результаты вызовов System.out в хранимой процедуре на языке Java, вы теперь знаете ответ. Они выдаются в трассировочный файл. Теперь вы сможете перенаправить их на экран в среде SQL*Plus.
Процедуры loadjava и dropjava Эти процедуры обеспечивают интерфейс для языка PL/SQL, позволяющий выполнять функции утилит командной строки loadjava и dropjava. Как и следовало ожидать для служебных процедур, при их вызове не надо указывать опцию -u имя_пользователя/пароль или задавать тип используемого JDBC-драйвера Ч вы ведь уже подключились к базе данных. Процедуры загрузят Java-объекты в текущую схему. Эти процедуры имеют следующие прототипы: PROCEDURE loadjava(options varchar2) PROCEDURE loadjava(options varchar2, resolver varchar2) PROCEDURE dropjava(options varchar2) Приложение А Их можно использовать для загрузки файла activation8i.zip, который используется также в разделе приложения А, посвященном пакету UTL_SMTP. Более детальную информацию об интерфейсе JavaMail можно найти на странице products/javamail/index.html. Рассмотрим пример: s s T Y E 1 > exec dbms_java.loadjava('-r -v -f -noverify -synonym -g y@ K T 8 6 -> public c:\temp\activation8i.zip') initialization complete loading : com/sun/activation/registries/LineTokenizer creating : com/sun/activation/registries/LineTokenizer loading : com/sun/activation/registries/MailcapEntry creating : com/sun/activation/registries/MailcapEntry loading : com/sun/activation/registries/MailcapFile creating : com/sun/activation/registries/MailcapFile loading : com/sun/activation/registries/MailcapParseException creating : com/sun/activation/registries/MailcapParseException Процедуры управления правами Это весьма странные процедуры. Выполните команду DESCRIBE для пакета DBMS_JAVA в базе данных и поищите в результатах упоминание о процедуре GRANT_PERMISSION. Вы его не найдете, хотя точно известно, что такая процедура должна быть (я несколько раз демонстрировал ее использование). Она существует, как и ряд других функций, связанных с правами доступа. Я опишу использование подпрограмм GRANT_PERMISSION/REVOKE_PERMISSION. Подробное описание использования процедур управления правами и всех соответствующих опций можно найти в руководстве Oracle Java Developers Guide. Процедуры управления правами описаны в главе 5, Security for Oracle 8i Java Applications, этого руководства. В Oracle 8.1.5 точность установки привилегий для Java была очень низкой. Можно было указать только JAVAUSERPRIV или JAVASYSPRIV. Это напоминает ситуацию, когда в базе данных имеются только роли RESOURCE и DBA Ч обе они предоставляют пользователям слишком много возможностей. В версии Oracle 8.1.6 реализация Java в базе данных поддерживает классы защиты Java 2. Теперь имеется очень детальный набор привилегий, которые можно избирательно предоставлять и отбирать, аналогично набору привилегий в базе данных. Описание и обсуждение соответствующих классов привилегий можно найти на Web-странице security/Permission.html.
Итак, для установки привилегий будем использовать две процедуры Ч GRANT_PERMISSION и REVOKE_PERMISSION. Вопрос в том, как определить необходимые привилегии? Проще всего установить Java-код, выполнить его и узнать, чего ему не хватает для нормальной работы. Например, обратимся к разделу, посвященному пакету UTL_SMTP. В нем я создаю хранимую процедуру SEND для посылки сообщения по электронной почте. Я также демонстрирую там, какие две привилегии необходимо предоставить с помощью процедуры GRANT_PERMISSION, чтобы процедура SEND заработала. Необходимые привилегии я определил именно так Ч выполняю SEND и читаю сообщения об ошибках. Например:
Пакет DBMS_JAVA tkyte@TKYTE816> set serveroutput on size 1000000 tkyte@TKYTE816> exec dbms_java.set_output(1000000) PL/SQL procedure successfully completed. tkyte@TKYTE816> declare 2 ret_code number;
3 begin 4 ret_code := send( 5 p_from => 'me@here.com', 6 p_to => 'me@here.com', 7 p_cc => NULL, 8 p_bcc => NULL, 9 p_subject => 'Use the attached Zip file', 10 p_body => 'to send email with attachments....', 11 p_smtp_host=> 'aria.us.oracle.com', 12 p_attachment_data => null, 13 p_attachment_type => null, 14 p_attachment_file_name => null);
15 if ret_code = 1 then 16 dbms_output.put_line ('Сообщение послано успешно...');
17 else 18 dbms_output.put_line ('Послать сообщение не удалось...');
19 end if;
20 end;
21 / java.security.AccessControlException: the Permission (java.util.Property Permission * read,write) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE)) Все предельно ясно. В сообщениях говорится, что пользователю TKYTE необходима привилегия java.util.PropertyPermission с именем * и параметрами read и write. Вот откуда я узнал, что надо выполнить: sys@TKYTE816> begin 2 dbms_java.grant_permission( 3 grantee => 'TKYTE', 4 permission_type => 'java.util.PropertyPermission', 5 permission_name => '*', 6 permission_action => 'read,write' 7 );
После этого при попытке выполнения я получил следующее сообщение об ошибке: java.security.AccessControlException: the Permission (java.net.SocketPer mission aria.us.oracle.com resolve) has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(TKYTE|PolicyTableProxy(TKYTE)) Предоставив соответствующую привилегию, я узнал, что кроме RESOLVE необходима привилегия CONNECT. Вот почему я выполнил: 8 9 10 dbms_java.grant_permission( grantee => 'TKYTE', permission_type => 'java.net.SocketPermission', 11 12 13 14 Приложение А permission_name => '*', permission_action => 'connect,resolve' );
end;
/ И получил все необходимые соответствующей схеме привилегии. Обратите внимание, что в качестве значения permission_name я задал *, чтобы процедура могла разрешать имя любого хоста и подключаться к любому хосту, а не только к моему серверу SMTP. Процедура REVOKE_PERMISSION выполняет действие, обратное GRANT_PERMISSION Она работает именно так, как ожидалось. Если передать те же параметры, что были переданы процедуре GRANT_PERMISSION, она отберет соответствующую привилегию у текущей схемы.
Резюме В этом разделе мы рассмотрели использование средств пакета DBMS_JAVA для выполнения различных действий, необходимых для поддержки Java-кода. Мы начали с описания того, как сервер Oracle, ограничивающий длину имен 30 символами, обрабатывает очень длинные имена, используемые в языке Java. Для каждого длинного имени Java-сервер создает уникальный 30-символьный идентификатор с помощью хеширования. Пакет DBMS_JAVA предоставляет функцию преобразования короткого имени в соответствующее ему длинное имя и функцию преобразования длинного имени в короткое с помощью хеширования. Затем было рассмотрено использование средств пакета DBMS_JAVA для установки, получения и сброса опций компилятора Java. Мы разобрались, как таблица JAVA$OPTIONS используется для постоянного хранения стандартных опций компилятора и как вернуть этим опциям стандартные значения. Затем мы кратко рассмотрели процедуру SET_OUTPUT. Она перенаправляет вывод результатов вызовов System.out.println в Java-коде в сеанс SQL*Plus или SVRMGRL, аналогично тому, как команда SET SERVEROUTPUT ON обеспечивает выдачу результатов вызовов процедур PL/SQL-пакета DBMS_OUTPUT. Мы также рассмотрели альтернативный способ загрузки (с помощью вызова хранимой процедуры) исходного кода на языке Java, файлов классов и Java-архивов, ставший возможным благодаря пакету DBMS_JAVA в версиях Oracle8i Release 2 (8.1.6) и выше. Наконец, мы изучили процедуры управления правами доступа, предоставляемые этим пакетом в версиях, начиная с Oracle8i Release 2. Эти процедуры позволяют гибко управлять привилегиями для Java-кода, устанавливая, что они могут и чего не могут делать. Если вы используйте язык Java в базе данных Oracle, то постоянно будете применять эти процедуры при программировании.
Пакет DBMS_JOB Пакет DBMS_JOB позволяет запланировать однократное или регулярное выполнение заданий в базе данных. Задание представляет собой хранимую процедуру, анонимный блок PL/SQL или внешнюю процедуру на языке С или Java. Эти задания выполняются серверными процессами в фоновом режиме. Задания можно выполнять регулярно (в 2 часа ночи каждые сутки) или однократно (выполнить задание сразу после фиксации транзакции и удалить его из очереди). Если вы знакомы с утилитами cron или at в ОС UNIX или Windows, то особенности использования пакета DBMS_JOB вам уже понятны. Задания выполняются в той же среде (пользователь, набор символов и т.п.), из которой они посланы на выполнение (но роли не действуют). Задания выполняются аналогично процедурам с правами создателя, т.е. роли для них не учитываются. Это можно продемонстрировать на следующем примере (процедуры, использованные в этом примере, позже будут рассмотрены подробно): tkyte@TKYTE816> create table t (msg varchar2(20), cnt int);
Table created. tkyte@TKYTE816> insert into t select 'from SQL*PLUS', count(*) from session_roles;
1 row created. tkyte@TKYTE816> variable n number tkyte@TKYTE816> exec dbms_job.submit(:n,'insert into t select ''from job'', count(*) from session_roles;
');
PL/SQL procedure successfully completed. tkyte@TKYTE816> print n Приложение А N 81 tkyte@TKYTE816> exec dbms_job.run(:n);
PL/SQL procedure successfully completed tkyte@TKYTE816> select * from t;
MSG from SQL*PLUS from job CNT 10 Как видите, в среде SQL*Plus имеется 10 действующих ролей, а в среде выполнения задания Ч ни одной. Поскольку пользователи в качестве задания обычно вызывают хранимую процедуру, это не имеет значения, потому что при выполнении хранимой процедуры роли все равно не учитываются. Проблема возникает только при попытке выполнить процедуру, доступную через роль. Такая процедура не сработает, поскольку в заданиях роли не действуют. Часто пользователи спрашивают, как лучше всего скрыть имя пользователя/пароль, связанные с пакетным заданием (например, для периодического анализа таблиц), посылаемым с помощью утилиты cron или ее аналогов в среде Windows NT/2000. Их беспокоит, что пароль хранится в файле (и это правильно) или явно представлен в результатах выполнения команды ps в среде UNIX и т.п. Я всегда рекомендую вообще не использовать средства ОС для планирования заданий в базе данных, а вместо этого написать хранимую процедуру, выполняющую необходимые действия, и запланировать ее выполнение с помощью средств пакета DBMS_JOB. При этом ни имя пользователя, ни пароль нигде не хранится, и задание выполнится только в том случае, когда доступна база данных. Если сервер базы данных не будет работать в соответствующий момент, задание, естественно, не выполнится, поскольку именно сервер базы данных и отвечает за выполнение задания. Часто спрашивают также: как ускорить выполнение? Необходимо выполнить продолжительное действие, а пользователь не хочет ждать. Причем иногда ускорить выполнение действия нельзя. Например, я уже многие годы посылаю сообщения электронной почты с сервера базы данных. Я использовал для этого различные механизмы: каналы базы данных, пакет UTL_HTTP, внешние процедуры и средства языка Java. Все они работали примерно с одинаковой скоростью, но всегда Ч медленно. Иногда завершения работы по протоколу SMTP приходится ждать достаточно долго. Слишком долго в случае моего приложения, для которого ожидания более четверти секунды вообще недопустимы. Посылка сообщения по протоколу SMTP может иногда потребовать 2-3 секунды. Ускорить этот процесс нельзя, но можно создать видимость его более быстрого выполнения. Вместо отправки сообщения при нажатии пользователем соответствующей кнопки в приложении должно посыпаться на выполнение задание, которое будет отправлять сообщение сразу после фиксации транзакции. При этом возникает два побочных эффекта. Во-первых, действие выполняется как бы быстрее, а во-вторых, отправка сообщения становится частью транзакции. Одно из свойств пакета DBMS_JOB состоит в том, что задание попадает в очередь только после фиксации транзакции. При откате транзакции задание удаляется из очереди и не выполняется. С помощью пакета Пакет DBMS_JOB DBMS_JOB мы не только создаем видимость более быстрой работы приложения, но и делаем его более надежным. Больше не будет посылаться уведомление по электронной почте из триггера при изменении строки, если это изменение затем было отменено. Либо строка изменяется, и отправляется сообщение;
либо строка не изменяется, и сообщение не отправляется. Так что пакет DBMS_JOB имеет широкую сферу применения. Он может включить выполнение действий, выходящих "за рамки" транзакций (таких как отправка сообщений по электронной почте или создание таблицы при вставке строки в другую таблицу) в транзакции. Он позволяет создать видимость более быстрого выполнения действий, особенно, если продолжительные действия не должны выдавать пользователю результатов. Пакет позволяет планировать и автоматизировать многие задачи, для решения которых обычно создавались сценарии вне базы данных. Это, безусловно, очень полезный пакет. Для корректной работы пакета DBMS_JOB необходимо выполнить небольшую настройку сервера. Надо установить два параметра инициализации. Х job_queue_interval. Задает периодичность (в секундах) проверки очередей и поиска заданий, готовых к выполнению. Если задание должно выполняться раз в 30 секунд, но параметр job_queue_interval имеет (стандартное) значение 60, это задание не будет выполняться раз в 30 секунд Ч в лучшем случае, раз в 60 секунд. Х job_queue_processes. Задает количество фоновых процессов для выполнения заданий. Значением может быть целое число от 0 (по умолчанию) до 36. Это значение можно менять без перезапуска сервера с помощью оператора ALTER SYSTEM SET JOB_QUEUE_PROCESSES=
(включая точку с запятой). Значение параметра WHAT будет помещено в следующий PL/SQL-блок: DECLARE job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN WHAT :mydate := n e x t _ d a t e ;
IF b r o k e n THEN :b := 1;
ELSE :b := 0;
END I F ;
END;
Вот почему любой оператор надо завершать точкой с запятой (;
). Чтобы WHAT можно было заменить соответствующим кодом, точка с запятой необходима. Х NEXT_DATE. Время следующего (а поскольку мы только посылаем задание Ч первого) выполнения задания. Стандартное значение Ч SYSDATE Ч означает "выполнять немедленно" (после фиксации транзакции). Х INTERVAL. Строка, содержащая функцию, вычисляющую время следующего выполнения задания. Можно считать, что значение этой функции выбирается с помощью оператора 'select... from dual'. Если передать строку sysdate+l, сервер выполнит SELECT sysdate+l INTO :NEXT_DATE FROM DUAL. Ниже описаны особенности задания интервала выполнения задания, предотвращающие его смещение. NO_PARSE. Указывает, анализировался ли параметр WHAT при отправке задания. Анализируя строку, можно определить, выполнимо ли вообще задание. В общем случае параметру NO_PARSE надо всегда оставлять стандартное значение, False. При установке значения True параметр WHAT принимается "как есть", без проверки допустимости.
Х Х INSTANCE. Этот параметр имеет значение только в режиме Parallel Server, когда сервер Oracle работает на кластере слабо связанных машин. Он задает экземпляр, на котором будет выполняться задание. По умолчанию он имеет значение ANY_INSTANCE.
Пакет DBMS_JOB Х FORCE. Этот параметр также имеет значение только в режиме Parallel Server. При установке значения True (принятого по умолчанию) можно посылать задание с любым идентификатором экземпляра, даже если при отправке соответствующий экземпляр недоступен. При установке значения False отправка задания завершится неудачно, если указанный экземпляр недоступен. В пакете DBMS_JOB есть и другие подпрограммы. Задание посылается на выполнение с помощью процедуры SUBMIT, a остальные подпрограммы позволяют манипулировать заданиями в очередях и выполнять действия по их запуску (RUN), удалению из очереди (REMOVE) и изменению (CHANGE). Ниже описаны наиболее часто используемые подпрограммы, включая входные данные и назначение.
Подпрограмма Входные данные Описание REMOVE номер задания Удаляет задание из очереди. Учтите, что если задание выполняется, удаление не остановит его выполнение. Удаление из очереди гарантирует, что задание не будет выполнено снова, но уже выполняющееся задание при этом не останавливается. Для остановки выполняющегося задания необходимо прекращать работу соответствующего сеанса с помощью оператора ALTER SYSTEM. Эта процедура работает как оператор UPDATE для представления JOBS. Она позволяет изменить любой параметр задания. Позволяет "разрушить" или "восстановить" задание. Разрушенное задание не выполняется. Задание, не выполнившееся успешно 16 раз подряд, автоматически помечается как разрушенное, и сервер Oracle больше не будет его выполнять. Выполняет задание немедленно, в приоритетном режиме (в пользовательском сеансе). Полезно при отладке не срабатывающих заданий.
CHANGE номер задания WHAT, NEXT_DATE, INTERVAL, INSTANCE, FORCE номер задания BROKEN (булево значение) NEXT_DATE BROKEN RUN номер задания Теперь, когда вы достаточно хорошо представляете устройство пакета DBMS_JOB и его возможности, рассмотрим, как выполнить задание однократно, как организовать его периодическое выполнение, как контролировать выполнение заданий и определять возникающие при этом ошибки.
Однократное выполнение задания Многие из заданий в моей базе данных Ч однократные. Я часто использую пакет DBMS_JOB как аналог запуска процесса в фоновом режиме с помощью & в ОС UNIX или команды start в среде Windows. Представленный ранее пример с отправкой сооб Приложение А щения по электронной почте относится как раз к этой категории. Я использую пакет DBMS_JOB не только для включения отправки сообщения в транзакцию, но и для того, чтобы ускорить выполнение этого действия. Вот одна из возможных реализаций этого действия, демонстрирующая однократное выполнение задания. Начну с небольшой хранимой процедуры для отправки сообщений по электронной почте с использованием средств стандартного пакета UTL_SMTP: tkyte@TKYTE816> create or replace 2 PROCEDURE send_mail (p_sender IN VARCHAR2, 3 p_recipient IN VARCHAR2, 4 p_message IN VARCHAR2) 5 as 6 Ч Учтите, что надо указать хост, 7 Ч поддерживающий протокол SMTP и доступный для вас. 8 Ч К указанному хосту вы не получите доступа, поэтому его надо -> изменить 9 l_mailhost VARCHAR2(255) := 'aria.us.oracle.com';
10 l_mail_conn utl_smtp.connection;
11 BEGIN 12 l_mail_conn :=utl_smtp.open_connection(l_mailhost, 25);
13 utl_smtp.helo(l_mail_conn, l_mailhost);
14 utl_smtp.mail(l_mail_conn, p_sender);
15 utl_smtp.rcpt(l_mail_conn, p_recipient);
16 utl_smtp.open_data(l_mail_conn) ;
17 utl_smtp.write_data(l_mail_conn, p_message);
18 utl_smtp.close_data(l_mail_conn);
19 utl_smtp.quit(l_mail_conn) ;
20 end;
21 / Procedure created. Теперь, чтобы определить продолжительность работы, я выполню эту процедуру дважды: tkyte@TKYTE816> set serveroutput on tkyte@TKYTE816> declare 2 l_start number := dbms_utility.get_time;
3 begin 4 send_mail('anyone@outthere.com', 5 'anyone@outthere.com', 'Привет!');
6 dbms_output.put_line 7 (round((dbms_utility.get_time-l_start)/100, 2) || 8 ' seconds');
9 end;
10 /.81 seconds PL/SQL procedure successfully completed. tkyte@TKYTE816> /.79 seconds PL/SQL procedure successfully completed.
Пакет DBMS_JOB Похоже, она выполняется примерно восемь десятых секунды, в лучшем случае. Для меня это слишком долго. Можно ускорить выполнение, точнее, создать видимость этого. Я использую задания для видимости ускорения работы и получаю при этом преимущества отправки сообщений в рамках транзакции. Начнем с создания таблицы для хранения сообщений и процедуры, которая сможет посылать находящиеся в ней сообщения. Эта процедура и будет выполняться как фоновое задание. Вопрос в том, зачем для хранения сообщений используется таблица? Почему просто не передать текст сообщения как параметр задания? Причина в использовании связываемых переменных и разделяемого пула. Поскольку все задания будут создаваться с параметром WHAT, a сервер будет просто выполнять эту строку, надо помнить, что значение параметра WHAT окажется в разделяемом пуле. Можно посылать задания и так: dbms_job.submit(x, 'send_mail(''someone@there.com'', ''someone0there.com'', ''Привет!'');
');
но в результате в разделяемом пуле окажутся сотни тысяч уникальных операторов, что отрицательно скажется на производительности сервера. Поскольку предполагается рассылка большого количества сообщений (больше одного Ч уже много, и использовать связываемые переменные при этом обязательно), надо иметь возможность посылать задания вида: dbms_job.submit(x, 'background_send_mail(константа);
');
Оказывается, этого очень легко добиться. Достаточно создать таблицу с полями для каждого передаваемого параметра при отправке задания на выполнение (в данном случае, отправитель, адресат и само сообщение) и первичным ключом. Например: tkyte@TKYTE816> 2 3 4 5 Table created. Я добавил в таблицу первичный ключ Ч столбец ID, и дату отправки сообщения в столбце senton. Мы будем использовать эту таблицу не только для организации очереди исходящих сообщений, но и как постоянный журнал, в котором будут регистрироваться все отправленные сообщения (пригодится, поверьте мне, когда кто-то скажет: "А меня не предупреждали..."). Осталось только придумать, как генерировать значение ключа для таблицы и передавать его фоновому процессу в виде строковой константы. К счастью, пакет DBMS_JOB содержит все необходимое для решения этой проблемы. При отправке задания на выполнение пакет автоматически создает для него уникальный идентификатор и возвращает его вызывающему. Поскольку блок кода, в который помещается переданное значение параметра WHAT, содержит идентификатор задания, мы можем его передавать. Процедура FAST_SEND MAIL будет выглядеть так: tkyte@TKYTE816> c r e a t e or replace 2 PROCEDURE fast_send_mail (p_sender IN VARCHAR2, 3 p_recipient IN VARCHAR2, c r e a t e t a b l e send_mail_data(id number primary key, sender varchar2(255), recipient varchar2(255), message varchar2(4000), senton date default NULL);
Приложение А 4 p_message IN VARCHAR2) 5 as 6 l_job number;
7 begin 8 dbms_job.submit(l_job, 'background_send_mail(JOB);
');
9 insert into send_mail_data 10 (id, sender, recipient, message) 11 values 12 (l_job, p_sender, p_recipient, p_message);
13 end;
14 / Procedure created. Эта процедура будет посылать на выполнение задание BACKGROUND_SEND_MAIL и передавать ему параметр JOB. Если обратиться к описанию параметра WHAT, вы увидите, что соответствующий блок кода включает три локальных переменных, к которым можно обращаться, Ч мы и передаем процедуре одну из них. Сразу после этого мы вставляем сообщение в таблицу очереди для последующей отправки. Итак, пакет DBMS_JOB создает первичный ключ, а затем мы вставляем этот первичный ключ и соответствующие данные в таблицу. Вот и все. Теперь необходимо создать несложную процедуру BACKGROUND_SEND_MAIL: tkyte@TKYTE816> create or replace 2 procedure background_send_mail(p_job in number) 3 as 4 l_rec send_mail_data%rowtype;
5 begin 6 select * into l_rec 7 from send_mail_data 8 where id = p_job;
9 10 send_mail(l_rec.sender, l_rec.recipient, l_rec.message);
11 update send_mail_data set senton = sysdate where id = p_job;
12 end;
13 / Procedure created. Она читает сохраненные данные, вызывает медленно работающую процедуру SEND_MAIL, а затем изменяет соответствующую запись, отражая в ней факт отправки сообщения по электронной почте. Теперь можно выполнить процедуру FAST_SEND_MAIL и определить, насколько быстро она выполняется: tkyte@TKYTE816> declare 2 l_start number := dbms_utility.get_time;
3 begin 4 fast_send_mai1('panda@panda.com', 5 'snake@snake.com', 'Привет!');
6 dbms_output.put_line 7 (round((dbms_utility.get_time-l_start)/100, 2) || 8 ' seconds');
9 end;
Пакет DBMS_JOB 10 /.03 seconds PL/SQL procedure successfully completed. tkyte@TKYTE816> /.02 seconds PL/SQL procedure successfully completed. С точки зрения пользователя процедура FAST_SEND_MAIL работает в 26-40 раз быстрее, чем исходная. На самом же деле она работает не быстрее, но создает видимость быстрого выполнения (именно это и имеет значение). Фактическая отправка сообщения будет выполнена в фоновом режиме после фиксации транзакции. Об этом важно помнить. При выполнении этого примера не забудьте выполнить COMMIT, иначе сообщение никогда не будет послано. Задание не будет доступно в очереди для соответствующих процессов, пока транзакция не будет зафиксирована (в сеансе можно будет увидеть задание в представлении USER_JOBS, но процессы обработки очередей его не увидят, пока не будет зафиксирована транзакция). Не считайте это ограничением. На самом деле это полезное свойство, с помощью которого мы только что включили отправку сообщений по электронной почте в транзакцию. Если откатить транзакцию, сообщение не будет послано. После фиксации транзакции оно будет отправлено.
Текущие задания Еще одно стандартное применение пакета DBMS_JOB Ч для организации периодического выполнения заданий в базе данных. Как уже упоминалось, многие пользователи пытаются применять для выполнения заданий в базе данных утилиты ОС cron или at, но сталкиваются при этом с проблемами защиты пароля и т.п. Я всегда предлагаю использовать очереди заданий. Они не только избавляют от необходимости хранения регистрационной информации, но и гарантируют выполнение заданий только в случае работоспособности и доступности сервера базы данных. В случае сбоя сервер будет повторно пытаться выполнить задание. Например, если при первой попытке выполнения задания удаленная база данных недоступна, задание возвращается в очередь и делается,попытка выполнить его снова. Сервер делает это 16 раз, с каждым разом немного увеличивая время ожидания, прежде чем пометит задание как "разрушенное". Подробнее об этом мы поговорим в подразделе "Контроль заданий и поиск ошибок". Утилиты cron и at автоматически этого не сделают. Кроме того, поскольку задания выполняются в базе данных, с помощью запросов можно определить их статус: когда последний раз выполнялось задание и выполнялось ли вообще, и т.п. Вся информация находится в одном месте. Другие средства сервера Oracle, такие как репликация и материализованные представления, неявно используют очереди заданий при реализации своих функциональных возможностей. Изменения моментальных снимков и обновления материализованных представлений выполняются с помощью заданий, вызывающих соответствующие хранимые процедуры. Предположим, необходимо ежесуточно в 3 часа ночи выполнять анализ всех таблиц в определенной схеме. Для этого можно использовать следующую хранимую процедуру:
Приложение А scott@TKYTE816> create or replace procedure analyze_my_tables 2 as 3 begin 4 for x in (select table_name from user_tables) 5 loop 6 execute immediate 7 'analyze table ' || x.table_name || ' compute statistics';
8 end loop;
9 end;
10 / Procedure created. Чтобы запланировать ее выполнение сегодня ночью в 3 часа (точнее, завтра утром), а затем ежесуточно в 3 часа ночи, можно использовать следующий вызов: scott@TKYTE816> declare 2 l_job number;
3 begin 4 dbms_job.submit(job => 5 what => 6 next_date => 7 interval => 8 end;
9/ l_job, 'analyze_my_tables;
', trunc(sysdate)+l+3/24, 'trunc(sysdate)+l+3/24');
PL/SQL procedure successfully completed. scott@TKYTE816> select job, to_char(sysdate,'dd-mon'), 2 to_char(next_date,'dd-mon-yyyy hh24:mi:ss'), 3 interval, what 4 from user_jobs 5/ JOB TO_CHA TO_CHAR(NEXT_DATE,'D INTERVAL WHAT 33 09-jan 10-jan-2001 03:00:00 trunc(sysdate)+l+3/24 analyze_my_tables;
Итак, в следующий раз это задание выполнится в 3 часа ночи 10 января. Для этого мы передали реальную дату, а не строку, как для параметра interval. Мы использовали функций для работы с датами, так что при выполнении, независимо от времени вызова, всегда будет возвращаться 3 часа следующего утра. Это важно. Точно такую же функцию, но в виде строки мы передали в качестве значения параметра INTERVAL. Используется функция, всегда возвращающая 3 утра завтрашнего дня, независимо от времени ее выполнения. Это предотвращает смещение заданий (jobs sliding). Может показаться, что, поскольку первый раз задание выполняется в 3 часа утра, можно задать интервал sysdate+l. Если выполнить это вычисление в 3 утра во вторник, в результате мы получим 3 утра среды. Получим, если задание гарантированно выполнится в указанное время, но это вовсе не обязательно. Задания в очереди обрабатываются последовательно, в соответствии с указанным временем выполнения. При наличии одного процесса обработки очереди сообщений и двух заданий, назначенных на 3 утра, очевидно, что одно из них не выполнится точно в 3 утра. Придется подождать, пока завершится выполнение первого задания. Даже при отсутствии заданий, назначенных на то же вре Пакет DBMS_JOB мя, очереди заданий просматриваются периодически, например каждые 60 секунд. Задание, назначенное на выполнение в 3 утра, может быть выбрано из очереди в 3:00:45 утра. Если использовать функцию sysdate+l, в следующий раз задание может быть поставлено на выполнение в 3:00:46 утра. На следующий день в 3:00:45 утра задание еще не будет готово для выполнения и выполнится при следующем просмотре очереди, в 3:01:45 утра. Время выполнения задания медленно сдвигается. Однако это не самое худшее. Предположим, на следующий день в 3 утра с таблицами будут работать и проанализировать их не удастся. Хранимая процедура не сработает и будет возвращена в очередь для выполнения. Теперь это задание окажется смещенным на несколько минут позже 3 утра. Поэтому, чтобы предотвратить смещение времени выполнения заданий, необходимо использовать функцию, возвращающую фиксированный момент времени, если выполнение в конкретный момент времени является существенным. Если важно, чтобы задание выполнялось именно в 3 утра, надо использовать функцию, всегда возвращающую время 3 утра, независимо от времени ее выполнения. Многие из таких "не смещающихся" функций реализовать очень легко, другие Ч намного сложнее. Например, однажды меня попросили создать задание, собирающее статистическую информацию с помощью STATSPACK c понедельника по пятницу ровно в 7 часов утра и 3 часа дня. Значение параметра INTERVAL для этого задания задать непросто, но давайте рассмотрим для начала псевдокод: if время - до 15:00 then вернуть 15:00 СЕГОДНЯ (другими словами, если мы выполняем это в 7 утра, надо выполнить задание сегодня в 3 часа дня) else вернуть 7 утра через 3 дня (если сегодня пятница) либо 1 день (в противном случае) end if Осталось реализовать эту логику в виде симпатичной функции DECODE или, если для вас это слишком сложно, в виде PL/SQL-функции. Я использовал интервал: decode(sign(15-to_char(sysdate,'hh24')), 1, trunc(sysdate)+15/24, trunc(sysdate+decode(to_char(sysdate,'d'), 6, 3, l))+7/24) Функция decode начинается с вычисления значения SIGN(15-TO_CHAR(SYSDATE,'HH24')). SIGN Ч это функция, возвращающая -1, 0 или 1, если переданное ей выражение имеет, соответственно, отрицательное, нулевое и положительное значение. Если значение было положительным, предполагается, что вызов произошел до 3 часов дня (до 15 часов), поэтому в следующий раз надо будет выполнить задание в TRUNC(SYSDATE)+15/24 (сегодня в 15 часов). С другой стороны, если sign возвращает 0 или - 1, надо вернуть значение TRUNC(SYSDATE + DECODE(TO_CHAR(SYSDATE,'D'), 6, 3, l))+7/24. Здесь с помощью функции DECODE мы определяем день недели, чтобы узнать, сколько добавлять дней Ч три (в пятницу, чтобы вернуть понедельник) или один (в остальные рабочие дни). Полученное количество дней мы добавляем к значению SYSDATE, усекаем время до полуночи и добавляем 7 часов.
Приложение А Бывает, что смещение даты вполне допустимо и даже желательно. Например, если необходимо собирать статистическую информацию из представлений V$ каждые 30 минут в процессе работы сервера, вполне допустимо использовать интервал SYSDATE+l/24/2, добавляющий к текущему моменту времени полчаса.
Нетривиальное планирование Бывает, как в рассмотренном выше примере, что значение NEXT_DATE вычислить одним оператором SQL сложно или время следующего выполнения задания определяется сложным алгоритмом. В этом случае можно определить время следующего выполнения в самом задании. В начале раздела было сказано, что задание выполняется в следующем PL/SQL-блоке: DECLARE job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN WHAT :mydate := next_date;
IF broken THEN :b := 1;
ELSE :b := 0;
END IF;
END;
Вы уже видели (в подразделе "Однократное выполнение задания"), как использовать доступное в блоке значение JOB. Его можно использовать как первичный ключ для таблицы параметров, чтобы в максимальной степени обеспечить совместное использование SQL-операторов сеансами. Можно воспользоваться и значением переменной NEXT_DATE. Как демонстрирует представленный выше блок кода, сервер Oracle использует для установки значения переменной NEXT_DATE связываемую переменную :mydate в качестве входного параметра процедуры, но он также получает ее значение после выполнения WHAT (заданной процедуры). Если процедура изменит значение переменной NEXT_DATE, сервер Oracle будет использовать его в качестве времени следующего выполнения задания. Для иллюстрации этого приема создадим небольшую процедуру P, которая будет выдавать сообщение в таблицу T и устанавливать значение NEXT_DATE: tkyte@TKYTE816> create table t (msg varchar2(80));
Table created. tkyte@TKYTE816> create or replace 2 procedure p(p_job in number, p_next_date in OUT date) 3 as 4 l_next_date date default p_next_date;
5 begin 6 p_next_date := trunc(sysdate)+l+3/24;
7 8 insert into t values 9 ('Next date имела значение "' || 10 to_char(l_next_date,'dd-mon-yyyyhh24:mi:ss') || Пакет DBMS_JOB 11 '" Next date ИМЕЕТ значение ' || 12 to_char(p_next_date,'dd-mon-yyyy hh24:mi:ss'));
13 end;
14 / Procedure created. Теперь пошлем эту процедуру на выполнение, используя метод, рассмотренный в подразделе "Однократное выполнение задания". Другими словами, не зададим значение параметра INTERVAL: tkyte@TKYTE816> variable n number tkyte@TKYTE816> exec dbms_job.submit(:n, 'p(JOB,NEXT_DATE);
');
PL/SQL procedure successfully completed. tkyte@TKYTE816> select what, interval, 2 to_char(last_date,'dd-mon-yyyyhh24:mi:ss') last_date, 3 to_char(next_date,'dd-mon-yyyyhh24:mi:ss') next_date 4 from user_jobs 5 where job = :n 6/ WHAT p(JOB,NEXT_DATE);
18:23:01 INTERVAL LAST_DATE null NEXT_DATE 28-apr- В данном случае процедуре переданы только параметры JOB и NEXT_DATE. Их значения будут получены при выполнении. Как видите, это задание еще не выполнялось (столбец LAST_DATE имеет значение Null), a параметр INTERVAL получил пустое значение, так что значение NEXT_DATE будет вычисляться как SELECT NULL FROM DUAL. Обычно это означает, что задание выполнится один раз, после чего будет удалено из очереди заданий. Однако при выполнении этого задания оказывается: tkyte@TKYTE816> exec dbms_job.run(:n);
PL/SQL procedure successfully completed. tkyte@TKYTE816> select * from t;
MSG Next date имела значение "" Next date ИМЕЕТ значение 29-apr-2001 03:00:00 tkyte@TKYTE816> select what, interval, 2 to_char(last_date,'dd-non-yyyyhh24:mi:ss') last_date, 3 to_char(next_date,'dd-mon-yyyyhh24:mi:ss') next_date 4 from user_jobs 5 where job = :n 6/ WHAT p(JOB,NEXT_DATE);
INTERVAL LAST_DATE null NEXT_DATE 28-apr-2001 18:23:01 29-apr-2001 03:00: что параметр NEXT_DATE получает другое значение. Это значение NEXT_DATE было вычислено в самой процедуре, и задание снова оказалось в очереди. Пока задание бу Приложение А дет устанавливать непустое значение NEXT_DATE, оно будет оставаться в очереди. Если когда-нибудь после успешного выполнения не будет установлено значение NEXT_DATE, задание будет удалено из очереди. Этот прием пригодится для заданий, в которых значение NEXT_DATE сложно вычислить или оно зависит от данных в других таблицах.
Контроль заданий и обнаружение ошибок Для контроля заданий в базе данных используется три основных представления. Х USER_JOBS. Список всех заданий, посланных текущим зарегистрированным пользователем. У этого представления есть также общедоступный синоним, ALL_JOBS. ALL_JOBS содержит ту же информацию, что и USER_JOBS. Х DBA_JOBS. Полный список всех заданий, находящихся в очередях базы данных. Х DBA_JOBS_RUNNING. Список выполняющихся заданий. Обычно представление USER_JOBS доступно всем пользователям, а представления DBA_* Ч только пользователям с привилегией DBA или получившим привилегию SELECT непосредственно для этих представлений. В этих представлениях находится следующая информация. Х LAST_DATE/LAST_SEC. Когда последний раз выполнялось задание. LAST_DATE столбец типа DATE. LAST_SEC Ч строка символов, содержащая только время (в формате часов:минут:секунд).
Х THIS_DATE/HIS_SEC. Если задание в настоящий момент выполняется, в этом столбце будет время начала выполнения. Как и пара столбцов LAST_DATE/ LAST_SEC, столбец THIS_DATE содержит дату и время, а столбец THIS_SEC символьную строку, в которой указано только время. Х NEXT_DATE/NEXT_SEC. Время, когда задание будет выполнено в следующий раз.
Х TOTAL_TIME. Общее время выполнения задания в секундах. Включает время выполнения предыдущих прогонов Ч это суммарное значение. Х BROKEN. Флаг Yes/No, показывающий, что задание разрушено. Разрушенные задания не выполняются процессами обработчик очередей. Задание разрушается после 16 неудачных попыток выполнения. Для разрушения задания можно использовать процедуру DBMS_JOB.BROKEN (что временно предотвращает его выполнение). Х INTERVAL. Функция, возвращающая дату, которая вызывается в начале следующего выполнения задания, чтобы определить, когда снова выполнять задание. Х FAILURES. Сколько раз подряд задание не было успешно выполнено. При успешном выполнении задания в этом столбце устанавливается значение 0. Х WHAT. Текст задания. Х NLS_ENV. Среда NLS (National Language Support Ч поддержка национальных языков), в которой будет выполняться задание. Включает язык, формат даты, фор Пакет DBMS_JOB мат чисел и т.п. Среда NLS полностью наследуется из среды, откуда было послано задание. При изменении этой среды и повторной отправке задания оно будет выполняться в измененной среде. Х INSTANCE. Имеет смысл только в режиме Parallel Server. Это идентификатор экземпляра, на котором может выполняться задание, а в представлении DBA_JOBS_RUNNING Ч экземпляра, на котором оно выполняется. Предположим, в этих представлениях обнаружено задание с положительным значением в столбце FAILURES. Где найти сообщения об ошибках для этого задания? Они не хранятся в базе данных, а записываются в журнал уведомлений (alert log) базы данных. Например, создана следующая процедура: tkyte@TKYTE816> create or replace procedure run_by_jobs 2 as 3 l_cnt number;
4 begin 5 select user_id into l_cnt from all_users;
6 Ч другой необходимый код 7 end;
8/ Procedure created. tkyte@TKYTE816> variable n number tkyte@TKYTE816> exec dbms_job.submit(:n, 'run_by_jobs;
');
PL/SQL procedure successfully completed. tkyte@TKYTE816> commit;
Commit complete. tkyte@TKYTE816> exec dbms_lock.sleep(60);
PL/SQL procedure successfully completed. tkyte@TKYTE816> select job, what, failures 2 from user_jobs 3 where job = :n;
JOB WHAT FAILURES 35 run_by_jobs;
Если в базе данных больше одного пользователя (во всех базах данных их больше), эта процедура, определенно, не сработает. Оператор SELECT... INTO всегда будет возвращать слишком много строк;
при программировании была допущена ошибка. Однако, поскольку она происходит в фоновом режиме, причину ошибки трудно определить. К счастью, сообщение об ошибке записывается в журнал уведомлений базы данных. Если открыть этот файл в редакторе, в конце файла можно будет найти следующее: Tue Jan 09 13:07:51 2001 Errors in file C:\oracle\admin\tkyte816\bdump\tkyte816SNP0.TRC: ORA-12012: error on auto execute of job 35 ORA-01422: exact fetch returns more than requested number of rows Приложение А ORA-06512: at "SCOTT.RUN_BY_JOBS", line 5 ORA-06512: at line 1 Сообщение свидетельствует о том, что задание 35 (наше задание) выполнить не удалось. Что важнее, в сообщении указана причина неудачи. Такие же сообщения об ошибках выдаются при попытке выполнения процедуры в среде SQL*Plus. Эта информация принципиально важна для определения причины сбоя задания. На ее основе можно, изменив задание, обеспечить его корректную работу. Вот, пожалуй, и все о контроле выполнения заданий. Надо либо следить за журналом уведомлений, alert.log (это администратор базы данных должен делать всегда), либо периодически просматривать представление DBA_JOBS, чтобы убедиться в успешном выполнении заданий.
Резюме Пакет DBMS_JOB Ч замечательное средство сервера, обеспечивающее выполнение действий в фоновом режиме. Его можно использовать для автоматизации рутинных операций, таких как анализ таблиц, архивирование и очистка временных таблиц, да и любых других. Его можно использовать в приложениях для обеспечения сравнительно быстрого выполнения продолжительных действий (причем, выполнение это будет действительно быстрым с точки зрения пользователя). Пакет позволяет не писать для каждой ОС специфические сценарии, периодически выполняющие действия в базе данных. Более того, он избавляет от необходимости "зашивать" имена пользователей и пароли в сценарии для регистрации на сервере. Задание всегда выполняется от имени пользователя, который его послал, Ч регистрироваться вообще не нужно. Наконец, в отличие от средств периодического выполнения заданий операционных систем, эти задания сервера базы данных выполняются только в случае доступности сервера. Если система не работала в момент запланированного выполнения задания, оно не будет выполнено (очевидно, если сервер не работает, то и очереди заданий не просматриваются). Пакет DBMS_JOB Ч надежное средство, для которого я нашел множество применений.
Пакет DBMS_LOB DBMS_LOB Ч это стандартный пакет для работы с большими объектами (Large OBjects Ч LOBs) в базе данных. Большими объектами называют данные новых типов, появившиеся начиная с версии Oracle 8. Большие объекты поддерживают хранение и обработку до 4 Гбайт данных в одном столбце. Они заменяют считающиеся теперь ненужными типы данных LONG и LONG RAW. Использование типов данных LONG в Oracle было связано с множеством ограничений, в том числе: Х в таблице можно было иметь только один столбец типа LONG или LONG RAW;
Х если объем данных превышал 32 Кбайт, с ними нельзя было работать в хранимых процедурах;
Х их нельзя было изменять по частям;
Х многие операции в базе данных, например INSERT INTO T SELECT LONG_COL FROM T2, для столбцов типа LONG и LONG RAW не поддерживались;
Х их нельзя было указывать в конструкции WHERE;
Х таблицы со столбцами типа LONG и LONG RAW нельзя было реплицировать;
Х и т.д... Типы данных LOB не имеют всех этих ограничений. Вместо писания всех функций и процедур пакета DBMS_LOB (их около 25) я собираюсь ответить на наиболее часто задаваемые вопросы об использовании пакета DBMS_LOB и больших объектов. Подпрограммы пакета либо чрезвычайно просты, либо хорошо описаны в документации Oracle. При использовании больших объектов основными являются два руководства:
Приложение А Х Oracle8i Supplied PL/SQL Packages Reference. В этом руководстве можно найти обзор пакета DBMS_LOB и описание каждой его подпрограммы, включая все входные и выходные параметры. Соответствующий раздел пригодится как справочник. Его надо бегло просмотреть, чтобы знать основные возможности обработки больших объектов. Х Oracle8i Application Developer's Guide Ч Large Objects (LOBs). Руководство, полностью посвященное описанию программирования с использованием больших объектов в различных средах и языках. Каждый разработчик, предполагающий использовать большие объекты, должен его прочитать. Кроме того, многое в работе с большими объектами зависит от языка программирования. Способ выполнения определенных действий в языке Java отличается от принятого в языке С, при программировании на PL/SQL и т.д. Вот почему корпорация Oracle разработала отдельные руководства Application Developer's Guide для языков PL/SQL, Pro*C, COBOL, VB и Java, а также библиотеки OCI, посвященные взаимодействию с большими объектами в каждом из языков. Кроме того, есть еще упомянутое исчерпывающее руководство Application Developer's Guide, посвященное большим объектам и полезное, независимо от используемого языка. Я рекомендую всем, кто собирается использовать большие объекты в приложениях, прочитать это руководство, а также специфическое руководство для выбранного языка разработки. В этих руководствах можно найти ответы на большинство вопросов. В этом разделе я собираюсь ответить на часто задаваемые вопросы о больших объектах, начиная с "как показать их на Web-странице?" и заканчивая "как преобразовать данные типа BLOB в тип CLOB, и наоборот? " Ч об этом недостаточно хорошо сказано в стандартной документации. С большими объектами работать очень просто, если разобраться в пакете DBMS_LOB (описан в руководстве Oracle 8i Supplied PL/SQL Packages Reference), и если вы этого еще не сделали, ознакомьтесь с его описанием сейчас, прежде чем читать данный раздел. Я предполагаю, что вы готовы к работе с большими объектами.
Как загружать большие объекты?
Методов загрузки больших объектов немного. В главе 9, например, я демонстрировал загрузку больших объектов в базу данных с помощью средств SQLLDR. Кроме того, в предлагаемом корпорацией Oracle руководстве Application Developer's Guide для каждого языка продемонстрировано, как создавать и получать значения больших объектов на этом языке (у всех есть небольшие отличия). Я, однако, думаю, что при наличии каталога с файлами для загрузки проще всего использовать тип данных BFILE, объект DIRECTORY и процедуру LOADFROMFILE. В главе 9 (в первой части книги Ч прим. научн. ред.), посвященной загрузке данных, мы подробно рассмотрели использование процедуры DBMS_LOB.LOADFROMFILE. Детальную информацию вы сможете найти в этой главе. Кроме того, в представленном далее подразделе "Преобразования" рассматривается полный пример загрузки данных типа CLOB с помощью процедуры LOADFROMFILE.
Пакет DBMS_LOB Функция substr Небольшое примечание относительно функции substr, предлагаемой в пакете DBMS_LOB. Все остальные реализации функции substr, с которыми мне приходилось сталкиваться ( в том числе в языках SQL и PL/SQL), принимают следующие аргументы, в указанном порядке: substr(стpoкa, с_какого_символа, сколько_символов);
Так что substr('hello', 3, 2) даст в результате строку || Ч третий и четвертый символы (начиная с символа 3 выбрать 2 символа). В функции DBMS_LOB.SUBSTR, однако, порядок параметров другой:
dbms_lob.substr(большой_объект, сколько_символов, с_какого_символа) Поэтому аналогичный вызов функции substr из пакета DBMS_LOB вернет строку ell. Небольшой тестовый пример подтверждает это: tkyte@TKYTE816> create table t (str varchar2(10), lob clob);
Table created. tkyte@TKYTE816> insert into t values ('hello', 'hello');
1 row created. tkyte@TKYTE816> select substr(str, 3, 2), 2 dbms_lob.substr(lob, 3, 2) lob 3 from t 4/ SU LOB 11 ell Я постоянно передаю аргументы не в том порядке. Это Ч одна из тех вещей, о которых надо помнить!
Оператор SELECT FOR UPDATE в языке Java Чтобы изменить большой (не временный) объект в базе данных, строка, содержащая его, должна быть заблокирована соответствующим сеансом. Обычно это не учитывают те, кто пишет программы на языке Java/JDBC. Рассмотрим представленную далее небольшую программу на Java. Она: Х вставляет запись (поэтому резонно предположить, что эта запись заблокирована);
Х читает локатор только что созданного большого объекта;
Х пытается использовать этот локатор большого объекта в процедуре DBMS_LOB.WRITEAPPEND. При выполнении этой Java-программы всегда выдается сообщение об ошибке: java Test java.sql.SQLException: ORA-22920: row containing the LOB value is not locked ORA-06512: at "SYS.DBMS_LOB", line 715 ORA-06512: at line Приложение А Оказывается, вставленный большой объект больше не заблокирован сеансом. Это печальный побочный эффект стандартного режима "поддержки транзакций" протокола JDBC Ч по умолчанию транзакции не поддерживаются! Фиксация выполняется немедленно после каждого оператора. Если в следующем приложении не добавить вызов conn.setAutoCommit (false);
сразу же после getConnection, оно не будет работать. Эта строка кода (по моему мнению) должна быть первой после любого подключения в программе, использующей интерфейс JDBC! import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
// Для выполнения этого приложения нужна следующая таблица: // create table demo (id int primary key, theBlob blob);
>
Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@aria:1521:ora8i", "scott", "tiger");
// Если хотите, чтобы программа сработала, уберите комментарий со // следующей строки! // conn.setAutoCoamit(false);
Statement stmt - conn.createStatement();
// Вставляем в таблицу пустой BLOB // При первом вызове создадим его. stmt.execute ("insert into demo (id,theBlob) " + "values (l,empty_blob())");
// Теперь прочитаем его, чтобы можно было загрузить данные. ResultSet rset = stmt.executeQuery ("SELECT theBlob " + "FROM demo "+ "where id = 1");
if(rset.next()) ( // Получить BLOB для загрузки. B O l_mapBLOB = ((OracleResultSet)rset).getBLOB(l);
LB // Вот данные, которые мы в него загрузим. File binaryFile - new File("/tmp/binary.dat");
FileInputStream instream = new FileInputStream(binaryFile);
// Мы будем загружать примерно по 32 Кбайт за раз. Это // максимальный фрагмент, поддерживаемый пакетом dbms_lob // (ограничение языка PL/SQL).
Пакет DBMS_LOB int chunk = 32000;
byte[] l_buffer = new byte[chunk];
int l_nread = 0;
// Используем простую процедуру writeappend для добавления // фрагмента файла в конец BLOB-бьекта. OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ( "begin dbms_lob.writeappend( :1, :2, :3 );
end;
" );
// Читаем и записываем, пока не загрузим весь файл. cstmt.registerOutParameter(1, OracleTypes.BLOB);
while ((l_nread= instream.read(l_buffer)) != -1) { cstmt.setBLOB(1, l_mapBLOB);
cstmt.setInt(2, l_nread);
cstmt.setBytes(3, l_buffer);
cstmt.executeUpdate();
l_mapBLOB = cstmt.getBLOB(l);
} // Закрываем входной файл и завершаем оператор. instream.close();
cstmt.close();
) // Завершаем остальные операторы. rset.close();
stmt.close();
conn.close ();
} } Это общее ограничение протокола JDBC, влияющее, в частности, и на работу с большими объектами. Я не знаю, сколько разработчиков с удивлением обнаруживало, что интерфейс по умолчанию автоматически фиксирует транзакцию Ч это ведь должно делать приложение. Этого могут ожидать только разработчики, ранее использовавшие интерфейс ODBC! Аналогичное действие выполняет протокол ODBC в стандартном для него режиме автоматической фиксации.
Преобразования Часто пользователи хранят данные в объектах типа BLOB, но иногда нужно представить их как данные типа CLOB. Типичный пример: в столбец типа BLOB загружены двоичные и текстовые данные, и необходимо проанализировать текст. Анализировать данные типа BLOB сложно, поскольку сервер постоянно пытается преобразовать их в шестнадцатиричное представление, что нежелательно. В других случаях имеются данные типа LONG или LONG RAW, которые хотелось бы обрабатывать как данные типов CLOB или BLOB, поскольку функциональные возможности обработки этих типов намного превосходят те, что поддерживаются для типов LONG и LONG RAW.
Приложение А К счастью, эти преобразования легко выполнить. Можно преобразовать: Х данные типа BLOB в VARCHAR2;
Х VARCHAR2 - в RAW;
Х данные типа LONG Ч в CLOB;
Х данные типа LONG RAW Ч BLOB. Рассмотрим сначала преобразование типа BLOB в VARCHAR2, и наоборот, а затем разберемся с преобразованиями типов LONG в CLOB и LONG RAW в BLOB.
Преобразование типа BLOB в VARCHAR2 и обратно В пакет UTL_RAW входят две полезные подпрограммы для работы с данными типа BLOB. Более детально пакет UTL_RAW мы рассмотрим в соответствующем разделе приложения. Пока речь идет о следующих подпрограммах: Х CAST_TO_VARCHAR2. Принимает данные типа RAW и меняет тип на VARCHAR2. Никакого преобразования данных фактически не происходит Ч речь идет только об изменении типа. Х CAST_TO_RAW. Принимает данные типа VARCHAR2 и меняет тип на RAW. И в этом случае данные не изменяются Ч изменяется только тип данных. Итак, если известно, что данные типа BLOB содержат текстовую информацию в соответствующей кодировке, эти функции действительно полезны. Используем рассмотренную ранее программу LOADFROMFILE для загрузки набора файлов в столбец типа BLOB. Хотелось бы просматривать значения в этом столбце в среде SQL*Plus (с маскировкой любых "недопустимых" символов, являющихся причиной некорректной работы программы SQL*Plus). Для этого можно использовать средства пакета UTL_RAW. Сначала загрузим ряд файлов в таблицу DEMO: scott@DEV816> create table demo 2 (id int primary key, 3 theBlob blob 4) 5/ Table created. scott@DEV816> create or replace directory my_files as '/export/home/tkyte';
Directory created. scott@DEV816> create sequence blob_seq;
Sequence created. scott@DEV816> create or replace 2 procedure load_a_file(p_dir_name in varchar2, 3 p_file_name in varchar2) 4 as 5 l_blob blob;
Пакет DBMS_LOB 6 l_bfile bfile;
7 begin 8 Ч Сначала необходимо создать большой объект в базе данных. 9 Ч Для загрузки нужен пустой объект типа CLOB, BLOB или большой 10 Ч объект, созданный с помощью вызова CREATE TEMPORARY. 11 12 insert into demo values (blob_seq.nextval, empty_blob()) 13 returning theBlob into l_Blob;
14 15 Ч Затем открываем файл (объект типа BFILE), 16 Ч из которого будем загружать данные. 17 18 l_bfile := bfilename(p_dir_name, p_file_name);
19 dbms_lob.fileopen(l_bfile);
20 21 22 Ч После этого вызываем LOADFROMFILE, загружая в только что 23 Ч созданный CLOB все содержимое файла (объекта типа BFILE), 24 Ч который только что открыли. 25 dbms_lob.loadfromfile(l_blob, l_bfile, 26 dbms_lob.getlength(l_bfile));
27 28 Ч Закрываем файл (объект типа BFILE), чтобы 29 Ч избежать возможной нехватки дескрипторов файла. 30 31 dbms_lob.fileclose(l_bfile);
32 end;
33 / Procedure created. scott@DEV816> exec load_a_file('MY_FILES', 'clean.sql');
PL/SQL procedure successfully completed. scott@DEV816> exec load_a_file('MY_FILES', 'expdat.dmp');
Pages: | 1 | ... | 18 | 19 | 20 | 21 | 22 | ... | 24 | Книги, научные публикации