Send SMS, MMS and Voice messages from Oracle PL/SQL

testing receipt of sms and mms

If you need to send almost any message to almost any phone from your Oracle database, and you want to use straight PL/SQL, you may want to consider using my Clicksend API.

  • SMS (Short Message Service)
  • MMS (Multimedia Message Service)
  • Text to Voice

I have released the first beta version of my Oracle PL/SQL API for Clicksend. Read the installation instructions, API reference and download the release from here:

http://jeffreykemp.github.io/clicksend-plsql-api/

Sending an SMS is as simple as adding this anywhere in your code:

begin
  clicksend_pkg.send_sms
    (p_sender  => 'TheDatabase'
    ,p_mobile  => '+61411111111'
    ,p_message => 'G''day, this is your database!'
    );
  clicksend_pkg.push_queue;
  commit;
end;

All you need to do is signup for a Clicksend account. You’ll only be charged for messages actually sent, but they do require you to pay in advance – e.g. $20 gets you about 300 messages (Australian numbers). You can get test settings so that you can try it out for free.

I’ve been using Clicksend for years now, and have been satisfied with their service and the speed and reliability of getting messages to people’s mobiles. When I encountered any issues, a chat with their support quickly resolved them, and they were quick to offer free credits when things weren’t working out as expected.

If you want to send a photo to someone’s phone via MMS (although I’m not sure what the use-case for this might be), you need to first upload the image somewhere online, because the API only accepts a URL. In my case, I would use the Amazon S3 API from the Alexandria PL/SQL Library, then pass the generated URL to the clicksend API. There is a file upload feature that ClickSend provides, I plan to add an API call to take advantage of this which will make this seamless – and provide some file conversion capabilities as well.

begin
  clicksend_pkg.send_mms
    (p_sender  => 'TheDatabase'
    ,p_mobile  => '+61411111111'
    ,p_subject => 'G''Day!'
    ,p_message => 'This is an MMS from your database!'
    ,p_media_file_url =>
'http://s3-ap-southeast-2.amazonaws.com/jk64/jk64logo.jpg'
    );
  clicksend_pkg.push_queue;
  commit;
end;

You can send a voice message to someone (e.g. if they don’t have a mobile phone) using the Text to Voice API.

begin
  clicksend_pkg.send_voice
    (p_phone_no     => '+61411111111'
    ,p_message      => 'Hello. This message was sent from your database. '
                    || 'Have a nice day.'
    ,p_voice_lang   => 'en-gb' -- British English
    ,p_voice_gender => 'male'
    ,p_schedule_dt  => sysdate + interval '2' minute
    );
  clicksend_pkg.push_queue;
  commit;
end;

You have to tell the API what language the message is in. For a number of languages, you can specify the accent/dialect (e.g. American English, British English, or Aussie) and gender (male or female). You can see the full list here.

All calls to the send_sms, send_mms and send_voice procedures use Oracle AQ to make the messages transactional. It’s up to you to either COMMIT or ROLLBACK, which determines whether the message is actually sent or not. All messages go into a single queue.

You can have a message be scheduled at a particular point in time by setting the p_schedule_dt parameter.

The default installation creates a job that runs every 5 minutes to push the queue. You can also call push_queue directly in your code after calling a send_xxx procedure. This creates a job to push the queue as well, so it won’t interfere with your transaction.

All messages get logged in a table, clicksend_msg_log. The log includes a column clicksend_cost which allows you to monitor your costs. To check your account balance, call get_credit_balance.

Please try it out if you can and let me know of any issues or suggestions for improvement.

Link: http://jeffreykemp.github.io/clicksend-plsql-api/

File Upload Improvements in APEX 5.1
Must See: AUSOUG Connect 2016 Perth

Comments

  1. I still need a smtp server setup to do in the oracle server box ?

  2. I CAN NOT CREATE PAKAGE BODY IN MY SCHEMA.

    • Hi Rafiquel, I might be able to help you if you provide some information – such as the error message that you’re getting when you try to compile the package body. Cheers, Jeff

  3. Does this work with Oracle Apex 18.2?

    • Yes, I’ve just upgraded to APEX 18.2 on 18cXE and it is working.

      Note that the instructions for setting up the network ACL need to be updated for Oracle 12+.

  4. Will you be updating the instructions soon? I am new to this and the easier the better. We are getting ready to upgrade to 18.2 18cXE as well.
    Thanks

  5. Hi Jeff

    Can I use it to send SMS in Canada?

  6. Hello, I can use this api in the oracle cloud https://apex.oracle.com!

    I am doing some tests in the oracle cloud and would like to validate a sms mobile phone with this api, do you know if this is possible?

    Thank you

    • Hi Ricardo,

      To validate someone’s mobile number I usually just check that it starts with “04” or “05” and has 10 digits, e.g. something like:

      regexp_like('0408123456', '^0[45][0-9]{8}$')

      I hope this helps.

      Jeff

  7. Hi
    I see references to needing a Clicksend account and Mailgun accounts. Which is recommended? The Mailgun site said they only do e-mails, not texts. I want to be able to do both e-mails and texts from PL/SQL procedures.
    Thanks
    Joe

  8. Dear sir,
    I want to integrate with whatsapp to oracle database 12c

Leave a Reply

Your email address will not be published / Required fields are marked *