Lesson 8	SQL*Plus @ command
Objective	Execute scripts written for SQL*Plus.

SQL*Plus @ Command(execute Oracle scripts)

To have SQL*Plus read commands from a file and execute them, use the @ command. The syntax for the @ command looks like this:

@[path]filename[.extension]

The default extension is ".sql". The path defaults to your current working directory. The following example shows the @ command being used to execute the db_objects_by_type.sql file created in the previous

SQL> @c:\db_objects_by_type

OWNER        OBJECT_TYP OBJECT_NAME
------------ ---------- ---------------------------
DBSNMP       SYNONYM    DBA_DATA_FILES
                        DBA_FREE_SPACE
                        DBA_SEGMENTS
                        DBA_TABLESPACES

Running Scripts

The START command retrieves a script and runs the commands it contains. Use START to run a script containing SQL commands, PL/SQL blocks, and SQL*Plus commands. You can have many commands in the file. Follow the START command with the name of the file:

START file_name

SQL*Plus assumes the file has a .SQL extension by default.

Example 5–8 Running a Script

To retrieve and run the command stored in SALES.SQL, enter

START SALES

SQL*Plus runs the commands in the file SALES and displays the results of the commands on your screen, formatting the query results according to the SQL*Plus commands in the file:

You can also use the @ (at sign) command to run a script:

@SALES

The @ and @@ commands list and run the commands in the specified script in the same manner as START. SET ECHO affects the @ and @@ commands in the same way as it affects the START command. To see the commands as SQL*Plus "enters" them, you can SET ECHO ON. The ECHO system variable controls the listing of the commands in scripts run with the START, @ and @@ commands. Setting the ECHO variable OFF suppresses the listing. START, @ and @@ leave the last SQL command or PL/SQL block of the script in the buffer.

If you do not want to specify the path for each script that you run, you can give SQL*Plus a list of directories to search each time you run a script.
Windows users especially, may want to do this because under Windows NT the default directory will be the Oracle bin directory.
SQL*Plus allows you to nest scripts--you can have one script use the @ command to call another. When you do that, you may want to

@@ command in Oracle

When you write one script that calls another, you may end up storing both scripts in the same directory because they are related. When the first script calls the second, it stands to reason that you want SQL*Plus to start its search from the directory that contains the scripts. You can use the @@ command for this. When you use the @@ command from within one script to call another, SQL*Plus starts its search in the directory containing the first script. The following table illustrates the difference between @ and @@:

Step 1:	@filename	@@filename
Step 2:	SQL*Plus looks in the current working directory	SQL*Plus looks in the directory that contains the parent script.
Step 3:	SQL*Plus follows the search path	SQL*Plus follows the search path

The difference between the two commands is in step 2, or the first directory to be searched. Using @@ to invoke one script from within another allows you to avoid any problems that might be caused by having two or more scripts with identical filenames in your sqlpath. Regardless of where SQL*Plus starts its search, it stops when it finds a file with the name that it is looking for. Step 3 will only be performed if a file was not found in step 2.

@@ (double at sign)

Syntax

@@{url | file_name[.ext] } [arg...]

Runs a script. This command is almost identical to the @ (at sign) command. When running nested scripts it looks for nested scripts in the same path or url as the calling script. The @@ command functions similarly to @ and START.

Terms

url: Specifies the Uniform Resource Locator of a script to run on the specified web server. SQL*Plus supports HTTP and FTP protocols, but not HTTPS. HTTP authentication in the form

http://username:password@machine_name.domain...

is not supported in this release.

file_name[.ext]

Represents the nested script you wish to run. If you omit ext, SQL*Plus assumes the default command-file extension (normally SQL).
For information on changing the default extension, use SET SUF[FIX] {SQL | text}. (See below) When you enter @@file_name.ext from within a script, SQL*Plus runs file_name.ext from the same directory as the script. When you enter @@file_name.ext interactively, SQL*Plus runs file_name.ext from the current working directory or from the same url as the script from which it was called. If SQL*Plus does not find the file, it searches a system-dependent path to find the file. Some operating systems may not support the path search. See the platform-specific Oracle documentation provided for your operating system for specific information related to your operating system environment.

SET SUF[FIX] {SQL | text}

Sets the default file extension that SQL*Plus uses in commands that refer to scripts. SUFFIX does not control extensions for spool files.
Example To change the default command-file extension from the default, .SQL to .TXT, enter

SET SUFFIX TXT

If you then enter

GET EXAMPLE

SQL*Plus will look for a file named EXAMPLE.TXT instead of EXAMPLE.SQL.