Real-time communication using Oracle

Since Oracle does not provide tools for Real-Time Message output, Oracle database developers always have to face the challenge of real-time monitoring of their reserve process execution. They must use dbms_output.put_line to call and return the result until the process is completed.

In this article, I want to demonstrate how to send emails directly from the Oracle8i Database as a real-time communication solution. In this way, we don't need to wait for them to complete the monitoring of stored procedures. This method also provides developers with some other benefits:

. You can debug a long batch process within several minutes without waiting for several hours;

. Calculate the execution time required for the specified code block;

This requires solving a problem. How do we output messages from the running stored procedure so that we can check them instantly even if we are not in the office? Our practice is to wrap all necessary procedures and functions in a custom package, and then use the Oracle8i utl_smtp package to directly send an email from the Oracle database. I will explain this process in detail below.

Utl_smtp package of Oracle

The utl_smtp package is introduced in Oracle8i (SMTP stands for Simple Mail Transfer Protocol, and TCP port 25 is used to establish communication between the client and the server ), allows developers to send emails from the database.

Utl_smtp can be used only when 8i or later versions with Java Virtual Machine (JVM) are installed. In addition, PLSQL. jar must be loaded into the database. Otherwise, when you call the utl_smtp API to send an email, we will get the following exception: ora-29540: Class Oracle/PLSQL/NET/tcpconnection does not exist.

The default $ ORACLE_HOME/JavaVM/install/initjvm. SQL script (with JVM installed) does not run the initplsj. SQL script that loads PLSQL. jar into the database. You can manually run the $ ORACLE_HOME/rdbms/admin/initplsj. SQL script to solve this problem. If you do not have a script available, you can either get it from Oracle Support or simply use loadjava to load the utility PLSQL. jar:

Loadjava-user SYS/password @ database-resolve PLSQL/jlib/PLSQL. Jar

Utl_smtp API:

The code in this article uses the following APIs in the utl_smtp package:

Open_connection (): Open the connection to the Simple Mail Transfer Protocol server.

Helo (): After the connection is executed, it establishes the initial sending and receiving relationship function with the Simple Mail Transfer Protocol server, which can identify the "messenger" sent to the server ".

Mail (): Initialize mail exchange with the server, but in fact do not send messages.

RCPT (): identifies the receiver of a message. To send a message to multiple recipients, you must call this process multiple times.

Data (): Specifies the email content.

Quit (): terminate an SMTP session and disconnect from the server.

To use the application programming interface, put the following calls into the program in the given order:

Call open_connection

Call helo

Call mail

Call RCPT for each recipient

Format the email content and then call mail

Call quit

Emailutils package specification

The emailutils package includes the following APIs:

Setsender/getsender-set/get the sender

Setrecipient/getrecipient-set/get recipient

Setccrecipient/getccrecipient-set/get recipient

Setmailhost/getmailhost-set/get email host

Setsubject/getsubject-set/get topic

Send-send email

Code 1 illustrates the emailutils package specification:

Create or replace package emailutils

Procedure setsender (psender in varchar2 );
Function getsender
Return varchar2;

Procedure setrecipient (precipient in varchar2 );
Function getrecipient
Return varchar2;

Procedure setccrecipient (pccrecipient in varchar2 );
Function getccrecipient
Return varchar2;

Procedure setmailhost (pmailhost in varchar2 );
Function getmailhost
Return varchar2;

Procedure setsubject (psubject in varchar2 );
Function getsubject
Return varchar2;

Procedure send (pmessage in varchar2 );

Procedure send (psender in varchar2,
Precipient in varchar2,
Pmailhost in varchar2,
Pccrecipient in varchar2: = NULL,
Psubject in varchar2: = NULL,
Pmessage in varchar2: = NULL );

End emailutils;
/
It can be seen that the send process is a heavy load process: the package specification includes two versions of this process. A version is referenced when at least three mandatory parameters are required: psender, precipient, and pmailhost:

Procedure send (psender in varchar2,
Precipient in varchar2,
Pmailhost in varchar2,
Pccrecipient in varchar2: = NULL,
Psubject in varchar2: = NULL,
Pmessage in varchar2: = NULL );

The other version is executed only when the pmessage parameter value is provided:

Procedure send (pmessage in varchar2 );

The second version is used for debugging. All email messages share the same sender, recipient, email host, CC recipient, and topic information. These are all set at the beginning of the session. The following is an example of a PL/SQL block:

Begin

Emailutils. setsender ('waynez @ mycompany.com ');
Emailutils. setrecipient ('waynezheng @ vip.sina.com ');
Emailutils. setccrecipient ('waynez @ mycompany.com ');
Emailutils. setmailhost ('myserver .mycompany.com ');
Emailutils. setsubject ('deletelassifications procedure: Run 1 ');

End;
/
 
An actual email message will be specified in each send process call. We can insert the emailutils. Send () call to our debugging code. We used to call dbms_output.put_line () to get the same debugging result. :

Vmessage: = 'point 1. '| utl_tcp.crlf |
'Rows processed: '| to_char (vrows) | utl_tcp.crlf |
'Elapsed time: '| vtime;

Emailutils. Send (vmessage );

Vmessage: = 'point 3. '| utl_tcp.crlf |
'Rows processed: '| to_char (vrows) | utl_tcp.crlf |
'Elapsed time: '| vtime;

Emailutils. Send (vmessage );
Code 2 shows the emailutils specification with the overloaded send process. We can see that the sending Process Code is quite simple. The utl_smtp package does not provide application programming interfaces for formatting message content. The user is responsible for formatting the message. This is why the following program blocks are included in each send process to format the email header.

Vmessage: = 'date: '|
To_char (sysdate, 'fmdy, DD mon yyyy fxhh24: MI: ss') |
Utl_tcp.crlf |
'From: '| psender | utl_tcp.crlf |
'Subject: '| psubject | utl_tcp.crlf |
'To:' | precipient | utl_tcp.crlf;

At the same time, if the message length exceeds 2000 characters, you may get an error (ora-06502: PL/SQL: numeric or value error ). To avoid this error, we use the following program block and do not allow messages to exceed 2000 characters:

If size (vmessage)> 2000
Then
Vmessage: = substr (vmessage, 1, 2000 );
End if;

If you want to send an email with more than 2000 characters, you can use the other three utl_smtp application programming interfaces to provide more refined control over the data () process. First, open_data () sends the data command. Then write_data () is added to the string you want to send. You can call write_data () multiple times, so that you can write 2000 characters at a time to overcome the limit on the number of words. Finally, close_data () ends an email message by sending a termination cycle encapsulated in CRLF.

Real-time messages make your life more comfortable

It is so easy to send emails from the database. Once you try this simple operation, I believe you will find it useful for your database operations, such as debugging, remote database monitoring, and database data output.

Every database developer has debugging experience of using a large number of dbms_output calls in the code. After starting an SQL * Plus session, enter SET serveroutput on and then run the process. The message that is put into dbms_output.put_line is displayed on the screen-but it can only be displayed after the process is completed. This process is extremely troublesome, especially when debugging a long batch processing, it usually needs to run all night. You can wait 10 to 12 hours to find out the error code, modify the code, and then wait for the next 10 to 12 hours? However, if you have a real-time way to access messages, you can capture problems in the first 5 to 10 minutes.

The dbms_output package also has other disadvantages. For example, it does not accept a variable Boolean Type and it has a limit of 255 characters per line (if you want to output a long message, you will get this exception: ora-20000: oru-10028: Line Length Overflow, limit of 255 bytes per line ). Listing all its shortcomings is beyond the scope of this article, but the important conclusion is that the dbms_output package does not allow database developers to see messages in real time.

Speaking of OS files on the server, would you like to output the selected data from the server to an Excel spreadsheet? One solution is to use the utl_file package of Oracle, which provides a restricted version of the input/output of the standard OS stream document. However, PL/SQL programs can only access the Directories specified by the utl_file_dir parameter in the initialization file init. ora. This parameter is null in most cases. To make this directory available for file access, you must ask the database administrator to modify the initialization file. This is troublesome. With the emailutils package, you can simply write data into an email, send it to yourself, and then copy it and paste it into your favorite document editor.