Oracle Application Express (APEX) is a free, hosted service provided by Oracle that provides a workspace for users that can be used to create database objects and run SQL statements without installing Oracle on your local machine. In essence, it is a free private database that you can create and access from a web browser. Strictly speaking, APEX is a standalone product offered by Oracle, but the company hosts a website that runs APEX and allows users to create accounts to evaluate the product.

To use APEX, we must first sign up for a free account. We navigate our browser to: http://apex.oracle.com and are presented with the following APEX login screen:

The first time we use APEX, we need to sign up for an account by clicking Sign Up. We're presented with the welcome screen that follows, and then we click Next.

The next screen asks us for our first and last name, along with our e-mail address. A verification message will be sent to this address, so we need to make sure our e-mail is valid.

When you sign in to APEX, you will be asked to provide the name of this workspace, which is filled in the Workspace field as shown in the following screenshot. The Username (e-mail address), Password, and Workspace name must all be entered when you login to APEX each time, so don't forget them.

The next screen asks whether we should use an existing database schema or create a new one. Our first workspace will require a new schema, so we want to make sure the Request a new schema button is selected.

Since we have chosen to create a new schema, we provide a name for it on the following screen. Choose a name that is appropriate for you. We can also choose an initial space allocation for storage, but the default of 25 MB should be fine for our examples.

The next screen asks us to provide a reason we're requesting access to the APEX service. Since this is a free service, this seems only fair. This request may actually be reviewed by a real person, so we need to make an appropriate request, as shown in the following screenshot:

Finally, we type in the verification code and click Submit Request. An e-mail will be sent to the address you used in your request. Follow the instructions in that e-mail to finalize your account.

Getting around in APEX is very intuitive. APEX is a virtual playground for an aspiring SQL programmer. It allows you to do the following:

There are many other capabilities of APEX for Oracle professionals. We can use the GUI Application Builder to create our own web applications. We can do team development of projects. We can even load and unload data quickly and easily. As an interesting note, nearly all the prototyping of the SQL statements used in this book was done using APEX. While it lacks some of the features of SQL Developer, it is easy to use.

To begin using APEX, we return to the APEX home page at http://apex.oracle.com. Click the Login button, and enter your account information.

The home page for APEX is shown in the next screenshot. Since our focus is on SQL development, we click the box for SQL Workshop.

We can choose from a number of options from the SQL Workshop page. The Object Browser will allow us to view our database objects through a user friendly GUI. We will use the SQL Commands page to execute our queries. But, first, since we opened a new account, we need to create the database objects used in this book. To do this, we click SQL Scripts.

Next, we click Upload to upload new scripts. Browse to the following two scripts that create the Companylink data and click Upload for each one. These scripts can be downloaded from the Packt website:

Once we've returned to the SQL Scripts page, we click to pencil icon (under Edit) next to our companylink_ddl.sql script. This script creates the table structures used in this book. The code is then previewed for us.

We simply click Run to execute the script. We may see a message that some commands are ignored, since they are needed for SQL Developer but not for APEX. Once this is complete, we do the same for the companylink_data.sql script that populates our tables with example data.

If we wish, we can also do this for the companylink_constraints.sql script. Once the scripts have run successfully, click SQL Workshop on the top blue bar to return to the workshop page. Click the SQL Commands button .

Here, we simply enter any SQL statements we wish, and click the Run button to execute it. In the following example, we use one of the SQL subquery statements we completed in Chapter 8, Combining Queries.

Any other query can be and run in the same way. When using APEX, remember that there are a few differences from SQL Developer. First, the default format for dates is DD/MM/YYYY. This requires us to make some alterations to the example code we enter when using the TO_CHAR and TO_DATE functions. Second, some functions, such as LPAD and RPAD, don't format row output in the same way as SQL Developer. Lastly, some formatting options are unavailable in APEX. However, beyond these few differences, we should be able to use APEX for the majority of the examples in this book. Also, don't be afraid to explore some of the other features of APEX. They can be both educational and fun.