Oracle как вывести сообщение

/привет/мир/etc

Непериодические заметки о программировании

суббота, 31 мая 2014 г.

Коммуникации между сеансами в СУБД Oracle

В любой среде, где выполняются параллельные процессы, актуальны вопросы совместного использования общих ресурсов и обмена сообщениями между процессами. Одновременно открытые сеансы СУБД Oracle — именно такие параллельные процессы. Я уже рассматривал конкурентный доступ к общим ресурсам с помощью пакета DBMS_LOCK, а теперь поэкспериментирую со средствами обмена сигналами и данными между сеансами СУБД Oracle 11gR2 — пакетами DBMS_ALERT и DBMS_PIPE .

Пакет DBMS_ALERT поддерживает отправку и получение асинхронных уведомлений о событиях (alerts). Это могут быть уведомления об изменении данных в БД, отправленные триггером, или об окончании выполнения некоторой процедуры. Приложение в отдельном сеансе ожидает уведомления, на которые подписалось, и обрабатывает их тем или иным образом, например, отражая наступившие события в пользовательском интерфейсе или выполняя операции с данными, зависящие от наступления события.

Вот основные свойства уведомлений DBMS_ALERT , почерпнутые мной из официальной документации:

• Привязка к транзакциям. Это значит, что ожидающий сеанс получит уведомление только после завершения транзакции в уведомляющем сеансе. А если транзакция отменена, то уведомление не будет отправлено.
• Уведомления различаются именами. Многие сеансы могут отправлять и многие сеансы могут ожидать уведомления с одним и тем же именем.
• Сеанс может подписаться на одно или более уведомлений и ожидать одно конкретное ( DBMS_ALERT.WAITONE ) или любое из тех, на которые он подписан ( DBMS_ALERT.WAITANY ).
• Сеанс, ожидающий уведомление, не может делать что-то еще в время ожидания.
• Процедуры DBMS_ALERT.WAITONE и DBMS_ALERT.WAITANY имеют опциональный параметр timeout для указания допустимого времени ожидания. Значение 0 означает проверку наличия уведомления и немедленный возврат. Значение по умолчанию задает максимальное время ожидания уведомления, 1000 дней.
• Вместе с уведомлением может быть послано опциональное сообщение.
• Если одно и то же уведомление отправляется чаще, чем принимается, то прежде отправленные уведомления теряются. Ожидающий сеанс получит последнее по времени.

Рассмотрим по шагам отправку и получение уведомления с помощью DBMS_ALERT , открыв два сеанса. В первом сеансе запустим следующий блок PL/SQL. В блоке выполняется подписка на уведомление myalert (строка 5), ожидание его до 1000 дней (строка 6), вывод сообщения о получении уведомления (строка 7) и удаление подписки на уведомление (строка 8):

Во втором сеансе отправим уведомление myalert и вернемся к первому сеансу, чтобы увидеть результат.

• один сеанс посылает уведомления при помощи DBMS_ALERT.SIGNAL и COMMIT .
• другой сеанс
  1. подписывается на уведомления при помощи DBMS_ALERT.REGISTER ,
  2. ожидает уведомления при помощи DBMS_ALERT.WAITONE (или WAITANY ) и обрабатывает их,
  3. удаляет подписку на уведомления, когда в них больше нет необходимости.

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

Для этого создам процедуру signaller , которая будет посылать 10 уведомлений bang или boom , выбирая из двух случайным образом (строка 8 ниже). Отправив уведомление, процедура спит случайное число секунд между 1 и 7, имитируя занятость, и затем отправляет следующее уведомление. Для создания процедуры текущий пользователь должен явно (не через роль) получить привилегии EXECUTE на пакеты SYS.DBMS_ALERT и SYS.DBMS_LOCK .

Для получения уведомлений bang и boom создам процедуру consumer с параметром p_sleep — числом секунд между вызовами DBMS_ALERT.WAITANY . На это время consumer будет делать паузы между ожиданиями уведомлений, что приведет к потере некоторых уведомлений, если они отправляются достаточно часто. По умолчанию p_sleep равен 0, что практически устраняет паузы между ожиданиями, и уведомления не должны теряться.

Каждый раз процедура ожидает уведомления не более 10 секунд — см. значение параметра timeout при вызове DBMS_ALERT.WAITANY в строке 11.

Теперь, с помощью DBMS_SCHEDULER , я запущу процедуру signaller параллельно в двух сеансах и процедуру consumer в текущем сеансе:

Как видим, в текущем сеансе приняты все отправленные уведомления. Повторим эксперимент, введя паузу в 10 секунд между ожиданиями уведомлений:

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

В официальной документации по СУБД Oracle 11gR2 можно подробно познакомиться со всеми процедурами и функциями DBMS_ALERT . А я перейду к экспериментам с пакетом DBMS_PIPE , удалив ненужные теперь процедуры:

Пакет DBMS_PIPE позволяет двум или более сеансам пересылать друг другу данные по именованным каналам (pipes). Вот основные сведения о DBMS_PIPE :

• Запись и чтение данных из каналов не зависит от транзакций в коммуницирующих сеансах.
• Бывают public и private каналы. Публичные каналы доступны сеансам всех пользователей, имеющих привилегию EXECUTE для DBMS_PIPE . Частные каналы доступны только сеансам того же пользователя, который создал канал.
• Публичные каналы могут быть явными (explicit) и неявными (implicit). Частные каналы всегда явные.
• Неявный публичный канал открывается автоматически при первой отправке в него данных и автоматически удаляется, когда из него прочитаны все данные.
• Явный канал создается при помощи DBMS_PIPE.CREATE_PIPE , параметр private указывает, создается ли публиччный или частный канал, и удаляется при помощи DBMS_PIPE.REMOVE_PIPE .
• Многие сеансы могут одновременно писать в канал, указывая его имя. Как только данные из канала прочитаны одним из сеансов, данные удаляются из канала и более недоступны для чтения.
• Сообщения для отправки в канал готовятся при помощи DBMS_PIPE.PACK_MESSAGE , после чего DBMS_PIPE.SEND_MESSAGE посылает все подготовленные сообщения в канал.
• Данные читаются из канала при помощи DBMS_PIPE.RECEIVE_MESSAGE , после чего DBMS_PIPE.UNPACK_MESSAGE извлекает отдельные сообщения.
• Деcкрипторы каналов и данные, записанные в канал, размещаются в SGA. А текущие открытые каналы отражаются во вью v$db_pipes .

Следующий PL/SQL блок открывает три канала — частный явный, публичный явный и публичный неявный:

Итак, каналы были созданы. Теперь удалю созданные каналы, воспользовавшись DBMS_PIPE.REMOVE_PIPE для явных каналов и прочитав данные из неявного:

Как видим, после удаления каналы остались во вью v$db_pipes . Однако, вызов DBMS_PIPE.REMOVE_PIPE сбросил в 0 размеры каналов и изменил тип канала my_private_pipe с PRIVATE на PUBLIC . Не совсем то, чего можно было ожидать! При этом вызовы функции DBMS_PIPE.REMOVE_PIPE вернули статус 0, следовательно, выполнились без ошибок. В утешение остается заметить, что вью v$db_pipes не упоминается в документации по пакету DBMS_PIPE . И нет необходимости в него смотреть.

Теперь продемонстрирую вывод PL/SQL процедурой отладочных сообщений в канал и чтение этих сообщений в другом сеансе. Для этой цели буду использовать публичный неявный канал. Необходимые процедуры помещу в пакет DBG . Для создания пакета DBG текущий пользователь должен явно (не через роль) получить привилегию EXECUTE на пакет SYS.DBMS_PIPE .

Воспользуюсь созданным пакетом, чтобы писать в канал debug_pipe отладочные сообщения:

Открыв другой сеанс, прочитаю сообщения из канала:

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

Выждав больше 5 секунд, переключаюсь в другой сеанс и читаю сообщения из канала:

Итак, сообщения, посланные в канал debug_pipe из трех параллельных сеансов, успешно прочитаны в текущем сеансе. Пакет DBG в самом деле удобное средство для сбора данных о работе выполняющегося приложения. Когда PL/SQL код, выводящий сообщения, запускается приложением, взаимодействующим с пользователем, отладочный канал позволяет разработчику видеть, что происходит.

Завершая разговор о DBMS_PIPE , замечу, что не все мои эксперименты с этим пакетом прошли гладко и привели к ожидаемому результату. Кто заинтересовался, может подробнее познакомиться с процедурами и функциями DBMS_PIPE по официально документации по СУБД Oracle и продолжить эксперименты.

Источник

68 DBMS_OUTPUT

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

This chapter contains the following topics:

Rules and Limits

Using DBMS_OUTPUT

This section contains topics which relate to using the DBMS_OUTPUT package.

Overview

The package is typically used for debugging, or for displaying messages and reports to SQL*DBA or SQL*Plus (such as are produced by applying the SQL command DESCRIBE to procedures).

The PUT Procedure and PUT_LINE Procedure in this package enable you to place information in a buffer that can be read by another trigger, procedure, or package. In a separate PL/SQL procedure or anonymous block, you can display the buffered information by calling the GET_LINE Procedure and GET_LINES Procedure.

If the package is disabled, all calls to subprograms are ignored. In this way, you can design your application so that subprograms are available only when a client is able to process the information.

Security Model

The dbmsotpt.sql script must be run as user SYS . This creates the public synonym DBMS_OUTPUT , and EXECUTE permission on this package is granted to public .

Operational Notes

If you do not call GET_LINE , or if you do not display the messages on your screen in SQL*Plus, the buffered messages are ignored.

SQL*Plus calls GET_LINES after issuing a SQL statement or anonymous PL/SQL calls.

Typing SET SERVEROUTPUT ON in SQL*Plus has the effect of invoking

with no limit on the output.

You should generally avoid having application code invoke either the DISABLE Procedure or ENABLE Procedure because this could subvert the attempt of an external tool like SQL*Plus to control whether or not to display output.

Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.

Exceptions

DBMS_OUTPUT subprograms raise the application error ORA-20000 , and the output procedures can return the following errors:

Table 68-1 DBMS_OUTPUT Errors

Line length overflow

Rules and Limits

The maximum line size is 32767 bytes.

The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

Examples

Example 1: Using a Trigger to Produce Output

You can use a trigger to print out some output from the debugging process. For example, you could code the trigger to invoke:

If you have enabled the DBMS_OUTPUT package, then the text produced by this PUT_LINE would be buffered, and you could, after executing the statement (presumably some INSERT , DELETE , or UPDATE that caused the trigger to fire), retrieve the line of information. For example:

You could then optionally display the buffer on the screen. You repeat calls to GET_LINE until status comes back as nonzero. For better performance, you should use calls to GET_LINES Procedure which can return an array of lines.

Example 2: Debugging Stored Procedures and Triggers

The DBMS_OUTPUT package is commonly used to debug stored procedures and triggers. This package can also be used to enable you to retrieve information about an object and format this output, as shown in «Example 3: Retrieving Information About an Object».

This function queries the employee table and returns the total salary for a specified department. The function includes several calls to the PUT_LINE procedure:

Assume the EMP table contains the following rows:

Assume the user executes the following statements in SQL*Plus:

The user would then see the following information displayed in the output pane:

Example 3: Retrieving Information About an Object

In this example, the user has used the EXPLAIN PLAN command to retrieve information about the execution plan for a statement and has stored it in PLAN_TABLE . The user has also assigned a statement ID to this statement. The example EXPLAIN_OUT procedure retrieves the information from this table and formats the output in a nested manner that more closely depicts the order of steps undergone in processing the SQL statement.

Data Structures

The DBMS_OUTPUT package declares 2 collection types for use with the GET_LINES Procedure.

CHARARR Table Type

This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.

DBMSOUTPUT_LINESARRAY Object Type

This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure.

Summary of DBMS_OUTPUT Subprograms

Table 68-2 DBMS_OUTPUT Package Subprograms

Disables message output

Enables message output

Retrieves one line from buffer

Retrieves an array of lines from buffer

Terminates a line created with PUT

Places a line in the buffer

Places partial line in buffer

The PUT Procedure that take a number are obsolete and, while currently supported, are included in this release for legacy reasons only.

DISABLE Procedure

This procedure disables calls to PUT , PUT_LINE , NEW_LINE , GET_LINE , and GET_LINES , and purges the buffer of any remaining information.

As with the ENABLE Procedure, you do not need to call this procedure if you are using the SERVEROUTPUT option of SQL*Plus.

ENABLE Procedure

This procedure enables calls to PUT , PUT_LINE , NEW_LINE , GET_LINE , and GET_LINES . Calls to these procedures are ignored if the DBMS_OUTPUT package is not activated.

Table 68-3 ENABLE Procedure Parameters

Upper limit, in bytes, the amount of buffered information. Setting buffer_size to NULL specifies that there should be no limit.

It is not necessary to call this procedure when you use the SET SERVEROUTPUT option of SQL*Plus.

If there are multiple calls to ENABLE , then buffer_size is the last of the values specified. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size ( NOT NULL ).

NULL is expected to be the usual choice. The default is 20,000 for backwards compatibility with earlier database versions that did not support unlimited buffering.

GET_LINE Procedure

This procedure retrieves a single line of buffered information.

Table 68-4 GET_LINE Procedure Parameters

Returns a single line of buffered information, excluding a final newline character. You should declare the actual for this parameter as VARCHAR2 (32767) to avoid the risk of » ORA-06502 : PL/SQL: numeric or value error: character string buffer too small».

If the call completes successfully, then the status returns as 0. If there are no more lines in the buffer, then the status is 1.

You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES procedure to retrieve an array of lines from the buffer.

You can choose to automatically display this information if you are using SQL*Plus by using the special SET SERVEROUTPUT ON command.

After calling GET_LINE or GET_LINES , any lines not retrieved before the next call to PUT , PUT_LINE , or NEW_LINE are discarded to avoid confusing them with the next message.

GET_LINES Procedure

This procedure retrieves an array of lines from the buffer.

Table 68-5 GET_LINES Procedure Parameters

Returns an array of lines of buffered information. The maximum length of each line in the array is 32767 bytes. It is recommended that you use the VARRAY overload version in a 3GL host program to execute the procedure from a PL/SQL anonymous block.

Number of lines you want to retrieve from the buffer.

After retrieving the specified number of lines, the procedure returns the number of lines actually retrieved. If this number is less than the number of lines requested, then there are no more lines in the buffer.

You can choose to retrieve from the buffer a single line or an array of lines. Call the GET_LINE procedure to retrieve a single line of buffered information. To reduce the number of calls to the server, call the GET_LINES procedure to retrieve an array of lines from the buffer.

You can choose to automatically display this information if you are using SQL*Plus by using the special SET SERVEROUTPUT ON command.

After calling GET_LINE or GET_LINES , any lines not retrieved before the next call to PUT , PUT_LINE , or NEW_LINE are discarded to avoid confusing them with the next message.

NEW_LINE Procedure

This procedure puts an end-of-line marker. The GET_LINE Procedure and the GET_LINES Procedure return «lines» as delimited by «newlines». Every call to the PUT_LINE Procedure or NEW_LINE Procedure generates a line that is returned by GET_LINE ( S ).

PUT Procedure

This procedure places a partial line in the buffer.

The PUT procedure that takes a NUMBER is obsolete and, while currently supported, is included in this release for legacy reasons only.

Table 68-6 PUT and PUT_LINE Procedure Parameters

Table 68-7 PUT and PUT_LINE Procedure Exceptions

Buffer overflow, limit of buf_limit > bytes.

Line length overflow, limit of 32767 bytes for each line.

You can build a line of information piece by piece by making multiple calls to PUT , or place an entire line of information into the buffer by calling PUT_LINE .

When you call PUT_LINE the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE . GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.

If your lines exceed the line limit, you receive an error message.

Output that you create using PUT or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.

For example, SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program.

PUT_LINE Procedure

This procedure places a line in the buffer.

The PUT_LINE procedure that takes a NUMBER is obsolete and, while currently supported, is included in this release for legacy reasons only.

Table 68-8 PUT and PUT_LINE Procedure Parameters

Table 68-9 PUT and PUT_LINE Procedure Exceptions

Buffer overflow, limit of buf_limit > bytes.

Line length overflow, limit of 32767 bytes for each line.

You can build a line of information piece by piece by making multiple calls to PUT , or place an entire line of information into the buffer by calling PUT_LINE .

When you call PUT_LINE the item you specify is automatically followed by an end-of-line marker. If you make calls to PUT to build a line, then you must add your own end-of-line marker by calling NEW_LINE . GET_LINE and GET_LINES do not return lines that have not been terminated with a newline character.

If your lines exceeds the line limit, you receive an error message.

Output that you create using PUT or PUT_LINE is buffered. The output cannot be retrieved until the PL/SQL program unit from which it was buffered returns to its caller.

For example, SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program. For example:

Источник