Use oracle to send emails

Welcome to the Oracle community forum and interact with 2 million technical staff to access RETURNL_FILE; END; terminal Delete directory ---------------------------------------- PROCEDUREDROP_DIRECTORY (P_DIRECTORY_NAMEVARCHAR2) ISBEGINEXECUTE

Welcome to the Oracle community forum and interact with 2 million technical staff> go to RETURN L_FILE; END; terminal to delete directory -------------------------------------- PROCEDURE DROP_DIRECTORY (P_DIRECTORY_NAME VARCHAR2) IS BEGIN EXECUTE

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

RETURN L_FILE;

END;

--------------------------------------------- Delete directory ------------------------------------

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

-------

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

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 -- indicates the email address to be processed.

-- '<>' 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 ------------------------------------------

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

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

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 = "'|

Rochelle filename | '"');

ELSE

WRITE_DATA (CONN, 'content-disposition', 'attachment; filename = "'|

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

[1] [2] [3]