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
Request_Failed: ORA-12532: TNS:invalid argument
Hello Faisal
Elaborate the error.
regards,
I want to send SMS using Forms6i Windows8 Database 10g 10.2, Would you like to help me in this regard
Hello Salman
I believe the solution provided could be easily integrated with forms 6i. What kind of specific help you need in your case?
regards,
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.
Hello Salman
I’ve already mentioned about creating a trigger for the base table, in your case where RECEIPTs will be stored. You can create INSERT trigger for the table, call the procedure I have provided, then push the SMS through the gateway. If you are not familiar with table triggers, please check this thread http://psoug.org/reference/table_trigger.html
regards,
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.
I sent you a zip file with samples :) Try and let me know whether you were able to understand the logic.
No ZIP file found my email address is bluesky_18011971@yahoo.com
https://drive.google.com/file/d/0B-3iVeOMTCbWSEFOZTlVTWxIOXc/view?usp=sharing
Please try to download it from the above link.
Thank you dear I just run your given form and compilation error on MY_SEND_SMS and not found any procedure or function.
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?
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.
You should save your database transaction first by issuing form commit. Then call the sms procedure
i CALL PROCEDURE FROM PRE-INSERT trigger.
That’s the problem :). Use the PRE-INSERT trigger to generate order id etc, and post commit, call the procedure.
no such type of trigger called POST COMMIT.
When I said POST commit, it means, after committing :)
Now try to solve the situation yourself. That’s how we all learn.
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.
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,
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…
Please check you email, respond to me with the entire PL/SQL sequence you have coded. That will help us to rectify your issues.
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
Hello there, I am traveling now. I should have a sample form somewhere. Please do send me a mail so that I can attach the form (if I find it)
Btw, do you have a contract with SMS service provides already?
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);
Please send me the PL/SQL script by email. Check the contact info page for further information.
It looks like the SMS gateway you have mentioned has some issues. Please share the PL/SQL
in case of HTTPS. its not working.
You asked this question few “years” back. https://forums.oracle.com/ords/apexds/post/sending-sms-using-https-instead-of-http-0450
Answers include suggestions about upgrading your database, that is 10g. Have you upgraded your infrastructure already?
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
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.
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.
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 :))
I just finished installation Forms12c and run test form. Now the error is showing on browser “A Plugin is needed to display this content”.
You need to install JRE 32-Bit 1.7/1.8 237 or later. In addition you need to go to java settings from control panel and under security, sites tab add the complete URL for your site. Example, http://myservername.mydomain.com:portnumber/
I have your email address, I will send you few screenshots that may help you.
Please send me a blank email to w7bugs@live.com