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.
Video content1. 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 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.
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;
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;
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; /
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.
More information