Oracle Stored Procedure sending email instance learning

Copy codeThe Code is as follows:
Create or replace procedure procsendemail (P_TXT VARCHAR2,
P_SUB VARCHAR2,
P_SENDOR VARCHAR2,
P_RECEIVER VARCHAR2,
P_SERVER VARCHAR2,
P_PORT number default 25,
P_NEED_SMTP int default 0,
P_USER VARCHAR2 default null,
P_PASS VARCHAR2 default null,
P_FILENAME VARCHAR2 default null,
P_ENCODE VARCHAR2 DEFAULT 'bit 7 ')
AUTHID CURRENT_USER IS

Copy codeThe Code is as follows:
/*
Purpose: use oracle to send emails
Main functions: 1. Supports multiple recipients.
2. Support for Chinese Characters
3. CC recipients are supported.
4. Support for attachments larger than 32 KB
5. Supports multi-line text
6. Support for multiple attachments
7. Supports text attachments and binary attachments
8. HTML format supported
8. Support
Author: suk
Parameter description:
P_txt: email body
P_sub: Mail title
P_SendorAddress: the sender's email address.
P_ReceiverAddress: The Receiving address, which can be sent to multiple addresses at the same time. The addresses are separated by "," or ";".
P_EmailServer: email server address, which can be a domain name or IP address
P_Port: port of the email server
P_need_smtp: Indicates whether smtp authentication is required. 0 indicates no, and 1 indicates yes.
P_user: User Name required for smtp authentication
P_pass: Password required for smtp Verification
P_filename: The attachment name, which must contain the complete path, for example, "d: tempa.txt ".
Multiple attachments are allowed. The attachment names are separated by commas (,) or semicolons (;).
P_encode: The attachment encoding conversion format. p_encode = 'bit 7' indicates the text type attachment.
P_encode = 'base64' indicates binary type attachment
Note:
1. Text attachments cannot be sent in base64 format. Otherwise, an error occurs.
2. Multiple attachments can only be sent in the same format

*/
Rochelle CRLF VARCHAR2 (2): = UTL_TCP.CRLF;
Rochelle sendoraddress VARCHAR2 (4000 );
Rochelle splite VARCHAR2 (10): = '+ + ';
Boundary constant VARCHAR2 (256): = '----- BYSUK ';
FIRST_BOUNDARY CONSTANT VARCHAR2 (256): = '--' | BOUNDARY | L_CRLF;
LAST_BOUNDARY CONSTANT VARCHAR2 (256): = '--' | BOUNDARY | '--' |
Rochelle CRLF;
MULTIPART_MIME_TYPE CONSTANT VARCHAR2 (256): = 'multipart/mixed; boundary = "'|
BOUNDARY | '"';
/* The following are the variables used to send large binary attachments */
Rochelle fil BFILE;
Rochelle file_len NUMBER;
Rochelle modulo NUMBER;
Rochelle pieces NUMBER;
L_FILE_HANDLE UTL_FILE.FILE_TYPE;
Rochelle AMT BINARY_INTEGER: = 672*3;/* ensures proper format; 2016 */
Rochelle filepos PLS_INTEGER: = 1;/* pointer for the file */
Rochelle chunks NUMBER;
Rochelle Buf RAW (2100 );
Rochelle data RAW (2100 );
Rochelle max_line_width NUMBER: = 54;
L_DIRECTORY_BASE_NAME VARCHAR2 (100): = 'dir _ FOR_SEND_MAIL ';
Rochelle line VARCHAR2 (1000 );
Rochelle mesg VARCHAR2 (32767 );
/* The preceding variables are used to send large binary attachments */
TYPE ADDRESS_LIST is table of VARCHAR2 (100) index by BINARY_INTEGER;
MY_ADDRESS_LIST ADDRESS_LIST;
TYPE ACCT_LIST is table of VARCHAR2 (100) index by BINARY_INTEGER;
MY_ACCT_LIST ACCT_LIST;

------------------------------------- Return the directory or name of the attachment source file --------------------------------------
Copy codeThe Code is as follows:
FUNCTION GET_FILE (P_FILE VARCHAR2, P_GET INT) RETURN VARCHAR2 IS
-- P_get = 1 indicates the returned directory.
-- P_get = 2 indicates the returned file name.
L_FILE VARCHAR2 (1000 );
BEGIN
If instr (P_FILE, '')> 0 THEN
-- Windows
IF P_GET = 1 THEN
L_FILE: = SUBSTR (P_FILE, 1, INSTR (P_FILE, '',-1)-1 );
ELSIF P_GET = 2 THEN
L_FILE: = SUBSTR (P_FILE,-(LENGTH (P_FILE)-INSTR (P_FILE, '',-1 )));
End if;
Elsif instr (P_FILE, '/')> 0 THEN
-- Linux/unix
IF P_GET = 1 THEN
L_FILE: = SUBSTR (P_FILE, 1, INSTR (P_FILE, '/',-1)-1 );
ELSIF P_GET = 2 THEN
L_FILE: = SUBSTR (P_FILE,
-(LENGTH (P_FILE)-INSTR (P_FILE, '/',-1 )));
End if;
End if;
RETURN L_FILE;
END;

--------------------------------------------- Delete directory ------------------------------------
Copy codeThe Code is as follows:
PROCEDURE DROP_DIRECTORY (P_DIRECTORY_NAME VARCHAR2) IS
BEGIN
Execute immediate 'drop directory' | P_DIRECTORY_NAME;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

-------------------------------------------------- Create a directory -----------------------------------------
Copy codeThe Code is as follows:
PROCEDURE CREATE_DIRECTORY (P_DIRECTORY_NAME VARCHAR2, P_DIR VARCHAR2) IS
BEGIN
Execute immediate 'create directory' | P_DIRECTORY_NAME | 'as' |
P_DIR | '''';
Execute immediate 'Grant read, write on directory' | P_DIRECTORY_NAME |
'To public ';
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

------------------------------------------ Separate the email address or attachment address -----------------------------------
Copy codeThe Code is as follows:
PROCEDURE P_SPLITE_STR (P_STR VARCHAR2, P_SPLITE_FLAG int default 1) IS
Rochelle ADDR VARCHAR2 (254): = '';
Rochelle Len INT;
Rochelle STR VARCHAR2 (4000 );
J int: = 0; -- indicates the number of email addresses or attachments.
BEGIN
/* Process the list of received mail addresses, including removing spaces, converting; and so on */
Rochelle STR: = TRIM (RTRIM (REPLACE (P_STR ,';',','),'',''),','));
Rochelle Len: = LENGTH (L_STR );
For I IN 1 .. L_LEN LOOP
If substr (L_STR, I, 1) <> ', 'then
L_ADDR: = L_ADDR | SUBSTR (L_STR, I, 1 );
ELSE
J: = J + 1;
IF P_SPLITE_FLAG = 1 THEN
-- Process the email address
-- '<>' Must be added before and after the email address; otherwise, many mailboxes cannot send emails.
Rochelle ADDR: = '<' | L_ADDR | '> ';
-- Call the mail sending Process
MY_ADDRESS_LIST (J): = L_ADDR;
ELSIF P_SPLITE_FLAG = 2 THEN
-- Indicates the name of the attachment to be processed.
MY_ACCT_LIST (J): = L_ADDR;
End if;
Rochelle ADDR: = '';
End if;
If I = L_LEN THEN
J: = J + 1;
IF P_SPLITE_FLAG = 1 THEN
-- Call the mail sending Process
Rochelle ADDR: = '<' | L_ADDR | '> ';
MY_ADDRESS_LIST (J): = L_ADDR;
ELSIF P_SPLITE_FLAG = 2 THEN
MY_ACCT_LIST (J): = L_ADDR;
End if;
End if;
End loop;
END;

------------------------------------------------ Write the mail header and content ------------------------------------------
Copy codeThe Code is as follows:
PROCEDURE WRITE_DATA (P_CONN in out nocopy UTL_SMTP.CONNECTION,
P_NAME IN VARCHAR2,
P_VALUE IN VARCHAR2,
P_SPLITE VARCHAR2 DEFAULT ':',
P_CRLF VARCHAR2 DEFAULT L_CRLF) IS
BEGIN
/* Utl_raw.cast_to_raw is very important for Solving Chinese garbled characters */
UTL_SMTP.WRITE_RAW_DATA (P_CONN,
UTL_RAW.CAST_TO_RAW (CONVERT (P_NAME | P_SPLITE |
P_VALUE | P_CRLF,
'Zhs16gbk ')));
END;

---------------------------------------- Write the MIME mail tail -----------------------------------------------------
Copy codeThe Code is as follows:
PROCEDURE END_BOUNDARY (conn in out nocopy UTL_SMTP.CONNECTION,
Last in boolean default false) IS
BEGIN
UTL_SMTP.WRITE_DATA (CONN, UTL_TCP.CRLF );
IF (LAST) THEN
UTL_SMTP.WRITE_DATA (CONN, LAST_BOUNDARY );
End if;
END;

---------------------------------------------- Send an attachment ----------------------------------------------------
Copy codeThe Code is as follows:
Procedure attachment (conn in out nocopy UTL_SMTP.CONNECTION,
MIME_TYPE IN VARCHAR2 DEFAULT 'text/plain ',
Inline in boolean default true,
Filename in VARCHAR2 DEFAULT 't.txt ',
TRANSFER_ENC IN VARCHAR2 DEFAULT '7bit ',
DT_NAME IN VARCHAR2 DEFAULT '0') IS
Rochelle filename VARCHAR2 (1000 );
BEGIN
-- Write the attachment Header
UTL_SMTP.WRITE_DATA (CONN, FIRST_BOUNDARY );
-- Set the attachment format
WRITE_DATA (CONN, 'content-type', MIME_TYPE );
-- If the file name is not empty, it indicates there is an attachment.
DROP_DIRECTORY (DT_NAME );
-- Create directory
CREATE_DIRECTORY (DT_NAME, GET_FILE (FILENAME, 1 ));
-- Obtain the attachment file name.
Rochelle FILENAME: = GET_FILE (FILENAME, 2 );
IF (INLINE) THEN
WRITE_DATA (CONN,
'Content-disposition ',
'Inline; filename = "'| L_FILENAME | '"');
ELSE
WRITE_DATA (CONN,
'Content-disposition ',
'Attachment; filename = "'| L_FILENAME | '"');
End if;
-- Set the attachment conversion format
IF (TRANSFER_ENC is not null) THEN
WRITE_DATA (CONN, 'content-Transfer-Encoding ', TRANSFER_ENC );
End if;
UTL_SMTP.WRITE_DATA (CONN, UTL_TCP.CRLF );
-- Begin attachment content
IF TRANSFER_ENC = 'bit 7' THEN
-- Text attachments
BEGIN
Rochelle file_handle: = UTL_FILE.FOPEN (DT_NAME, L_FILENAME, 'R'); -- open the file
-- Divide the attachment into multiple parts, so that more than 32 KB of attachments can be sent.
LOOP
UTL_FILE.GET_LINE (L_FILE_HANDLE, L_LINE );
Rochelle mesg: = Rochelle Line | Rochelle CRLF;
WRITE_DATA (CONN, '', L_MESG ,'','');
End loop;
UTL_FILE.FCLOSE (L_FILE_HANDLE );
END_BOUNDARY (CONN );
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE (L_FILE_HANDLE );
END_BOUNDARY (CONN );
NULL;
END; -- END processing of text attachments
ELSIF TRANSFER_ENC = 'base64' THEN
-- For binary attachments
BEGIN
-- Divide the attachment into multiple parts, so that more than 32 KB of attachments can be sent.
Rochelle filepos: = 1; -- reset offset. It must be reset when multiple attachments are sent.
Rochelle fil: = BFILENAME (DT_NAME, L_FILENAME );
L_FILE_LEN: = DBMS_LOB.GETLENGTH (L_FIL );
L_MODULO: = MOD (L_FILE_LEN, L_AMT );
Rochelle pieces: = TRUNC (L_FILE_LEN/Rochelle AMT );
IF (L_MODULO <> 0) THEN
Rochelle pieces: = Rochelle pieces + 1;
End if;
DBMS_LOB.FILEOPEN (L_FIL, DBMS_LOB.FILE_READONLY );
DBMS_LOB.READ (L_FIL, L_AMT, L_FILEPOS, L_BUF );
Rochelle data: = NULL;
For I IN 1 .. L_PIECES LOOP
L_FILEPOS: = I * L_AMT + 1;
L_FILE_LEN: = L_FILE_LEN-L_AMT;
Rochelle data: = UTL_RAW.CONCAT (Rochelle data, Rochelle BUF );
Rochelle chunks: = TRUNC (UTL_RAW.LENGTH (L_DATA)/Rochelle max_line_width );
IF (I <> L_PIECES) THEN
Rochelle chunks: = maid-1;
End if;
UTL_SMTP.WRITE_RAW_DATA (CONN, UTL_ENCODE.BASE64_ENCODE (L_DATA ));
Rochelle data: = NULL;
IF (L_FILE_LEN <L_AMT AND L_FILE_LEN> 0) THEN
Rochelle AMT: = maid;
End if;
DBMS_LOB.READ (L_FIL, L_AMT, L_FILEPOS, L_BUF );
End loop;
DBMS_LOB.FILECLOSE (L_FIL );
END_BOUNDARY (CONN );
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FILECLOSE (L_FIL );
END_BOUNDARY (CONN );
RAISE;
END; -- END processing of binary attachments
End if; -- END processing of the attachment content
DROP_DIRECTORY (DT_NAME );
END; -- END Process ATTACHMENT

--------------------------------------------- The process of actually sending emails --------------------------------------------
Copy codeThe Code is as follows:
PROCEDURE P_EMAIL (P_SENDORADDRESS2 VARCHAR2, -- sending Address
P_RECEIVERADDRESS2 VARCHAR2) -- accept address
IS
Rochelle conn UTL_SMTP.CONNECTION; -- Define a connection
BEGIN
/* Initialize the email server information and connect to the email server */
Rochelle Conn: = UTL_SMTP.OPEN_CONNECTION (P_SERVER, P_PORT );
UTL_SMTP.HELO (L_CONN, P_SERVER );
/* Smtp server logon check */
IF P_NEED_SMTP = 1 THEN
UTL_SMTP.COMMAND (L_CONN, 'auth login ','');
UTL_SMTP.COMMAND (L_CONN,
UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (UTL_RAW.CAST_TO_RAW (P_USER ))));
UTL_SMTP.COMMAND (L_CONN,
UTL_RAW.CAST_TO_VARCHAR2 (UTL_ENCODE.BASE64_ENCODE (UTL_RAW.CAST_TO_RAW (P_PASS ))));
End if;
/* Set the Sending address and Receiving address */
UTL_SMTP.MAIL (L_CONN, P_SENDORADDRESS2 );
UTL_SMTP.RCPT (L_CONN, P_RECEIVERADDRESS2 );
/* Set the mail header */
UTL_SMTP.OPEN_DATA (L_CONN );
WRITE_DATA (L_CONN, 'date', TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24: mi: ss '));
/* Set the sender */
WRITE_DATA (L_CONN, 'from', P_SENDOR );
/* Set the recipient */
WRITE_DATA (L_CONN, 'to', P_RECEIVER );
/* Set the Email Subject */
WRITE_DATA (L_CONN, 'subobject', P_SUB );
WRITE_DATA (L_CONN, 'content-type', MULTIPART_MIME_TYPE );
UTL_SMTP.WRITE_DATA (L_CONN, UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA (L_CONN, FIRST_BOUNDARY );
WRITE_DATA (L_CONN, 'content-type', 'text/plain; charset = gb2312 ');
-- Empty one line. Otherwise, the body content is not displayed.
UTL_SMTP.WRITE_DATA (L_CONN, UTL_TCP.CRLF );
/* Set the mail body
Returns the separator to chr (10 ). This is mainly used to call this process in shell. If there are multiple rows, merge the contents of multiple rows into one row and separate them with l_splite.
Then replace chr (10) with l_crlf ). This step is required. Otherwise, you cannot send emails with multiple lines in the mail body.
*/
WRITE_DATA (L_CONN,
'',
REPLACE (P_TXT, L_SPLITE, CHR (10), CHR (10), L_CRLF ),
'',
'');
END_BOUNDARY (L_CONN );
-- If the file name is not blank, an attachment is sent.
IF (P_FILENAME is not null) THEN
-- Split the attachment address by commas or semicolons
P_SPLITE_STR (P_FILENAME, 2 );
-- Send attachments cyclically (in the same email)
For k in 1 .. MY_ACCT_LIST.COUNT LOOP
ATTACHMENT (CONN => L_CONN,
FILENAME => MY_ACCT_LIST (K ),
TRANSFER_ENC => P_ENCODE,
DT_NAME => L_DIRECTORY_BASE_NAME | TO_CHAR (K ));
End loop;
End if;
/* Close Data Writing */
UTL_SMTP.CLOSE_DATA (L_CONN );
/* Close the connection */
UTL_SMTP.QUIT (L_CONN );
/* Exception Handling */
EXCEPTION
WHEN OTHERS THEN
NULL;
RAISE;
END;

------------------------------------------------- Main process -----------------------------------------------------
Copy codeThe Code is as follows:
BEGIN
Rochelle sendoraddress: = '<' | P_SENDOR | '> ';
P_SPLITE_STR (P_RECEIVER); -- process the email address
For k in 1 .. MY_ADDRESS_LIST.COUNT LOOP
P_EMAIL (L_SENDORADDRESS, MY_ADDRESS_LIST (K ));
End loop;
/* Process the email address and separate the email by commas */
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

PROCSENDEMAIL ('mail content', 'mail topic ', 'sender', 'recipient', 'mail Server IP address', 'username', 'Password ','', 'base64 ');
Note that the email server must use an ip address, but not a domain name.