Scripts

What is a script?

Very simply, a script is a series of commands, usually stored in a text file, that can be run together as a single operation. The commands are processed by a command interpreter which is itself a software application. This is what differentiates a script from a program: a program is a software application that has been specifically compiled to run directly on a particular hardware platform or virtual machine.

Scripts are extremely useful for automating complex operations. In this module, we will look at two specific uses of scripts in the context of relational database. This first is the parameterised report and the second is the database creation script. In this practical, you will only be doing the first of these since we will not be covering the creation of database objects until week 8.

Creating a script

You already know that in SQL Developer you can type SQL commands into the Worksheet pane and execute them immediately. You can also save the contents of the Worksheet pane to a file and load the contents of the saved file again at a later date. This is the simplest way to create a script. Try it now as shown below by connecting as HR (1), entering and running a simple SQL query (2), and then saving the query (3).

You will be prompted for a file name and a directory to save it in. Notice that the file extension is .sql and make a note of the saved location. When your script is saved, close the tab containing the worksheet.

Word is not a text editor

Microsoft Word is a word processing application which inserts a lot of extra code into files. Use a proper text editor to edit source code. Download Notepad++ from the link in the Introduction.

Because the file you have saved is an ordinary text file, you can also edit it with a standard text editor. Locate Notepad++ on the Windows START menu and open the file you have just saved.

You will find that it contains exactly the statement that you typed into the Worksheet pane. In Notepad++, add a second SQL statement as shown below. Before saving and closing the file, notice that Notepad++ has recognised the type of file from its extension, and it has highlighted the SQL keywords.

If you prefer, you can create a script file using a text editor like Notepad++ from scratch rather than using SQL Developer. However, syntax highlighting will only happen once you have saved the file with the correct extension.

Returning to SQL Developer, you can now load this script using the Open icon as shown below or by choosing Open... from the file menu.

Once the file has been loaded, you will see the contents in the Workshop pane as shown below. You can now choose to run the whole script (1), or just the statement where your cursor is (2). Try both of these options and notice how the results are presented.

There is one further way to create a new script file. Close the current file, then click the New icon labelled 3 in the graphic above. You will be offered the chance to create a new connection, different types of database object, or finally an SQL file. Selecting the last option and clicking OK will let you choose a filename and location. If you use this method, remember that the file extension should be .sql.