Introduction to Oracle APEX SQL Workshop- The object browser

In this article I am going to show how we can use and utilize SQL Workshop before we start developing any web application using APEX. In my earlier articles I have shown how to install and upgrade to APEX 5 version. Here are the links to my earlier articles:

• Upgrading Oracle APEX 3.2.1.00.10 to 4.2.6.00.03 version
• Upgrading Oracle APEX to Oracle Application Express Release 5.0.1

Before we get into the SQL Workshop we must have an account or we must have installed APEX on server or desktop. I installed Oracle database 11gR2 and then installed Oracle APEX 5. Assuming that we have the Oracle APEX set-up done and we open the URL of APEX version 5 we will see the screen below:
We have to login to the ADMIN account and create a workspace where we can login and start developing the application using the builder or we can use SQL Workshop. In my case I have created a workshop earlier and now I will login into the application.

We can see the SQL workshop options as shown in the above image. Navigate to SQL Workshop where we can see there are various options. This will allow us to manipulate the database directly from APEX. The two major options like “Object browser and SQL Commands”.

Click on “Object Browser” and we will see the screen below.
 
The object browser provides a graphical interface to interact with the database easily. We can utilize this option to see each description of each table, its column properties, constraints, indexes and triggers including contents. It also allows us to make any changes to descriptions or the data of a table.
From the drop down we can choose the object types that will later will display the details. For example we select the options as procedures and then we will see the list:




Let us see the search option available on the object browser and how we can use it. For example I have selected Table as object type and want to search a table name called “SI_USERS_T”.

This will allow us to see all the tables that have “SI_USERS”; if we use like operators this will not list any tables. For example say we try to search like “%SI_USERS%’ then we will not get any results.

The refresh button present just beside the search operation will display all the objects where the object type is “TABLE” as this option is already selected by us.
Select one of the tables. Once we select any of the table using object browser we will see the options highlighted.

Let us see what the options available under “data” are:

We can see that we can edit the data, add another row, check the count by clicking count option, query the table by putting conditions to each column, and scroll. We can also download the data in CSV format.

The object browser also allows us to create a list of the object types.

Click on the option “Table” from the drop down. The interface allows us to give the table name and columns details.

Once you have filled the information -- the table name, how many columns you need and what the datatype should be -- click on Next button. To show an example I am calling my table name as “slobatab” and used two columns, one with number and another with varchar2 datatypes.
We can define the Primary key of the table or we can leave it, based on our needs. If we want to use the primary key then we can define a sequence to prepopulate the data using a sequence number. Here we will use a primary key on our “ID” field and use sequence but will create it using the option ”Populated from a new sequence” .
 
Now click on Next button, and we will see the options to create the foreign key.
 
Next, we will get the option to specify the constraints. I am using the default option and proceeding further.
 
Finally we will see the option to create our table using the object browser.

Click on Create Table button.

 
Now we see the table is created. This is one example of how we can utilize the object browser.
 
Thank you for reading my article. Please feel free to leave me some feedback or to suggest any future topics. Please 'Vote this article as helpful' if you liked on the bug green button at the bottom of this article.

Looking forward to hear from you - Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )