How to send SMS messages from Oracle

In this guide you can learn how to send and receive SMS messages using an Oracle database. This guide is an extension of the general SQL to SMS configuration guide. It contains Oracle specific information. It is assumed, that you already have an Oracle database server and the Oracle client components installed. You also need Ozeki NG SMS Gateway installed on your system to have this example work.

In order to be able to send and receive SMS messages from Oracle, you need to install and appropriately configure Ozeki NG - SMS Gateway. Ozeki NG SMS Gateway can send and receive SMS messages through a GSM phone or GSM modem that is attached to your computer with a phone-to-pc data cable or through IP SMS service provider connections over the Internet, such as SMPP, CIMD2 or UCP/EMI connections. After one of these connections is set up, you need to install a Database user in the "Users and Applications" menu of Ozeki NG. Once the Database user is installed and configured properly, you will be able to send SMS messages from Oracle simply by inserting a record into a database table, and incoming SMS messages will be saved into another database table in your Oracle database server.

To configure the database user in Ozeki NG, please use the following information:

Connection string type:Oracle
Connection string:Data Source=127.0.0.1; User ID=username; Password=pwd123;

(Alternative connection strings for Oracle can be found at the Oracle connections strings page)

Of course you have to replace the 127.0.0.1 IP address to the IP address or the Data source name of your Oracle database server, and the username and pwd123 field to the database username and database password that matches your user account information stored in Oracle.

Once the connection is configured in Ozeki NG, all you have to do is create two database tables in your Oracle database according to the following table definition.

Oracle create table script: 

CREATE TABLE ozekimessagein (
    id int,
    sender varchar(255) default NULL,
    receiver varchar(255) default NULL,
    msg varchar(160) default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    operator varchar(120) default NULL,
    msgtype varchar(160) default NULL,
    reference varchar(100) default NULL
    );
    CREATE index index_id ON ozekimessagein(id);
    CREATE SEQUENCE X;
    CREATE TRIGGER ozekimessagein_auto BEFORE INSERT on ozekimessagein
    for each row
    when (new.id is null)
    begin
    SELECT x.nextval INTO :new.id FROM DUAL;
    end;
CREATE TABLE ozekimessageout (
    id int,
    sender varchar(255) default NULL,
    receiver varchar(255) default NULL,
    msg varchar(160) default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    operator varchar(120) default NULL,
    msgtype varchar(160) default NULL,
    reference varchar(100) default NULL,
    status varchar(20) default NULL,
    errormsg varchar(250) default NULL
    );
    CREATE index index_id2 ON ozekimessageout(id);
    CREATE SEQUENCE Y;
    CREATE TRIGGER ozekimessageout_auto BEFORE INSERT on ozekimessageout
    for each row
    when (new.id is null)
    begin
    SELECT y.nextval INTO :new.id FROM DUAL;
    end;

If you want to work with larger messages, you can increase the size of the msg field, or you may also change it's data type.

It is strongly recommended to maintain an index in the database for the 'id' field of the outgoing SMS table to support SQL updates.

If you want to use unicode characters, you should use NVARCHAR2 as the datatype, you should put "Unicode=True" into the connection string and you should make sure that the NLS_CHARACTERSET and NLS_LANGUAGE parameters for your database are set to your language. You may also try to set the following registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEID\NLS_LANG to UTF8

Note: Oracle 8i clients cannot access NVARCHAR2 data in Oracle 9i databases when the server's national character set is specified as AL16UTF16 (the default setting for Oracle 9i). Because support for the UTF-16 character set was not introduced until Oracle 9i, Oracle 8i clients cannot read it.

More information