Kamran Agayev's Oracle Blog

Recently, I was required by our company to solve an important task related to subscriber payments. 
The subscribers transfer their payment through some post offices. Despite the fact that these post offices use our online payment transfer system, however some of them use their own programs to make payments. In order to view the list of our subscribers and from time to time to update list of subscribers, they need to add new subscribers to their database system. We were also asked to send newly created subscriber codes automatically to these post offices. To put this into practice I followed the steps mentioned below

Firstly, I decided that, I have to:

1. Provide SQL script to prepare subscribers list
2. To make daily schedule
3. To make schedule to run the above mentioned SQL script and write it to a file
4. When prepared, make this file zipped and mailed automatically to relevant person

On the end of this task we’ll be familiar with
1. Creating a file based on SQL script using Oracle UTL_FILE built in Package
2. Creating shell script which runs Sql procedure
3. Sending e-mail with attachment using Oracle UTL_SMTP built in Package
4. Using cron to schedule a job in Linux
5. Combine all together and automate them

Lets implement above mentioned steps by explaining them one by one

1. First of all we create SQL script and get the list of subscriber codes.  As it is not permissible to view our main table’s structure online, we create virtual code table and use it during this process

CREATE TABLE subscriber_list (kod VARCHAR2(5))
/
INSERT INTO subscriber_list VALUES(‘11111’)
/
INSERT INTO subscriber_list VALUES(‘65498’)
/
INSERT INTO subscriber_list VALUES(‘78946’)
/
INSERT INTO subscriber_list VALUES(‘55666’)
/
INSERT INTO subscriber_list VALUES(‘46667’)
/
COMMIT

Our SQL script will be as simple as our table

SELECT * FROM subscriber_list;

2. Each time when this SQL script is called, its output should be written to file and file name should bear today’s date

In order to create this file we use UTL_FILE package and create a procedure. To create the file in a proper directory, we need to create a directory object

CREATE DIRECTORY export_dir AS ‘/home/oracle/export’;

And create the following procedure to create the file:

CREATE OR REPLACE PROCEDURE proc_export_file AS
        CURSOR cur_kod IS
                SELECT kod from subscriber_list;

        CURSOR cur_date IS
                SELECT sysdate FROM dual;

rec1 cur_kod%ROWTYPE;
rec2 cur_date%ROWTYPE;
created_file_name VARCHAR2(100);
file_name UTL_FILE.FILE_TYPE;

BEGIN
 –Here, we open cur_date cursor, get today’s date and set it to created_file_name variable
 OPEN cur_date;
       
 LOOP
                FETCH cur_date INTO rec2;
                EXIT WHEN cur_date%NOTFOUND;
                created_file_name:=rec2.sysdate;
 
  –We send parameter ‘w’ (to create the file), file name and directory name to UTL_FILE.FOPEN function to create this file with .exp extention
       
         file_name:=UTL_FILE.FOPEN(‘EXPORT_DIR’,created_file_name||’.exp’,’W’);
                OPEN cur_kod;
                LOOP

   –We open our mail cursor and get all rows and put it to our file line by line
                 FETCH cur_kod INTO rec1;
                        EXIT WHEN cur_kod%NOTFOUND;
                        UTL_FILE.PUTF(file_name,’%s\n’,rec1.kod);
                END LOOP;
                UTL_FILE.FCLOSE(file_name);
        END LOOP;

CLOSE cur_kod;

CLOSE cur_date;
END proc_export_file;
/

Here, we declared 2 cursors. One of them set today’s date to a variable called created_file_name. Then we create a file using UTL_FILE.FOPEN and ‘w’ parameter, give it a name which we have obtained from the first cursor (today’s date) and then open our second cursor to get all rows from subscriber table and insert them to file with .exp extension

3. In this step we create a shell script which calls above mentioned procedure. Its name will be export_code.sh. We give it execute permission to make it executable

[oracle@localhost ~]

# We move to /home/oracle directory
cd /home/oracle  

#Create new directory to save file that will be created using proc_export_file procedure
mkdir export

#Give it permission
chmod 755 export

#Change directory
cd export

#Create export_code.sh file
touch export_code.sh

#Give it executable permission
chmod 755 export_code.sh

#Open it in order to change its source
vi export_code.sh

4. Shell Script’s source is as follows (Don’t forget to change your ORACLE_SID value in this script)

###################### Shell Script to create file using procedure, zip and send it as an attachment to relevant address ####################
#!/bin/sh

#Export Oracle Environment Variables
export ORACLE_HOME=/home/oracle/OraHome_1
export ORACLE_SID=#######
export PATH=/home/oracle/OraHome_1/bin:$PATH

#Open sqlplus and connect as sys user with sysdba privilege
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect / as sysdba

#Run proc_export_file procedure to create file
exec proc_export_file;
exit;
EOF

#Change directory to /home/oracle/export
cd /home/oracle/export

#Find newly created .exp file, take its name, zip it by using its name with tar utility
tar -cvf “$(ls *.exp).tar” “$(ls *.exp)”

#Remove original .exp file,
rm -rf *.exp

#Get list of tar file in /home/oracle/export directory. As there’s only one file, the file name will be set to tar_file variable
tar_file=’/home/oracle/export/*.tar’

#Here we connect to sqlplus and use email_files procedure to send mail
$ORACLE_HOME/bin/sqlplus /nolog <<EOF
connect / as sysdba
exec email_files(from_name=>’kamran.agayev@bes.az’,to_names=>’kamran.agayev@bes.az’, subject=> ‘Subscriber codes’,filename1=>’$(printf $tar_file)’, filetype1=> ‘application/x-tar’);
exit
EOF

#Remove .tar file, because we’ve just sent it as an attachment
rm -rf /home/oracle/export/*.tar

######################### Shell Script to create file using procedure, zip and send it as an attachment to relevant address ##########################

Let’s examine this script step by step

In the first three lines, we set Oracle environment variables to their proper values to use sqlplus and connect to our database. Then we call SqlPlus with nolog option. The code that between <<EOF and EOF,  will be used by SqlPlus. Firstly, we connect to our database as sys user and call our newly created procedure named proc_export_file to create a file with subscriber codes and then exit SqlPlus

Then we switch to the directory where our file have been created and zip it. As our file is created with .exp extension and it is only one file in this directory with such extension, we list it and get its name by “$(ls *.exp)” command. By using tar command with cvf parameter and “($ls *.exp).tar” command, we find the file with .exp extension and zip it using its own name and by adding tar extension

Then, as we’ve obtained zipped format of .exp file, we delete original .exp file with rm -rf *.exp command

In the next step, we create variable tar_file and get the name of only one newly created tar file from /home/oracle/export directory and set it to this variable

Then again, we connect to SqlPlus with sys user and run email_files procedure. This procedure, is used to send mail with attachment to defined recipient. In the from_name parameter we write our mail, in the to_name parameter we write recipient mail, in the subject parameter we write mail’s subject, in the message parameter, we write mail’s text and in the filename1 parameter, we write newly created zipped file’s name. As we’ve set its name to tar_file variable, we can use this variable inside our script, using $(printf $tar_file) command

In order to create email_files procedure, we use below mentioned code. The v_smtp_server and v_smtp_server_port variables which we use in this procedure, are our SMPT Server’s IP address and Port number. Change it and make it to be correct values to be able to send mail

———– This procedure used to send file as attachment using UTL_SMTP package ————

create or replace procedure email_files(from_name varchar2,
                      to_names varchar2,
                      subject varchar2,
                      message varchar2 default null,
                      html_message varchar2 default null,
                      cc_names varchar2 default null,
                      bcc_names varchar2 default null,
                      filename1 varchar2 default null,
                      filetype1 varchar2 default ‘text/plain’,
                      filename2 varchar2 default null,
                      filetype2 varchar2 default ‘text/plain’,
                      filename3 varchar2 default null,
                      filetype3 varchar2 default ‘text/plain’)
is

   — Change the SMTP host name and port number below to your own values,
   — if not localhost on port 25:

   smtp_host          varchar2(256) := ‘####################’;
   smtp_port          number := 25;

   boundary           constant varchar2(256) := ‘CES.Boundary.DACA587499938898’;

   recipients         varchar2(32767);
   directory_path     varchar2(256);
   file_name          varchar2(256);
   crlf               varchar2(2):= chr(13) || chr(10);
   mesg               varchar2(32767);
   conn               UTL_SMTP.CONNECTION;
   type varchar2_table is table of varchar2(256) index by binary_integer;
   file_array         varchar2_table;
   type_array         varchar2_table;
   i                  binary_integer;

   PROCEDURE split_path_name(file_path IN VARCHAR2, directory_path OUT VARCHAR2,
      file_name OUT VARCHAR2) IS

      pos number;

   begin

      — Separate the filename from the directory name

      pos := instr(file_path,’/’,-1);
      if pos = 0 then
         pos := instr(file_path,’\’,-1);
      end if;
      if pos = 0 then
         directory_path := null;
      else
         directory_path := substr(file_path,1,pos – 1);
      end if;
      file_name := substr(file_path,pos + 1);

   end;

   — Procedure to append a file’s contents to the e-mail

   PROCEDURE append_file(directory_path IN VARCHAR2, file_name IN VARCHAR2,
      file_type IN VARCHAR2, conn IN OUT UTL_SMTP.CONNECTION) IS

      generated_name  varchar2(30) := ‘CESDIR’ || to_char(sysdate,’HH24MISS’);
      directory_name  varchar2(30);
      file_handle     utl_file.file_type;
      bfile_handle    bfile;
      bfile_len       number;
      pos             number;
      read_bytes      number;
      line            varchar2(1000);
      data            raw(200);
      my_code         number;
      my_errm         varchar2(32767);

   begin

      begin

         — Grant access to the directory, unless already defined, and open
         — the file (as a bfile for a binary file, otherwise as a text file).
   
         begin
            line := directory_path;
            select dd.directory_name into directory_name from dba_directories dd
               where dd.directory_path = line and rownum = 1;
         exception
            when no_data_found then
               directory_name := generated_name;
         end;
         if directory_name = generated_name then
            execute immediate ‘create or replace directory ‘ || directory_name ||
               ‘ as ”’ || directory_path || ””;
            execute immediate ‘grant read on directory ‘ || directory_name ||
               ‘ to public’;
         end if;
         if substr(file_type,1,4) != ‘text’ then
            bfile_handle := bfilename(directory_name,file_name);
            bfile_len := dbms_lob.getlength(bfile_handle);
            pos := 1;
            dbms_lob.open(bfile_handle,dbms_lob.lob_readonly);
         else
            file_handle := utl_file.fopen(directory_name,file_name,’r’);
         end if;
   
         — Append the file contents to the end of the message
   
         loop
   
            — If it is a binary file, process it 57 bytes at a time,
            — reading them in with a LOB read, encoding them in BASE64,
            — and writing out the encoded binary string as raw data
   
            if substr(file_type,1,4) != ‘text’ then
               if pos + 57 – 1 > bfile_len then
                  read_bytes := bfile_len – pos + 1;
               else
                  read_bytes := 57;
               end if;
               dbms_lob.read(bfile_handle,read_bytes,pos,data);
               utl_smtp.write_raw_data(conn,utl_encode.base64_encode(data));
               pos := pos + 57;
               if pos > bfile_len then
                  exit;
               end if;
   
            — If it is a text file, get the next line of text, append a
            — carriage return / line feed to it, and write it out
   
            else
               utl_file.get_line(file_handle,line);
               utl_smtp.write_data(conn,line || crlf);
            end if;
     
         end loop;
   
      — Output any errors, except at end when no more data is found
   
      exception
         when no_data_found then
            null;
         when others then
            my_code := SQLCODE;
            my_errm := SQLERRM;
            dbms_output.put_line(‘Error code ‘ || my_code || ‘: ‘ ||
               my_errm);
      end;

      — Close the file (binary or text)

      if substr(file_type,1,4) != ‘text’ then
         dbms_lob.close(bfile_handle);
      else
         utl_file.fclose(file_handle);
      end if;
      if directory_name = generated_name then
         execute immediate ‘drop directory ‘ || directory_name;
      end if;

   end;

begin

   — Load the three filenames and file (mime) types into an array for
   — easier handling later

   file_array(1) := filename1;
   type_array(1) := filetype1;
   — Open the SMTP connection and set the From and To e-mail addresses

   conn := utl_smtp.open_connection(smtp_host,smtp_port);
   utl_smtp.helo(conn,smtp_host);
   recipients := from_name;
   utl_smtp.mail(conn,recipients);
   recipients := to_names;
   utl_smtp.rcpt(conn,recipients);
   utl_smtp.open_data(conn);

   — Build the start of the mail message

   mesg := ‘Date: ‘ || TO_CHAR(SYSDATE,’dd Mon yy hh24:mi:ss’) || crlf ||
      ‘From: ‘ || from_name || crlf ||
      ‘Subject: ‘ || subject || crlf ||
      ‘To: ‘ || to_names || crlf;
   mesg := mesg || ‘Mime-Version: 1.0’ || crlf ||
      ‘Content-Type: multipart/mixed; boundary=”‘ || boundary || ‘”‘ ||
      crlf || 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;
   utl_smtp.write_data(conn,mesg);

   — Write the text message or message file, if any

   if message is not null then
      mesg := ‘–‘ || boundary || crlf ||
         ‘Content-Type: text/plain; name=”message.txt”; charset=US-ASCII’ ||
          crlf ||
         ‘Content-Disposition: inline; filename=”message.txt”‘ || crlf ||
         ‘Content-Transfer-Encoding: 7bit’ || crlf || crlf;
      utl_smtp.write_data(conn,mesg);
      if substr(message,1,1) = ‘/’ then
         split_path_name(message,directory_path,file_name);
         append_file(directory_path,file_name,’text’,conn);
         utl_smtp.write_data(conn,crlf);
      else
         utl_smtp.write_data(conn,message || crlf);
      end if;
   end if;

   — Append the files
      if file_array(1) is not null then

         split_path_name(file_array(1),directory_path,file_name);

         — Generate the MIME boundary line according to the file (mime) type
         — specified.

         mesg := crlf || ‘–‘ || boundary || crlf;
         if substr(type_array(1),1,4) != ‘text’ then
            mesg := mesg || ‘Content-Type: ‘ || type_array(1) ||
               ‘; name=”‘ || file_name || ‘”‘ || crlf ||
               ‘Content-Disposition: attachment; filename=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Transfer-Encoding: base64’ || crlf || crlf ;
         else
            mesg := mesg || ‘Content-Type: application/octet-stream; name=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Disposition: attachment; filename=”‘ ||
               file_name || ‘”‘ || crlf ||
               ‘Content-Transfer-Encoding: 7bit’ || crlf || crlf ;
         end if;
         utl_smtp.write_data(conn,mesg);

         — Append the file contents to the end of the message

         append_file(directory_path,file_name,type_array(1),conn);
         utl_smtp.write_data(conn,crlf);

      end if;
  

   — Append the final boundary line

   mesg := crlf || ‘–‘ || boundary || ‘–‘ || crlf;
   utl_smtp.write_data(conn,mesg);

   — Close the SMTP connection

   utl_smtp.close_data(conn);
   utl_smtp.quit(conn);

end;
/

———– This procedure used to send file as attachment using UTL_SMTP package ————

5. Finaly we have SQL script, procedure that writes its result to file, shell script that runs this procedure, zips and sends the file as attachment to required mail

In the end, we need to create schedule to automatically implement it independently. In Linux we can do it with cron. We need to create cron, make it run within time interval

In Linux system, we need to follow below mentioned steps to create a cron with any username

[oracle@localhost ~] crontab /etc/crontab
With this command we make /etc/crontab file as oracle user’s default cron file. Based on the text of this file, crond service defines the processes and their run times

No we change this crontab’s text and make it to run our shell script in our desired time. To change this file’s text, run:

[oracle@localhost ~]crontab -e

We have to note that, when you run crontab -e command, standard editor vi is opening. In order to change the file opened with this editor, type “i” and change the file

To make shell script run every night, we add this line into our cron file

0 0 * * * /home/oracle/export/export_code.sh

When changes made, type “:wq” to save these changes and exit

To create a specific cron setting you need to understand a bit about the way a command is written for cron. The best way to help you understand is to show you some examples.

Remember that a cron entry is specified in the following sequence: Minute (0-59)  Hour (0-23)  Day of Month (1-31)  Month (1-12 or Jan-Dec)  Day of Week (0-6 or Sun-Sat)  Command

The line we added will run the shell script named export_code.sh file at midnight, of every month, for all days of the week. The 0 is in place of Hour and minutes, and represents midnight (0-23 of a 24 hour clock).

[oracle@localhost ~] crontab -l
By running crontab -l command, we can see connected user’s crontab file entry

Our script is ready to use. Let’s run and test it
[oracle@localhost ~] ./export_code.sh

Now, this script will run, create a file, zip it and send it as attachment to our mail