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/

Jeffrey Kemp 17 August 2016

Clicksend / open-source / PL/SQL /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

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

Comments

  1. kaneric
    10 November 2016 - 11:23 am

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

  2. rafiquel
    24 August 2017 - 12:56 pm

    I CAN NOT CREATE PAKAGE BODY IN MY SCHEMA.

    • Jeffrey Kemp
      24 August 2017 - 12:57 pm

      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. Barry
    18 December 2018 - 3:58 am

    Does this work with Oracle Apex 18.2?

    • Jeffrey Kemp
      18 December 2018 - 5:31 am

      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. Barry
    19 December 2018 - 1:32 am

    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. Azz
    18 January 2019 - 10:25 pm

    Hi Jeff

    Can I use it to send SMS in Canada?

  6. Ricardo
    26 June 2019 - 11:59 pm

    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

    • Jeffrey Kemp
      27 June 2019 - 8:13 am

      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. Joe Kerr
    9 July 2019 - 5:12 am

    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. Avinash Kumar
    26 September 2019 - 4:29 am

    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

    • Jeffrey Kemp
      7 April 2020 - 9:52 am

      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. Makinde
    9 April 2020 - 3:07 am

    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. Makinde
    9 April 2020 - 4:21 am

    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.

    • Jeffrey Kemp
      9 April 2020 - 7:36 am

      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.

    • Jeffrey Kemp
      10 April 2020 - 3:58 pm

      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.

    • Jeffrey Kemp
      10 April 2020 - 7:00 pm

      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. Krunal
    22 June 2020 - 10:53 pm

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

  21. Krunal
    6 July 2020 - 2:44 pm

    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. Krunal
    7 July 2020 - 6:58 pm

    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. Krunal
    14 August 2020 - 6:11 pm

    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.

    • Jeffrey Kemp
      14 August 2020 - 8:55 pm

      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;

  24. Kumar
    27 November 2020 - 11:29 pm

    Hi,
    I am getting below error while compailing package body
    PLS-00201: identifier ‘SYS.DBMS_AQ’ must be declaredCompilation failed.

  25. Kumar
    28 November 2020 - 12:14 am

    Hi Jeff,
    Does this work for India?

    thanks.

    • Jeffrey Kemp
      28 November 2020 - 9:14 pm

      Hi Kumar, I think it should work – Clicksend advertise global coverage. It’s worth giving a try at least.

  26. Kumar
    28 November 2020 - 9:29 pm

    Thanks for the quick response Jeff,
    Please help me out to fix below error
    PLS-00201: identifier ‘SYS.DBMS_AQ’ must be declaredCompilation failed.

    Thanks

  27. Kumar
    28 November 2020 - 9:47 pm

    Thanks Jeff!!

  28. Kumar
    8 December 2020 - 5:40 pm

    Hi Jeff,
    I am getting below error
    ORA-20000: get_json call failed 404 Not Found [https://rest.clicksend.com/v3//send]

    Please help me to fix this issue

    • Jeffrey Kemp
      8 December 2020 - 6:36 pm

      Hi Kumar,

      That’s puzzling. It seems your instance is getting messages enqueued with no message type (which is causing the double // which should be /sms/ or /mms/ or /voice/).

      Have you installed the latest version of the API?

      Jeff

  29. Kumar
    8 December 2020 - 7:10 pm

    Thnks for the quick response jeff!
    Yes, I have installed latest one.
    getting error:
    ORA-24010: QUEUE ENOTEDB.CLICKSEND_QUEUE does not exist
    ORA-06512: at “SYS.DBMS_AQ”, line 185
    ORA-06512: at “ENOTEDB.CLICKSEND_PKG”, line 622
    ORA-06512: at line 2
    ORA-06512: at “SYS.DBMS_SQL”, line 1721

    • Jeffrey Kemp
      8 December 2020 - 7:18 pm

      Sounds like you need to recreate the queue. Try running:

      begin clicksend_pkg.create_queue; end;

  30. Kumar
    8 December 2020 - 7:23 pm

    Thanks Jeff.
    the above issue got fixed.
    but again geeting below error
    ORA-20000: get_json call failed 404 Not Found [https://rest.clicksend.com/v3//send]
    ORA-06512: at “ENOTEDB.CLICKSEND_PKG”, line 248
    ORA-06512: at “ENOTEDB.CLICKSEND_PKG”, line 407
    ORA-06512: at “ENOTEDB.CLICKSEND_PKG”, line 1068

    • Jeffrey Kemp
      8 December 2020 - 7:26 pm

      Ok. Can you post a sample call you are making to the API? You can anonymize the phone number – e.g. use +61411111111.

  31. Kumar
    8 December 2020 - 7:27 pm

    Thanks you so much jeff!
    it’s working fine.

    I really appriciate your help!!

  32. Syed Nasir Jamal Shah
    25 February 2021 - 3:46 am

    Dear Jeff! hope you will be fine and shine….

    Is it possible to run over Oracle 10G database?

    • Jeffrey Kemp
      25 February 2021 - 7:59 am

      Hi Syed,

      The API has only been tested on 11gR2. You could try compiling it on 10g – if you get no compilation errors, then it will probably work fine.

      Please note, Oracle 10g has been out of extended support for over 7 years now.

      Jeff

  33. Louis M.
    26 October 2021 - 9:49 pm

    I need help with the Cert, I have tried several ways to send Rest Requests to ClickSend, both with your awesome API and writing my own Rest Call using PL/SQL and Apex. But No matter what I do I keep getting the ORA-29024: Certificate validation failure. I must be doing something wrong or is it my DBA when adding it to the Wallet. (We have done this before so I doubt it). I have tried downloading from this site https://rest.clicksend.com/v3, I had him try all formats (DER, Base64, PKCS) and No Cigar! I Keep getting the Invalid Certificate error. Any ideas or clues Please?

    • Jeffrey Kemp
      26 October 2021 - 10:14 pm

      Hi Louis,

      Sorry I don’t know – the best I can do is point you to this article: https://oracle-base.com/articles/misc/utl_http-and-ssl

      I believe Oracle Wallet can import both Base64-encoded X.509 certificates, as well as the PKCS #7 certificate chains.

      You might try using openssl from your database’s OS to test if it can verify the certificate. Apart from that you might need to contact Oracle support for assistance.

      Jeff

  34. John Ng
    8 July 2022 - 4:17 pm

    Hi Jeff, I am excited to get this going because it is seems to be what I want. Unfortunately, I cannot get pass the install stage. It is complaining about MAILGUN_SETTINGS.SETTING_VALUE not being declared. I know MailGun is something that you wrote and I do not mind installing it but I am afraid the install errors might go on and on, and sooner I would have clogged up my database with things that may not properly uninstall. Do you have instructions on what the dependencies are?
    Thanks Jeff.
    John

    • Jeffrey Kemp
      8 July 2022 - 4:23 pm

      Hi John,

      An older version of the scripts incorrectly referred to that table, but the latest version of the scripts should be fine.

      Can you check that you are working with the latest scripts from https://github.com/jeffreykemp/clicksend-plsql-api – and if so, please send me the installation log if the problem persists.

      You could do a text search of the scripts for “mailgun_settings” – there should be no mention of this.

      Thanks

      Jeff

  35. Alexandros Valatsos
    23 November 2022 - 8:22 pm

    Hey Jeff, what’s up buddy?

    I come again (after more than 2 years) with the same issue. I have spent lots of hours in trying to send SMS from the database but nothing (only bad mental health). I had a correspondence with maxapex guys and they told me that their servers have all certificates installed and to use APEX_WEB_SERVICE instead of UTL_HTTP (which you use in your code). Anyway… I could not find any example how to make a call to clicksend and send an SMS. Is it really sooooo difficult to send an SMS from the database??? That’s insane. Anyway… I know you’re a guru in Oracle and i have no other option to make it, but only with your help. Can you please send me a very simple example how to send an SMS using PL/SQL and clicksend service? Consider that i have no administration on any server. I just have the apex environment on maxapex.net hosting. If you want my credentials to clicksend, just let me know. Thanks in advance.

  36. Daren
    15 March 2023 - 11:24 am

    Hey Jeff…. I’m looking for a SMS Solution. Is it possible to use your API with APEX running on Autonomous Database? How big of a deal is it to convert the UTL_HTTP Call over to apex_web_service? I don’t think you can specify the wallet parameters on Autonomous.

    Thanks
    Daren

  37. Ahmed
    6 July 2023 - 12:04 am

    Hi Jeff,

    can I create this code inside my form in ORACLE FORMS BUILDER 10G, and through that I send text messages through this form of my form, and if I can do that, what is the way I have to do it in order to get this service.

    • Jeffrey Kemp
      6 July 2023 - 7:49 am

      Yes, you call the API to send a message in the same way you call any server-side PL/SQL.

  38. Ahmed
    7 July 2023 - 11:21 am

    Hi Jeff,

    Sorry for the inconvenience, but I would like to add the sms sending feature inside my form, so is it possible to follow the code that I have included, or are there steps to be followed because I do not have prior information on how to add this feature.

    With full of love.

  39. Ahmed
    8 July 2023 - 10:44 am

    Are there instructions that I follow to add the API inside my form, and is it possible to send messages if the computer is not connected to the Internet?

    • Jeffrey Kemp
      8 July 2023 - 10:47 am

      I’m sorry Ahmed, I don’t think I will be able to help you via this forum. I suggest engaging someone with some software development experience to help guide you in this regard.

Leave a Reply

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