Introduction to Oracle APEX SQL Workshop- The object browser

Summary

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.

 

In this blog 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:

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:

1.jpg

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.
2.jpg

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”.

3.jpg

Click on “Object Browser” and we will see the screen below.

4.jpg

 

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.

5.jpg

 

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:

6.jpg

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”.

7.jpg

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.

8.jpg

 

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.

9.jpg

 

Select one of the tables. Once we select any of the table using object browser we will see the options highlighted.

10.jpg

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

11.jpg

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.

12.jpg

 

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

13.jpg

 

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

14.jpg

15.jpg

 

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.

16.jpg

 

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” .

17.jpg

Now click on Next button, and we will see the options to create the foreign key.

18.jpg

Next, we will get the option to specify the constraints. I am using the default option and proceeding further.

19.jpg

 

Finally we will see the option to create our table using the object browser.

20.jpg

 

Click on Create Table button.

21.jpg

Now we see the table is created. This is one example of how we can utilize the object browser.

Thank you for reading my blog , the reference is taken from one of my article that was published by Experts-Exchange. Please feel free to leave me some feedback or to suggest any future topics.

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s