OraCmd - Oracle access without client tool »

Download OraCmd Free Trial »

Sql*Plus Glossary

What is SQL*Plus and where does it come from?

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server. It can be used interactively or driven from scripts. SQL*Plus is frequently used by DBAs and Developers to interact with the Oracle database. 
If you are familiar with other databases, sqlplus is equivalent to "sql" in Ingres, "isql" in Sybase and SQLServer, "db2" in IBM DB2, "psql" in PostgresQL, and "mysql" in MySQL. 

SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was included in the first Oracle releases up to Oracle v4. The UFI interface was extremely primitive and, in today's terms, anything but user friendly. If a statement was entered incorrectly, UFI issued an error and rolled back the entire transaction (ugggh). 


--------------------------------------------------------------------------------

How does one use the SQL*Plus utility?

Start using SQL*Plus by executing the "sqlplus" command-line utility. Valid options are:
	userid/password@db -- Connection details
	/nolog             -- Do not login to Oracle. You will need to do it yourself.
	-s or -silent      -- start sqlplus in silent mode. Not recommended for beginners!
	@myscript          -- Start executing script called "myscript.sql"  
Look at this example session: 
	sqlplus /nolog
	SQL> connect scott/tiger
	SQL> select * from tab;
	SQL> disconnect
	SQL> exit
Please note that one must prepare the environment before starting sqlplus. Linux/ Unix example: 
	 $ . oraenv
	 ORACLE_SID = [orcl] ? orcl
	 $ sqlplus scott/tiger
Windows Example: 
	 Click on "Start" -> "Run" and enter "cmd"
	 C:> set ORACLE_SID=orcl
	 C:> sqlplus scott/tiger
	or
	 C:> sqlplus scott/tiger@orcl


--------------------------------------------------------------------------------

What commands can be executed from SQL*Plus?

One can enter three kinds of commands from the SQL*Plus command prompt: 
SQL*Plus commands - SQL*Plus commands are used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on. SQL*Plus commands do not interact with the database. These commands do not have to be terminated with a semicolon (;), as is the case with SQL commands. The rest of this page is dedicated to SQL*Plus commands, eg.

SHOW USER

SQL commands - for more information see the Oracle SQL FAQ. Eg:

SELECT * FROM TAB;

PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg:

	BEGIN
	  DBMS_OUTPUT.PUT_LINE('Hello World!');
	END;
	/

--------------------------------------------------------------------------------

What are the basic SQL*Plus commands?

The following SQL*Plus commands are available: 

ACCEPT  Get input from the user  
DEFINE  Declare a variable (short: DEF)  
DESCRIBE  Lists the attributes of tables and other objects (short: DESC)  
EDIT  Places you in an editor so you can edit a SQL command (short: ED)  
EXIT or QUIT  Disconnect from the database and terminate SQL*Plus  
GET  Retrieves a SQL file and places it into the SQL buffer  
HOST  Issue an operating system command (short: !)  
LIST  Displays the last command executed/ command in the SQL buffer (short: L)  
PROMPT  Display a text string on the screen. Eg prompt Hello World!!!  
RUN  List and Run the command stored in the SQL buffer (short: /)  
SAVE  Saves command in the SQL buffer to a file. Eg "save x" will create a script file called x.sql  
SET  Modify the SQL*Plus environment eg. SET PAGESIZE 23  
SHOW  Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE etc.  
SPOOL  Send output to a file. Eg "spool x" will save STDOUT to a file called x.lst  
START  Run a SQL script file (short: @)  

--------------------------------------------------------------------------------

How does one trace (and explain) SQL statements from SQL*Plus?

When the AUTOTRACE setting is enabled, SQL*Plus will print an EXPLAIN PLAN and execution statistics after each SQL statement. Look at this example: 
SQL> set autotrace on
SQL> select * from dept where deptno = 40;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=18)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=1 Card=1 Bytes=18)
   2    1     INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Follow these steps to enable this feature: 
Run the PLUSTRCE.SQL script from the SYS database user. This script is located in the $ORACLE_HOME/sqlplus/admin directory.

Create a PLAN_TABLE table by running the UTLXPLAN.SQL script. This script is in $ORACLE_HOME/rdbms/admin.

Use the "SET AUTOTRACE ON" command to trace SQL execution. This will print an explain plan and high level trace information after your query results. 

--------------------------------------------------------------------------------

Can one pass operating system parameters to SQL*Plus?

One can pass operating system variables to sqlplus using this syntax: 
	sqlplus username/password @cmdfile.sql var1 var2 var3Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc. Look at this example:


	sqlplus scott/tiger @x.sql  '"test parameter"' dual
Where x.sql consists of: 
	select '&1' from &2;
	exit 5;

--------------------------------------------------------------------------------

Something's wrong with my CLOB/ NCLOB/ LONG column. Where is my data?

SQL*Plus by default only shows the first 80 bytes of any LONG, CLOB and NCLOB datatypes. The data is there, but since sqlplus is a command-line tool it tries not to print out too much data. You can override this to tell sqlplus exactly how many bytes you want to see: 
 
	  SET LONG 32000
	  SELECT text FROM user_views WHERE rownum = 1;

--------------------------------------------------------------------------------

How does one copy data from one database to another in SQL*Plus?

The SQL*Plus COPY command is one of the fastest ways of copying data between databases and schemas. This is also one of the few methods that will handle LONG columns correctly. Look at this example: 
	COPY FROM SCOTT/TIGER@LOCAL_DB TO SCOTT/TIGER@REMOTE_DB  -
	CREATE IMAGE_TABLE USING                                 -
	       SELECT IMAGE_NO, IMAGE                            -
	       FROM   ../images;
		

Download Sql*Plus Alternative (not need Oracle client)