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

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

How to send SMS from Oracle database

Configuration steps

Test

More information