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! :-)

My nominations for the Oracle Database Developer Choice Awards 2015

I'm honored to have been nominated for the Oracle Database Developer Choice Awards 2015, in no less than three (!) categories: PL/SQL, Application Express (Apex) and Oracle REST Data Services (ORDS).

Here's a short video that explains what the Oracle Database Developer Choice Awards are all about:

If you are a regular reader of my blog, you are probably familiar with my contributions to the Oracle Database Developer community, but here is a short recap of my work in the relevant categories:

• PL/SQL: I've been programming in PL/SQL for almost 20 years. I've written and released as open source the widely-acclaimed Alexandria PL/SQL Utility Library, which contains around 50 PL/SQL packages that deal with everything from general math and string utilities to Amazon web service integration, NTLM authentication, CSV parsing, MS Exchange integration, and much more. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages. As of February 2015, the library had been downloaded more than 12,000 times. Furthermore, I've created a presentation called PL/SQL: The Good Parts, which in Steven Feuerstein's words is a "fast, entertaining high-level glimpse of what makes PL/SQL so great" (and Steven should know what he's talking about! :-).
• Apex: Since 2008 I've published numerous articles related to Oracle Application Express on this blog. Among other things, I've created popular Apex plugins, including a pivot table plugin and a plugin for generating on-demand dynamic PL/SQL content. I've created and open-sourced the jQGrid Integration Kit for PL/SQL. And I created ApexGen, a framework for generating Apex apps using PL/SQL code (which I may revisit and enhance in the future if the Apex team provides a supported API for it). I've also been active in the Apex community by asking and answering questions on the Apex forum on OTN, actively testing Apex early adopter (EA) releases for Apex 4 and 5, and reporting numerous technical issues and bugs back to the Apex development team. My coworkers know me as "the Apex guy" since I constantly promote the virtues of Apex for new development projects.
• ORDS: I've long been interested in the technology that connects the Oracle Database to the web, including the PL/SQL Web Toolkit (aka OWA) and the forerunners of ORDS such as mod_plsql and the Embedded PL/SQL Gateway. Using this knowledge I implemented and released as open source a mod_plsql alternative for Microsoft Internet Information Server (IIS) called the Thoth Gateway. I've published many articles on the use of JSON from PL/SQL, a key part of REST. Earlier this year I wrote a four-part blog post series with all the nitty-gritty details on installing and running Apex with ORDS that became very popular and widely distributed.

Now go and vote for me! :-)

Click here to vote in the PL/SQL category, here to vote in the Apex category, and here to vote in the ORDS category.

Thank you very much for your vote! :-)

Image credit.