Lesson 3	Editing and Running a Query in SQL*Plus
Objective	Change text by using the in-line editor and run a query.

Editing and Running Queries in SQL*Plus

In this lesson you will learn how to use the SQL*Plus in-line editor to make quick changes to a query and then rerun it. The in-line editor is not meant to replace a full-featured code editor, but it is ideal when you spot a simple mistake, such as a misspelled column name, and want to correct it without retyping the entire statement.

SQL*Plus keeps your most recent command in a memory buffer. The in-line editor works directly against this buffer, allowing you to list lines, change text, insert or delete lines, and then execute the corrected command. This workflow is especially useful when you are iterating on ad-hoc queries directly from the SQL*Plus prompt.

SQL*Plus In-line Editing Commands

The SQL*Plus editor is similar to older line editors such as the UNIX ed utility. Although it is minimal, a small set of commands allows you to correct most simple mistakes. The table below summarizes the core commands you will use:

Command	Description
A[PPEND] text	Append text to the end of the current line.
C[HANGE] separator old [separator [new [separator]]	Replace old with new on the current line. The separator can be any non-alphanumeric character, such as `/`. If new is omitted, old is removed.
DEL [n]	Delete line number n from the buffer. If n is omitted, delete the current line.
I[NPUT] [text]	Insert a new line with text immediately after the current line. If text is omitted, a blank line is created and you type into it.
L[IST] [n]	List line n and make it the current line. If n is omitted, list all lines in the buffer.

Starting SQL*Plus and Reaching the Prompt

You can start SQL*Plus from a desktop client or terminal, depending on your platform and Oracle client installation. After launching SQL*Plus, you will be prompted for a username and password. Once you authenticate successfully, SQL*Plus confirms the connection and displays the prompt:


SQL>

At this point, SQL*Plus is ready to accept SQL, PL/SQL, and SQL*Plus commands. Every statement you execute from this prompt is stored in the buffer until it is replaced by the next statement.

Example: Fixing a Typo with the In-line Editor

The following example walks through the typical workflow when you need to correct a simple error in a query using the in-line editor and then re-run it.

You type a query and execute it, but SQL*Plus reports an error such as an invalid column name. For example, you typed C.CUST_IN instead of C.CUST_ID.
To focus on the line that contains the error, list that line and make it the current line. If the error is on line 3, enter:
```
SQL> L 3
    
```
SQL*Plus displays line 3, and that line becomes the current line in the buffer.
Use the CHANGE command to replace the incorrect text. For example:
```
SQL> C/IN/ID/
    
```
This tells SQL*Plus to change the text IN to ID on the current line. SQL*Plus echoes the updated line so you can confirm the change.
Before executing the corrected query, list the entire buffer to verify that the full statement is now correct:
```
SQL> L
    
```
SQL*Plus displays each line in order, with an asterisk (*) marking the current line.
When the query looks correct, execute it by typing a forward slash (/) on its own line:
```
SQL> /
    
```
SQL*Plus re-runs the statement from the buffer, and the query results are displayed. You have now successfully changed the text using the in-line editor and run the corrected query.

Oracle Database SQL

Direct SQL Access and Common Login Errors

Many development tools provide direct SQL access to Oracle databases. The SQL commands demonstrated in this module can usually be executed from these tools as well. However, commands that are specific to SQL*Plus, such as DESCRIBE or SQL*Plus environment commands, might not be recognized outside SQL*Plus.
If you have trouble starting or connecting with SQL*Plus, typical causes include:

Oracle client binaries not in your executable path.
Your user account not authorized to connect.
Oracle not installed or not configured correctly on your system.

If you enter invalid credentials, you may see:


ERROR: ORA-1017: invalid username/password; logon denied

After three failed login attempts, SQL*Plus exits with:


unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

If this occurs and you believe your credentials are correct, contact your database administrator to verify your account and connection details.

Exiting SQL*Plus

When you are done working in SQL*Plus, use one of the following commands to exit cleanly:


SQL> quit

or:


SQL> exit

In the next lesson, you will explore an alternative method for editing SQL*Plus commands using external editors, which is often more convenient for larger scripts and complex PL/SQL blocks.