Sendmail gmail

  1. Fabiana

    Fabiana Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Brazil
    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;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,704
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I believe you will need an SSL configuration for Oracle to get this to work; there are a number of websites and blogs that can help you set up an SSL configuration for Oracle. Also the support team at Google may be able to assist you with such a configuration.