Send Email From Oracle Database

 --Following are the steps to send email from oracle database--

CREATE DIRECTORY MYEMAIL AS 'C:\file_data';

GRANT READ,WRITE ON DIRECTORY MYEMAIL TO SCOTT;

-----------------------------------

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_attach_name IN VARCHAR2 DEFAULT NULL,

                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,

                                       p_attach_blob IN BLOB DEFAULT NULL,

                                       p_smtp_host   IN VARCHAR2,

                                       p_smtp_port   IN NUMBER DEFAULT 25)

                                                                            

AS

  l_mail_conn   UTL_SMTP.connection;

  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';

  l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573

BEGIN

  

DBMS_OUTPUT.put_line('TEST1');

DBMS_OUTPUT.put_line('TEST1');

  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

  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/mixed; 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_attach_name 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: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP

      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));

    END LOOP;

    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;

----------------------------------------------

CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS

  dest_loc  BLOB := empty_blob();

  src_loc   BFILE := BFILENAME('MYEMAIL', p_file_name);

BEGIN

  

  -- Open source binary file from OS

 DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

  -- Create temporary LOB object

  DBMS_LOB.CREATETEMPORARY(

        lob_loc => dest_loc

      , cache   => true

      , dur     => dbms_lob.session

  );

  -- Open temporary lob

  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

  -- Load binary file into temporary LOB

  DBMS_LOB.LOADFROMFILE(

        dest_lob => dest_loc

      , src_lob  => src_loc

      , amount   => DBMS_LOB.getLength(src_loc));

  -- Close lob objects

  DBMS_LOB.CLOSE(dest_loc);

  DBMS_LOB.CLOSE(src_loc);

  -- Return temporary LOB object

  RETURN dest_loc;

END loadBlobFromFile;

-------------------------------------------

DECLARE

  l_blob BLOB;

BEGIN

  

  SELECT   loadBlobFromFile('ayaz.pdf')

  INTO   l_blob

  FROM   dual;

 

  

  send_mail(p_to          => 'faisalkhan@nbp.com.pk',

            p_from        => 'faisalkhan@nbp.com.pk',

            p_subject     => 'Message attached',

            p_text_msg    => 'This is a test message',

            p_attach_name => 'ayaz.pdf',

            p_attach_mime => 'image/gif',

            p_attach_blob => l_blob,

            p_smtp_host   => 'nbparray.nbp.com.pk');

END;

/

--------------------------------

Oracle 12c: SEC_CASE_SENSITIVE_LOGON and ORA-1017: invalid username/password; logon denied

In which situations you may receive an ORA-1017?

This is outlined in the Oracle 12.1 documentation already:

• “Ensure that the SEC_CASE_SENSITIVE_LOGON parameter is not set to FALSE if the SQLNET.ALLOWED_LOGON_VERSION_SERVER parameter is set to 12 or 12a. This is because the more secure password versions used for this mode only support case-sensitive password checking. For compatibility reasons, Oracle Database does not prevent the use of FALSE for SEC_CASE_SENSITIVE_LOGON when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a. Setting SEC_CASE_SENSITIVE_LOGON to FALSE when SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12 or 12a causes all accounts to become inaccessible.”

The key is the sqlnet.ora parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER. And here’s the difference between Oracle Database 12.1 and Oracle Database 12.2:

• Oracle Database 12.1: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 11 out of the box
• Oracle Database 12.2: SQLNET.ALLOWED_LOGON_VERSION_SERVER defaults to 12 out of the box

Behavior difference Oracle 12.1 vs Oracle 12.2

See this simple example after switching SEC_CASE_SENSITIVE_LOGON=FALSE in both databases (as shown above):

Oracle Database 12.1.0.2:

SQL> alter user hr identified by hr; 

User altered. 

SQL> connect hr/hr Connected.

Oracle Database 12.2.0.1:

SQL> alter user hr identified by hr; 

User altered. 

SQL> connect hr/hr

 

ERROR: ORA-01017: invalid username/password; logon denied

 

Warning: You are no longer connected to ORACLE.

How to resolve the ORA-1017 error?

The solution is very simple, first you need to edit your sqlnet.ora adding (or lowering) the parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER to a value below 12. But if you try to connect directly after restarting your listener you will receive the same ORA-1017 again. The secret is mentioned in the above documentation link as well: you will have to restart the database along with listner and then recreate the user’s passwords if you need the logon process to work as it did work before Oracle Database 12.2.

sqlnet.ora:

# sqlnet.ora Network Configuration File: Oracle_Home/product/12.2.0.1/network/admin/sqlnet.ora 

# Generated by Oracle configuration tools.  

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11


Check in DBA_USERS:

SQL> select username, password_versions from DBA_USERS where username='HR'; 

USERNAME                              PASSWORD_VERSIONS 

-------------                         --------------------- 

HR                                    11G 12C

There’s no “10G” mentioned. This will prevent the connection.

SQL> connect hr/hr 

ERROR: ORA-01017: invalid username/password; logon denied 

Warning: You are no longer connected to ORACLE.

Still you are getting the same error.

Solution: First restart the database service along with listener service and then after restart, You will have to change the password again using ALTER command:

SQL> alter user hr identified by hr; 

User altered. 

SQL> select username, password_versions from DBA_USERS where username='HR'; 

USERNAME               PASSWORD_VERSIONS 

--------------         ---------------------- 

HR                     10G 11G 12C 

SQL> connect hr/hr 

Connected.