Previous | Table of Contents | Next

Page 123

Table 8.1 SQL*Plus Commands Used with the SQL Buffer


Command Abbreviation Action
APPEND text A text Adds text to the end of a line
CHANGE old/new C old/new Changes old text with new in a line
CHANGE /text C /text Deletes text from a line
CLEAR BUFFER CL BUFF Deletes all lines
DEL (NONE) Deletes current line in buffer
INPUT I Adds one or more lines to the buffer
INPUT text I text Adds a line consisting of text
LIST L Lists the contents of the SQL*Plus buffer
LIST n L n or n Lists line n
LIST * L * Lists the current line
LIST m n L m n Lists lines m to n
LIST LAST L LAST Lists the last line in buffer

With the exception of the LIST command, all other editing commands affect only a single line in the buffer. This line is called the current line. It is marked with an asterisk when you list the SQL command or PL/SQL block. When you run the LIST command, the current line will always be the last line in the buffer. Also note that the semicolon (;) that ends a SQL command is not saved in the buffer, as shown in Listing 8.1.

Listing 8.1 SQL Statement Stored in Buffer

SQL> LIST

        1  SELECT empno, ename, deptno, job, sal, comm
     2  FROM emp
     3* WHERE comm IS NOT NULL

If you get an error message, the line containing the error becomes the current line so that you can edit it right away to correct the error(s). Listing 8.2 shows a listing with an error.

Listing 8.2 Line with Error Becomes Current Line

SQL> SELECT empno, empname, deptno, job, sal, comm
     2  FROM emp
     3  WHERE comm IS NOT NULL;
SELECT empno, empname, deptno, job, sal, comm
                *
ERROR at line 1:
ORA-00904: invalid column name

Page 124

Entering and Editing SQL*Plus Commands

SQL*Plus commands, such as DESCRIBE, entered directly at the SQL*Plus prompt, are not saved in the SQL buffer. Therefore, you cannot retrieve the last SQL*Plus command entered to edit and/or rerun it. The result of trying to list an SQL*Plus command from the buffer is shown in Listing 8.3.

Listing 8.3 SQL*Plus Commands Are Not Buffered

SQL> DESCRIBE EMP
 Name              Null?      Type
 --------------   --------   ------
 EMPNO             NOT NULL   NUMBER(4)
 ENAME                        VARCHAR2(10)
 JOB                          VARCHAR2(9)
 MGR                          NUMBER(4)
 HIREDATE                     DATE
 SAL                          NUMBER(7,2)
 COMM                         NUMBER(7,2)
 DEPTNO            NOT NULL   NUMBER(2)
SQL> LIST
No lines in SQL buffer.

To store SQL*Plus commands in the buffer, enter INPUT with no text and press Enter. SQL*Plus prompts you with a line number where you can enter the commands. When you are finished entering the commands, enter a blank line by pressing the Return key. Listing 8.4 shows an example.

Listing 8.4 Storing SQL*Plus Commands in the Buffer

SQL> INPUT
     1  DESCRIBE EMP
     2
SQL> L
     1* DESCRIBE EMP
NOTE
See Table 8.1 for the abbreviation "L," which stands for "List."

You cannot execute the command from the buffer, but you can save it in a file, which you can retrieve and run later in SQL*Plus. A failed attempt at running an SQL*Plus command from the buffer but then successfully saving it to a file is shown in Listing 8.5.

Page 125

Listing 8.5 Save SQL*Plus Commands to Files

SQL> RUN
     1* DESCRIBE EMP
DESCRIBE EMP
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> SAVE test
Created file test
SQL> GET test
     1* DESCRIBE EMP

The newly created file is saved in the directory pointed to by the SQLPATH environment variable.

Using Your Operating System Editor in SQL*Plus

The editing capabilities offered by SQL*Plus are poor and not intuitive compared to other text editors. (For instance, you cannot use the arrow, Home or End keys.) Therefore, many users prefer to create their command files using editors they feel comfortable with, then run these files in SQL*Plus using the START or @ command.

If you would rather work with your operating system editor than use the editing capabilities of SQL*Plus, you can do that in SQL*Plus by using the EDIT command. The syntax of the EDIT command is

EDIT [file_name[.ext]]

This will open file_name with the editor defined by the variable _editor in the glogin.sql or login.sql file. If you want to use a different editor in your SQL*Plus session, you can redefine the _editor variable with the SQL*Plus command DEFINE:

DEFINE _editor=emacs

If the _editor variable is not defined, the EDIT command tries to use the default operating system editor (for example, Notepad in Windows 95).

When you issue the EDIT command, you invoke an editor from within SQL*Plus without leaving it, which is convenient.

If you do not supply a filename when running the EDIT command, SQL*Plus will save the contents of the SQL buffer in a file and open that file with the editor. By default, the name of the file is afiedt.buf, and it is created in the current directory or the directory defined in the SQLPATH environment variable. When opening the editor, you can use the full pathname for the file you want to edit, for example: EDIT C:\MYDIR\MYFILE.SQL.

SQL> EDIT
Wrote file afiedt.buf

Page 126

You can change the name of the file where SQL*Plus saves the contents of the buffer by setting the appropriate value in the editfile variable, as shown in Listing 8.6.

Listing 8.6 Edited Files Have a Default Name

SQL> SHOW editfile
editfile "afiedt.buf"
SQL> SET editfile "buffer.txt"
SQL> SHOW editfile
editfile "buffer.txt"

If you do not enter a filename when running the EDIT command and the SQL buffer is empty, SQL*Plus returns a notification message, like the one shown in Listing 8.7.

Listing 8.7 Empty Buffers Have Nothing to Save

SQL> CLEAR BUFFER
buffer cleared
SQL> EDIT
Nothing to save.

The default extension for the filename is .sql. So, if you do not specify a file extension, SQL*Plus will look for a file named file_name.sql. If you want to edit a file with an extension other than .sql, you have to explicitly specify the extension. You can also change the default value for the file extension through the SUFFIX variable, as shown in Listing 8.8.

Listing 8.8 Changing the Default Suffix

SQL> SHOW SUFFIX
suffix "SQL"
SQL> SET SUFFIX sh
SQL> SHOW SUFFIX
suffix "sh"
NOTE
The SUFFIX variable applies only to command files, not to spool (output) files. Depending on the operating system, the default extension for spool files is .lst or .lis.

Running SQL*Plus/SQL Commands

You can run SQL commands and PL/SQL blocks three ways:

Previous | Table of Contents | Next