Executing Scripts

i SQL*Plus provides you with two mechanisms for running SQL*Plus scripts. Which you use depends largely on where the script is located. One method is optimized for running scripts off a web server (or FTP server) over the Internet. The other method, slightly more cumbersome, allows you to run scripts from your workstation's hard drive.

3.5.1 Using the @ Command

In Chapter 2, you saw how to use the @ command to execute a script file containing SQL*Plus commands, SQL statements, and PL/SQL blocks. You can use the @ command in i SQL*Plus but only to execute scripts over the Internet. For example, suppose that you had the following commands in a file named login.sql that was accessible from a web server:

SET ECHO ON SET MARKUP HTML PREFORMAT ON SET PAGESIZE 20

You could easily invoke this file to configure your environment each time you logged in to i SQL*Plus. You would use an @ command similar to the following:

@http://gennick.com/login

Figure 3-12 shows what it would look like to execute this script.

Figure 3-12. Executing a script from the Internet

You can't use the @ command to execute scripts on the server. For security reasons, you are not allowed to use i SQL*Plus as a mechanism for accessing files on the server. You also can't use the @ command to execute scripts located on your client PC because i SQL*Plus, running on the database server as it does, has no way to access files on your local disk. There is, however, a way to execute scripts stored on your local hard drive.

3.5.2 Using the Load Script Button

To execute a script from your local hard drive, follow these steps:

1. Click the Load Script button. You'll see the page shown in Figure 3-13.
2. Click the Browse button to open a file chooser dialog.
3. Navigate to the file you want, select it, and click the OK button. You'll be brought back to the page shown in Figure 3-13, and the File text box will be filled in with the path to the file you chose.
4. Click either Load button. The file will be loaded and placed into the text box in the Workspace page.
5. Optionally edit the script you are about to execute.
6. Click the Execute button to run the script.

Figure 3-13. Loading a script from your workstation's hard drive

The Load Script functionality is helpful if you want to look at the contents of a SQL script that you would otherwise execute from a web server using the @ command. You can type the script's URL into the URL text box (in place of step 3), click the Load button, and i SQL*Plus will load the script into the Workspace text box for you to look at, edit, and possibly execute.