Manipulating SQL, SQL*Plus, and PL/SQL Commands

Use the following commands to edit SQL commands and PL/SQL blocks.

A[PPEND] text

Adds specified text to the end of the current line in the SQL buffer. To separate text from the preceding characters with a space, enter two spaces between APPEND and text. To append text that ends with a semicolon, end the command with two semicolons (SQL*Plus interprets a single semicolon as a command terminator).

C[HANGE] sepchar old [sepchar [new [sepchar]]]

Changes text on the current line in the SQL buffer. You can use any non-alphanumeric character such as "/" or "!" as a sepchar. You can omit the space between CHANGE and the first sepchar.

DEL [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Deletes one or more lines of the buffer ("*" indicates the current line). You can omit the space between DEL and n or *, but not between DEL and LAST. Enter DEL with no clauses to delete the current line of the buffer.

I[NPUT] [text]

Adds one or more new lines of text after the current line in the buffer.

L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]

Lists one or more lines of the buffer ("*" indicates the current line). You can omit the space between LIST and n or *, but not between LIST and LAST. Enter LIST with no clauses to list all lines.

Use the following commands to create and modify command files.

@ file_name[.ext] [arg ...]

Runs the specified command file. Specified arguments are substituted for &1, &2, and so on.

@@ file_name[.ext]

Runs the specified nested command file.

ED[IT] [file_name[.ext]]

Invokes a host operating system text editor on the contents of the specified file or on the contents of the SQL buffer. To edit the buffer contents, omit the file name.

GET file_name[.ext] [LIS[T]|NOL[IST]]

Loads a host operating system file into the SQL buffer.

REM[ARK]

Begins a comment in a command file. The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line (a line cannot contain both a comment and a command). SQL*Plus does not interpret the comment as a command.

SAV[E] file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Saves the contents of the buffer into a host operating system file (a command file).

STORE {SET} file_name[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Saves the attributes of the current SQL*Plus environment in a host operating system file (a command file).

STA[RT] file_name[.ext] [arg ...]

Executes the contents of the specified command file. Specified arguments are substituted for &1, &2, and so on.

WHENEVER OSERROR
{EXIT [SUCCESS|FAILURE|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Exits SQL*Plus if an operating system error occurs (such as a file I/O error).

WHENEVER SQLERROR
{EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]}

Exits SQL*Plus if a SQL command or PL/SQL block generates an error.

Use the following commands to write interactive commands.

ACC[EPT] variable [NUM[BER]|CHAR|DATE] [FOR[MAT] format] [DEF[AULT] default] [PROMPT text|NOPR[OMPT]] [HIDE]

Reads a line of input and stores it in a given user variable.

DEF[INE] [variable]|[variable = text]

Specifies a user variable and assigns it a CHAR value. Alternatively, lists the value and variable type of a single variable or all variables.

PAU[SE] [text]

Displays an empty line followed by a line containing text, then waits for the user to press [Return]. Alternatively, displays two empty lines and waits for the user's response.

PROMPT [text]

Sends the specified message or a blank line to the user's screen.

UNDEF[INE] variable ...

Deletes given user variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).

Use the following commands to create and display bind variables.

PRI[NT] [variable ...]

Displays the current values of bind variables.

VAR[IABLE] [variable {NUMBER|CHAR|CHAR (n)|NCHAR|NCHAR (n)|VARCHAR2 (n)| NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR}]

Declares a bind variable which can then be referenced in PL/SQL. If no arguments are supplied, VARIABLE lists all declared bind variables.

Use the following symbols to create substitution variables and parameters for use in command files.

&n

Specifies a parameter in a command file you run using the START command. START substitutes values you list after the command file name as follows: the first for &1, the second for &2, and so on.

&user_variable, &&user_variable

Indicates a substitution variable in a SQL or SQL*Plus command. SQL*Plus substitutes the value of the specified user variable for each substitution variable it encounters. If the user variable is undefined, SQL*Plus prompts you for a value each time an "&" variable is found, and the first time an "&&" variable is found.

. (period)

Terminates a substitution variable followed by a character that would otherwise be part of the variable name.