Introduction to SQL Command Tool in Oracle APEX

Introduction:

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.

The SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database objects which can later be used in our application.
We can use most of the actions using SQL command tool that we have seen using APEX object browser. For example creating any database objects or manipulating it. But in object browser we cannot execute the simple SQL statements that we can use here. In general we can utilize this tool like how we use SQL Developer tool to query or manipulate the database objects.
Navigation to SQL Command tool:

  • Login to your APEX application:

1.png

 

Click on “SQL Workshop” as shown in above screen shot.
2.png

 

Now you can see under “SQL workshop” we have an option to select “SQL Commands” . This is the place where we can select and write any SQL statements or any PLSQL code to execute and check the output. Once you select the option as shown in above screen shot we will see the below screen which is called as “SQL Command Tool”.
3.png

 

The above screen shot provide the details on where we can write our SQL or PLSQL statements and how we can execute it. The tool is basically divided into two sections , in the top section we can type any SQL queries  or PLSQL block and the bottom section is the place where we get the results of SQL/PLSQL block.
The below screen shot shows an example like how we can execute SQL and PLSQL statements on the top sections and see the result in the bottom sections.
SQL-Tool.png

 

In the top section of the SQL commands tool we can see there is button called “Clear Command” , to clear the screen of the top section this option is used. Form the above screen shot we can see that we have executed on PLSQL block now let us see what will happen when we click on the  “Clear Command” button.

 
4.png

 

From the above screen we can see that only the top section is cleared where we have written our PLSLQ code to execute but the result remains the same unless the next SQL or PLSQL statements are executed.
We also see another button beside that called ” Find Tables” , once clicked on it we will see another window which helps us to search any table name from our database and check the details of the columns and its types.
6.png

I selected the table name called “SI_USERS_T” .
7.png

We can create an database object using SQL command tool for example let us create a simple table called “XX_EMP” :
8.png

From the above screen shot we can see that we have created the table successfully and now let us write one PLSQL block to insert few records.
9.png

Now let us query the table and see the result.
10.png

We can also check the explain plan for the query that was executed by clicking the Explain tab in the bottom section of the SQL command tool.
11.png

 

This tool also allows us to save any queries using the save option on top section beside “Run” button.  Once we click on “SAVE” we will get the window to give a name and provide some description as shown in below screen.
12.png

Once you filled the information then click on SAVE as shown in the above screen shot.  Now if we want to see what the SQL that are been saved , we can check under “Saved SQL” under the bottom section of the SQL command tool .
13.png

Now we know how to use SQL command tool. Both SQL command tool and object browser allows us to view and modify the database objects but the only difference is the interface .  This tool is for those who are familiar with Oracle SQL and PLSQL.

 

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