Home » RDBMS Server » Server Utilities » Email from a stored procedure
| Email from a stored procedure [message #69486] |
Fri, 18 January 2002 12:51  |
Naveen Patlola
Messages: 3
Registered: January 2002
|
Junior Member |
|
|
My Stored procedure inserts a row into a database. I would like to send an email to my email account once the data is saved into the database with the information. Is that possible. If yes can some help me show that code.
Thank you so much
|
|
|
|
|
|
| Re: Email from a stored procedure [message #69502 is a reply to message #69486] |
Tue, 22 January 2002 03:39  |
Vijay Kamath
Messages: 4
Registered: January 2002
|
Junior Member |
|
|
Below is a stored Procedure to send emails from PL/sql. This works on 8i
CREATE OR REPLACE procedure mail_files ( from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2,
mail_server varchar2,
max_size number default 9999999999,
filename1 varchar2 default null,
filename2 varchar2 default null,
filename3 varchar2 default null,
debug number default 0 ) is
/*
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.
Written: Dave Wotton, 14/6/01 (dwotton@clara.co.uk)
This script comes with no warranty or support (although I welcome
any bug reports and will attempt to fix them). You are free to
modify it as you wish, but please retain an acknowledgement of
my original authorship.
Amended: Dave Wotton, 10/7/01
Now uses the utl_smtp.write_data() method to send the message,
eliminating the 32Kb message size constraint imposed by the
utl_smtp.data() procedure.
Amended: Dave Wotton, 20/7/01
Increased the v_line variable, which holds the file attachment
lines from 400 to 1000 bytes. This is the maximum supported
by RFC2821, The Simple Mail Transfer Protocol specification.
Amended: Dave Wotton, 24/7/01
Now inserts a blank line before each MIME boundary line. Some
mail-clients require this.
Amended: Dave Wotton, 4/10/01
Introduced a 'debug' parameter. Defaults to 0. If set to
non-zero then errors in opening files for attaching are
reported using dbms_output.put_line.
Include code to hand MS Windows style pathnames.
*/
/*
You may need to modify the following variable if you don't have a local
SMTP service running (particularly relevant to Windows 2000 servers).
Refer to http://home.clara.net/dwotton/dba/oracle_smtp.htm for more
details.
*/
v_smtp_server varchar2(15) := mail_server;
v_smtp_server_port number := 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);
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_len number;
mesg_too_long exception;
invalid_path exception;
mesg_length_exceeded boolean := false;
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;
-- 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 );
utl_smtp.rcpt( conn, to_name );
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_name || crlf ||
'Mime-Version: 1.0' || crlf ||
'Content-Type: multipart/mixed; boundary="DMW.Boundary.605592468"' || 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 ||
'--DMW.Boundary.605592468' || 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 ||
message || crlf ;
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..3 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;
-- 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;
/
|
|
|
|
| Re: Email from a stored procedure [message #70606 is a reply to message #69502] |
Fri, 05 July 2002 09:07 |
samgabam
Messages: 2
Registered: June 2002
|
Junior Member |
|
|
|
I've written a similar code and was successfully able to send attachments. However, I would like to attach a .txt file from my local machine. How do I specify the path so that procedure will retrieve the file and attach it?
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 13:59:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
