SQL Script Support in Oracle 10G Express Edition (
Page 1 of 4 )
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.
