Oracle Live SQL: An online database scripting tool for the smart

Have you always desired to test on the fly, a script you just thought up to see if it would work? If you are that someone like me then you are in luck as Oracle Corporation has designed something for us (those guys think of everything). Ladies and Gentlemen, I introduce to you ORACLE LIVE SQL.

Disclaimer:
Please note that I am in no way affiliated to Oracle nor was I paid to spin this hype.😀

Oracle Live SQL provides an online platform where SQL and PLSQL can be tested/executed. There are restrictions however which we will look into later.

It is a perfect alternative in an event where an installed oracle database is not available.

It allows for scripting on the fly where conceptual queries can be quickly tested anywhere/anytime.

Oracle Live SQL was made globally available in the last quarter of 2015 and has since been growing in awareness. 
The Live SQL runs on Oracle Database 12c Enterprise Edition - 12.2.0.1.0.

Features

With Oracle Live SQL you have access to run scripts (SQL or PL/SQL) on an online oracle database

It has the ability to save all the scripts executed. You can share these script with other users in the community and there are several educational tutorials available for learning (which I will show you soon)

What you need to use Oracle Live SQL

Basically all that is needed is an OTN account. This is the Oracle Technology Network account which is by the way free. 

Now let’s start the real stuff.

Url for Live SQL is:

https://livesql.oracle.com

You can also Click Here to the url

1.       Create an OTN account: This is a single sign on account which allows you to authenticate into any site of Oracle that requires authentication. This can be seen below pointed by a red arrow

2.       If you do not have an OTN account you will be required to create one. So let’s go ahead to create one. Click on create account button and fill the form.

3.       Once done, go to your mail box to verify the email address. Please note that the “verify Email” button will expire in 24 hrs.

4.       Once you verify you are good to go

5.       Log in to your new OTN account and let’s start. You will be required to accept the Application disclaimer agreement form.

6.       Behold your Live SQL Home page! Now let’s look at what it can do.

7.       To start writing SQL script you will need  to click on the “SQL Worksheet" tab at the Left hand corner of the Live SQL home page

8.       Let us run our very first query. Please type the following simple script in the sql worksheet space

select * from hr.employees

Once done, click on the Run command button at the top right corner of the worksheet. It should give you an output in form of a table

9.       You can save your session by clicking on the “save” button just beside the “run” button or Click on “My session” link at the left hand corner of the worksheet.

Please note that Live SQL saves your session in form of a script. This will always be re-run when recalled to be opened. Please click on save.

10.       This will take you to a page where you will have 3 options to share your script.

    You can either:

o   Make it Private, where only you can see it.

o   Make it Unlisted, where you will have to provide a link to the person you will like to view it. The link can be sent via email or any other medium.

o   Make it Public. Here everyone can see your script.

11.       The Schema option allows you to view your schema objects. You are allowed to create schemas of your own. 
         Simply put a schema is a collection of database objects that you own (Tables, triggers, views etc). We will get to that soon.

12.       Let us create our first schema by writing the following script:

Create table TEST_EMPLOYEE as select * from HR.EMPLOYEES;

        13.   Now we have a schema object of ours which is a table called TEST_EMPLOYEE as seen below

14.       Please note that if you do not save your session you will lose all the tables you have created.

Why should you create tables in the first place?

Oracle Live SQL will not allow you to make any modification to their online tables as they are shared. The tables are flagged as read only. So if you are running queries that make modification to tables like inserts and updates, the script would fail. To get such script to work would require you to make a copy of the table to your schema for modification.

15.       The "Design" link at the leftmost part of the page is used to create new table structures

16.       You can search for your SQL scripts using the “My Scripts” link.

17.       The Code Library displays all the scripts written by the community and more like tutorials

What are the restriction on Live SQL

According to Oracle the following control has been put in place to ensure the shared community database is well sanitized and generally available for all:

·         Resources available to user profiles are limited

·         Restrictions have been places on the privileges accessible by a Schema.

·         Statements that can be run per session have been limited to ensure equal performance distribution.

·         Idle time-out is enforced. This is so that there is no redundant usage of resources

·         There is a limit on how long your session can stay active

·         There’s a limit on storage (oh for sure!!!)

·         Data dictionary views and system packages are heavily limited

Oracle has also limited us to the following CREATE commands:

·         CREATE TABLE

·         CREATE INDEX

·         CREATE VIEW

·         CREATE PROCEDURE

·         CREATE PACKAGE

·         CREATE FUNCTION

·         CREATE SEQUENCE

·         CREATE TRIGGER

·         CREATE SESSION

·         CREATE DIMENSION

·         CREATE INDEXTYPE

·         CREATE OPERATOR

·         CREATE TYPE

And that's it!

I hope you enjoyed this post and have been enlightened on the benefits of Live SQL.

And before I go, guess what? 

I also run Live SQL on my mobile phone. Don't believe me? 

Check out the screenshot below.

Scripting on the fly.....😃😃