SHARE YOUR KNOWLEDGE AS MUCH AS YOU CAN !!!

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.

Now click on “SQL Workshop” option.

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

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.

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

Once clicked the editor will open as shown below:

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

Now click on create.

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.

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

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:

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

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.

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:

Now click on “Delete Checked”. After clicking it will prompt you for confirmation.

Click “OK” .

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.

2 thoughts on “Using SQL Scripts in Oracle Application Express”

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.