Pular para o conteúdo principal

Send authenticated email using UTL_SMTP

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_text_msg  IN VARCHAR2 DEFAULT NULL,
                                       p_html_msg  IN VARCHAR2 DEFAULT NULL,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 587)
AS
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN


-- Autentication Configuration
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
utl_smtp.command(l_mail_conn,'AUTH LOGIN');
utl_smtp.command(l_mail_conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('denilson@denilson.com.br'))));
utl_smtp.command(l_mail_conn,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('password'))));

  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.open_data(l_mail_conn);
 
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, p_text_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  IF p_html_msg IS NOT NULL THEN
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);
  UTL_SMTP.quit(l_mail_conn);
END;
/

Execute Procedure

DECLARE
  l_html VARCHAR2(32767);
BEGIN
     l_html := '<html>
    <head>
      <title>Test HTML message</title>
    </head>
    <body>
      <p>TESTE DE ENVIO DE EMAIL UTILIZANDO SMTP AUTENTICADO</p>
      <p><a href="http://dicasoracledba.blogspot.com" /> Dicas Oracle DBA </a>
    </body>
  </html>';

  send_mail(p_to        => 'denilson@denilson.com.br',
            p_from      => 'denilson@denilson.com.br',
            p_subject   => 'teste',
            p_text_msg  => l_html,
            p_html_msg  => l_html,
            p_smtp_host => 'smtp.denilson.com.br',
            p_smtp_port => '587'          
            );
END;
/

Marcadores

Marcadores:

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Assign Backup Policy for Boot and Block Volumes - OCI

To get Availability Domain oci iam availability-domain list  #!/bin/bash OCI_AD="hQsi:SA-SAOPAULO-1-AD-1" OCI_COMPARTMENT="ocid1.compartment.oc1..aaaaaaaavgyjpoffz5qliieawamjaksfb6juxejurtduclqjkvv66ookcxja" #Get Silver policy ocid funct_get_policy(){ OCI_POL_OCID=$(oci bv volume-backup-policy list | jq -r '.data[] | select(."display-name"|contains("silver"))' | jq -r '.id') } funct_boot_vol (){ #Create a file with all boot volume ocid oci bv boot-volume list --availability-domain ${OCI_AD} -c  ${OCI_COMPARTMENT} | jq -r '.data[].id' > boot-vol.txt while IFS="" read -r p || [ -n "$p" ] do   printf 'Seeting backup policy for %s\n' "$p"   oci bv volume-backup-policy-assignment create  --asset-id  $p  --policy-id ${OCI_POL_OCID} done < boot-vol.txt } funct_block_vol (){ #Create a file with all block volume ocid oci bv volume list  -c ${OCI_COMPARTMENT} | jq -r '.data[].id' ...
Postar um comentário
Leia mais

OHS 12c noprompt password when stop/start component

cd $OHS_DOMAIN/bin $ ./stopComponent.sh ohs1 Stopping System Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands Reading domain from /u01/oracle/domains/ohs_domain Please enter your password : password Connecting to Node Manager ... Successfully Connected to Node Manager. Killing server ohs1 ... Successfully killed server ohs1 Successfully disconnected from Node Manager. Exiting WebLogic Scripting Tool. Done $ OHS_HOME/oracle_common/common/bin $ wlst.sh readDomain('/u01/oracle/domains/ohs_domain') ohs_domain>cd('/SecurityConfiguration/ohs_domain') set('NodeManagerUsername','weblogic') set('NodeManagerPasswordEncrypted','password') updateDomain() closeDomain() exit() $ ./startComponent.sh ohs1  Starting system Component ohs1 ... Initializing WebLogic Scripting Tool (WLST) ... Welco...
Postar um comentário
Leia mais

Configurar Tamanho de UNDO e tempo de RETENÇÃO (UNDO_RETENTION)

-- Tamanho Undo Atual SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts#; -- Blocos de UNDO por Seg. SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM v$undostat; --Tamanho do Bloco SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]" FROM v$parameter WHERE name = 'db_block_size'; -- Select calcula UNDO RETENTION SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' ...
2 comentários
Leia mais