start

Run the commands in an SQL script. The script can contain SQL commands, PL/SQL blocks, and SQL*Plus commands.

     START file_name

By default SQL*Plus will assume the file has a .SQL extension.

Example

To run SS64.SQL:

SQL> START SS64
or
SQL> @SS64

Run SQL*Plus and start an SQL script:

SQL> SQLPLUS Username/ @SS64.sql
or
SQL> SQLPLUS @SS64.sql Include your username as the first line of the .sql file.
or
SQL> SQLPLUS @SS64.sql /nolog Include a connect statement as the first line of the file.

When SQL*Plus starts, it will prompt for your password and will run the script, note that embedding a password in the script presents a security risk.

Nesting Scripts

To run a series of scripts in sequence, first create a script containing several START commands, each followed by the name of a script in the sequence. Then run the script containing the START commands. For example, you could include the following START commands in a script named UPGRADE.sql:

START Part1.sql
START Part2.sql
START Part3.sql
START Part4.sql
START last.sql

@@File_name will also run a script, this is almost identical to @File_name the difference being that @@ looks for nested scripts in the same path or url as the calling script.

Substitution Variables in SQL*Plus

Enter your script using & and && as the prefix for variables.

SQL> @MyScript.sql parameter1 parameter2 parameter3 
or 
SQL> START MyScript.sql parameter1 parameter2 parameter3 
      In the SQL-Script, refer to the parameters as &1  &2  &3

For example @MyScript.sql 1234 FinanceDept

Substitution variables can be used anywhere in SQL and SQL*Plus commands, except as the first word entered.

If you wish to append characters (other than a space) immediately after a substitution variable, use a period to separate the variable from the character.
For example WHERE PART_ID='&A.64' now if &A is set to 100 the expression becomes WHERE PART_ID='10064' (see also SET CONCAT)

When SQL*Plus encounters an undefined substitution variable it will prompt you for the value. With single ampersand substitution variables (&B) you are prompted to substitute a value for each occurrence of that substitution variable, so if the undefined variable is used 10 times in the script you will get 10 prompts.

Double ampersand substitution variables (&&C) will remain defined. You will not be prompted to enter values for those variables again until they have been undefined, or you log out of SQL*Plus.

To customise the substitution prompt use ACCEPT like this: ACCEPT ss64 NUMBER PROMPT 'Enter the code number:' This will create the variable &ss64

If you use DEFINE to define variables in a script, the defined values will take precedence.
If you enter a substitution value at the prompt, SQL*Plus will list the line with and without your substituted value. You can suppress this with SET VERIFY OFF.

Related

SET DEFINE - Define the substitution character (by default the ampersand "&") and turn substitution on and off.
SET ESCAPE - Define an escape character you can use before the substitution character. ignore variable substitution. default =\
SET NUMFORMAT - Set the default format for displaying numbers, including numeric substitution variables.
SET NUMWIDTH - Set the default width for displaying numbers, including numeric substitution variables.
SQL*Plus
Editing SQL scripts in SQL*Plus
CONNECT - Connect to a database instance.