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
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.
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. |
You can run SQL commands and PL/SQL blocks three ways: