How Oracle monitors the operation of a plsql process (iii) more professional DBMS_PIPE implementation

Yesterday a friend asked me how to monitor the operation of a pl/sql, here is a brief introduction to several methods.

Introduce more professional dbms_pipe implementation methods.

If you think the Dbms_application_info package is not professional enough, here is a more professional solution, through Dbms_pipe to achieve interprocess communication.

Using this method, the Pl/sql process of operation can send messages to the receiving process through pipelines. Here's a simple example:

sql> CREATE TABLE T_record

2 (STR VARCHAR2 (30),

3 time DATE);

Table has been created.

Create a record table.

Subsequently, the process responsible for monitoring initiates a pipe and then periodically detects the presence of data in the pipe:

Sql> DECLARE

2 V_return number;

3 v_str VARCHAR2 (32767);

4 BEGIN

5 V_return: = Dbms_pipe. Create_pipe (' p_test ');

6 while (V_return = 0) loop

7 V_return: = Dbms_pipe. Receive_message (' P_test ', 360);

8 IF v_return = 0 THEN

9 Dbms_pipe. Unpack_message (V_STR);

INSERT into T_record VALUES (V_STR, sysdate);

one end IF;

End LOOP;

V_return: = Dbms_pipe. Remove_pipe (' p_test ');

End;

15/

It is important to note that, under normal circumstances, this pl/sql is always in the monitoring state, but this requires that the process be modified so that the transaction is committed.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

For the sake of simplicity, the current example sets the expiration time of 10 minutes to ensure that the pl/sql messages are monitored at intervals of less than 10 minutes. In this 10 minutes, run the Pl/sql code in another session:

Sql> DECLARE

2 V_STR VARCHAR2 (4000): = ' step BEGIN ';

3 V_return number;

4 BEGIN

5 dbms_pipe. Pack_message (V_STR);

6 V_return: = Dbms_pipe. Send_message (' p_test ');

7 Dbms_lock. Sleep (60);

8 V_str: = ' step middle ';

9 Dbms_pipe. Pack_message (V_STR);

Ten V_return: = Dbms_pipe. Send_message (' p_test ');

One by one dbms_lock. Sleep (60);

V_STR: = ' step end ';

Dbms_pipe. Pack_message (V_STR);

V_return: = Dbms_pipe. Send_message (' p_test ');

End;

16/

The PL/SQL process has completed successfully.

When this code is finished for 10 minutes, the previous monitoring process runs due to timeout, and then checks log information:

The PL/SQL process has completed successfully.

Sql> ALTER session SET Nls_date_format = ' yyyy-mm-dd HH24:MI:SS ';

The session has changed.

Sql> SELECT * from T_record;

STR time

------------------------------ -------------------

Step BEGIN 2010-04-28 16:33:43

Step Middle 2010-04-28 16:34:43

Step End 2010-04-28 16:35:43

This approach is applicable to specialized monitoring procedures. But the Dbms_pipe package requires additional authorization.