SQL*Plus is an interactive command-line interface to execute SQL Statement, Creating PL/SQL Program script, creating SQL Result output to a file, and creating report. SQL*Plus installed automaticly when we install Oracle Database in our computer. Since 10g version of Oracle Database, oracle provide iSQL*Plus, which similar as SQL*Plus but the main different is iSQL*Plus is a web-based program and only accessible via web browser. SQL*Plus Commands can be divided into three categories :

1. Execute Commands


2. Editing Commands


3. Formatting and Reporting Commands

Running SQL Plus from the Run menu in Windows OS

You can run SQL Plus console using Run dialog box instead of running it from start menu in the Microsoft Windows operating system.

• Start | Run | in the run dialog type SQLPLUSW and then press enter

Logon to the Database

Before you can connect to a database you will be prompted a login dialog box. To logon to a database you can do with several way

Method 1 : type Username, Password, and Connect Identifier in each textbox.

Method 2 : type with the following format : Username/Password@connect_identifier in the username textbox.

example : scott/tiger@ocp

SQL*Plus Commands

Every SQL Statement which executed in SQL*Plus console will be stored in the SQL buffer by Oracle. SQL Buffer stored the last executed Query. Bellow is a list of SQL*Plus Commands which often used in the SQL*Plus Console :

• / : Execute the last SQL Command in the SQL Buffer


• [R]UN : Display and Execute the last SQL Command in the SQL Buffer


• @ dan @@ : execute SQL command stored in a file.


• HELP INDEX : Displaying the lists of SQL*Plus Help Index


• SPOOL : Stores query results in an operating system file, or sends the file to a printer.
  SPO[OL] [file_name[.ext] | OFF | OUT]



• EXECUTE : Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure. EXEC[UTE] statement


• SAVE : Saves the contents of the SQL buffer in a host operating system command file. SAV[E] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]


• CONNECT : Connects a given username to Oracle. CONN[ECT] [logon] [AS [SYSOPER|SYSDBA]]. where logon has the following syntax:
  username[/password] [@connect_identifier] | /


• DISCONNECT : Commits pending changes to the database and logs the current username out of Oracle, but does not exit SQL*Plus. Use EXIT or QUIT to log out of Oracle and return control to your host computer's operating system. DISC[ONNECT]
  

For the complete help index and explanation about specific command, in the SQL*Plus Console type a command you want to get the explanation after HELP keyword. example : HELP SPOOL

Re-Login as a Different User

For example you already login as Scott, and want to logout and login again as user HR using the same SQL Plus console window. first you must disconnected user Scott from the Database by typing disconnect; in the SQL Plus console window and then type login parameter with the following format : Connect Username/Password@connect_identifier;
example : hr/hr@doe;
please note that you must add a ";" character at the end of a command to execute them.

Setting Linesize and Pagesize

Pagesize : Display how many rows per page. Syntax : SET pagesize value.
bellow is an example using Pagesize to display all the rows from the Jobs table in the HR schema in one page

Pagesize using SQL*Plus default value :

Pagesize after SET by 50 rows per page :

Linesize : Display how many character per line. Syntax : SET linesize value.
bellow is an example using Linesize to adjust the line width.

Linesize using SQL*Plus default value :

Linesize after SET by 200 characters per line :