Oracle Database, Send SMS through SMS Gateway

On By windows7bugsIn Oracle related

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

38 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.

      3. 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.

      7. Salman

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

      8. 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?

      9. 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.

      15. 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.

      16. 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,

  3. 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…

    Reply

  4. 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

    Reply
  5. 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);

    Reply
      1. BLUESKY

        well still struggling to upgrade database 21c

        facing issues in installation forms 12c & Report 12c installation and every time installation end with different problems.

        I search a lot at google and youtube and trying to follow the given instruction, but the result is ZERO.

        Initially installation is issue and i do not know when i go to migrate my forms what will happen.

        I learn that its 2 big issue for me (number 1 is JAVA and number 2 is browser) it should be match with your installation of Forms & Reports.

        anyway. I am still using Forms6i and worried about sms service is not working due to HTTPS and not yet succeed to shift Forms12c

        could you support me

        regards

      2. I will suggest you that you get right resources for the upgrade, incase if you were unsuccessful with your prior attempts. From the blogging perspectives that is limited to a lab like environment, I never had issues with setting up 12c forms/reports with 12c, 19c databases. I don’t think you will able to use https with given setups.
        Sorry, I don’t do such upgrade activities.

      3. BLUESKY

        Thanks dear, you are replying on my comments. I appreciate it. Every one learn timely and continuously in this world and trying to move up for better result. In my case, I am very hopeful and that i will INSHALLAH migrate to new vesion. I will continue learning for this purpose. Thank you once again. In case if you have any material for installation share with me. TAKE CARE AND STAY BLESSED.

      4. Thanks for the kind words Salman. I just noticed that you were trying to upgrade to 21C. I don’t think 21C is yet certified with Weblogic 12c. Your best friend is 19c database. I have a post that explains how to use 12c or 19c multitenant architecture with Weblogic 12c + Forms 12c. Please browse through my posts & give the upgrade a try from a clean installation of Windows 10/11/2016 onwards server OS. Make sure you have the latest windows updates and Visual C++ prerequisites installed. All the best. Yes, learning is a painful process :))

      5. BLUESKY

        I just finished installation Forms12c and run test form. Now the error is showing on browser “A Plugin is needed to display this content”.

Leave a Reply to windows7bugsCancel reply

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