Send SMS from Oracle SQL (part 1/4)

Oracle Installation

Oracle is a database management product, which is used not only to store data, but effectively manage, and provide it to other industry-standard softwares. It works great with the Ozeki SMS Gateway software, but you will need to install it on your computer. This guide will help you with that. It is a very easy process, it should not take more than 15 minutes, depending on your internet connection.

Video content
1. Download Oracle
2. Install Oracle

Please jump to the next video or if you scroll down you can find screenshots that describe how to download and install Oracle Database 18c Express and how to set the port numbers and SYSTEM password.

The first thing you want to do, is to install the Oracle 18c Express Edition on your computer. Please head over to oracle.com/downloads, and search for the Database 18c Express Edition download link. If you have found it, proceed to the next step. You can see the download page on Figure 1.

download oracle database
Figure 1 - Download Oracle Database from Oracle's webpage

You are now on the download page of the Oracle Database Express Edition. The software is capable of running on Windows, and Linux, so it has two version. Make sure to download the Windows x64 edition. To see where to click, please look at Figure 2.

choose oracle database
Figure 2 - Choose Oracle Database 18c Express Edition for Windows x64

To have a copy of the software on your computer, you need to accept the Oracle License Agreement. Without it, you will not be able to download the required files. Tick in the box for accepting the License Agreement and click the Download OracleXe184_Win64.zip button. You can see the tickbox and the button on Figure 3.

accept license agreements
Figure 3 - Accept license agreements

In order to download the files, you need to sign in to your Oracle account. To do that, please provide your username and your password into the appropriate field. If you does not have an Oracle Account, you can create on for free with the Create Account button down below. You can find these on Figure 4. Press the sign in button to proceed.

sign in
Figure 4 - Sign in

Now, your browser should start the process of downloading all the required files. You can watch the download status on the bottom of your browser window, as you can see on Figure 5.

downloading
Figure 5 - Downloading

Now you need to find the files, your browser downloaded for you. The default location for the chrome downloaded files is C:\Users\username\Downloads. You can get to the downloads folder using chrome’s downloads tab. You can see the files you need to search for in Figure 6. The files are in a compressed format, you need to extract them. To do that, please right-click the downloaded folder,and from the options, select the Extract all option.

extracting the file
Figure 6 - Extract the downloaded file

Now that you have the files extracted, search for the installer file. It is the Oracle Database 18c Express Edition executable file. Click on it twice to start the installer. Now we will guide you through the setup process in the following steps. You can see the file you are looking for on Figure 7.

open the installer
Figure 7 - Open the installer

Now you have started the Installation. Click the Next button the proceed to the next step. You can see the first page of the installation on Figure 8.

installation
Figure 8 - Installation

To have an official copy of the software, you need to accept the license agreement of the Oracle database. Simply tiick the “I accept” option and click “Next”. You can print of a copy of the license agreement if you want with the “Print” button. You can see the license agreement on Figure 9.

accept license agreement
Figure 9 - Accept license agreement

Now you can choose where to install the Oracle Database software. You could choose any destination you wish, just make sure that you have enough free space in the destination. You can change the place using the Change… button. You can see the window on Figure 10.

choose a destination folder
Figure 10 - Choose destination folder

Now you can choose a strong password for the main accounts. Make sure to choose a Database password that you will remember and type it in the first and the second textbox. The second textbox is making sure you have no misspell in the password. Make sure that only those know the password who should have access to the databases. Click Next if you have a good password. You can see the textboxes on Figure 11.

specify system database accounts password
Figure 11 - Specify SYSTEM database account's password

Now you have everything ready, you can start the installation progress. Please click the “Install” button to start the installation itself. The installer will start to set everything up. You can see the button on Figure 12.

click the install button
Figure 12 - Click the 'Install' button

Now all you need to do is wait for the installer to finish the process of installation. As you can see on Figure 13, you get a process indicator bar, which can be used to check the installation progress. If it is filled with green, it means that you have a working copy of the software. You can see the progress indicator bar on Figure 12.

wait until the installation is finished
Figure 13 - Wait until the installation is finished

If you see the window you can see on Figure 14, it means that you have a working Oracle Database software. Please click on Finish button. It will close the installer. You can now open the software.

oracle eighteen c expresss installation is finished
Figure 14 - Oracle 18c Express's installation has been finished

Create ozeki database schema

create ozekidb database
Figure 15 - Create ozekidb database

CREATE TABLESPACE ozekidb
DATAFILE 'ozekidb.dbf'
SIZE 20M AUTOEXTEND ON;

create ozekiuser user in oracle
Figure 16 - Create ozekiuser user in oracle

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

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

create ozekimessagein table with ozekiuser
Figure 17 - Create ozekimessagein table with ozekiuser

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 ozekimessageout table with ozekiuser
Figure 18 - Create ozekimessageout table with ozekiuser

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

More information