Mail from ORACLE database by using UTL_SMTP package

This procedure uses the UTL_SMTP package to send an email message.
Up to three file names may be specified as attachments.

  Parameters are:

    1) from_name (varchar2)

    2) to_name   (varchar2)

    3) subject   (varchar2)

    4) message   (varchar2)

    5) max_size  (number)

    5) filename1 (varchar2)

    6) filename2 (varchar2)

    7) filename3 (varchar2)

  eg.

  mail_files( from_name => 'oracle' ,

                    to_name   => 'someone@somewhere.com' ,

                    subject   => 'A test',

                    message   => 'A test message',

                    filename1 => '/data/oracle/dave_test1.txt',

                    filename2 => '/data/oracle/dave_test2.txt');

Most of the parameters are self-explanatory. "message" is a varchar2 parameter, up to 32767 bytes long which contains the text of the message to be placed in the main body of the email.

filename{1,2,3} are the names of the files to be attached to the email. The full pathname of each file must be specified. The files must exist in one of the directories specified in the init. ora parameter

UTL_FILE_DIR. All filename parameters are optional: It is not necessary to specify unused file parameters (eg. filename3 is missing in the above example).

 The max_size parameter enables you to place a constraint on the maximum size of message, including all attachments, that the procedure will send.  If this limit is exceeded, the procedure will truncate the message at  that point with a '*** truncated ***' message. The default is effectively unlimited. However, the text of message body is still limited to 32Kb, as it is passed in as a varchar2.

Obviously, as with any Oracle procedure, the parameter values can (and usually will be) PL/SQL variables, rather than hard-coded literals, as shown here.

You may need to modify the following variable if you don't have a local SMTP service running (particularly relevant to Windows 2000 servers).

CREATE OR REPLACE PROCEDURE send_mail_attachment(  
   from_name VARCHAR2,  
   to_name VARCHAR2,  
   cc_name VARCHAR2 DEFAULT NULL,  
   bcc_name VARCHAR2 DEFAULT NULL,  
   subject VARCHAR2,  
   message VARCHAR2,  
   max_size NUMBER DEFAULT 9999999999,  
   filename1 VARCHAR2 DEFAULT NULL,  
   filename2 VARCHAR2 DEFAULT NULL,  
   filename3 VARCHAR2 DEFAULT NULL,  
   filename4 VARCHAR2 DEFAULT NULL,  
   DEBUG NUMBER DEFAULT 0 )  
 IS  
   v_smtp_server       VARCHAR2(30) := 'MAIL_SERVER_DOMAIN_NAME';  
   v_smtp_server_port  NUMBER := 25; -- SMTP PORT NAME (Default port 25)  
   v_directory_name    VARCHAR2(100);  
   v_file_name         VARCHAR2(100);  
   v_line              VARCHAR2(1000);  
   crlf                VARCHAR2(2):= chr(13) || chr(10);  
   mesg                VARCHAR2(32767);  
   msg_header          VARCHAR2(32767);  
   mesg_len            NUMBER;  
   conn                UTL_SMTP.CONNECTION;  
   TYPE varchar2_table IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;  
   file_array          varchar2_table;  
   i                   BINARY_INTEGER;  
   v_file_handle       utl_file.file_type;  
   v_slash_pos         NUMBER;  
   mesg_too_long       EXCEPTION;  
   invalid_path        EXCEPTION;  
   mesg_length_exceeded BOOLEAN := FALSE;  
   var_length          INTEGER;  
   var_rcpt            VARCHAR2(500);  
   var_i               INTEGER;  
   var_start           INTEGER;  
 BEGIN  
   -- first load the three filenames into an array for easier handling later ...  
   file_array(1) := filename1;  
   file_array(2) := filename2;  
   file_array(3) := filename3;  
   file_array(4) := filename4;  
   -- Open the SMTP connection ...  
   -- ------------------------  
   conn := utl_smtp.open_connection( v_smtp_server, v_smtp_server_port );  
   -- Initial handshaking ...  
   -- -------------------  
   utl_smtp.helo( conn, v_smtp_server );  
   utl_smtp.mail( conn, from_name );  
   -- Handling multiple TO List ...  
   -- ----------------  
   var_i := 1;   
   var_length := nvl(length(to_name), 0);  
   LOOP  
     EXIT WHEN var_i >= var_length;  
     var_start := var_i;  
     LOOP  
      EXIT WHEN var_i >= var_length OR substr(to_name, var_i+1, 1) = ',';  
      var_i := var_i + 1;      
     END LOOP;  
     var_rcpt := substr(to_name, var_start, var_i-var_start+1);  
     utl_smtp.rcpt(conn, var_rcpt);  
     var_i := var_i + 2;  
   END LOOP;   
   -- Handling multiple CC List ...  
   -- ----------------  
   var_i := 1;   
   var_length := nvl(length(cc_name), 0);  
   LOOP  
     EXIT WHEN var_i >= var_length;  
     var_start := var_i;  
     LOOP  
      EXIT WHEN var_i >= var_length or substr(cc_name, var_i+1, 1) = ',';  
      var_i := var_i + 1;      
     END LOOP;  
     var_rcpt := substr(cc_name, var_start, var_i-var_start+1);  
     utl_smtp.rcpt(conn, var_rcpt);  
     var_i := var_i + 2;  
   END LOOP;  
   -- Handling multiple BCC List ...  
   -- --------------------------  
   var_i := 1;   
   var_length := nvl(length(bcc_name), 0);  
   LOOP  
     EXIT WHEN var_i >= var_length;  
     var_start := var_i;  
     LOOP  
      EXIT WHEN var_i >= var_length OR substr(bcc_name, var_i+1, 1) = ',';  
      var_i := var_i + 1;      
     END LOOP;  
     var_rcpt := substr(bcc_name,var_start,var_i-var_start+1);  
     utl_smtp.rcpt(conn, var_rcpt);  
     var_i := var_i + 2;  
   END LOOP;  
   utl_smtp.open_data ( conn );  
   -- build the start of the mail message ...  
   -- -----------------------------------  
   msg_header := 'Date: ' || TO_CHAR( SYSDATE-1/4, 'dd Mon yy hh24:mi:ss' ) || crlf ||  
      'From: ' || from_name || crlf ||  
      'Subject: ' || subject || crlf ||  
      'To: ' || to_name || crlf ||  
      'CC: ' || cc_name || crlf ||  
      'BCC: ' || bcc_name || crlf ||  
      'Mime-Version: 1.0' || crlf ||  
      'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || crlf ||  
      '';  
   utl_smtp.write_data ( conn, msg_header);     
   mesg:= crlf ||  
      'This is a Mime message, which your current mail reader may not' || crlf ||  
      'understand. Parts of the message will appear as text. If the remainder' || crlf ||  
      'appears as random characters in the message body, instead of as' || crlf ||  
      'attachments, then you''ll have to extract these parts and decode them' || crlf ||  
      'manually.' || crlf ||  
      '' || crlf ||  
      '--DMW.Boundary.605592468' || crlf ||  
      'Content-Type: text/plain; charset=US-ASCII' || crlf ||  
      'Content-Disposition: inline; ' || crlf ||  
      'Content-Transfer-Encoding: 7bit' || crlf ||  
      '' || crlf ||  
      message || crlf ;  
   -- To send message as attachment  
   -- In Content-Type  
   mesg_len := length(mesg);  
   IF mesg_len > max_size THEN  
     mesg_length_exceeded := TRUE;  
   END IF;  
   utl_smtp.write_data ( conn, mesg );  
   -- Append the files ...  
   -- ----------------  
   FOR i IN 1..4 LOOP  
     -- Exit if message length already exceeded ...  
     EXIT WHEN mesg_length_exceeded;  
     -- If the filename has been supplied ...  
     IF file_array(i) IS NOT NULL THEN  
      BEGIN  
        -- locate the final '/' or '\' in the pathname ...  
        v_slash_pos := instr(file_array(i), '/', -1 );  
        IF v_slash_pos = 0 THEN  
         v_slash_pos := instr(file_array(i), '\', -1 );  
        END IF;  
        -- separate the filename from the directory name ...  
        v_directory_name := substr(file_array(i), 1, v_slash_pos - 1 );  
        v_file_name   := substr(file_array(i), v_slash_pos + 1 );  
        -- open the file ...  
        v_file_handle := utl_file.fopen(v_directory_name, v_file_name, 'r' );  
        -- generate the MIME boundary line ...  
        mesg := crlf || '--DMW.Boundary.605592468' || crlf ||  
        'Content-Type: application/octet-stream; name="' || v_file_name || '"' || crlf ||  
        'Content-Disposition: attachment; filename="' || v_file_name || '"' || crlf ||  
        'Content-Transfer-Encoding: 7bit' || crlf || crlf ;  
        mesg_len := mesg_len + length(mesg);  
        utl_smtp.write_data ( conn, mesg );  
        -- and append the file contents to the end of the message ...  
        LOOP  
          utl_file.get_line(v_file_handle, v_line);  
          IF mesg_len + length(v_line) > max_size THEN  
           mesg := '*** truncated ***' || crlf;  
           utl_smtp.write_data ( conn, mesg );  
           mesg_length_exceeded := TRUE;  
           RAISE mesg_too_long;  
          END IF;  
          mesg := v_line || crlf;  
          utl_smtp.write_data ( conn, mesg );  
          mesg_len := mesg_len + length(mesg);  
        END LOOP;  
      EXCEPTION  
        WHEN utl_file.invalid_path THEN  
          IF DEBUG > 0 THEN  
           dbms_output.put_line('Error in opening attachment '|| file_array(i) );  
          END IF;  
        WHEN UTL_SMTP.INVALID_OPERATION THEN  
          dbms_output.put_line(' Invalid Operation in SMTP transaction.');  
        WHEN UTL_SMTP.TRANSIENT_ERROR THEN  
          dbms_output.put_line(' Temporary problems with sending email - try again later.');  
        WHEN UTL_SMTP.PERMANENT_ERROR THEN  
          dbms_output.put_line(' Errors in code for SMTP transaction.');  
          -- All other exceptions are ignored ....  
        WHEN OTHERS THEN  
          NULL;  
      END;  
      mesg := crlf;  
      utl_smtp.write_data ( conn, mesg );  
      -- close the file ...  
      utl_file.fclose(v_file_handle);  
     END IF;  
   END LOOP;  
   -- append the final boundary line ...  
   mesg := crlf || '--DMW.Boundary.605592468--' || crlf;  
   utl_smtp.write_data ( conn, mesg );  
   -- and close the SMTP connection ...  
   utl_smtp.close_data( conn );  
   utl_smtp.quit( conn );  
 END; 

--Execution Example

 BEGIN  
   send_mail_attachment    (from_name  => 'abc@domain_name.com',  
                         to_name    => 'xyz@domain_name.com',  
                         cc_name    => null,  
                         bcc_name   => mill,  
                         subject      => 'Test Mail',  
                         message    => 'Hello',  
                         filename1   => 'abc.jpg',  
                         filename2   => 'pqr.pdf',  
                         filename3   => null,  
                         filename4   => null );  
 END; 

Database Trigger with Example

A trigger:

·         Is a PL/SQL block or a PL/SQL procedure associated with a table, view, schema, or database

·         Executes implicitly whenever a particular event takes place

·         Can be either of the following:

Application trigger: Fires whenever an event occurs with a particular application

Database trigger: Fires whenever a data event (such as DML) or system event (such as logon or shutdown) occurs on a schema or database.

Guidelines for Designing Triggers:

•       You can design triggers to:

–      Perform related actions

–      Centralize global operations

•       You must not design triggers:

–      Where functionality is already built into the Oracle server

–      That duplicate other triggers

•       You can create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy.

•       The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.

Creating DML Triggers:

CREATE [OR REPLACE] TRIGGER trigger_name  

  timing  

  event1 [OR event2 OR event3]  

 ON object_name  

 [[REFERENCING OLD AS old | NEW AS new] 

  FOR EACH ROW  

  [WHEN (condition)]]  

 trigger_body  

•       A statement trigger fires once for a DML statement.

•       A row trigger fires once for each row affected.

Note: Trigger names must be unique with respect to other triggers in the same schema.

Types of DML Triggers:

The trigger type determines if the body executes for each row or only once for the triggering statement.

•       A statement trigger:

–      Executes once for the triggering event

–      Is the default type of trigger

–      Fires once even if no rows are affected at all

•       A row trigger:

–      Executes once for each row affected by the triggering event

–      Is not executed if the triggering event does not affect any rows

–      Is indicated by specifying the FOR EACH ROW clause

Trigger Timing:

When should the trigger fire?

•       BEFORE: Execute the trigger body before the triggering DML event on a table.

•       AFTER: Execute the trigger body after the triggering DML event on a table.

•       INSTEAD OF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

Note: If multiple triggers are defined for the same object, then the order of firing triggers is arbitrary

Trigger Event Types and Body:

A trigger event:

•       Determines which DML statement causes the trigger to execute

•       Types are:

–      INSERT

–      UPDATE [OF column]

–      DELETE

A trigger body:

•       Determines what action is performed

Is a PL/SQL block or a CALL to a procedure.

Creating a DML Statement Trigger:

CREATE OR REPLACE TRIGGER secure_emp  

 BEFORE INSERT ON employees BEGIN  

  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR  

    (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00') THEN  

  RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||' 

    into EMPLOYEES table only during ' ||' business hours.');  

  END IF;  

 END; 

Testing SECURE_EMP:

INSERT INTO employees (employee_id, last_name,first_name, email, 

                       hire_date,job_id, salary, department_id)  

 VALUES     (300, 'Smith', 'Rob', 'RSMITH', 

            SYSDATE,'IT_PROG', 4500, 60);  

Using Conditional Predicates:

CREATE OR REPLACE TRIGGER secure_emp BEFORE  

 INSERT OR UPDATE OR DELETE ON employees BEGIN  

  IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR  

   (TO_CHAR(SYSDATE,'HH24')   

     NOT BETWEEN '08' AND '18') THEN  

   IF DELETING THEN RAISE_APPLICATION_ERROR(  

   -20502,'You may delete from EMPLOYEES table'||  

       'only during business hours.');  

   ELSIF INSERTING THEN RAISE_APPLICATION_ERROR(  

   -20500,'You may insert into EMPLOYEES table'||   

       'only during business hours.');  

   ELSIF UPDATING('SALARY') THEN  

   RAISE_APPLICATION_ERROR(-20503, 'You may '||

        'update SALARY only during business hours.');  

   ELSE RAISE_APPLICATION_ERROR(-20504,'You may'||  

    ' update EMPLOYEES table only during'||  

    ' normal hours.');  

   END IF;  

  END IF;  

 END;  

Creating a DML Row Trigger:

CREATE OR REPLACE TRIGGER restrict_salary  

 BEFORE INSERT OR UPDATE OF salary ON employees  

 FOR EACH ROW  

 BEGIN  

  IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP'))  

    AND :NEW.salary > 15000 THEN  

   RAISE_APPLICATION_ERROR (-20202,  

    'Employee cannot earn more than $15,000.');  

  END IF;  

 END;  

 /  

Using OLD and NEW Qualifiers:

CREATE OR REPLACE TRIGGER audit_emp_values  

 AFTER DELETE OR INSERT OR UPDATE ON employees  

 FOR EACH ROW  

 BEGIN  

  INSERT INTO audit_emp(user_name, time_stamp, id,  

   old_last_name, new_last_name, old_title,  

   new_title, old_salary, new_salary)  

  VALUES (USER, SYSDATE, :OLD.employee_id,  

   :OLD.last_name, :NEW.last_name, :OLD.job_id,  

   :NEW.job_id, :OLD.salary, :NEW.salary);  

 END;  

 /  

Restricting a Row Trigger: Example

CREATE OR REPLACE TRIGGER derive_commission_pct  

 BEFORE INSERT OR UPDATE OF salary ON employees  

 FOR EACH ROW  

 WHEN (NEW.job_id = 'SA_REP')  

 BEGIN  

  IF INSERTING THEN  

   :NEW.commission_pct := 0;  

  ELSIF :OLD.commission_pct IS NULL THEN  

   :NEW.commission_pct := 0;  

  ELSE   

   :NEW.commission_pct := :OLD.commission_pct+0.05;  

  END IF;  

 END;  

 /