No results found

Your search did not match any results.

We suggest you try the following to help find what you’re looking for:

  • Check the spelling of your keyword search.
  • Use synonyms for the keyword you typed, for example, try “application” instead of “software.”
  • Try one of the popular searches shown below.
  • Start a new search.
Trending Questions
 

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:

      CONNECT myuser/mypassword@myhost/myservice:1521;
      
  •  

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

    Runs SQL*Plus statements in the specified script.

    Example:

      @c:\myscripts\createHR.sql;
      
      @~/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:

    DESCRIBE EMPLOYEES;
      
  •  

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

    Controls when Oracle Database commits pending changes to the database.             

    Example:

    SET AUTOCOMMIT OFF;
      
  •  

  • SET ECHO {ON | OFF}    

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

    Example:

    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:

    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:

    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:

     

    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:

    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:

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

  • PRINT [variable ...]

    Displays the current value of a bind variable.

    Example:

    PRINT bindv;
      
  •  

  • PRO[MPT] [text]

    Sends the specified message to the screen.

    Example:

    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:

    SPOOL output.log;
      
  •