Home » SQL & PL/SQL » SQL & PL/SQL » Procedure SendMail Gmail (Oracle 11)
Procedure SendMail Gmail [message #677282] Mon, 09 September 2019 14:14
fabissilva
Messages: 1
Registered: September 2019
Location: Brazil
Junior Member

I'm Fabiana and I developed in PL / SQL a program that sends an email that is working perfectly. However, the email server has been changed to Gmail, which requires authentication and the connection type is SSL. Could you help me with these changes?
The database version is 11.



CREATE OR REPLACE PROCEDURE PRC_JUN_SEND_MAIL
(
TO_NAME VARCHAR2,
SUBJECT VARCHAR2,
MESSAGE VARCHAR2
)
IS
L_BODY VARCHAR2(32767);

L_MAILHOST VARCHAR2(64) := ''smtp.gmail.com'';
P_USERNAME_ VARCHAR2(50) := ''admin@jun.net'';
P_PASSWORD_ VARCHAR2(50) := ''***********'';
L_DE VARCHAR2(64) := ''Remetente'';
L_FROM VARCHAR2(64) := ''admin@jun.net'';
l_port VARCHAR(7) := ''587'';
L_MAIL_CONN UTL_SMTP.CONNECTION;


BEGIN
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(L_MAILHOST,l_port);
Dbms_Output.Put_Line (''apos conectar'');
UTL_SMTP.HELO(L_MAIL_CONN, L_MAILHOST);

Dbms_Output.Put_Line (''hello'');
UTL_SMTP.COMMAND (L_MAIL_CONN, ''AUTH LOGIN'');
Dbms_Output.Put_Line (''AUTH LOGIN'');
UTL_SMTP.COMMAND (L_MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USERNAME_))));
UTL_SMTP.COMMAND (L_MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASSWORD_))));
UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);
UTL_SMTP.RCPT(L_MAIL_CONN, TO_NAME);

UTL_SMTP.OPEN_DATA(L_MAIL_CONN);

UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''FROM:'' ||L_DE||''<''|| L_FROM|| ''>'' || UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''TO:'' ||TO_NAME||UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''SUBJECT:'' ||SUBJECT||UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''CONTENT-TYPE: TEXT/HTML; CHARSET=ISO-8859-1''||UTL_TCP.CRLF));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, '' ''||UTL_TCP.CRLF);

UTL_SMTP.WRITE_RAW_DATA(L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF||MESSAGE));

UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);

UTL_SMTP.QUIT(L_MAIL_CONN);
END;
Previous Topic: BI Publisher Apex web Services
Next Topic: ORA-00001: unique constraint On Merge Insert
Goto Forum:
  


Current Time: Wed Oct 16 11:58:03 CDT 2019