Home » SQL & PL/SQL » SQL & PL/SQL » Sending message to mobile phones from oracle
Sending message to mobile phones from oracle [message #38664] Sun, 05 May 2002 01:00 Go to next message
SACHIN BRAHMANKAR
Messages: 2
Registered: February 2002
Junior Member
Sir/Madam
I just want to know wheather it is possible to send message to mobile/cellular phones from oracle database.
If yes then how?
Re: Sending message to mobile phones from oracle [message #38670 is a reply to message #38664] Sun, 05 May 2002 19:10 Go to previous messageGo to next message
sreekanth
Messages: 6
Registered: April 2002
Junior Member
you can send text messages to cell phones by sending an email. Email can be sent using the following pl/sql code. for the variable v_smtp_server assign your mail server ip address.

create or replace procedure SEND_MAIL ( from_name varchar2,
to_name varchar2,
subject varchar2,
message varchar2,
max_size number default 9999999999,
filename1 varchar2 default null,
filename2 varchar2 default null,
filename3 varchar2 default null,
debug number default 0 ) is

v_smtp_server varchar2(500) := 'give the ip address of mail server here';
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: Sending message to mobile phones from oracle [message #40111 is a reply to message #38664] Tue, 10 September 2002 01:49 Go to previous messageGo to next message
ramshankar
Messages: 1
Registered: September 2002
Junior Member
hi,
I am working oracle database. I want to know how to send messages to mobile thru oracle please send reply as early as possible.

thank u.

Rams
Re: Sending message to mobile phones from oracle [message #199100 is a reply to message #38670] Fri, 20 October 2006 02:58 Go to previous message
APPLE_ZHANG
Messages: 6
Registered: October 2006
Location: CHINA
Junior Member

Afternoon, friend,
I wonder where the variable for receiver ip is or where I can give the receiver ip?

Apple ^_^
thanks a lot...
Previous Topic: HOW TO PULL DATA FROM 2 TABLE
Next Topic: Sending an mail attachment thru Oracle8i database
Goto Forum:
  


Current Time: Sun Dec 04 19:05:00 CST 2016

Total time taken to generate the page: 0.10450 seconds