Home » SQL & PL/SQL » SQL & PL/SQL » How to call a URL in PL/SQL
How to call a URL in PL/SQL [message #244486] Wed, 13 June 2007 01:45 Go to next message
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 #244496 is a reply to message #244486] Wed, 13 June 2007 02:19 Go to previous messageGo to next message
ThomasG
Messages: 3113
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can use the utl_http for that.

Example :

SELECT utl_http.request('http://www.oracle.com/') FROM dual;


More Docs :

http://www.cs.umbc.edu/help/oracle8/server.815/a68001/utl_http.htm
Re: How to call a URL in PL/SQL [message #244500 is a reply to message #244486] Wed, 13 June 2007 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at UTL_HTTP.

Regards
Michel
Re: How to call a URL in PL/SQL [message #244561 is a reply to message #244500] Wed, 13 June 2007 06:23 Go to previous messageGo to next message
kanis
Messages: 61
Registered: November 2006
Member
Thanks all for response.

My url contains parameters e.g
http://www.abcdef.com/sms/send?user=<userid>&pass=<password>&number=<destination_mobile_no>&plain=yes&message=<text message>

which are promting for values when using
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;


I am working in Oracle 8i.
How will I get rid of the '&' ???
Re: How to call a URL in PL/SQL [message #244571 is a reply to message #244561] Wed, 13 June 2007 06:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't do it but maybe these threads on AskTom will help you:
HTTP request
UTL_HTTP package usage

Regards
Michel
Re: How to call a URL in PL/SQL [message #244627 is a reply to message #244571] Wed, 13 June 2007 09:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
andrew again
Messages: 2574
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

Re: How to call a URL in PL/SQL [message #573278 is a reply to message #244486] Wed, 26 December 2012 03:51 Go to previous messageGo to next message
aliazeez
Messages: 1
Registered: December 2012
Junior Member

if you did this sms sending procedure.. please guide me.. I want to do the same and I am not an expert in ORACLE.

Thnaks
Re: How to call a URL in PL/SQL [message #573289 is a reply to message #573278] Wed, 26 December 2012 05:10 Go to previous message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the topic and the links it contains?

Regards
Michel
Previous Topic: SQL UPDATION
Next Topic: insert or update records into target table
Goto Forum:
  


Current Time: Sat Nov 22 19:25:44 CST 2014

Total time taken to generate the page: 0.04177 seconds