How Oracle monitors the operation of a plsql process (i) The simplest method of insert

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

Describes the simplest method of insert.

Their problem is to execute the following Pl/sql code:

Sql> SET Serverout on

Sql> BEGIN

2 Dbms_output. Put_Line (' A ');

3 Dbms_lock. Sleep (10);

4 Dbms_output. Put_Line (' B ');

5 Dbms_lock. Sleep (10);

6 Dbms_output. Put_Line (' C ');

7 End;

8/

A

B

C

The PL/SQL process has completed successfully.

They want to get a at the start of the Pl/sql code, run 10 seconds later, get B, and finally run to finish, get C.

However, the Dbms_output packet call occurs after the end of the Pl/sql code, that is, it is not possible to run the Pl/sql in batches of dbms_output output results, the final output is a, B, C at the same time output.

In fact, there are many ways to solve this problem, the simplest way is to solve this problem by insert.

sql> CREATE TABLE T_record

2 (STR VARCHAR2 (10),

3 time DATE);

Table has been created.

Sql> BEGIN

2 INSERT into T_record VALUES (' A ', sysdate);

3 COMMIT;

4 Dbms_lock. Sleep (10);

5 INSERT into T_record VALUES (' B ', sysdate);

6 COMMIT;

7 Dbms_lock. Sleep (10);

8 INSERT into T_record VALUES (' C ', sysdate);

9 COMMIT;

Ten end;

11/

The PL/SQL process has completed successfully.

At this point, you can check the step of the session through another session:

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

The session has changed.

Sql> SELECT * from T_record;

STR time

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

A 2010-04-26 16:37:18

Sql> SELECT * from T_record;

STR time

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

A 2010-04-26 16:37:18

Sql> SELECT * from T_record;

STR time

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

A 2010-04-26 16:37:18

B 2010-04-26 16:37:28

Sql> SELECT * from T_record;

STR time

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

A 2010-04-26 16:37:18

B 2010-04-26 16:37:28

Sql> SELECT * from T_record;

STR time

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

A 2010-04-26 16:37:18

B 2010-04-26 16:37:28

C 2010-04-26 16:37:39

Of course, the premise of this approach is that PL/SQL can be submitted, if the entire Pl/sql code is a complete transaction, or commit is based on business needs, then this method can not be used. You need to make a simple workaround and take advantage of autonomous transactions:

sql> TRUNCATE TABLE T_record;

The table has been truncated.

sql> CREATE OR REPLACE PROCEDURE p_record (p_str in VARCHAR2) as

2 PRAGMA autonomous_transaction;

3 BEGIN

4 INSERT into T_record VALUES (P_STR, sysdate);

5 COMMIT;