Sending a SMS to a mobile phone

Today I read an article on Grant Rolands blog about sending a SMS from Oracle Forms through a Web-Service.

I thought it would be nice to have the same in APEX and PL/SQL, too. For example if you have a system failure or get a new order in your shop…

Grant is using a Web-Service provided by Esendex, but I’m sure there are others service providers available too. First I thought using the offered Web-Service and create a PL/SQL wrapper for it our use the Web-Service feature of APEX.

But I decided to go with the simple POST version which is also offered by them. Because why invoke all the Java stuff when there is also a simple version available. The APEX web-service couldn’t be used, because then you would not be able to call it from PL/SQL.

The code is quite straight forward. Get a free account, replace the constant values in the example procedure and you are ready to go!

Note: If you have a proxy then you have to uncomment the proxy line. There is also a secure version available which uses HTTPS, but that requires a wallet, … and I just wanted to demonstrate that it works. The error handling of the procedure could also be improved, but hey, it’s just a demonstration. :-)

Have fun waking up your operators/DBAs in the night! :-)

To send a SMS just call the procedure with

BEGIN
    sendSMS('+436991812345','this is a test');
END;

Here comes the example procedure:

CREATE OR REPLACE PROCEDURE sendSMS
  ( pRecipient IN VARCHAR2
  , pBody      IN VARCHAR2
  )
IS
    ESENDEX_USERNAME CONSTANT VARCHAR2(40) := 'your_username';
    ESENDEX_PASSWORD CONSTANT VARCHAR2(40) := 'your_password';
    ESENDEX_ACCOUNT  CONSTANT VARCHAR2(40) := 'your_account';
    --
    vRequest      Utl_Http.req;
    vPostText     VARCHAR2(500);
    vResponse     Utl_Http.resp;
    vResponseText VARCHAR2(2000);
    vErrorText    VARCHAR2(200);
BEGIN
    ----------------------------------------------------------------------------
    -- Build text for the post action.
    -- For a field description, see
    -- http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx
    ----------------------------------------------------------------------------
    vPostText :=
      'EsendexPlainText=YES'                                     ||CHR(38)||
      'EsendexUsername=' ||Utl_Url.escape(ESENDEX_USERNAME, TRUE)||CHR(38)||
      'EsendexPassword=' ||Utl_Url.escape(ESENDEX_PASSWORD, TRUE)||CHR(38)||
      'EsendexAccount='  ||Utl_Url.escape(ESENDEX_ACCOUNT,  TRUE)||CHR(38)||
      'EsendexRecipient='||Utl_Url.escape(pRecipient,       TRUE)||CHR(38)||
      'EsendexBody='     ||Utl_Url.escape(pBody,            TRUE);
    ----------------------------------------------------------------------------
    -- if you need to set a proxy, uncomment next line.
    ----------------------------------------------------------------------------
    /* Utl_Http.set_proxy('proxy.it.my-company.com', 'my-company.com'); */
    ----------------------------------------------------------------------------
    -- Send SMS through the Esendex SMS service.
    ----------------------------------------------------------------------------
    vRequest := Utl_Http.begin_request
                  ( url    => 'http://www.esendex.com/secure/messenger/formpost/SendSMS.aspx'
                  , method => 'POST'
                  );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Type'
      , value => 'application/x-www-form-urlencoded'
      );
    Utl_Http.set_header
      ( r     => vRequest
      , name  => 'Content-Length'
      , value => LENGTH(vPostText)
      );
    Utl_Http.write_text
      ( r    => vRequest
      , data => vPostText
      );
    vResponse := Utl_Http.get_response(vRequest);
    IF vResponse.status_code = '200'
    THEN
        Utl_Http.read_text(vResponse, vResponseText);
        --
        IF vResponseText NOT LIKE 'Result=OK%'
        THEN
            vErrorText := vResponseText;
        END IF;
    ELSE
        vErrorText := 'HTTP status: '||vResponse.status_code||'-'||vResponse.reason_phrase;
    END IF;
    --
    Utl_Http.end_response(vResponse);
    --
    IF vErrorText IS NOT NULL
    THEN
        RAISE_APPLICATION_ERROR(-20001, 'Sending SMS failed with '||vErrorText);
    END IF;
END sendSMS;

10 thoughts on “Sending a SMS to a mobile phone

  1. Hey,
    its very interesting. I have to implement one.
    what the username, password and account for ?
    and where should create them ?

    ESENDEX_USERNAME CONSTANT VARCHAR2(40) := ‘your_username';
    ESENDEX_PASSWORD CONSTANT VARCHAR2(40) := ‘your_password';
    ESENDEX_ACCOUNT CONSTANT VARCHAR2(40) := ‘your_account';

    thanks

  2. can i send sms from my mobile to database in my pc and return the information from database in my pc to my mobile through GSM modem ???

    tell me in detail

    thank you

  3. Dear All

    i am trying to send message from oracle to mobile i try the above code but it give me error

    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at “SHAMS.SENDSMS”, line 46
    ORA-06512: at line 2

    Please Help me what to do

    Thanks & Best Regard
    Shams

  4. plz tell me who i can send sms from my GSM to my web and also from my web to GSM ????????????/

  5. So many times I tried to create free trial account in esendex but I got sorry message.
    So how I can send sms from oracle forms 10g to mobile phone without using esendex.

  6. So many times I tried to create free trial account in esendex but I got sorry message

  7. I am getting vResponse.status_code = ‘302’ in between. Not able to find the reason for that, can you specify it.

  8. Thank you so much for sharing with us
    it works fine with me i only had to add a wallet to the code just before the vrequest like this
    — Send SMS through the Esendex SMS service.
    —————————————————————————-
    UTL_HTTP.set_wallet (‘file:c:\’, ‘pass_word’);
    UTL_HTTP.set_detailed_excp_support (TRUE);
    just in case anyone got the authorization error

  9. Genrate Error :
    Sending SMS failed with HTTP status: 403-Forbidden

Comments are closed.