Send SMS from Oracle SQL (part 2/4)

In this video series you can see how to connect Ozeki SMS Gateway and Oracle SQL database server for SMS messaging. This video shows how to create the proper database table structure by using Oracle SQL through it's command line.

Video content
1. Connect to Oracle
2. Copy CREATE TABLE statement

Please scroll down to copy the SQL statements Figure 2-4 used in the video. If you have created the database in Oracle 11g Express, you can jump to the next video.

If you have the Oracel SQL, you need to connect it to the Ozeki SMS Gateway. To do that, you will need to have some tables, which you now will create. First, please run an SQL Command Line. Type in “connect”. Now you will need to provide the user-name and the password, which you choose during the installation procedure. If you are connected, please proceed to the next step.

connect to the oracel database and create ozekidb database
Figure 1 - Connect to the Oracle database and create ozekidb database

Oracle SQL statements to copy:

This code snippet will create a suitable tablespace for your SQL database. Copy the code, paste it into the SQL Command Line and hit enter.

CREATE TABLESPACE:

CREATE TABLESPACE ozekidb
DATAFILE 'ozekidb.dbf'
SIZE 40M autoextend on;
Figure 2 - CREATE TABLESPACE ozeki

create ozekiuser user
Figure 3 - Create ozekiuser user

This code snippet will create a user, which will have access to the database and permission to modify it. Copy the code and paste it into the SQL Command Line just as you did before

CREATE USER:

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

CREATE USER ozekiuser 
IDENTIFIED BY ozekipass
DEFAULT TABLESPACE ozekidb;
 
GRANT DBA TO ozekiuser;
Figure 4 - CREATE USER ozeki and GRANT database access TO ozeki

This code will create the table, all the data fields in it, the index, the sequence, and the trigger needed for the database table to function. Copy the code and run it in the SQL Command Line.

CREATE TABLE:


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_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;
    /
Figure 5 - CREATE TABLE ozekimessagein and ozekimessageout

All you need to do, is to copy all the code snippets from above and paste it in order of the tutorial. You can do it, by highlighting the whole code part, and press “CTRL+C”, then paste it into the SQL Command Line by hitting “CTRL+V”. You can see the procedure on Figure 6.

copy sql statements
Figure 6 - Copy SQL statements

Run all of them and you will have a working database table, which can communicate with the Ozeki SMS Gateway. You can see all the codes pasted and executed on Figure 7 and 8.

paste the statements and run them to create ozekimessagein table structure
Figure 7 - Paste the statements and run them to create ozekimessagein table structure

paste the statements and run them to create ozekimessageout table structure
Figure 8 - Paste the statements and run them to create ozekimessageout table structure

More information