Lesson 5 - Part 2	Working with Query Files
Objective	Save a query to a file, replace a file, and edit a file.

Working with SQL*Plus Query Files

In earlier lessons you worked directly in the SQL*Plus buffer, either running statements immediately or editing them with the in-line editor or a text editor. In this lesson, you will learn how to save queries as script files, replace or append to existing files, and edit those files for reuse. Being able to store queries in files is essential for repeatable reporting, automation, and version-controlled development.

SQL*Plus provides several file-handling commands that interact with the SQL buffer and with scripts stored on disk. You will use these commands to:

Save the current buffer to a script file.
Replace or append to an existing script.
Load a script into the buffer and execute it.

Core SQL*Plus File Commands

The following commands form the foundation of working with query files in SQL*Plus. Modern SQL*Plus best practices favor START and @ for executing files, and SAVE for creating and maintaining script files.

Command	Description
GET filename[.ext] [LIS[T] \| NOL[IST]]	Reads the contents of a script file into the SQLPlus buffer. If no option is specified, the file is listed as it is loaded. Use `NOLIST` to load the file silently. If you omit `.ext`, SQLPlus assumes `.sql` by default.
REM[ARK] text or `-- text` or `/* text */`	Adds comments to your script. `REM` marks an entire line as a comment. `--` comments from that point to the end of the line (often used at the end of a command line). `/* ... */` can span multiple lines and is useful for longer documentation blocks.
`RUN`	Re-executes the current contents of the SQLPlus buffer. Modern SQLPlus does not use `RUN filename`; to execute a file, use `START` or `@` as shown below.
SAV[E] filename[.ext] [CRE[ATE] \| REP[LACE] \| APP[END]]	Writes the current SQLPlus buffer to a file. CREATE* (default): Create a new file; fails if the file already exists. REPLACE: Overwrite the existing file. APPEND: Append the buffer to the end of an existing file. If you omit the extension, `.sql` is used by default.
STA[RT] filename[.ext] or @filename[.ext] or @@filename[.ext]	Executes the commands in a script file. `START` and `@` execute the specified file; the last statement in the file becomes the new buffer. `@@` is useful inside scripts to run nested scripts relative to the directory of the parent file.

The SQL Buffer and File Workflow

SQL*Plus always keeps your most recent SQL or PL/SQL statement in memory in an area called the SQL buffer. You can display the buffer with the LIST command:


SQL> L
  1  select *
  2* from employees
SQL>

Typical workflows involve:

Typing or editing a statement at the prompt and running it.
Saving that statement into a script file with SAVE.
Re-running the script later with START or @.
Loading and editing existing scripts using GET and a text editor.

Remember that each new statement you enter overwrites the SQL buffer. Once you begin working with files, your goal is to keep reusable logic in scripts rather than relying on the buffer alone.

Where SQL*Plus Saves Files

By default, SQL*Plus saves and loads files in the current directory used by your SQL*Plus session. You can specify a full or relative path in the SAVE, GET, or START command to work with files in other directories.

Saving and Running a Script File

The following example illustrates how to save a query to a script, verify that the file exists, and then run it:

Start with a query in the SQL buffer that you want to reuse. To save it as PR_CUST.sql, enter:
```
SQL> SAVE PR_CUST
      
```
SQL*Plus automatically appends .sql if you omit the extension.
SQL*Plus confirms that the file was created. To verify at the operating system level, use HOST (Windows) or ! or HOST (Linux/UNIX) to open a shell:
```
SQL> HOST
      
```
In the new shell, you can issue OS-level commands such as:
```
dir PR*        -- Windows
ls PR*         -- Linux/UNIX
      
```
When finished, exit the shell (for example, EXIT on Windows or exit on Linux/UNIX) to return to SQL*Plus.
Back in SQL*Plus, run the saved script with:
```
SQL> START PR_CUST
      
```
or:
```
SQL> @PR_CUST
      
```
SQL*Plus assumes .sql if no extension is provided. The file is read, executed, and the results are displayed.

Saving Commands: CREATE, REPLACE, and APPEND

Use the SAVE command to protect useful statements from being lost when you overwrite the buffer:


PMT  Process modeling techniques DSG 1
RSD  Relational system design   DSG 2
PRO  Prototyping                DSG 5
GEN  System generation          DSG 4
10 rows selected.

SQL> SAVE result1
SP2-0540: File "result1.sql" already exists.
Use "SAVE filename[.ext] REPLACE".

SQL> SAVE result1 REPLACE
Created file result1.sql

Key points:

If the target file does not exist, SAVE filename creates it.
If the file exists, you must explicitly choose:
- REPLACE — overwrite the existing file with the current buffer.
- APPEND — add the buffer to the end of the existing file, useful when building scripts incrementally.
By default, SQL*Plus uses the .sql extension. You can change the default extension using the SUFFIX setting (for example, to use .sqlplus or another convention), but .sql remains the most common choice.

Once a script is saved, you can open it in a system text editor, make revisions, save the changes, and then reuse the script across environments and sessions. This pattern helps you move from ad-hoc experimentation to controlled, repeatable execution.

Editing Saved Files

After a script has been created, you can edit it either from outside SQL*Plus (for example, using Notepad, Notepad++, VS Code, or vi) or by invoking the editor from SQL*Plus with EDIT filename:


SQL> EDIT PR_CUST

This opens PR_CUST.sql in your configured editor. After you save and close the editor, the file is updated on disk. You then run the updated script as usual:


SQL> @PR_CUST

When combined with source control systems (such as Git), this approach gives you a modern, auditable way to maintain database scripts while still leveraging the lightweight power of SQL*Plus.

In the next lesson, you will begin exploring SQL*Plus environment settings to control output formatting, pagination, and reporting options for the scripts you create.