Oracle real-time communication: UTL_SMTP package details

LieHuo. Net documentSince 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 in a few minutes without waiting for several hours. Calculate the execution time required to specify the 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:

Reference content is as follows: 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; /

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page