Executing SQL*Plus Scripts with @, @@, and START

Lesson 7 introduced saving your work into a .sql file, but stopped short of the actual command that runs it. This lesson closes that gap: how @, @@, and START each execute a saved script, the full syntax each one supports, and how to set up a search path so you never have to type a directory name twice.

To have SQL*Plus read commands from a file and execute them, use the @ command. The full, official syntax is broader than a single local file path:

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

In everyday use, file_name is a path to a script on your own machine, and the default extension is .sql if you omit one. But the url form is genuinely available too: @ can run a script sitting on a web server, not just a file on disk, which matters if you need to pull a script from a shared location rather than copying it locally first.

Here is @ running the db_objects_by_type.sql file built in Lesson 7:

SQL> @c:\db_objects_by_type

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

Notice OWNER and OBJECT_TYP repeat on every row here. A plain SELECT with no additional formatting always repeats these values; SQL*Plus only suppresses repeated values on consecutive rows when you add a BREAK command, something like BREAK ON owner ON object_type, which is worth knowing precisely because you will see reports elsewhere with visually blank repeated cells and should understand that BREAK, not some default behavior, produced that look.

The START command retrieves a script and runs the commands it contains: SQL commands, PL/SQL blocks, and SQL*Plus commands, all in one pass. Follow START with the file name:

START file_name

SQL*Plus assumes a .SQL extension by default here as well. To retrieve and run commands stored in SALES.SQL, for example:

START SALES

SQL*Plus runs every command in the file and displays the results on your screen, formatted exactly as the SQL*Plus commands within the file specify:

LAST NAME                    MONTHLY SALARY COMMISSION %
---------------------------  -------------- ------------
Russell                              $14,000         0.40
Partness                             $13,500         0.30
Errazuriz                            $12,000         0.30
Cambrault                            $11,000         0.30
Zlotkey                              $10,500         0.20

You can run the same script with @ instead of START, and the result is identical:

@SALES

The @ and @@ commands list and run the commands in a script the same way START does. SET ECHO affects all three identically: turn it on with SET ECHO ON to see each command as SQL*Plus enters it from the script, or leave it off, the default, to suppress that listing and see only the output. All three commands share one more behavior worth remembering: START, @, and @@ each leave the last SQL command or PL/SQL block of the script sitting in the SQL buffer afterward, so you can immediately re-run or edit that final statement with commands like RUN or the slash character, without retyping it.

If you do not want to specify a full path every time you run a script, give SQL*Plus a list of directories to search automatically. This search path is called SQLPATH, and it is worth being precise about the name: it is SQLPATH, in uppercase, not a lowercase variant. On Unix and Linux, environment variable names are case-sensitive, so setting a lowercase sqlpath does nothing at all; SQL*Plus specifically looks for SQLPATH.

On Unix, using the Korn shell, set it like this:

SQLPATH=/sql_scripts:/sql_reports
export SQLPATH

Note the colon between the two directories. SQLPATH is a colon-separated list on Unix, matching the same convention used by the PATH variable itself. If you are using the C shell instead, use setenv:

setenv SQLPATH /sql_scripts:/sql_reports

Replace the directories shown with your own, of course. One detail worth knowing: there is no default SQLPATH value on Unix installations; if you never set it, SQL*Plus simply searches your current directory and nowhere else automatically.

Windows handles this differently, through a registry entry rather than a shell environment variable, and the separator convention is genuinely different there too: Windows uses a semicolon, not a colon, to concatenate directories. You can set it through the System Properties dialog's Environment Variables settings, creating a SQLPATH entry the same way you would any other system environment variable on a modern Windows installation.

You can also edit the underlying registry variable^[1] directly if you prefer. On Windows, the SQLPATH registry entry lives under the key for the relevant Oracle Home, specifically HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEn, where HOMEn corresponds to a specific installed Oracle Home. It is created automatically with a default value of ORACLE_HOME\DBS. Each Oracle Home on your system has its own separate SQLPATH entry, which matters if you have multiple Oracle Homes installed and need script search behavior to differ between them.

When one script calls another, and both live in the same directory because they are genuinely related, you generally want SQL*Plus to start searching for the second script from wherever the first one lives, not from whatever directory you happened to launch from. That is exactly what @@ is for. The table below shows the difference between the two commands directly:

The only real difference is Step 2, the first directory searched. Using @@ from within one script to call another avoids problems that could arise from having two or more identically named scripts scattered across your SQLPATH; regardless of where the search starts, it stops the moment it finds a matching file, and Step 3 only runs at all if Step 2 came up empty.

The full syntax for @@ mirrors @ exactly:

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

This command is almost identical to @; the only functional difference is where it looks first when running a nested script, and it functions similarly to both @ and START in every other respect.

One term worth defining precisely: url specifies the location of a script to run on a web server rather than your local filesystem. SQL*Plus supports HTTP and FTP protocols for this, but not HTTPS, and this remains true in current Oracle AI Database 26ai documentation, not just older releases. HTTP authentication embedded directly in a URL, in the form http://username:password@machine_name.domain, is explicitly not supported either. If you need a script from a remote server, plan around these specific limitations rather than assuming modern encrypted transport is available for this particular command.

file_name[.ext] represents the nested script itself. If you omit ext, SQL*Plus assumes the default command-file extension, normally SQL. When you enter @@file_name.ext from within a script, SQL*Plus runs it from that script's own directory. When you enter the same command interactively rather than from within a script, SQL*Plus instead runs it from your current working directory. If the file is not found there, SQL*Plus falls back to a system-dependent path search, though support for that fallback varies by operating system; consult your platform-specific Oracle documentation for the exact behavior on your system.

SET SUFFIX controls the default file extension SQL*Plus assumes for commands referring to scripts:

SET SUF[FIX] {SQL | text}

This setting does not affect spool file extensions, only script-related commands like @, @@, START, and GET. To change the default from .SQL to .TXT:

SET SUFFIX TXT

With that setting in place, entering:

GET EXAMPLE

causes SQL*Plus to look for EXAMPLE.TXT rather than EXAMPLE.SQL. GET itself loads a script into the SQL buffer without executing it, useful when you want to review or edit a script's contents before running it rather than running it immediately.

Between @, @@, and START, you now have every practical way to execute a saved SQL*Plus script, along with the search path mechanics that let you organize those scripts across directories without retyping paths constantly. The next lesson builds on this foundation directly.

[1]Registry variable: A variable stored in the Windows Registry, edited using the regedit utility or, more conveniently, through the System Properties Environment Variables dialog.