Email a File from the Oracle Application Server

Below is the most useful code snippet, that can be used to read a file from the application server or any concurrent program output file from the desired location

*********************************************************

declare

l_conn           utl_smtp.connection;
l_file_handle    UTL_FILE.FILE_TYPE;

l_dirpath        VARCHAR2 (150) := '/usr/tmp/';
l_filename       VARCHAR2 (50) :=  'Test.txt';
l_sender         VARCHAR2 (50) := 'abc@gmail.com';
l_recpnt         VARCHAR2 (255):= 'xyz@gmail.com';
l_msg            VARCHAR2 (32767);
l_line           VARCHAR2 (1000);
lc_message       VARCHAR2 (1000);
crlf             VARCHAR2 (2)  := CHR (13) || CHR (10);

l_reply          UTL_SMTP.REPLY;
l_clob           CLOB := '';
l_subject        VARCHAR2(2000) := 'This is a sample test email';
l_body           VARCHAR2(2000) := 'Body email';
l_boundary       VARCHAR2(50) := '----=*#abc1234321cba#*=';
ln_amount        NUMBER := 0;
ln_temp          NUMBER := 0;
l_filedata       VARCHAR(32767);

begin

l_conn := utl_smtp.Open_Connection('127.0.0.1', 25);

utl_smtp.Helo(l_conn, '127.0.0.1');
utl_smtp.Mail(l_conn, l_sender);
utl_smtp.rcpt(l_conn, l_recpnt);
utl_smtp.open_data(l_conn);
l_filedata := l_filedata || 'Date:' || TO_CHAR(SYSDATE, 'DD-MON-RRRR HH24:MI:SS') || crlf;
l_filedata := l_filedata || 'To: ' || l_recpnt || crlf;
l_filedata := l_filedata || 'From: ' || l_sender || crlf;
l_filedata := l_filedata || 'Subject: ' || l_subject || crlf;
l_filedata := l_filedata || 'MIME-Version: 1.0' ||  crlf; 
l_filedata := l_filedata || 'Content-Type: multipart/mixed; boundary=' ||chr(34)||l_boundary|| chr(34)||crlf;
l_filedata := l_filedata || '--'||l_boundary|| crlf;
l_filedata := l_filedata || 'Content-Type: text/plain;'|| crlf ||
                                           'Content-Transfer_Encoding: 7bit'|| crlf ||
                                           crlf || l_body || crlf || crlf ||
                                           '--'||l_boundary|| crlf ||
                                           'Content-Type: text/plain;'|| crlf ||
                                           ' name="'||l_filename||'"'|| crlf ||
                                           'Content-Transfer_Encoding: 8bit'|| crlf ||
                                          'Content-Disposition: attachment;'|| crlf ||
                                          ' filename="'||l_filename||'"'|| crlf || crlf;

dbms_lob.createtemporary(l_clob, false, 10);
dbms_lob.write(l_clob, LENGTH(l_filedata), 1, l_filedata);

l_file_handle := UTL_FILE.FOPEN (l_dirpath, l_filename, 'R');

ln_temp := dbms_lob.getlength(l_clob) + 1;

LOOP

  BEGIN
    utl_file.get_line(l_file_handle, l_msg);
    ln_temp := dbms_lob.getlength(l_clob) + 1;
    IF DBMS_LOB.GETLENGTH(l_msg) > 0 THEN
       dbms_lob.write(l_clob, LENGTH(l_msg)+1, ln_temp, l_msg||CHR(10));
    END IF;   
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
    EXIT;
  END;

END LOOP;

utl_file.fclose(l_file_handle);

ln_temp := 1;
ln_amount := 1900;

WHILE ln_temp < dbms_lob.getlength(l_clob) LOOP
   utl_smtp.write_data(l_conn, dbms_lob.substr(l_clob, ln_amount, ln_temp));
   ln_temp := ln_temp + ln_amount;
   ln_amount := least(1900, dbms_lob.getlength(l_clob) - ln_amount);
END LOOP;

utl_smtp.write_data(l_conn, crlf||crlf);
utl_smtp.close_data(l_conn);
utl_smtp.quit(l_conn);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error:'||SQLERRM);

end;

*********************************************************

Print This Post

 

Useful Scripts

1. Diabling all the users:

DECLARE

CURSOR c_user is
SELECT user_name
  FROM fnd_user
 WHERE TRUNC(NVL(end_date,SYSDATE)) >= TRUNC(sysdate);

type t_user_name is table of fnd_user.user_name%TYPE;
lt_user_name t_user_name;

begin

open c_user;
fetch c_user bulk collect into lt_user_name;
close c_user;

for i in 1..lt_user_name.COUNT LOOP
  fnd_user_pkg.updateuser(x_user_name => lt_user_name(i)
                         ,x_owner     => null
                         ,x_end_date  => TRUNC(sysdate-1) );
                        
  dbms_output.put_line('User Disabled:'||lt_user_name(i));
END LOOP;

COMMIT;

dbms_output.put_line('commit Complete');

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('Exception');

END;
/ 


Print This Post