Manipulating SQL, SQLcl, 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. To append text that ends with a semicolon, end the command with two semicolons (a single semicolon is interpreted as a command terminator).

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

Changes first occurrence of old on the current line of the SQL buffer. The buffer has no command history list and does not record SQLcl commands. 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 SQL 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. The buffer has no command history list and does not record SQLcl commands.

I[NPUT] [text]

Adds one or more new lines of text after the current line in the SQL buffer. The buffer has no command history list and does not record SQLcl commands.

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

Lists one or more lines of the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer. Asterisk (*) 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.

In SQLcl, you can also use ";" to list all the lines in the SQL buffer. The buffer has no command history list and does not record SQLcl commands.

Use the following commands to run scripts:

@ { url | file_name[.ext] } [arg ...]

Runs the SQLcl statements in the specified script. The script can be called from the local file system or a web server. You can pass values to script variables in the usual way.

@@ { url | file_name[.ext] } [arg ...]

Runs the SQLcl statements in the specified script. This command is almost identical to the @ command. It is useful for running nested scripts because it has the additional functionality of looking for the specified script in the same path or url as the calling script.

REPEAT <iterations> <sleep>

Repeats the current SQL in the buffer at the specified times with sleep intervals. The maximum sleep interval is 120 seconds.

SCRIPT <script file>

Runs the SQLcl statements in the specified script.

STA[RT] { url | file_name[.ext] } [arg ...]

Runs the SQLcl statements in the specified script. The script can be called from the local file system or a web server. You can pass values to script variables in the usual way.

Use the following commands to create and modify scripts:

ED[IT] [file_name[.ext]]

Invokes an 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.

The DEFINE variable _EDITOR can be used to set the editor to use. In SQLcl, _EDITOR can be set to any editor that you prefer. Inline will set the editor to be the SQLcl editor. This supports the following shortcuts:

^R - Run the current buffer
^W - Go to top of buffer
^S - Go to bottom of buffer
^A - Go to start of line
^E - Go to end of line

FORMAT

FORMAT BUFFER - formats the script in the SQLcl Buffer
FORMAT RULES <filename> - Loads SQLDeveloper Formatter rules file to formatter
FORMAT FILE <input_file> <output_file>

GET file_name[.ext] [LIST | NOLIST]

Loads a SQL statement or PL/SQL block from a file into the SQL buffer. The buffer has no command history list and does not record SQLcl commands.

REM[ARK]

Begins a comment in a script. 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). SQLcl does not interpret the comment as a command.

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

Saves the contents of the SQL buffer in a script. The buffer has no command history list and does not record SQLcl commands.

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

Saves attributes of the current SQLcl environment in a file.

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

Performs the specified action (exits SQLcl by default) if an operating system error occurs (such as a file writing error).

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

Performs the specified action (exits SQLcl by default) if a SQL command or PL/SQL block generates an error.

Use the following commands to write interactive commands:

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

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

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

PAU[SE] [text]

Displays the specified text then waits for the user to press RETURN.

PRO[MPT] [text]

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

UNDEF[INE] variable ...

Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with a START command argument).

Use the following commands to create and display bind variables:

PRINT [variable ...]

Displays the current values of bind variables, or lists all bind variables.

Use the following symbols to create substitution variables and parameters for use in scripts:

&n

Specifies a parameter in a script you run using the START command. START substitutes values you list after the script 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 SQLcl command. SQLcl substitutes the value of the specified substitution variable for each substitution variable it encounters. If the substitution variable is undefined, SQLcl 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.