Oracle Developer Tools for VS Code - SQL*Plus Command Reference

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:

• Connecting and disconnecting
• Enabling and disabling autocommit
• Running other SQL*Plus scripts
• Describing database object metadata
• Defining and using substitution variables
• Defining and using bind variables
• Saving script output to a file

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)

Supported SQL*Plus Commands

• @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.