Wednesday, September 6

Sending SMS from Oracle (abuse warning)

Ok, this post is mostly to annoy people who think stuff like this should be done from the application layer. I do agree with them but these things can done from the db layer as well.
Anyway, here is a little code sniplet to send a SMS text message from the database just using a insert statement. The SMS provider we use at work for basic Nagios alerts and stuff like that is Sign.Up To, cheap and quite reliable (they did have some downtime yesterday though). To send the actually message we use a trigger which in turn calls the quite handy (and abusive) dbms package utl_http, utl_http works in Oracle 8 and up (including XE).
If you haven't got utl_http installed you can do so by running the two scripts ?/rdbms/admin/utlhttp.sql and ?/rdbms/admin/prvthttp.plb as sysdba on the server. The response you get back is either a message id or the keyword "fail" if the message fails for some reason, the trigger simply catch that message and store it a table.
This code is just proof of concept and should not be used for anything "real", no error handeling or input checking. It just sends off the data to the message aggregator. And it can't handle any fancy characters in the message nor spaces. You can easily implement basic URL encoding using this function written by Tome Kyte.
create table smslog (
sms_id number(8),
smsno varchar2(18) not null,
message varchar2(160) not null,
status varchar2(150),
sendtime date
);

alter table smslog add constraint
smslog_pk primary key (sms_id);

create sequence smslog_pk_seq;

CREATE OR REPLACE TRIGGER smslog_insert_trigger
BEFORE INSERT ON smslog FOR EACH ROW
DECLARE
req UTL_HTTP.REQ;
resp UTL_HTTP.RESP;
val VARCHAR2(2000);
url VARCHAR2(300);
BEGIN
url := 'http://sms.sign-up.to/smpp_send.php?username=username&from=oracle&
account=account_no&password=yourpassword&
message='||:new.message||'&to='||:new.smsno;
req := UTL_HTTP.BEGIN_REQUEST(url);
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0 Oracle');
resp := UTL_HTTP.GET_RESPONSE(req);
UTL_HTTP.READ_LINE(resp, val, TRUE);
UTL_HTTP.END_RESPONSE(resp);
:new.status := val;
:new.sendtime := sysdate;
SELECT smslog_pk_seq.nextval INTO :new.sms_id FROM dual;
END smslog_insert_trigger;
/

-- And a sample sent message.SQL> insert into smslog(message,smsno)
2 values('Eat_more_fruit','4479697xxxxx');

1 row created.

SQL> select * from smslog;

SMS_ID SMSNO MESSAGE STATUS SENDTIME
------- --------------- -------------------- ------------- ---------
1 4479697xxxxx Eat_more_fruit 3211821 06-SEP-06

7 comments:

Anonymous said...

Hi I have tried with the above sniplet code...When i was creating the sequence it was asking for the arguments. i have created one user login id and password for the sign-up.to.It is not sending message to the corresponding number which is inserted to the smslog table. Please help..

Anonymous said...

Hi I have tried with the above sniplet code...When i was creating the sequence it was asking for the arguments. i have created one user login id and password for the sign-up.to.It is not sending message to the corresponding number which is inserted to the smslog table. Please help.. mail ur info to selvakumarganesan@yahoo.com

a person said...

somnath:
hi i tried this and it worked for first few message, but later on its giving this error..... in the status field "Error title.head.The parameter is incorrect" get populated, once i send.... can you tell me why?

Qamar Abbas said...

I have a very simple solution, I have created my own sms gateway to send sms from Oracle Applications. Please visit my blog : http://qamarsyed.blogspot.com/

cheers.

Qamar Abbas

Anonymous said...

i try doing same thing all created successfully....
but while inserting data in table it showing me error as...
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1231
ORA-29263: HTTP protocol error
ORA-06512: at "APPS.SMSLOG_INSERT_TRIGGER", line 13
ORA-04088: error during execution of trigger 'APPS.SMSLOG_INSERT_TRIGGER'


can u send me test case....from openig account and passing values to trigger as well

mail id: surana143.lokesh@gmail.com

MSSR said...

I tried this and found getting "Authorization failed to sendsms".

gamalbadr said...

I tried it and it is PERFECT
Thanks.