4 Replies Latest reply on Jan 12, 2016 9:14 AM by Billy~Verreynne

    Triggering SMS in PLSQL

    Parameshwaran Duraisamy

      Hi all,


      Good day !


      Is it possible to send SMS using PLSQL. If Yes, Kindly let me know the steps for the same.


      Thanks in advance.



      Parameshwaran D

        • 2. Re: Triggering SMS in PLSQL
          Jarkko Turpeinen

          Hi Parameshwaran,


          would you like to have coffee while waiting your solution. With cream and sugar?


          i'm sure you googled it first but did you?



          • 3. Re: Triggering SMS in PLSQL

            Even just typing "SMS" in the search box of this PL/SQL space gives many previous threads on the same subject.


            In short, yes you can send SMS messages but it depends on what SMS gateway you are going to use.

            If the SMS service is some online service, then they will likely provide various API's for you to call, which could be some webservices requiring SOAP messages or HTTP POST's etc.


            If the SMS service is provided internally in your organisation they may have some other method for calling it e.g. in our organisation, our mail server provides the means to send SMS by emailing to phonenumber@SMS which is then forwarded to the SMS gateway which has been set up externally to our Oracle servers and we don't have to worry about.  Thus, sending an email is easy enough to do, as Oracle provides UTL_MAIL (not the best package) or UTL_SMTP (better package) for doing so.


            If you have to call webservice API's then using the UTL_HTTP package to communicate over HTTP is the way to go to achieve that.

            • 4. Re: Triggering SMS in PLSQL

              Trust that by triggering a SMS, you are not implying sending a SMS from a table trigger.


              Such a notification will be send prior to the transaction committing - it still can fail or be rolled back. In which case the notification will be for something (data inserted/updated/deleted) that never happened.


              Also, the same trigger for the same row in the same transaction, can be fired again in special and specific circumstances when a transaction is "reset" internally and "redone" - in which case duplicate notifications will result for the same thing.


              As for sending SMS - we have a SMS gateway that supports TCP clients, authentication, and sending a specifically formatted text line containing the SMS data received from the client. I use the UTL_TCP PL/SQL package to interact as a TCP client with the SMS gateway server.


              As you can see from this and Blu's comments, numerous options are available - but you need the SOMETHING first, that can send SMS to a cellular network. And that something will determine what you need to use in PL/SQL to communicate with it.