Oracle Developer Tools for VS Code supports executing SQL commands and PL/SQL blocks. It also supports some SQL*Plus commands which provide additional useful functionality such as:
This is a list of SQL*Plus commands supported by Oracle Developer Tools for VS Code. For more details about individual SQL*Plus commands visit the SQL*Plus User's Guide and Reference (Please note that not all SQL*Plus commands and functionalities are supported by Oracle Developer Tools for VS Code at this time. In some instances, some parameters or options for a particular command may not be supported)
@file_name[.ext] [arg ...]
Runs SQL*Plus statements in the specified script.
@@file_name[.ext] [arg ...]
Runs a script. This command is similar to the @ (at sign) command. It is useful for running nested scripts because it looks for the specified script in the same path as the calling script.
/ (slash)
Executes the SQL command or PL/SQL block.
CL[EAR] BUFF[ER] SQL TIMI[NG]
Resets or erases the current clause or setting for the specified option.
CONN[ECT] [{logon | / | proxy} [AS { SYSDBA | SYSOPER}]]
Connects a given user to Oracle Database.
DEF[INE] [variable] | [variable = text]
Specifies a substitution variable and assigns it a CHAR value, or lists the value and variable type of a single variable or all variables.
DEL [n | n m | n * | n LAST | * | * n | * LAST | LAST]
Deletes one more lines of the buffer.
DESC[RIBE] [schema.]object
Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function procedure.
DISC[ONNECT]
Commits pending changes to the database and logs the current user off Oracle Database, but does not exit SQL*Plus.
EXEC[UTE] statement
Executes a single PL/SQL statement.
{EXIT | QUIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Terminates processing of the script
I[NPUT] [text]
Adds one or more new lines after the current line in the buffer.
PASSW[ORD] [username]
Enables a password to be changed without echoing the password on an input device.
PRINT [variable ...]
Displays the current value of a bind variable.
PRO[MPT] [text]
Sends the specified message to the screen.
{QUIT | EXIT} [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] [COMMIT | ROLLBACK]
Terminates processing of the script. QUIT is identical to EXIT.
REM[ARK]
Begins a comment in a script.
R[UN]
Lists and runs the SQL command or PL/SQL block currently stored in the SQL buffer.
SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves the contents of the buffer in an operating system file (a script).
SET system_variable value
Sets a system variable to alter the SQL*Plus environment for your current session. See below for supported SET commands.
SHO[W] [option]
Shows the value of a SQL*Plus system variable or the current SQL*Plus environment. Supported SHOW commands follow:
SHOW ALL
Lists the settings of all SHOW options
SHOW ERR[ORS]
Shows the compilation errors
SHOW SPOO[L]
Shows whether output is being spooled.
SHOW SQLCODE
Shows the value of SQL.SQLCODE
SHOW USER
Shows the username .
SHOW XQUERY
Shows the current values of the XQUERY settings, BASEURI, CONTEXT, NODE and ORDERING.
SPO[OL] [filename[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]
Stores query results in an operating system file and, optionally, sends the file to a printer.
STA[RT] file_name[.ext [arg ...]
Runs the SQL statements in the specified script.
STORE {SET} file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]
Saves attributes of the current SQL*Plus environment in an operating system script.
TIMING [START text | SHOW | STOP]
Records timing data for an elapsed period of time, lists the current timer's title and timing data, or lists the number of active timers.
UNDEF[INE] variable ...
Deletes one or more substitution variables that you defined either explicitly (with the DEFINE command) or implicitly (with an argument to the START command).
VAR[IABLE] [variable [type][=value]]
Declares a bind variable that can be referenced in PL/SQL, or lists the current display characteristics for a single variable or all variables.
XQUERY xquery_statement
Runs an XQuery 1.0 statement.
Supported SET commands
SET APPI[NFO]{ON | OFF | text}
Sets automatic registering of scripts through the DBMS_APPLICATION_INFO package.
SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}
Controls when Oracle Database commits pending changes to the database.
SET AUTOP[RINT] {ON | OFF}
Sets the automatic printing of bind variables.
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
Displays a report on the execution of successful SQL DML statements (SELECT, INSERT, UPDATE, DELETE or MERGE).
SET BLO[CKTERMINATOR] {. | c | ON | OFF}
Sets the non-alphanumeric character used to end PL/SQL blocks to c.
SET CMDS[EP] {; | c | ON | OFF}
Sets the non-alphanumeric character used to separate multiple SQL*Plus commands entered on one line to c.
SET COLINVI[SIBLE] [ON | OFF]
ON sets the DESCRIBE command to display column information for an invisible column..
SET COLSEP { | text}
Sets the text to be printed between selected columns.
SET CON[CAT] {. | c | ON | OFF}
Sets the character you can use to terminate a substitution variable reference if you wish to immediately follow the variable with a character that SQL*Plus would otherwise interpret as a part of the substitution variable name.
SET DEF[INE] {& | c | ON | OFF}
Sets the character used to prefix variables to c.
SET DESCRIBE [DEPTH {1 | n | ALL}] [LINENUM {ON | OFF}] [INDENT {ON | OFF}]
Sets the depth of the level to which you can recursively describe an object.
SET ECHO {ON | OFF}
Controls whether the START command lists each command in a script as the command is executed.
SET ERRORL[OGGING] {ON | OFF} [TABLE [schema.]tablename] [TRUNCATE] [IDENTIFIER identifier]
Enables recording of SQL, PL/SQL and SQL*Plus errors to an error log table which you can query later.
SET ESC[APE] {\ | c | ON | OFF}
Defines the character you enter as the escape character.
SET ESCCHAR {@ | ? | % | OFF}
Specifies a special character to escape in a filename. Prevents character translation causing an error.
SET EXITC[OMMIT] {ON | OFF}
Specifies whether the default EXIT behavior is COMMIT or ROLLBACK.
SET FEED[BACK] {6 | n | ON | OFF }] [SQL_ID]
Displays the number of records returned by a query when a query selects at least n records.
SET FLAGGER {OFF | ENTRY | INTERMED[IATE] | FULL}
Checks to make sure that SQL statements conform to the ANSI/ISO SQL92 standard.
SET HEA[DING] {ON | OFF}
Controls printing of column headings in reports.
SET HEADS[EP] { | c | ON | OFF}
Defines the character you enter as the heading separator character.
SET INSTANCE [instance_path | LOCAL]
Changes the default instance for your session to the specified instance path.
SET LOBOF[FSET] {1 | n}
Sets the starting position from which BLOB, BFILE, CLOB and NCLOB data is retrieved and displayed.
SET LONG {80 | n}
Sets maximum width (in bytes) for displaying LONG, BLOB, BFILE, CLOB, NCLOB and XMLType values; and for copying LONG values.
SET NULL text
Sets the text that represents a null value in the result of a SQL SELECT command.
SET SERVEROUT[PUT] {ON | OFF} [SIZE {n | UNL[IMITED]}]
Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus.
SET SHOW[MODE] {ON | OFF}
Controls whether SQL*Plus lists the old and new settings of a SQL*Plus system variable when you change the setting with SET.
SET SQLBL[ANKLINES] {ON | OFF}
Controls whether SQL*Plus puts blank lines within a SQL command or script.
SET SQLC[ASE] {MIX[ED] | LO[WER] | UP[PER]}
Converts the case of SQL commands and PL/SQL blocks just prior to execution.
SET SQLN[UMBER] {ON | OFF}
Sets the prompt for the second and subsequent lines of a SQL command or PL/SQL block.
SET SUF[FIX] {SQL | text}
Sets the default file that SQL*Plus uses in commands that refer to scripts.
SET TI[ME] {ON | OFF}
Controls the display of the current time.
SET TIMING {ON | OFF}
Controls the display of timing statistics.
SET TRIM[OUT] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each displayed line.
SET TRIMS[POOL] {ON | OFF}
Determines whether SQL*Plus puts trailing blanks at the end of each spooled line.
SET VER[IFY] {ON | OFF}
Controls whether SQL*Plus lists the text of a SQL statement or PL/SQL command before and after SQL*Plus replaces substitution variables with values.
SET WRA[P] {ON | OFF}
Controls whether SQL*Plus truncates the display of a SELECTed row if it is too long for the current line width.