Use Oracle to implement real-time communication

xiaoxiao2021-03-06  39

Because Oracle does not provide tools for real-time output messages, Oracle database developers always face the challenge of monitoring their reserve processes in real time. They must be called using dbms_output.put_line, this call is not returned until the process is completed.

In this article, I want to demonstrate how to send emails from the Oracle8i database as a real-time communication solution. This way we want to monitor the stored procedure, no longer need to wait for them to complete, this method also provides developers with some of the benefits:

You can debug some long batch processes within a few minutes without waiting for a few hours;

Calculate the execution time required to specify the code block;

This needs to solve a problem. How do we output a message from a running store so we can check them instantly, even if we are not in the office? Our approach is to put all the required processes and functions in custom packets, then use Oracle8i UTL_SMTP packets to send emails from the Oracle database. Below I will explain some of this process in detail.

Oracle's UTL_SMTP package introduces UTL_SMTP packets in Oracle8i (SMTP represents Simple Mail Transfer Protocol Simple Mail Transfer Protocol, using TCP port 25 to establish communication between clients and servers), enabling developers to send emails from the database. UTL_SMTP can only be used with 8i or higher version installed with Java Virtual Machine (JVM). Also, PLSQL.jar must be loaded into the database. Otherwise, when the UTL_SMTP API is called 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 (installed JVM) does not run the plsql.jar to load the plsql.sj.sql script. System users or internal users can run $ ORACLE_HOME / RDBMS / Admin / INTPLSJ.SQL script to solve this problem. If you don't have a available script, you either get it from Oracle, or you can simply use the loadJava load utility plsql.jar:

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

UTL_SMTP API: This article uses the API in the following UTL_SMTP package in the code:

Open_CONNECTION (): Open the connection to the simple mail delivery protocol server. Helo (): After performing the connection, establish the initial transceiver relationship between the Simple Mail Transfer Protocol server, which can identify "letter" sent to the server. Mail (): Initialize the mail exchange with the server, but the truth does not send a message. RCPT (): Identify the recipient of the message. In order to send a message to multiple recipients, you must call multiple times. Data (): Specify the content of the email. Quit (): Terminate an SMTP session and disconnects to the server. In order to use the application programming interface, the following call is placed in the program in the order:

Call the open_connection call helo call Mail call Rcpt for Each Recipient Format the content of the email and then call Mail call Quit

EmailUtils package comprising the package specification EmailUtils API: SetSender / GetSender- provided / acquired sender SetRecipient / GetRecipient - Set / recipients made SetCcrecipient / GetCcrecipient - provided / acquired copy recipients SetMailHost / GetMailHost - Set / get mail host SetSubject / GetSubject - set / send Mail Code Send- made relating to a package of the normative EmailUtils: create or replace package EmailUtils as procedure SetSender (pSender in varchar2); function GetSenderreturn varchar2; procedure SetRecipient (pRecipient in varchar2); function GetRecipientreturn varchar2; procedure SetCcRecipient ( pCcRecipient in varchar2); function GetCcRecipientreturn varchar2; procedure SetMailHost (pMailHost in varchar2); function GetMailHostreturn varchar2; procedure SetSubject (pSubject in varchar2); function GetSubjectreturn 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 an overload process: two versions of this process in the package specification. A version is referenced when at least three mandatory parameters should be specified, psenter, precipient, and pmailhost:

PROCEDURE Send (psenter in varcha2, precipient in varcha2, pmailhost in varcha2: = null, psubject in varchar2: = null, pimentage in varchar2: = null);

Another version is only executed when the PMessage parameter value is provided:

Procedure Send (PMessage In Varchar2);

The second version is a version used as debugging. All email messages share the same sender, recipient, mail host, copy recipient, and topic information, which are all set when I started at the beginning of the session. Below 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 (' deleteclassification); end; / A actual email message will be specified in each Send process call. We can insert the emailutils.send () call into our debug code, we used to use the DBMS_output.put_line () call for obtaining the same debug results. :

VMessage: = 'POINT 1.' || UTL_TCP.CRLF || 'Rows Processed:' || to_TCHAR (VROWS) || UTL_TCP.CRLF || 'ELAPSED TIME:' || VTIME; EmailUtils.send (VMAESSAGE); VMESSAGE : = 'Point 3.' || UTL_TCP.CRLF || 'Rows Processed:' || to_CHAR (VROWS) || UTL_TCP.CRLF || 'Elapsed Time:' || VTIME; EmailUtils.send (VMAESSAGE);

Code 2 shows the emailutils specification with overloaded SEND procedures. We can see that the code of the Send process is quite simple. The UTL_SMTP package does not provide an application programming interface for formatting the content of the message. Instead, the user is responsible for formatting the message. This is why the following blocks are to be included in each Send process to format the headers of the email.

vMessage: = 'DATE:' || to_char (sysdate, 'fMdy, DD MON YYYY FXH24: MI: SS') || UTL_TCP.CRLF || 'from:' || psenter || 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). So in order to avoid this error, we use the following block, not allowed to more than 2,000 characters:

If Length (VMessage)> 2000ThenvMessage: = SUBSTR (VMessage, 1, 2000); endiff;

If you need to send an email with more than 2,000 words, you can use another three UTL_SMTP application programming interface to provide more refined control than the DATA () process. First, Open_DATA () sends a data command. Then write_data () add data to the string you want to send. You can call Write_Data () any time, so you can write 2,000 characters in a time to overcome the limit of the word number. Finally, Close_Data () ends the email message by sending a termination cycle package in CRLF.

Real-time messages make your life more comfortable from the database email is so easy. Once you tried this simple operation, I believe you will find it is useful, easy to make your database operation, such as debugging, remote database monitoring, and outputting database data. Each database developer has commissioning experiences in the code using a large number of DBMS_OUTPUT calls. After starting a SQL * PLUS session, enter the SET Serveroutput on and then run this process. The message put in DBMS_OUTPUT.PUT_LINE is displayed on the screen - but only after the process is completed. This process is extremely troublesome, especially when the batch of the batch is usually running all night. You can wait 10 to 12 hours just to find the wrong code, then modify, then wait for the next 10 to 12 hours? However, if you have a real-time approach to access messages, you can capture problems within 5 to 10 minutes. The dbms_output package also has other shortcomings. For example, it does not accept variable Boolean types and it has 255 characters per row limit (if you want to output a long message, then you will get this exception: Ora - 20000: ORU - 10028: Line Length overflow, Limit Of 255 BYTES Per Line). Listing it all the shortcomings of this has exceeded the scope of this article, but important conclusions are the DBMS_OUTPUT package does not allow database developers to see the message in real time.

Talking about the OS file on the server, will you like to output the selected data from the server to an Excel spreadsheet? One way is to use Oracle's UTL_FILE package, which provides a standard OS stream document input / output restriction level version. However, the PL / SQL program can only access the directory specified by the UTL_FILE_DIR parameter of the initialization file init.ora. This parameter is empty. In order for this directory to be used for file access, you must request the database manager to modify the initialization file. This has some trouble. Using the EmailUtils package, you can simply write data into an email, send it to yourself, and then receive its copy paste into your favorite document editor.

转载请注明原文地址:https://www.9cbs.com/read-68285.html

New Post(0)