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).
Figure 1 - Log into Oracle
Click on "SQL" button (Figure 2).
Figure 2 - Click on SQL button
Select "SQL commands" button (Figure 3).
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(30) default NULL,
receiver varchar(30) 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
) |
Figure 4 - Specify SQl statement
CREATE index index_id1 ON ozekimessagein(id) |
Figure 5 - 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
|
Figure 7 - Specify SQl statement
CREATE TABLE ozekimessageout (
id int,
sender varchar(30) default NULL,
receiver varchar(30) 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
) |
Figure 8 - Specify SQl statement
CREATE index index_id2 ON ozekimessageout(id) |
Figure 9 - 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
|
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).
Figure 12 - Add new user or application
Select "Database user interface" in the list and click on
"Install" next to it (Figure 13).
Figure 13 - Select and install user
Provide a name for the created database user (e.g. oracle)
and click on "OK" (Figure 14).
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 |
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).
Figure 16 - SQL query
On Figure 17 you can see the sent message in "Sent" folder
of Ozeki NG SMS Gateway.
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).
Figure 18 - Status has been updated
Dig deeper!
People who read this also read...
Oracle
Connection string
Database user
Users and applications
Service provider connections
Installation steps
Next page:
Sybase SQL Anywhere
| 
