Inside Oracle APEX by Patrick Wolf

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;