Oracle Live SQL: The Complete Guide

In this guide, you’ll learn all about Oracle Live SQL, the web-based tool by Oracle that has a built-in Oracle database for practising your SQL.

What Is Oracle Live SQL?

Oracle Live SQL is a web-based SQL editor with an Oracle database built-in. It’s maintained by Oracle and allows you to write and run SQL statements easily without having to set up your own Oracle database.

As of October 2022, Oracle Live SQL runs Oracle database version 19c Enterprise Edition – 19.14.0.0.0.

 

How to Access

You can access Oracle Live SQL by going to livesql.oracle.com.

You’ll see a page that looks like this:

The main feature of this page is to allow you to search or browse for tutorials. We’ll cover this later in the guide, but Oracle Live SQL includes a range of tutorials to help you learn specific features of Oracle, where you can read instructions and run the SQL right in the browser.

You can also just access an SQL editor by clicking Start Coding Now.

However, you’ll need to be logged in to do this.

You’ll be taken to the login screen if you click Start Coding Now. You can also access this by clicking the Sign In button on the top right of the main screen.

If you have an Oracle account, you can enter your username and password and click Sign In.

If not, you can create a free account by clicking the Create Account button.

 

Create an Account

To create an account, enter all of the mandatory fields and click Create Account.

In my opinion, there are far too many fields on this screen that are needed to create an account, and far too many of them are mandatory.

For example, why does Oracle need to know my company name and my address to create an account?

Anyway, once you’ve filled out this page, click Create Account and you will have a new Oracle account.

 

Overview of the User Interface

Once you’ve signed in, this is the main Oracle Live SQL screen you’ll see.

There is a range of things on the screen:

Sidebar: On the left, you’ll see a sidebar that includes a few menu items such as Home, My Session, and Schema. We’ll cover these options later in this guide. If you don’t see this sidebar, you can click on the hamburger menu to show and hide it.

SQL Worksheet: the top part of the main area of the screen is the SQL Worksheet. You can write and run SQL statements here.

Output: the output of your SQL statements is shown at the bottom of the screen. This can be resized up and down to make more room for the SQL or more room for the output.

Buttons: on the top right of the screen you can see a few buttons:

• Clear: clear the SQL in the worksheet
• Find: find a string in your SQL worksheet
• Actions: view session details, reset your session, or set the maximum rows preference.
• Save: save your SQL worksheet
• Run: run the script

We’ll learn more about these buttons later in the guide.

Footer: on the bottom of the screen, you can see some information about the tool: what version of Oracle Live SQL is published, what Oracle database version it uses, and some links to documentation and other tools.

Let’s start running some SQL.

 

Running SQL Statements

To run an SQL statement in Oracle Live SQL, you type it into the SQL Worksheet and either click the green Run button in the top right or press Command + Enter (on Mac) or Ctrl + Enter (on Windows).

Let’s create a table. Here’s the SQL to do it.

CREATE TABLE customer (
  id NUMBER,
  cust_name VARCHAR2(50)
);

When we run the script, we can see a “Table Created” message.

We can insert some data by running an Insert statement:

INSERT INTO customer (id, cust_name)
VALUES (1, 'ABC Company');

We can add this statement to our worksheet after the Create Table statement.

We can run just this statement (and not the Create Table statement again) by selecting the Insert statement and running it.

It says that 1 row is inserted.

Now let’s select from our new table.

SELECT id, cust_name
FROM customer;

We can see our results are shown in the output panel.

 

Export Results as CSV

You can export the results of a SELECT query to a CSV file by clicking the “Download CSV” link that appears at the bottom of the results table.

This will save a CSV file of the results to your computer.

 

Save SQL as Script

If you want to save the SQL you have written, you can do this in Oracle Live SQL.

Click on the Save button on the top right of the screen.

This will show a Save Session window. This will actually save your SQL Worksheet contents and all database objects you have created as a script that you can run again.

The intro message says:

Saving your session will allow you to restore your database objects and share your session with others. Once you save your session, it will show under the My Scripts tab within this application.

Enter a name for your script. I’ll call this one “Customer Script”.

Set the visibility:

• Private: only you can see this
• Publicly Shareable: a URL is generated and you can share the script using this IRL
• Published: the URL for the script is included in the Live SQL Code Library

Select Private for now.

Enter a description for your script.

In the Area drop-down, you can select which category this script belongs to, which can be helpful for organising your scripts if you have a lot of them.

You can also enter some tags to help categorise your script.

Click “Save Session as Script” when you are done.

Your script is then saved.

On the page that loads, you can see:

• the details you entered about the script (name, description, area, tags, visibility)
• when it was last updated
• each statement in the script and its output

We’ll go more into this script page and what else you can do in the My Scripts section of this guide.

 

My Session

Click on the My Session item on the left sidebar and you can see some information about your current session.

Here we can see a history of SQL statements that have been run on your session, along with their output.

You can sort the statements by most recent first or last. You can also show statements only, statements and results, results only, or errors only.

There are a few buttons on the top right of the screen. Here’s what they do.

 

Actions – Re-Run

Clicking Actions then Re-Run will let you run the scripts you have already run in this session. You have the option to drop all objects that you have created, to avoid any errors about objects that exist.

A popup will appear:

Here’s what the description says:

Pressing the Re-Run Script button will run all statements in the session. If you have already performed actions using this session and wish to first remove all existing database objects and/or remove your current session history, check the appropriate checkboxes as well.

Click the Re-Run Script button and your statements will be run again.

 

Actions – Replace Script

Click on Actions then Replace Script and you’ll see this popup.

This will let you replace a script you have previously saved with the script from your current session.

First, select a script name to replace.

Then, select the visibility of your new script:

• Private: only you can see this
• Publicly Shareable: a URL is generated and you can share the script using this IRL
• Published: the URL for the script is included in the Live SQL Code Library

Just like with saving a new SQL script or session, you can enter a description for your script, select an area, and enter tags.

Click Replace Script when you are done and the script you have selected will be replaced.

 

Actions – Delete Statements

If you click Actions then Delete Statements you can delete multiple statements in the list.

You’ll see this popup.

Click Delete to delete all statements.

 

Actions – Download

Click Actions then Download and you can download the scripts from the current session.

You’ll see this popup.

In the Options drop-down, you can select what you want to download:

• All statements
• Data Definition (DDL) only
• Data Manipulation (DML) only
• Queries only

Click Download and an SQL file will be saved to your computer.

However, I tried this a couple of times and the resulting SQL file shows an error message:

error: ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275

I’m not sure what the issue is here, and perhaps I’m doing something wrong.

 

Reset Session

Click on Actions then Reset Session and you’ll see this window.

This will allow you to reset your session, which means all database objects will be removed, session history is removed, and NLS settings are restored to their defaults.

You can check or uncheck any of these options.

Click Perform Actions and the selected actions will be performed.

 

Save

This will save the scripts as described in the Save SQL as Script section above.

 

Previous Sessions

On the left sidebar, there is an entry called Previous Sessions.

If you click this, you can see information about all of your previous sessions or the times you’ve accessed Oracle Live SQL in the past.

Here’s what my list shows.

It shows you when the sessions were last created and accessed, and how many statements were included.

If you click on View Session in the Actions column, you can see a list of statements that were run in that session.

You can then save the session for future access, or copy the scripts to another file.

I think this is a very handy feature.

 

Utilization

Click on the Utilization menu item under the My Session item on the sidebar and you can see information about your current session.

You can see the name of the session, if you have saved it.

You can also see a range of resource metrics, such as session duration, idle time, sessions in history, and more.

 

NLS

The final item in the sidebar under My Sessions is NLS. If you click on this, you can see all of the current NLS (National Language Support) values.

You can’t change any of the values here, but you can change them by running an Alter Session statement in the SQL Worksheet.

 

Explore the Database Schema

So you’ve created some objects. How can you see them?

You can view the objects in your schema by clicking the Schema item on the sidebar.

This screen is shown. You can see all of your objects, such as tables, in the middle of the screen.

In the image above, we can see the Customer table that has been created.

On the left of the main panel, you can search for objects, filter based on a schema, and sort objects by name or date.

If you click on a table, you can see more information about that table.

You can do a few other things in this Schema screen.

 

Upload Script

Click on the Upload Script button and you can upload an SQL script file from your computer and save it as a Script on your account. See more about how to do this in the Open and Run a Script section later in this guide.

 

Actions – Drop All Objects

Click on Actions then Drop All Objects, and you will be prompted to confirm if you want to drop all objects in your session.

If you click Drop Objects, all objects will be dropped.

 

Actions – Generate DDL

Click on Actions then Generate DDL, and a window will appear that shows the SQL that has been generated to create the objects in your database.

Here’s the script in this example:

CREATE TABLE  "CUSTOMER" 
   ( "ID" NUMBER,
"CUST_NAME" VARCHAR2(50) COLLATE "USING_NLS_COMP"
   )  DEFAULT COLLATION "USING_NLS_COMP"
/
CREATE OR REPLACE EDITIONABLE SYNONYM  "DBMS_XPLAN"
FOR "LIVESQL"."ORACLE_SQL_USER_XPLAN"
/
CREATE OR REPLACE EDITIONABLE SYNONYM  "V$SESSION"
FOR "LIVESQL"."ORACLE_SQL_USER_V$SESSION"
/
CREATE OR REPLACE EDITIONABLE SYNONYM  "V$SQL_PLAN_STATISTICS_ALL"
FOR "LIVESQL"."ORACLE_SQL_USER_V$SQL_PLAN_S_A"
/

It’s different to the script that we ran, as this is generated from what is on the database.

 

Create Database Object

Click on the Create Database Object button, and you can create one of a range of object types on the database. You’ll see this window.

Here are the object types you can create:

• Table
• View
• PL/SQL Procedure
• PL/SQL Function
• PL/SQL Package
• Sequence
• Type
• Trigger
• Index
• Synonym

If you click on one of the options, you’ll see another screen where you can input the object name, parameters, and other options.

The screen is different for each type of object.

Here’s what the Create Table screen looks like.

Here’s what the Create Sequence screen looks like.

Once you click Generate SQL, you’ll be taken to the SQL Worksheet screen, with the SQL to create the new object populated into the worksheet.

Click Run to run the SQL and to create the object.

 

Open and Run a Script

To upload an existing SQL script file that you have created on your computer, you can open the Schemas item and click on the Upload Script button.

The following window will appear.

This window lets you select an SQL file from your computer and upload it to your Oracle Live SQL account. It gets saved as a Script in your account, which you can then use to run on your database.

Select the file to load, provide a name and a description, and click Upload Script. The script is then uploaded.

Here’s an example of a script:

CREATE TABLE product (
prodname VARCHAR2(100)
);
INSERT INTO product (prodname) VALUES ('item1');

This can be uploaded using the Upload Script feature.

This is saved as a new Script.

You can then run the script.

 

Quick SQL

The Quick SQL feature allows you to use some formatted text to generate a data model and SQL.

From the help section:

Quick SQL provides a quick way to generate the SQL required to create a relational data model from an indented text document. This tool is designed to reduce the time and effort required to create SQL tables, triggers, and index structures. This tool is not designed to be a replacement for data modeling, it is simply a quick way to develop a script for simple tables and views. Once the SQL is generated it can be tweaked and expanded upon.

You can enter text in a shorthand format, which is well-described in the help guide.

You can also load a sample to see how it works.

Here, we’ve loaded a sample to create employee and department tables.

On the left, we can see some text written in a specific way.

On the right, we can see the SQL to create tables, create views, and insert some data. This SQL on the right was generated based on the text on the left.

I won’t go into the full use of this feature in this guide, but if you want to learn more, check out the help guide within Oracle Live SQL.

 

My Scripts

Click on the My Scripts section on the sidebar and you’ll see a list of all of the scripts you have saved in your account.

This includes scripts you have saved in previous sessions as well as the current session, and any scripts you have uploaded using the Upload Script feature.

The heart icon indicates the number of likes (scripts can be liked by others if they are visible), and the number of times the script has been run.

On this screen, you can:

• search for scripts
• sort by date added, date last touched, name, invocations, and statements
• filter by visibility and area

If you click on a script in the list, you can see more information about it.

You can edit the attributes of the script here, or run the script.

 

My Tutorials

Oracle Live SQL lets you create tutorials. A tutorial is a collection of instructional steps and SQL that users can read and run within Oracle Live SQL.

There is a range of tutorials already created, and accessible in the Code Library. But you can also create your own.

Click on My Tutorials on the sidebar to see this screen.

You can search, sort, and filter your tutorials here.

 

Create Tutorial

We don’t have any yet, so let’s click Add Tutorial.

The Tutorial Details window is shown.

You can enter a range of details for this:

• The name of the tutorial
• The area that it belongs to
• The overview or description of the tutorial
• Any setup SQL that is needed
• Any tags that you want to apply
• The user display name

Click Add Tutorial when you are done. The tutorial is created and is set to Private by default.

 

Modules

Within a tutorial are modules. To add a module, click on the plus button in the modules section.

The Module window is displayed.

Enter a name for the module, the tutorial it belongs to (which is already set), and the display sequence (which is the order it appears in).

Then, enter the content of the module. You can use HTML tags to format it.

You can click on the Example Module Content to see an example that can be helpful.

Let’s see the example and copy it into our module.

Click on Add Module.

The module is added to the list.

We have one module in our tutorial, and could add more if we need to.

 

Run Tutorial

To run a tutorial, open the tutorial and click on the Run button.

The SQL Worksheet screen will be opened, with the tutorial displayed in a panel on the right.

This will let you:

• execute the prerequisite SQL that you had defined in the tutorial
• see the text within each module
• copy any code (which was formatted in code tags) to the editor for you to run.

This makes it easy for others to follow along with your script.

 

Code Library

Click on the Code Library menu item on the left, and you’ll see the Code Library in Oracle Live SQL.

This contains a range of tutorials and scripts that have been shared publicly within Oracle Live SQL. Your script or tutorial will appear here if you set the visibility to Public.

On this screen you can:

• search for tutorials or scripts
• filter based on an area
• filter for tutorials, scripts, or both
• sort by date added, number of executions, name, or number of likes
• show only the liked tutorials or scripts
• change the number of results per page

On the main part of the screen, you can see a list of tutorials or scripts.

You can see:

• the title
• the description or overview
• whether it’s a tutorial or a script
• a list of tags
• the number of likes (the heart icon)
• the number of times it has been run (the play icon)
• when it was created
• who created it

You can open an item by clicking on it. Here’s an example of opening a tutorial.

It looks similar to a tutorial you may have created yourself.

If you want to run the tutorial or script, click on Run Tutorial or Run Script. You’ll then see the SQL Worksheet, where you can read the tutorial and run the scripts inside it.

 

Frequently Asked Questions

 

How Can I Change the Maximum Number of Rows to Show?

On the SQL Worksheet, click on Actions, then Maximum Rows Preference. Change the value of Maximum Rows to Query to another value in the list. The maximum is 5,000.

The maximum is shown to prevent your browser from being overloaded with results.

 

Can Oracle Live SQL Import CSV Data?

Unfortunately, at the moment Oracle Live SQL does not have the ability to import a CSV file.

An alternative would be to convert it to a series of SQL Insert statements (in a spreadsheet, for example) and upload the SQL script.

 

How Can I Show Tables in Oracle Live SQL?

You can see all of your tables in the Schema section. You can also select from the user_tables view to see a list.

SELECT *
FROM user_tables;

This will show all tables in your schema.

 

Can I Load the HR Database in Oracle Live SQL?

Yes, there is a script in Oracle Live SQL for creating these tables. You can find it here, or search for “HR” within the Code Library.

 

Can I Show Line Numbers for Debugging?

There is no setting to enable this, but you can follow the steps in this script to help with debugging and seeing a stack trace.

 

Conclusion

Oracle Live SQL is a helpful web-based tool and a great way to get started working with an Oracle database or practicing SQL online.

It has various features to work with database objects and save your scripts in your account.