Using SQL Scripts in Oracle Application Express

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

 

Once you see the page as shown in above screen shot you can click on “SQL Scripts” as highlighted .

img-3.JPG

The 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.JPG

Once clicked the editor will open as shown below:

img-6.JPG

Now 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.JPG

Now 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.JPG

We can click on Run now option to show the result as below:

img-10.JPG

From 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.JPG

When wee select the option as “Detail” and click on go we will see the below page as a result.

img-12.JPG

In 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.JPG

In 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.JPG

Click “OK” .

img-16.JPG

Now 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 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

2 thoughts on “Using SQL Scripts in Oracle Application Express

  1. Nice Article. I have a question. Can this SQL script be called in a page process and executed from there. I have a situation that I want to copy/create an application via a page process. I thought of exporting the application and storing it as a SQL Script and then I want to call this sql script from page process. Please suggest something.

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