title: Send email from gmail using PL/SQL, Oracle
body: <p>I'm trying to build a procedure to send an email from 'Gmail' account to another receipt. Can you help me to make a procedure to complete this objective. What I need is just call the procedure to send the 'gmail'. If it is necessary to cc the mail. I have mentioned the code below which I tried. Thank you!</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE sendMail (
  smtpHost IN VARCHAR2,
  smtpPort IN PLS_INTEGER DEFAULT 25,
  mailFrom IN VARCHAR2,
  rcptTo IN VARCHAR2,
  --ccs IN t_ccs,
  messageSubject IN VARCHAR2,
  messageBody IN VARCHAR2,
  username IN VARCHAR2,
  password IN VARCHAR2)
  IS
  l_conn UTL_SMTP.connection;
  l_ccs VARCHAR2(2000);
  
  l_encoded_username VARCHAR2(2000);
  l_encoded_password VARCHAR2(2000);
  BEGIN
  --open connection
  --l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  --UTL_SMTP.helo(l_conn, smtpHost);
  
  
  l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
  l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
  l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
  UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
  UTL_SMTP.command(l_conn, l_encoded_username);
  UTL_SMTP.command(l_conn, l_encoded_password);
  
  --prepare headers
  UTL_SMTP.mail(l_conn, mailFrom);
  UTL_SMTP.rcpt(l_conn, rcptTo);
  
  /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
  however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
  /*IF ccs IS NOT NULL THEN
  FOR i IN ccs.FIRST..ccs.LAST LOOP
  UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
  l_ccs:=l_ccs||ccs(i)||',';--mark as CC
  END LOOP;
  --now remove the trailing comma at the end of l_ccs
  l_ccs:=substr(l_ccs,0,length(l_ccs)-1 );
  END IF; */
  
  --start multi line message
  UTL_SMTP.open_data(l_conn);
  
  --prepare mail header
  /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
  and the mail date will appear as 01/01/1970*/
  UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);
  
  --include the message body
  UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);
  
  --send the email
  UTL_SMTP.close_data(l_conn);
  UTL_SMTP.quit(l_conn);
  END; 
  </code></pre>