Sending SMS text messages from PL/SQL

Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.

Do a google search for "sms gateway api" to find a suitable gateway provider. I won't provide any specific recommendations or endorsements; for this blog post I just picked a random provider to use in the code example below. I have no idea if they offer a good/cheap/reliable service or not. (Leave a comment below if you have any recommendations that you would like to share.)

Most gateways offer an API that takes a HTTP(S) GET or POST request with some parameters in the query string to specify the recipient and the message itself, and then returns some kind of status back in some format (XML, JSON, plain text). The concept is usually the same, but the specifics vary.

I've added a new package called SMS_UTIL_PKG to the Alexandria PL/SQL Utility Library that provides a generic interface to any SMS gateway.

To use the package, you need to check the API documentation of your chosen gateway provider, and set up the URL template accordingly. You can use tags like #username# and #password# and #message#, etc. in the template, which will be replaced with actual values when sending a message.

Here is an example of using the package to set up a gateway and send a message:

There's also support for adding a callback to a custom error handler that you can use to determine if the gateway returns an error message. This should be a function that accepts a clob as an input parameter (this will contain the response message from the gateway) and returns a varchar2 with an error message, or return null if successful.

Remember that you might have to modify your database Network ACL settings to open traffic to the gateway's hostname. And if the SMS gateway requires HTTPS, you need to set up an Oracle wallet with the certificate and call the set_wallet procedure before you send the message.

Happy texting! :-)