Oracle Database, Send SMS through SMS Gateway

Tested on 10g 10.2.0.3 database with default installation(no additional packages were installed to achieve the results)

There are many APIs (developed by 3rd parties) which will allow you to send SMS from a Oracle database on demand. Many times such APIs would become costly, depending upon your requirements.

Here, we are providing a zero cost solution, incase if your SMS gateway provider allows you to send SMS through a web service portal.

You may wrap the entire procedure and call it against a table trigger or through a button click available with user form(s)

SET SERVEROUTPUT ON;
 SET DEFINE OFF;
 DECLARE
 HTTP_REQ    UTL_HTTP.REQ;
 HTTP_RESP   UTL_HTTP.RESP;
 URL_TEXT    VARCHAR2 (32767);
 URL         VARCHAR2 (2000);
 SMS_MSG     VARCHAR2 (160):= 'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway';
 BEGIN
 DBMS_OUTPUT.ENABLE (1000000);
 --Based ON your SERVICE provider, THE FOLLOWING LINK FORMAT may differ FROM
 --What we have specified below!
 URL :='http://yourwebsmsdomain.com/alerts/api/web2sms.php?username=demo&password=demo2&to=95xxxxxxx&sender=ODBSMS&message='
 || UTL_URL.Escape (SMS_MSG, TRUE);
 --UTL_URL.Escape manages ESCAPE CHARACTERS LIKE SPACE BETWEEN words IN A message.
 HTTP_REQ := UTL_HTTP.BEGIN_REQUEST (URL);
 UTL_HTTP.SET_HEADER (HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
 HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);
 -- PROCESS Request
 LOOP
 BEGIN
 URL_TEXT := NULL;
 UTL_HTTP.READ_LINE (HTTP_RESP, URL_TEXT, TRUE);
 DBMS_OUTPUT.PUT_LINE (URL_TEXT);
 EXCEPTION
 WHEN OTHERS
 THEN
 EXIT;
 END;
 END LOOP;
 UTL_HTTP.END_RESPONSE (HTTP_RESP);
 END;

Enjoy another quality solution from us :)

Regards,

Admin

28 thoughts on “Oracle Database, Send SMS through SMS Gateway

      1. Salman

        I need to know step by step instruction required to integrate. I have a table called name RECEIPT I want when I post customer receipt SMS generate to customer for acknowledgment. I am using Forms6i on Windows 8 / 7 and Oracle 10g 10.2 on Windows Server 2003.

      2. Salman

        Dear Brother

        I need to know the coding where my SMS transmit from my system to the mobile. Mobile number is in my table. If you have a solution then request you to forward it. Thank you.

      3. Salman

        Thank you dear I just run your given form and compilation error on MY_SEND_SMS and not found any procedure or function.

      4. Did you receive my email yesterday? You need to create the database level procedure using sms.sql which has the procedure pl/sql. Before proceeding, do you have a subscription for the SMS gateway?

      5. Salman

        I just check sms work out and its working but now another problem facing. My net is down and my document is not save due to net is not available. How to check net connection in Forms6i. If available then do SMS otherwise skip SMS execution.

      6. Salman

        Once Again i stuck. I change Mask name as telecom provide me when I pass Mask name through variable its not responding. If same syntax apply on browser its working. I dont understand where i am going wrong. Also I try to put my name in place of mask but still not working.

        URL := ‘http://999.999.999.99:9999/sendsms_url.html?Username=0xxxxxxxx&Password=pwd&From=Masks&To=’||Mobile||’&Message=’||UTL_URL.Escape(SMS_MSG,TRUE);

        Mask = ‘My Company Name’ also telecom registered with the same name.

      7. Hello Salman
        Please forward me the complete PL/SQL, SMS portal details (username/pword & a local mobile number) to w 7 b u g s AT g m a il DOT com

        regards,

  1. shahin

    when i use this code then reply this error…
    ORA-29273: HTTP request failed
    ORA-06512: at “SYS.UTL_HTTP”, line 1130
    ORA-29024: Certificate validation failure.

    I also configure acl. Please help me…

  2. I am trying to send sms from oracle froms 6i to mobile networks but i vain from last 3 months my deadline is now coming close day by day please There is any fmb file you have to send sms using forms6i please provide m very thanksfull to you or give me any help
    i tried many procedures and methods but not succed including yours please give ma help
    thanks in advance

  3. ABDUL LATEEF Ansari

    hello Sir, I am getting a problem. Can you please help me out with it.
    Your SMS program is working fine on Simple PL/SQL but when I make it a procedure and try to run it is showing me error continuously.
    Kindly help me out with it. Thanks.

    ORA-29273: HTTP request failed
    ORA-06512: as SYS.UTL_HTTP, line 1130
    ORA-29264: unknown or unsupported URL scheme
    ORA-06512: at “MYUSR.PRC_SMS”, line 85
    ORA-06512: at line 2
    ——————–
    line 85 : HTTP_REQ := UTL_HTTP.BEGIN_REQUEST(URL);

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.