Using SQL Scripts in Oracle Application Express

Swadhin Ray
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
How it can be really helpful to use SQL scripts under SQL Workshop?

Let us take an example of any application which is developed using APEX with one form and report. The development team wanted to test it by inserting few records into the form and then viewing the reports. But once the testing is completed the team wanted to clear the data from the base tables and from the reporting tables, logs and other database objects. To achieve this the team can create the delete scripts for each object and execute them on database. This activity can also be used by saving the code into a file within the application so that it can be executed from the application and also be used in future. 

In other words it's a set of SQL commands that are saved into a file as scripts. The file or the script can have more than one SQL commands or it can be a PLSQL block. 

Few things we need to keep in mind while using SQL scripts from SQL workshop. 
  • All the SQL Plus commands are ignored in SQL scripts
  • We cannot interact between SQL commands with SQL scripts
  • Bind variables are not supported in SQL scripts
  • We can copy the SQL commands from SQL scripts and run them on SQL Command under SQL workshop
To access SQL Scripts login to your Oracle Application Express. 

Now click on "SQL Workshop" option. 

img-2.JPGOnce you see the page as shown in above screen shot you can click on "SQL Scripts" as highlighted . 

img-3.JPGThe above screen shot is the page where we can create any new script or use an existing scripts. This page will show a report where we can view all the scripts that are created by the current user and if the user is a workshop administrator then it will display all the scripts created in this workspace. We can also change the view by applying the filters.

The search options (i.e. a magnifying glass shown as above) can be used to search any specific columns or any columns. The view icons will change the view of the reports to display as ICON based reports. The delete option at the right is used to delete any script that we might need to by selecting the check box. The upload button is used to upload any new scripts from our local system to the SQL scripts and create option is used to create any new script.

Creating a new script :

Click on "Create button".

img-5.JPGOnce clicked the editor will open as shown below:

img-6.JPGNow we can enter the script name but the extension is optional so we will leave as it is. To demonstrate I am using a select statement to select all the records from employee table with name as "Sample_Create_Script".

Now click on create. 

img-8.JPGNow we can see our script is created now let us run it. Once you click on run option we will see the below screen. 

img-9.JPGWe can click on Run now option to show the result as below:

img-10.JPGFrom the above page we can see the execution of the SQL statement is completed and the time taken for the execution. Now when we click on the magnifying glass we will see a summary report as shown in below screen:

img-11.JPGWhen wee select the option as "Detail" and click on go we will see the below page as a result. 

img-12.JPGIn this page we have an additional option to edit the existing script, so when we click on edit script it will show the below page. 

img-13.JPGIn the above page we can modify the existing SQL statements and apply the changes by clicking on "Apply Changes".

To delete an existing SQL Script: 
Select the script that you want to delete by selecting the check box as shown in below screen:
Now click on "Delete Checked". After clicking it will prompt you for confirmation.

img-15.JPGClick "OK" .
img-16.JPGNow we can see the script is deleted from the application. 

We can export or import the scripts by using the export/import option from the Task lists, so we can download them and save to our local system or may utilize them on another workspace. To know how much we can store, we can check the SQL script quotas by clicking on "Show Quotas" option from Tasks. 
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 )
Swadhin Ray

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.