How to call a URL in PL/SQL [message #244486] |
Wed, 13 June 2007 01:45  |
kanis
Messages: 61 Registered: November 2006
|
Member |
|
|
I want to send out SMS to Mobile by executing a PL/SQL procedure.
I have a URL like http://www.abcdef.com/sms/send?user=<userid>&pass=<password>&number=<destination_mobile_no>&plain=yes&message=<text message>
here parameters 'number' and 'message' takes dynamic values and Others are constant.
If I put the above URL with parameters values in a internet browser it sends out SMSs to Mobiles.
I want to call this url in a PL/SQL proc so that by executing the proc I can send SMSs to a group of Mobile nos, just like a batch process.
Can anyone help me in this regard ???
My main concern is how to call a URL in PL/SQL ........
Thanks in advance....
|
|
|
|
|
|
|
Re: How to call a URL in PL/SQL [message #244627 is a reply to message #244571] |
Wed, 13 June 2007 09:34   |
kanis
Messages: 61 Registered: November 2006
|
Member |
|
|
Thanks Michel !!!
The links provided by you were really useful.
Just before executing the query
SELECT utl_http.request(http://www.abcdef.com/sms/send?user=<userid>&pass=<password>&number=<destination_mobile_no>&plain=yes&message=<text message>') FROM dual;
do 'Set Define Off' of sqlplus to get rid of '&' character.
Thanks everyone once again. I can now send SMSs from a PL/SQL proc.
There are some better and easier way to do the same thing in higher version of Oracle like 9i or 10g. But since I have Oracle 8i, I have not much options.
Cheers,
Kanis
|
|
|
Re: How to call a URL in PL/SQL [message #244956 is a reply to message #244627] |
Thu, 14 June 2007 13:08   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
You probably need to encode all your "special" characters on the URL. So spaces become "%20" etc. "&", "?", "=", CR-LF etc will all cause problems. Remember that URLs have max lengths (2k?) but SMS messages are quite short, so should be ok.
Add chr(10), chr(13), chr(9) etc to the list too...
(snippet is from AskTom expert 1-on-1)
function urlencode( p_str in varchar2 ) return varchar2
as
l_tmp varchar2(6000);
l_bad varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"';
l_char char(1);
begin
for i in 1 .. nvl(length(p_str),0) loop
l_char := substr(p_str,i,1);
if ( instr( l_bad, l_char ) > 0 )
then
l_tmp := l_tmp || '%' ||
to_char( ascii(l_char), 'fmXX' );
else
l_tmp := l_tmp || l_char;
end if;
end loop;
return l_tmp;
end;
[Updated on: Thu, 14 June 2007 13:09] Report message to a moderator
|
|
|
|
|