Implementing real-time communication with Oracle

Oracle
Because Oracle does not provide tools for real-time output messages, Oracle database developers face the challenge of real-time monitoring of their storage process execution. They must use the dbms_output.put_line call, and this call will not return results until the process completes.

In this article, I want to show you how to send e-mail directly from the Oracle8i database as a real-time communication solution. This way we're going to monitor the stored procedures no longer need to wait for them to complete, and there are some other benefits that developers can offer:



. You can debug some very long batch processes in minutes without having to wait a few hours;

. Calculates the execution time required to specify the code block;

This requires solving a problem, how do we output messages from running stored procedures so that we can check them immediately, even if we are not in the office space? Our approach is to wrap all the necessary processes and functions in a custom package, and then use the Oracle8i UTL_SMTP package to send e-mail directly from the Oracle database. I'll explain some of the process in detail below.

Oracle's UTL_SMTP Package

The introduction of the UTL_SMTP package in Oracle8i (SMTP represents the Simple Mail Transfer Protocol, which uses TCP port 25 to establish a communication connection between the client and the server) enables the developer to send e-mail messages from the database.

You can use UTL_SMTP only if you install a 8i or higher version with a Java Virtual machine (JVM). In addition, the Plsql.jar must be loaded into the database. Otherwise, when the UTL_SMTP API is invoked to send e-mail, we get the following exception: Ora-29540:class oracle/plsql/net/tcpconnection does not exist.

The default $oracle_home/javavm/install/initjvm.sql script (which has the JVM installed) does not run the Initplsj.sql script that loads the Plsql.jar into the database. System users or internal users can manually run $oracle_home/rdbms/admin/initplsj.sql scripts to resolve this problem. If you don't have a script available, you can either get it from Oracle support or simply use the Loadjava load utility directly Plsql.jar:

Loadjava-user Sys/password@database-resolve Plsql/jlib/plsql.jar

UTL_SMTP API:

The APIs in the following UTL_SMTP packages are used in the code in this article:

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

HELO (): After performing the connection, establish the initial transceiver function with the Simple Mail delivery protocol server, which identifies the "messenger" sent to the server.

Mail (): Initializes a mail exchange with the server, but does not actually send messages.

RCPT (): The recipient who identifies the message. In order to send a message to multiple recipients, you must call the process multiple times.

DATA (): Specifies the contents of the e-mail message.

QUIT (): Terminates an SMTP session and disconnects from the server.

To take advantage of the application programming interface, put the following calls into the program in the order given:

Call Open_connection

Call HELO

Call MAIL

Call RCPT for each recipient

Format the contents of an e-mail message and call mail

Call QUIT

Emailutils Package Specification

The Emailutils package includes the following APIs:

setsender/getsender-Set/Get Sender

Setrecipient/getrecipient-Set up/Get recipients

Setccrecipient/getccrecipient-set/Get Tineke recipient

Setmailhost/getmailhost-set/Get mail host

Setsubject/getsubject-set/Get theme

send-Send mail

Code 1 illustrates the specification of the Emailutils package:


Create or replace package emailutils as

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;
/


As you can see, the send process is an overloaded procedure: The package specification includes two versions of this procedure. A version when at least three mandatory parameters are to be specified at the time of reference, 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 only performed when the Pmessage parameter value is supplied:



Procedure Send (pMessage in varchar2);

The second version is the version that is used as a debug. All e-mail messages share the same sender, recipient, mail host, CC recipient, and subject information that I set up at the beginning of the session. The following is an example of a PL/SQL program 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 (' deleteclassifications procedure:run 1 ');

End
/


An actual e-mail message is specified in each send procedure call. We can insert the Emailutils.send () call into the code we are debugging, and we used dbms_output to get the same debug results. Put_Line () call. :


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. As we can see, the code for the send process is fairly straightforward. The UTL_SMTP package does not provide an application programming interface for formatting message content. Instead, the user is responsible for formatting the message. This is why the following pieces of the program are included in each send process to format the head of the e-mail message.

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;

Also, if the message is longer than 2000 characters, you may get an error (Ora-06502:pl/sql:numeric or value error). So to avoid this error, we use the following block, which does not allow messages to exceed 2000 characters:

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

If you need to send an e-mail message with more than 2000 words, you can use another three UTL_SMTP application programming interfaces to provide finer control over the data () process. First, Open_data () sends the data command. Then Write_data () adds the data to the string you want to send. You can call Write_data () any number of times so that you can write 2000 characters at a time to overcome the word limit. Finally, Close_data () ends the e-mail message by sending a termination period encapsulated in CRLF.

Live messages make your life more comfortable

It's so easy to send e-mail from a database. Once you try this simple operation, I'm sure you'll find it useful for your database operations, such as debugging, remote database monitoring, and outputting database data.

Each database developer has a debugging experience that uses a large number of dbms_output calls in the code. After starting a SQL * Plus session, enter set Serveroutput on and then run the procedure. Put it in the dbms_output. Messages that are invoked by the Put_Line are displayed on the screen-but only after the process is complete. This process is extremely cumbersome, especially if you are debugging long batches that are usually run all night. Can you wait 10-12 hours just to find the wrong code and then modify it to wait for the next 10-12 hours? However, if you have a real-time approach to accessing the message, you can catch the problem within the first 5-10 minutes.

Dbms_output packs have other drawbacks as well. For example, it does not accept a mutable Boolean type and it has 255 characters per line limit (if you want to output a long message, you will get this exception: ora-20000:oru-10028:line length overflow, limit of 255 byte s per line). It is beyond the scope of this article to list all its drawbacks, but the important conclusion is that the Dbms_output package does not allow the database developer to see the message in real time.

When it comes to OS files on the server, would you prefer to export the selected data from the server to an Excel spreadsheet? One approach is to use Oracle's Utl_file package, which provides a limited-level version of the standard OS flow document input/output. However, the Pl/sql program can access only the directories specified in the Utl_file_dir parameters of the initialization file Init.ora. This parameter is empty in most cases. In order for this directory to be used for file access, the database manager must be asked to modify the initialization file. It's a bit of a hassle. With the Emailutils package, you can simply write the data to an email, send it to yourself, and then copy it to your favorite document editor.