SQL*Plus Editing Commands

SQL is a command language that is used for communication with the Oracle server from any tool or application. Oracle SQL contains many extensions. When you enter a SQL statement, it is stored in a part of memory called the SQL buffer and remains there until you enter a new SQL statement. SQL*Plus is an Oracle tool that recognizes and submits SQL statements to the Oracle9i Server for execution. It contains its own command language.

SQL*Plus Editing Commands

Table below lists the most commonly used SQL*Plus editing commands:

Using LIST, n, and APPEND

1. Use the L[IST] command to display the contents of the SQL buffer. The asterisk (*) beside line 2 in the buffer indicates that line 2 is the current line. Any edits that you made apply to the current line.

LIST
1 SELECT last_name
2* FROM employees

2. Change the number of the current line by entering the number (n) of the line that you want to edit. The new current line is displayed.

1
1* SELECT last_name

3. Use the A[PPEND] command to add text to the current line. The newly edited line is displayed. Verify the new contents of the buffer by using the LIST command.

A , job_id
1* SELECT last_name, job_id

Using the CHANGE Command

1. Use L[IST] to display the contents of the buffer:

LIST
1* SELECT * from employees

2. Use the C[HANGE] command to alter the contents of the current line in the SQL buffer.In this case, replace the employees table with the departments table. The new current line is displayed:

c/employees/departments
1* SELECT * from departments

3. Use the L[IST] command to verify the new contents of the buffer:

LIST
1* SELECT * from departments

SQL*Plus File Commands

SQL statements communicate with the Oracle server. SQL*Plus commands control the environment, format query results, and manage files. You can use the commands described in the following table:

Using the SAVE, START Commands

SAVE
Use the SAVE command to store the current contents of the buffer in a file. Thus, you can store frequently-used scripts for use in the future.

START
Use the START command to run a script in SQL*Plus. You can also, alternatively, use the symbol @ to run a script.

@my_query

Examples:

LIST
1 SELECT last_name, manager_id, department_id
2* FROM employees

SAVE my_query
Created file my_query

START my_query
LAST_NAME                 MANAGER_ID DEPARTMENT_ID
------------------------- ---------- -------------
King                                        90
Kochhar                       100           90
...
107 rows selected.

SERVEROUTPUT Command

Most of the PL/SQL programs perform input and output through SQL statements, to store data in database tables or query those tables. All other PL/SQL input/output is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures, such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to DBMS_OUTPUT.

Use the SET SERVEROUT[PUT] command to control whether to display the output of stored procedures or PL/SQL blocks in SQL*Plus. The DBMS_OUTPUT line length limit is increased from 255 bytes to 32767 bytes. The default size is now unlimited. Resources are not preallocated when SERVEROUTPUT is set. Because there is no performance penalty, use UNLIMITED unless you want to conserve physical memory.

SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}]
[FOR[MAT] {WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]}]

SQL*Plus does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON as follows:

SET SERVEROUTPUT ON

Using the SQL*Plus SPOOL Command

The SPOOL command stores query results in a file or optionally sends the file to a printer. The SPOOL command has been enhanced. You can now append to, or replace an existing file, where previously you could use SPOOL only to create (and replace) a file. REPLACE is the default.

Syntax:

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] |
APP[END]] | OFF | OUT]

Spool Command Options:

To spool the output generated by commands in a script without displaying the output on screen, use SET TERMOUT OFF. SET TERMOUT OFF does not affect the output from commands that run interactively.

You must use quotation marks around file names that contain white space. To create a valid HTML file using SPOOL APPEND commands, you must use PROMPT or a similar command to create the HTML page header and footer. The SPOOL APPEND command does not parse HTML tags. SET SQLPLUSCOMPAT[IBILITY] to 9.2 or earlier to disable the CREATE, APPEND, and SAVE parameters.

Using the AUTOTRACE Command

It displays a report after the successful execution of SQL DML statements such as SELECT, INSERT, UPDATE, or DELETE. The report can now include execution statistics and the query execution path.

Syntax:

SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]]
[STAT[ISTICS]]

SET AUTOTRACE ON
-- The AUTOTRACE report includes both the optimizer
-- execution path and the SQL statement execution
-- statistics

EXPLAIN shows the query execution path by performing an EXPLAIN PLAN. STATISTICS displays SQL statement statistics. The formatting of your AUTOTRACE report may vary depending on the version of the server to which you are connected and the configuration of the server. The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several predefined formats.