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 its command line.

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

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 ozekidb
DATAFILE 'ozekidb.dbf'
SIZE 40M autoextend on;

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

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

CREATE USER ozekiuser 
IDENTIFIED BY ozekipass
DEFAULT TABLESPACE ozekidb;
 
GRANT DBA TO ozekiuser;

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 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;
    /

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.

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.