SQL*Plus @ Command(execute Oracle scripts)

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:

Output of the SALES script.

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 use @@ instead.