How to send SMS from Oracle database

If you have downloaded and installed Ozeki NG SMS Gateway, you can send SMS text messages from Oracle 21C XE with the use of this software product. To achieve this sms messaging solution, you need to configure Ozeki NG SMS Gateway to be able to send out messages from your database. Ozeki NG SMS Gateway can handle bulk SMS messages. 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 high volume SMS messages from PC to mobile phones and receive text messages from phones to PC. This software is also supports sending messages from databases such as Oracle.

To be able to send and receive SMS messages from Oracle 21C 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 data cable (GSM connectivity) or the SMS gateway can connect directly to the SMS centre 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 21C 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).

click on sql button
Figure 2 - Click on SQL button

Select "SQL commands" button (Figure 3).

select sql commands
Figure 3 - Select SQL commands

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)

specify sql statement
Figure 5 - Specify SQl statement

CREATE SEQUENCE X

specify sql statement
Figure 6 - Specify SQl statement

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

specify sql statement
Figure 7 - Specify SQl statement

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
    )

specify sql statement
Figure 8 - Specify SQl statement

CREATE index index_id2 ON ozekimessageout(id)

specify sql statement
Figure 9 - Specify SQl statement

CREATE SEQUENCE Y

specify sql statement
Figure 10 - Specify SQl statement

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

specify sql statement
Figure 11 - Specify SQl statement

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).

add new user or application
Figure 12 - Add new user or application

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).

provide a name
Figure 14 - Provide a name

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).

sql query
Figure 16 - SQL query

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

sent message
Figure 17 - Sent message

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