Thursday, August 16, 2018

New Features for PL/SQL in Oracle 18c

Following two new PL/SQL features came with Oracle 18c Database -

PL/SQL Hierarchical Profiler (DBMS_HPROF) Enhancements

PL/SQL Qualified Expressions

Oracle VirtualBox 5.2.18 is here!

Oracle VirtualBox 5.2.18 is here (released August 14 2018)

Just an update in case you missed it- 

For details-
https://www.virtualbox.org/wiki/Changelog#18

The following items were fixed and/or added:

    VMM: See user manual.
    VMM: fix loading with recent binutils and self-built versions of VirtualBox (bug #17851)
    NAT: fix --nataliasmode sameports which is a valid setting (bug #13000)
    VRDP: fixed VM process termination on RDP client disconnect if 3D is enabled for the virtual machine

Monday, August 13, 2018

Sending email from Oracle Database with PL/SQL.

Here is my recent PL/SQL package to send email directly from Oracle database with attachment or without attachment. I used other UTL packages before but this one UTL_TCP looks better. I am using it and found very efficient.

Note: please review it, test it before using any production environment. thanks.

If you face  ORA-24247: network access denied by access control list (ACL) . please find the solution in below link-
https://halimdba.blogspot.com/2015/02/ora-24247-network-access-denied-by.html

 SQL> select * from dba_network_acls;


1) Package specification

 CREATE OR REPLACE PACKAGE SAY_HELLO_FROM_DB  
 IS  
   PROCEDURE pro_send_emails_with_attach (  
    sender_email_address     VARCHAR2,   
    recipient_email_addresses   VARCHAR2,  
    email_subject         VARCHAR2,  
    email_body_text        VARCHAR2,  
    email_attachment_text     CLOB,    
    email_attachment_file_name  VARCHAR2);  
   PROCEDURE pro_send_emails_without_attach (  
    sender_email_address     VARCHAR2,   
    recipient_email_addresses  VARCHAR2,  
    email_subject        VARCHAR2,  
    email_body_text       VARCHAR2);  
 END;  
 /  

2)  Package body


 CREATE OR REPLACE PACKAGE BODY SAY_HELLO_FROM_DB  
 IS  
   PROCEDURE pro_send_emails_with_attach (  
    sender_email_address     VARCHAR2,  
    recipient_email_addresses   VARCHAR2,  
    email_subject         VARCHAR2,  
    email_body_text        VARCHAR2,  
    email_attachment_text     CLOB,  
    email_attachment_file_name  VARCHAR2)  
   IS  
    c                UTL_TCP.connection;  
    rc               BINARY_INTEGER;  
    v_next_column          BINARY_INTEGER;  
    v_recipient_email_length    BINARY_INTEGER;  
    v_recipient_email_addresses   VARCHAR2 (500);  
    v_single_recipient_email_addr  VARCHAR2 (100);  
    j                BINARY_INTEGER;  
    current_position        BINARY_INTEGER := 1;  
    email_attachment_text_segment  CLOB;  
   --- v_date varchar2(100);  
   BEGIN  
    v_recipient_email_addresses := recipient_email_addresses;  
    v_recipient_email_length := LENGTH (v_recipient_email_addresses);  
    v_recipient_email_addresses := v_recipient_email_addresses || ',';  
    v_next_column := 1;  
    IF INSTR (v_recipient_email_addresses, ',') = 0  
    THEN  
      v_single_recipient_email_addr := v_recipient_email_addresses;  
      v_recipient_email_length := 1;  
    END IF;  
    -- -- CHANGE SMTP SERVER'S ip AND port  
    c := UTL_TCP.open_connection ('192.168.X.XXX', 25);  
    rc := UTL_TCP.write_line (c, 'HELO localhost');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    WHILE v_next_column <= v_recipient_email_length  
    LOOP  
      v_single_recipient_email_addr :=  
       SUBSTR (  
         v_recipient_email_addresses,  
         v_next_column,  
          INSTR (v_recipient_email_addresses, ',', v_next_column)  
         - v_next_column);  
      v_next_column :=  
       INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;  
      rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (c,  
                 'RCPT TO: ' || v_single_recipient_email_addr);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc := UTL_TCP.write_line (c, 'DATA');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));  
      ----   v_date := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') ;  
      rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);  
      rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');  
      rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);  
      rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);  
      rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');  
      rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '-------SECBOUND');  
      rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');  
      rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, email_body_text);  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '-------SECBOUND');  
      rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         ' name="' || email_attachment_file_name || '"');  
      rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');  
      rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');  
      rc := UTL_TCP.write_line (c, '');  
      current_position := 1;  
      j := (ROUND (LENGTH (email_attachment_text)) / 32000) + 1;  
      DBMS_OUTPUT.put_line (  
       'j= ' || j || ' length= ' || LENGTH (email_attachment_text));  
      FOR i IN 1 .. j  
      LOOP  
       email_attachment_text_segment :=  
         SUBSTR (email_attachment_text, current_position, 32000);  
       rc := UTL_TCP.write_line (c, email_attachment_text_segment);  
       current_position := current_position + 32000;  
      END LOOP;  
      rc := UTL_TCP.write_line (c, '-------SECBOUND--');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '.');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      DBMS_OUTPUT.put_line (  
         '***** Single E-mail= '  
       || v_single_recipient_email_addr  
       || ' '  
       || v_next_column);  
    END LOOP;  
    rc := UTL_TCP.write_line (c, 'QUIT');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    UTL_TCP.close_connection (c);  
   END;  
   PROCEDURE pro_send_emails_without_attach (  
    sender_email_address     VARCHAR2,  
    recipient_email_addresses  VARCHAR2,  
    email_subject        VARCHAR2,  
    email_body_text       VARCHAR2)  
   IS  
    c                UTL_TCP.connection;  
    rc               BINARY_INTEGER;  
    v_next_column          BINARY_INTEGER;  
    v_recipient_email_length    BINARY_INTEGER;  
    v_recipient_email_addresses   VARCHAR2 (500);  
    v_single_recipient_email_addr  VARCHAR2 (100);  
   BEGIN  
    v_recipient_email_addresses := recipient_email_addresses;  
    v_recipient_email_length := LENGTH (v_recipient_email_addresses);  
    v_recipient_email_addresses := v_recipient_email_addresses || ',';  
    v_next_column := 1;  
    IF INSTR (v_recipient_email_addresses, ',') = 0  
    THEN  
      v_single_recipient_email_addr := v_recipient_email_addresses;  
      v_recipient_email_length := 1;  
    END IF;  
    -- CHANGE SMTP SERVER'S ip AND port  
    c := UTL_TCP.open_connection ('192.168.XX.XX', 25);  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    rc := UTL_TCP.write_line (c, 'HELO localhost');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    WHILE v_next_column <= v_recipient_email_length  
    LOOP  
      v_single_recipient_email_addr :=  
       SUBSTR (  
         v_recipient_email_addresses,  
         v_next_column,  
          INSTR (v_recipient_email_addresses, ',', v_next_column)  
         - v_next_column);  
      v_next_column :=  
       INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;  
      rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (c,  
                 'RCPT TO: ' || v_single_recipient_email_addr);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc := UTL_TCP.write_line (c, 'DATA');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));  
      rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);  
      rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');  
      rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);  
      rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);  
      rc := UTL_TCP.write_line (c, email_body_text);  
      rc := UTL_TCP.write_line (c, '.');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      DBMS_OUTPUT.put_line (  
         '***** Single E-mail= '  
       || v_single_recipient_email_addr  
       || ' '  
       || v_next_column);  
    END LOOP;  
    rc := UTL_TCP.write_line (c, 'QUIT');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    UTL_TCP.close_connection (c);  
   END;  
 END;  
 /  

3)  Calling the procedure for this package for sending email

If you want send to multiple person, please use comma separated  email address as below
In SQL plus -
exec SAY_HELLO_FROM_DB.pro_send_emails_with_attach('Halim@test.com','Halim1@test.com,Halim2@test.com','Test From Database','Check the alter log','have you checked alertlog today?','attention');