<

Using SQL Scripts in Oracle Application Express

Published on
4,152 Points
952 Views
2 Endorsements
Last Modified:
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. 

img-1.JPG
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.

img-4.JPG
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".

img-7.JPG 
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:
img-14.JPG
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 )
2
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month