How to send SMS from Oracle 10G XE

If you have downloaded and installed Ozeki NG SMS Gateway you can send SMS text messages from Oracle 10G XE with the use of this software product. To achieve this solution you need to configure Ozeki NG SMS Gateway to be able to send out messages from your database. Please find detailed configuration instructions on this page with helpful screenshots.

Ozeki NG SMS Gateway is a powerful software product that can be installed to your computer. As it is a two-way SMS gateway, it allows you to send SMS messages from PC to mobile phones, and receive text messages from phones to PC. This software is also support sending messages from databases such as Oracle.

To be able to send and receive SMS messages from Oracle 10G XE first you need to create two database tables: "ozekimessagein" and "ozekimessageout". You can find the instructions on this process on this page. The SMS gateway will use SQL queries for sending messages.

Ozeki NG SMS Gateway needs to be connected to the mobile network. You can do this in two ways: you can attach a GSM modem to the PC with a datacable (GSM connectivity) or the SMS gateway can connect directly to the SMS center of the mobile service provider over the Internet (IP SMS connectivity).

You can configure Ozeki NG SMS Gateway with the help of the Graphical User Interface (GUI).

Configuration steps

To configure your SMS system first you need to install Oracle 10G XE to your computer. Then log into it on the web interface (Figure 1).

log into oracle — Figure 1 - Log into Oracle

Click on "SQL" button (Figure 2).

Select "SQL commands" button (Figure 3).

Now specify SQL commands. Enter the following statements to create the table layouts (Figure 4-11).

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
    )

specify sql statement — Figure 4 - Specify SQl statement

CREATE index index_id1 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

Now log into Ozeki NG SMS Gateway with your username and password and click on "Add new user or application" to create a new database user (Figure 12).

Select "Database user interface" in the list and click on "Install" next to it (Figure 13).

select and install user — Figure 13 - Select and install user

Provide a name for the created database user (e.g. oracle) and click on "OK" (Figure 14).

In "Database connection" tab you need to specify the follows (Figure 15):

Connection string type: "OleDb"
Connection string:

Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=SYSTEM;Password=qwe123

database connection tab — Figure 15 - Database connection tab

Test

Now to test the system I will enter an SQL query. I select "INSERT INTO ozekimessageout (receiver, msg, status) VALUES ('+36205460691','Hello World','send')" from the list. Remove the semicolon and click on "Execute" (Figure 16).

On Figure 17 you can see the sent message in "Sent" folder of Ozeki NG SMS Gateway.

If I query "ozekimessageout" table with "SELECT * from ozekimessageout" statement then the status of the message will be updated (Figure 18).

status has been updated — Figure 18 - Status has been updated

More information

Connection strings | Sybase SQL Anywhere