Using SQL*Plus Commands with Oracle Developer Tools for VS Code

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 page describes some of the most useful and popular of the SQL*Plus commands. For a full list of supported commands visit the Oracle Developer Tools for VS Code - SQL*Plus Command Reference (Note that not all SQL*Plus commands and functionalities are supported by Oracle Developer Tools for VS Code)

Commonly Used SQL*Plus Commands

• CONN[ECT] [{logon | / | proxy} [AS { SYSDBA |SYSOPER}]]

  Connects a given user to Oracle Database.

  Example:

  Copy

    CONNECT myuser/mypassword@myhost/myservice:1521;
  
    

 

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

  Runs SQL*Plus statements in the specified script.

  Example:

  Copy

    @c:\myscripts\createHR.sql;
  
    

  Copy

    @~/scripts/createHR.sql;
  
    

 

• DESC[RIBE] [schema.]object;

  Lists the column definitions for the specified table, view, or synonym or the specifications for the specified function procedure.

  Example:

  Copy

  DESCRIBE EMPLOYEES;
  
    

 

• SET AUTO[COMMIT]{ON | OFF | IMM[EDIATE] | n}         

  Controls when Oracle Database commits pending changes to the database.             

  Example:

  Copy

  SET AUTOCOMMIT OFF;
  
    

 

• SET ECHO {ON | OFF}    

  Controls whether the command being executed is outputted with the results.

  Example:

  Copy

  SET ECHO OFF;
  
    

 

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

  Example:

  Copy

  SET SERVEROUTPUT OFF;
  
    

 

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

  Example:

  Copy

  SET FEEDBACK ON;
  
    

 

• SET LONG {80 | n}          

  Sets maximum width (in bytes) for displaying LONG, BLOB, BFILE, CLOB, NCLOB and XMLType values; and for copying LONG values.        

  Example:

   

  Copy

  SET LONG 150;
  
    

 

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

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

  Example:

  Copy

  DEFINE myvar = 'King';
  
  select employee_id from employees where last_name = '&myvar';
    

 

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

  Example:

  Copy

  VARIABLE bindv number;
  
  begin
  :bindv := 8;
  end;
  /
  

 

• PRINT [variable ...]

  Displays the current value of a bind variable.

  Example:

  Copy

  PRINT bindv;
  
    

 

• PRO[MPT] [text]

  Sends the specified message to the screen.

  Example:

  Copy

  PROMPT My substitution variable is set to &myvar;
  
    

 

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

  Example:

  Copy

  SPOOL output.log;