Login

SQL Script Support in Oracle 10G Express Edition

In an earlier tutorial we saw how to use SQL commands in the Oracle 10g Express Edition, or simply Oracle XE. We also saw how to use the PL/SQL commands as well. In another tutorial we saw how to use the Query Builder, the graphical interface for fashioning and running SQL in the Oracle XE. The present tutorial describes the SQL scripting support in Oracle XE. In order to bring out the basic features of scripting support a few example scripts are considered in the discussion.

Introduction

Scripts are text files which can be developed using a text editor such as Notepad. They can reduce the labor intensive activity of typing by storing them for future reuse. You find scripts in almost all computer-related activities, and they are available for most programming languages. Also you will find a lot of vendor support for SQL scripting, like Embarcadero, ApexSQL, AciWorldwide and many others.

Scripts make the process of application development easier, because with well developed scripts, errors are reduced, and reading from a file is faster than one can type. They are extremely useful for automated report generation, file backup, network activity monitoring and whole lot of other things. In Oracle 10G XE, you can develop reusable SQL scripts and save them for future use quite easily. This article takes you through the process of using the script support available in Oracle 10G XE, but it is by no means a tutorial on Oracle SQL scripts.

Opening the SQL Script Editor

It is assumed that you have downloaded Oracle 10g Express Edition and that you have unlocked the HR database so that you can log on to this database as a user. Now from the shortcut on your desktop you can log in by clicking on the “Go to the Database Homepage…” shortcut which can be accessed from All Programs–>Oracle 10g Express Edition. This opens up the following window.

Log in to the database using the credentials Username: hr and Password: hr as shown in the above picture. This should open up the following window. There are five main objects (nodes). The Object Browser shows all the objects in the database.

Now click on the SQL icon. This will open up a window which gives you access to all the SQL functionalities in Oracle XE, SQL Commands, SQL Scripts, and Query Builder as shown. Please review the SQL Command icon related tutorial as well as the Query builder tutorial, as they will help you in understanding the present tutorial.

Click on the SQL Scripts icon, which opens up the following window. This screen will list all the scripts that exist in the database, showing the name with which the script was saved, its owner, the user who updated it, the last time it was updated, the number of bytes, the results, and whether it has run previously. You could also search for scripts, or by owner. By default search results are limited to 15, but you may change that. In addition to searching and sorting, the SQL script may be uploaded and created. In this screen you see three saved scripts: Selct, Adval and First_Last, all owned by the owner, HR. In addition to managing scripts, you can access export/import functionalities as well as review your script quote. More on these later.

{mospagebreak title=Sample scripts}

For the purpose of explaining the various activities you can perform in the script editor, some sample scripts will be considered. These are very simple scripts, but enough for the present purpose.

Selct

Select * from First_Last;

Adval

INSERT INTO first_last VALUES (‘Jay’, ‘Krishnaswamy’);

First_Last

create table first_last(
first_name CHAR(20),
last_name CHAR(20)
)
;

As you can see above, the script First_Last creates a table which is populated by the statement shown by the script Adval and the script Selct queries the table First_Last. This is a set of some very simple basic scripts.

Editing an existing script

In order to edit the script Selct, place a check mark in the extreme left as shown for this script, and click on the icon for “Edit” as shown in the next picture.

This opens up the Script Editor window as shown in the next picture. The script name you selected comes up and the script itself is displayed in the text editor as shown here. You may do a number of things in this text editor, such as undo, redo, and find text. For the script itself you can save, run, delete or download script from another location.

Let’s hit the Run button now to get to the next window as shown. You will be asked to confirm in this window. This was a simple select query and running it will cause no changes. But there will be times when it is better to caution the user to confirm. You may also notice that the query is using the schema of HR.

Let’s hit Run again. You get the window shown in the next picture. In this screen you manage the script results. It can show the status of the script, whether still executing, completed, and so on.

If you want to see the result of running this script, click on the “View Results” icon instead of the view script Icon as suggested in the above picture (it probably is an error). You will see the summary of results (default view) as shown in the next pciture.

To see the results of this query, you need to choose the View: Details radio button and hit the Go button as shown in the next picture.

Next, we will edit the script for Adval and change the first and last names to “James” and “Bond” so that the edited query will insert new values into the First_Last table. The Selct query is run again, and you will notice that you have added another row to the returned results as shown. Several steps are not shown because they are a repetition of the steps described earlier.

INSERT INTO first_last VALUES (‘James’, ‘Bond’);

When you run this Adval script you will see the following result.

Now if you run the Selct script again, you will see the following result.

{mospagebreak title=Creating a script}

As we saw earlier, the script editor gives you access to create, modify, delete, save, and so on. Let’s create a script file for demonstrating the use of looping using PL/SQL. This control structure in PL/SQL starts with a variable having a value 1 and increments it through 2 each time through the loop, until the final value reached does not exceed 10. It writes out the values as well. The next picture shows the script created (all steps deleted) except for the results, which shows the script as well as the results.

Errors in scripting show up with excellent help to troubleshoot the errors, as it shows the line where the error occurred and other details.

{mospagebreak title=Exporting and Importing of Scripts}

You can both export files from and import them into Oracle 10G XE by clicking the appropriate buttons while you are in the script editor. The next picture illustrates exporting out of the WLOOP script.

Place a check mark against the script you want to export, or you may also export the whole lot, and click on export to open up the next screen.

Now clicking on the Export ALL button opens up the following two windows (one on top of the other, shown displaced in the picture). When you click save, it saves it to a folder named download (but what if I do not have a directory called “download”?).

Importing is just as easy. Clicking on Import will bring up the window where you browse to find the script as shown. Again the dialog to choose the file from opens to the “download” directory. However, in a multi-user application these export/import functionalities would probably allow for the exchange of scripts between users.

Miscellaneous items

The script quotas window shows your personal quota, used or otherwise. This is a useful number to be aware of, if you are using the database under high usage conditions. This window for this infrequent user is shown in the next pciture.

Another feature of the UI that you should be aware of is the breadcrumbs, a very useful navigational feature that allows you to traverse the hierarchy of windows. You could move through the windows in a way very similar to navigational buttons by clicking on the crumbs.

Summary

The SQL Command, Query Editor, and SQL Scripts provide all the needed SQL Support. The interfaces are very easy to use, with excellent navigational features. The script editor includes line numbering, undo, redo, and find features which are excellent aids in scripting. Oracle’s online support for this product is terrific. One email I sent to Oracle got me a response in a record time of under 15 minutes. Unbelievable, but true.