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