A Quick Guide to Using SQL Within Oracle APEX

Oracle APEX is an Oracle application development environment which will allow you to access your
Oracle database from outside of the university network. Note that it has restricted functionality
compared to SQL Developer and some advanced SQL and PL/SQL facilities may not run as expected.

The URL to access Oracle APEX is [Link]

It is strongly recommended that you use Google Chrome to access Oracle APEX.

Your Workspace name is your user-id, e.g. cs0xyz

Your username is your user-id followed by ‘_apx’, e.g. cs0xyz_apx

Your password is your student registration number, i.e. the long number on your student card.

You should type these details into the window, and then click the
button.

You will then be presented with the following window once you have successfully logged in:

© David Nelson, Version 2, 5th October 2018

You will notice that in the top menu bar there are four applications, Application Builder, SQL
Workshop, Team Development and Administration. The one you will be using for entering and running
SQL commands is SQL Workshop. Click this and you will be taken to the next menu.

The list of tables ‘Recently Created Tables’ and ‘Recent SQL Commands’ will vary from user to user.

© David Nelson, Version 2, 5th October 2018

There are three applications which we will need to use within SQL Workshop:

1. Object Browser – this is where you can view and edit tables, views, stored procedures,
functions, etc.
2. SQL Commands – this is where you can type in and directly run SQL commands.
3. SQL Scripts – this allows you to upload and download .sql script files from your local computer
into/from Oracle APEX.

We will now give a brief discussion of each one.

Object Browser

Selecting ‘Object Browser’ will present the screen below. Notice that the list of tables will vary
depending on the tables that you have created in Oracle.

Clicking on any table, for example BOOK, will give you the table definition for that table.

© David Nelson, Version 2, 5th October 2018

Above the table definition you will see a collection of tabs, which allow you to view various properties
of the table, for example, clicking ‘Data’, will allow you to see the data within the table that you have
selected.

© David Nelson, Version 2, 5th October 2018

In the left hand window, you can select to view object other than the ‘Tables’. Pressing the mouse

pointer on the pull-down menu will display a list of objects that you
can view. For example, selecting ‘Views’ would give you a list of views that you have created.

To exit the ‘Object Browser’ application and return to SQL Workshop is easy. Oracle APEX uses
breadcrumbs which allow you to navigate around applications. You will see these near the top of the
window.

Clicking the Home breadcrumb would take you back to the Oracle main menu. Clicking the
SQL Workshop breadcrumb will take you back to the main SQL Workshop interface.

SQL Commands

SQL Commands allows us to type and run SQL commands. There are however a couple of differences
to the SQL Worksheet in SQL Developer:

1. You can type in multiple SQL commands into the window but you can only run one command
at a time. To do this, you highlight with the mouse the command that you want to run, e.g.

© David Nelson, Version 2, 5th October 2018

If you do not highlight the command you want to run, and the screen contains multiple commands
then you will get an error message:

© David Nelson, Version 2, 5th October 2018

 This means that it is very difficult to run scripts from the SQL Commands application. To do
this you should use the Scripts application within the SQL Workshop.

2. Oracle environment commands such as ‘SET AUTOTRACE ON’ will not run in Oracle APEX. If
you want, for example, to display the query plan for an SQL query then you should select the
‘Explain’ tab in the output window.

3. When you run an SQL query it will normally display ten rows the first ten rows of the query
result. If your query returns more than ten rows then you can change the number of rows to
return using the pull down menu near the top of the windows.

Selecting a value such as 1000 will always ensure that all rows are returned.

4. The EXECUTE command for stored procedures does not work in Oracle APEX. Instead, if you
have a procedure then you need to run this within a PL/SQL block by embedding the
procedure call within a BEGIN … END block, for example, for the procedure
TENANTS_FULL_NAME which takes a tenant number as an input parameter:

© David Nelson, Version 2, 5th October 2018

 5. Finally, a number of advanced features that you will encounter in the object-relational tutorial
do not work. Oracle APEX does not support the use of REF and will give a syntax error.

© David Nelson, Version 2, 5th October 2018

 Any query which returns a data type will also not work, displaying the message ‘Unsupported
data type’ within the query result.

At the moment it is unclear whether any other features that we will using during the tutorials
are unsupported.

SQL Scripts

The SQL Scripts application allows you to upload, run, edit and download scripts to/from your local
computer. You must use this feature rather than the SQL Commands application to run scripts which
contain multiple SQL commands.

© David Nelson, Version 2, 5th October 2018

It is best to create the script on your local computer using a text editor such as Notepad++ or Brackets.

Then to upload that script into Oracle APEX use the button.

This will give you a new Windows Explorer window where you can browse for the file that you want
to upload.

© David Nelson, Version 2, 5th October 2018

It is not necessary to the give the script a name. Once you have located the file you want to upload,

press the button to load the script into Oracle APEX.

© David Nelson, Version 2, 5th October 2018

Note this has not yet run the script. To run the script, located its name in the bottom left window, it

should appear at the top of the list, and click the Run button .

You will get the choice of running the script either immediately or in the Background. Select Run
Now and your script will run. Once it has run you will see a new screen showing the results of all
scripts you have run. The one you have just run should appear at the top of the list.

© David Nelson, Version 2, 5th October 2018

Click the View Results icon for the script you have just run to show you whether the SQL
commands in your script run successfully.

Again, you will see the results 15 commands at a time, and can use the buttons at the bottom of the
page:

to move left and right through the pages. Alternatively you can change the number of rows to display
at the top of the page to view all results in one page.

You need to check the results of all commands to ensure your script has run successfully.

If not, you can use the Edit Script button at the top right had corner of the page, which
will then allow you to edit the script stored within APEX.

© David Nelson, Version 2, 5th October 2018

You should correct the script and then Save the changes you have made by clicking the Save button

at the top right hand corner of the screen. This will then return you to the SQL Scripts main
window where you can run the script again.

It is important to note that when you make edits to the script, you are editing the version that is
stored inside Oracle APEX. The version of the file that is on your local PC does not change. To

download the edited file from Oracle APEX to your local PC, you need to click on the ‘Edit’ icon
next to the name of the script that you want to download, which takes you back to the script editing

window. Then you click the Download button at the top of the screen, which will then open
up a web browser window giving you the option of opening the file within a local text editor or saving
the file onto your local PC.

© David Nelson, Version 2, 5th October 2018

Clearly, the window and options may vary depending on which Web browser you are using.

Summary

This contained a quick introduction to some of the SQL Workshop features which should enable you
to run and test your Oracle SQL on any computer. Note however that the entire functionality is
untested and some further features that we may use during tutorial sessions and required for
assessments may not always work as expected. You should always test using SQL Developer on the
university computers before, for example, submitting assignments.

© David Nelson, Version 2, 5th October 2018