Using a Text Editor with SQL*Plus

In the previous lesson you learned how to make quick fixes to a query using the SQL*Plus in-line editor. That approach works well for short statements and minor changes. In this lesson, you will learn how to use a full text editor to change a query stored in the SQL*Plus buffer, which is the preferred method when your query is longer, more complex, or you want the comfort of a modern editor.

SQL*Plus can launch an external text editor, write the current buffer to a temporary file, and then reload your changes when you save and close the editor. On Windows, the default editor is typically Notepad; on Linux and UNIX it is often vi or another console editor. You can also configure SQL*Plus to use editors such as Notepad++, VS Code, or any other tool installed on your system.

How SQL*Plus Uses a Text Editor

To send the current SQL*Plus buffer to your system editor, use the EDIT command:

EDIT

• SQL*Plus writes the contents of the current buffer (usually your last statement) to a temporary file, historically named AFIEDT.BUF.
• Your configured editor opens that file so you can make changes.
• After you save and close the editor, SQL*Plus reads the file back into the buffer and displays the updated statement.
• You can then execute the modified query directly from SQL*Plus.

For quick, one-off changes, you may continue to use the in-line editor. For multi-line queries, complex joins, or scripts you want to keep, using a text editor is faster, safer, and easier to maintain.

Step-by-Step: Changing a Query with a Text Editor

The following 12 images walk through a realistic workflow: starting from an existing query, opening it in a text editor, revising it, correcting an error, and finally running the successful version in SQL*Plus.

Starting SQL*Plus and Connecting to a Database

To use a text editor with SQL*Plus, you must first connect to an Oracle database. Common connection patterns include:

1. Open a terminal (Linux/UNIX) or command prompt (Windows) and start SQL*Plus:

   
   sqlplus
   

   When prompted, enter your Oracle username and password.
2. Alternatively, specify the username directly:

   
   sqlplus username
   

   SQL*Plus will then prompt for your password.
3. To connect to a specific database service (for example, in a multi-database or cloud environment), use a connect identifier:

   
   sqlplus username@connect_identifier
   

   You will be prompted for your password and, on success, SQL*Plus displays the SQL> prompt.

SQL Style and Readability

SQL*Plus itself is case-insensitive for keywords and object names that are not quoted. The following two statements are equivalent:

SeLect feaTURE, section, PAGE FROM newsPaPeR;

select Feature, Section, Page from NEWSPAPER;

Although case does not affect execution (unless you use quoted identifiers), consistent styling improves readability for you and your team. Common conventions include:

• Write SQL keywords such as select, from, where, group by, and order by in a consistent style (often all lowercase or all uppercase).
• Use a clear naming convention for columns and tables (for example, table names in uppercase, column names in mixed case).
• Align predicates and joins vertically when editing in a text editor so logical conditions are easy to scan.

Adopting consistent style guidelines in your text editor makes it easier to review, share, and maintain scripts over time.

Using Line and Text Editors Together

In practice, you will often use both the in-line editor and a text editor:

• Use the in-line editor (L, C, DEL, and so on) for very small fixes, such as correcting a single column name.
• Use a text editor via EDIT when:
  • Your query spans many lines.
  • You are adding multiple columns, joins, or aggregate functions.
  • You want to save the statement as a reusable script.

A typical workflow is:

1. Run a query from the SQL*Plus prompt and review the output or error message.
2. Use EDIT to open the statement in your text editor.
3. Refactor or extend the query (for example, add COUNT, SUM, or additional join conditions).
4. Save and close the editor so SQL*Plus reloads the updated query into the buffer.
5. Execute the revised query with / and review the results.

SQL*Plus Date Format

The way dates are displayed in SQL*Plus is controlled by the database NLS_DATE_FORMAT parameter. In some environments this may still use a two-digit year, which is not ideal for modern reporting. You can override the display format within your queries using:

• The SQL TO_CHAR function inside SELECT statements.
• The SQL*Plus COLUMN FORMAT command to format date columns in reports.

When you are editing scripts in a text editor, it is good practice to standardize on an unambiguous date format such as 'YYYY-MM-DD' or an ISO-like format ('YYYY-MM-DD HH24:MI:SS') so that reports remain clear across regions and time zones.

Writing Scripts with a System Editor

Instead of editing only the current buffer, you can also use your system editor to maintain reusable SQL and PL/SQL scripts that you run regularly from SQL*Plus.

You can tell SQL*Plus which editor to use by setting the special _EDITOR variable:

DEFINE _EDITOR = vi

On Windows you might set this to notepad, notepad++, or code (for VS Code), depending on what you have installed. Include this definition in your site or user profile so that SQL*Plus always launches your preferred editor.

To create or edit a script file, use EDIT followed by a filename:

EDIT SALES

If you omit a file extension, SQL*Plus adds .SQL, so the script is saved as SALES.SQL. Inside the editor, you can compose any combination of SQL*Plus, SQL, and PL/SQL statements.

Example: Using a System Editor to Create a Script

Suppose you want to review salesperson performance each month. You can write a reusable script in your system editor:

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY' FORMAT $99,999
COLUMN COMMISSION_PCT   HEADING 'COMMISSION %'   FORMAT 90.90

SELECT LAST_NAME,
       SALARY,
       COMMISSION_PCT
FROM   EMP_DETAILS_VIEW
WHERE  JOB_ID = 'SA_MAN';

Save this as SALES.SQL. To run the script from SQL*Plus, simply execute:

SQL> @SALES

By combining SQL*Plus with a modern text editor, you can quickly change individual queries, maintain a library of scripts, and align your workflow with current best practices for Oracle Database development and administration.