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

  9. Makinde Olojede
    7 April 2020 - 12:46 am

    Hi Jeffrey,

    I am getting “PLS-00201: identifier ‘MAILGUN_SETTINGS.SETTING_VALUE’ must be declared” when compiling clicksend_pkg package body, do I need to create another table called MAILGUN_SETTINGS similar to CLICKSEND_SETTINGS?

    Thank you,
    Makinde

    • Sorry, this is a bug.

      I copied-and-pasted some code from my mailgun API and failed to replace references of that table.

      Until I release a fix you can edit the clicksend API package to replace all instances of “MAILGUN_SETTINGS” with “CLICKSEND_SETTINGS” and it should work.

      Thanks – Jeff

  10. Thank you Jeff, I am now able to compile clicksend_pkg package body successfully. However, I am getting another error
    “ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at “APEX_SCHEMA.CLICKSEND_PKG”, line 8″
    when executing:
    begin
    clicksend_pkg.create_queue;
    end;

  11. Please ignore the previous message, I already figure it out. I had to substitute “sys_context(‘userenv’,’current_schema’)||’.clicksend_queue’;” with actual queue_name.

    • Hi Makinde, thanks but actually that is a bug in my code, you’re not expected to update that line. The definition of the constant should have been:

      queue_name constant varchar2(500) := sys_context(‘userenv’,’current_schema’)||’.clicksend_queue’;
      (and similarly for the following ones)

      Jeff

  12. Makinde Olojede
    10 April 2020 - 12:04 pm

    Thank you Jeff. All the setup seems to be completed successfully now. But I am now getting “ORA-29273: HTTP request failed
    ORA-29024: Certificate validation failure” when I tried to test SMS message. I added the root certificate from “https://www.clicksend.com/” and I tested it as per “https://oracle-base.com/articles/misc/utl_http-and-ssl” the test was successful but I am still getting Certificate validation failure. Any suggestion would be appreciated.

    • Hi Makinde,

      I’m not sure – first thing I’d go back to your test and make sure it works for https://rest.clicksend.com as that is the endpoint you should be using, not www.clicksend.com.

      Jeff

  13. Makinde Olojede
    10 April 2020 - 6:55 pm

    Hi Jeff,

    The test at oracle-base did not work for https://rest.clicksend.com. The interesting thing is that https://rest.clicksend.com is always redirected to https://developers.clicksend.com/ on the web. When I tried https://developers.clicksend.com/ it worked but since that is not the endpoint. Thanks for your response.

    • Hi Makinde,

      Yes, if you direct a web browser at the base URL it redirects to a different page; but if you send a properly formatted REST request to the correct endpoint (https://rest.clicksend.com/v3) it should respond with

      {"http_code":200,"response_code":"SUCCESS","response_msg":"Welcome to ClickSend API v3.","data":null}

      .

      If you successfully set up your certificate for rest.clicksend.com I expect you will find it will work for the API.

      Jeff

  14. Makinde Olojede
    15 April 2020 - 3:43 am

    Thanks so much Jeff, everything is now working fine!
    {“http_code”:200,”response_code”:”SUCCESS”,”response_msg”:”Welcome to ClickSend API v3.”,”data”:null}

  15. Alexandros Valatsos
    26 May 2020 - 11:43 pm

    Jeffrey, you’re AWESOME !!! It works perfectly !!!

    millions of thanks !!!!

  16. Feroze Najmee
    30 May 2020 - 5:46 pm

    Hi,
    is it necessary to have https server configured to use sms. I’ve done the setup and while trying to use the below code, it throws error Netwrok Access denied. do I’ve done the ACL configuration properly. hope you’ll help on this.

    begin
    clicksend_pkg.send_sms
    (p_sender => ‘tester’
    ,p_mobile => ‘+61411111111’ — +61411111111 is a free test number, no msg will be sent or charged
    ,p_message => ‘testing ‘ || to_char(systimestamp,’DD/MM/YYYY HH24:MI:SS.FF’)
    );
    clicksend_pkg.push_queue;
    commit;
    end;

    • Jeffrey Kemp
      3 June 2020 - 7:34 am

      Hi Feroze,

      1. You can use https from the database to connect directly to the clicksend server, but you don’t have to – the alternative is to put a reverse proxy in front of your database server.

      2. Whichever way you choose, you must set up the ACL correctly, otherwise you will get Network Access Denied. How you set up your ACL will depend on what method you use to connect to the clicksend server.

      Full installation instructions including details for both options may be read here: https://github.com/jeffreykemp/clicksend-plsql-api/wiki/installation

      Jeff

  17. Alexandros Valatsos
    8 June 2020 - 5:14 pm

    Hi Jeff,

    While it was working perfectly, now it says that http://api.jk64.com/clicksend/v3/ is forbidden. Can you help?

    • Jeffrey Kemp
      8 June 2020 - 5:35 pm

      I was wondering why traffic on my proxy was getting external traffic! I blocked it. You need to use the correct clicksend API endpoint as per the documentation.

  18. Alexandros Valatsos
    8 June 2020 - 5:45 pm

    Thanks Jeff, I’ll give a try with the documentation

  19. Alexandros Valatsos
    8 June 2020 - 6:04 pm

    Buddy help…

    i can’t figure out what to do.

    I changed the CLICKSEND_SETTINGS table, the api_url to https://rest.clicksend.com/v3/sms/send and now it says “Certificate validation failure”. And i don’t know how to handle wallets and this stuff. My apps and schemas are hosted in maxapex.net.

    With your proxy, it was everything ok, but now i have to mess with stuff i don’t know.

    Any advise will be helpful…

  20. Hi Jeffery,
    Does it allow reply from the receiver? If yes then where will I be able to see the reply?

  21. Hi Jeffery,
    Sorry to bother you again. I got root certificate from https://rest.clicksend.com/v3 and added to wallet. Later on created ACL for rest.clicksend.com as well. but still i am getting the ORA-29024: Certificate validation failure. Can you guide me what I am doing wrong?
    Thanks in advance.

  22. Hi Jeffery,
    I found the issue, When I was trying to get root certificate from https://rest.clicksend.com/v3/ it was showing me different set of hierarchy but in another system(same browser) it was showing something else (correct one). So after getting that certificate. it worked as expected.

  23. Hi Jeffery,
    Sorry to bother you again.
    I have purchased dedicated number to send the SMS. But still whenever I am sending SMS, the receivers are receiving SMS from different numbers. Do I need to modify any parameter to user dedicated number?
    Thanks in Advance.

    • Hi Krunal,

      To use your dedicated number you probably need to enter it as the Sender parameter, e.g.

      clicksend_pkg.send_sms
          (p_sender  => '... put your your dedicated number here ...'
          ,p_mobile  => '+61411111111'
          ,p_message => 'Hi, message sent at '
                     || to_char(systimestamp,'DD/MM/YYYY HH24:MI:SS.FF')
          );
      

      You can set it to be the default for all outgoing SMS’s by running init:

      begin
        clicksend_pkg.init
          (p_default_sender  => '... put your your dedicated number here ...');
      end;
      

Leave a Reply

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