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.