Home > Oracle Database Usage Infor... > SQL*Plus: Usage Information
SQL*Plus is an interactive and batch command-line query tool that is installed with Oracle Database. This topic includes the following topics:
See Also:
|
SQL*Plus is a command-line tool for accessing Oracle Database. It enables you to enter and execute SQL, PL/SQL, and SQL*Plus commands to:
Query, insert, and update data
Execute PL/SQL procedures
Examine table and object definitions
Develop and run batch scripts
Perform database administration
You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet.
This topic describes SQL*Plus, Oracle's command line tool for executing SQL and PL/SQL.
To start SQL*Plus from the operating system command prompt, enter the following:
sqlplus
When prompted, enter the username and password of the user account (schema) that you want to access. For example, enter HR
for the username and your_hr_password
when prompted.
After you have started SQL*Plus, the SQL>
prompt displays for you to type in SQL statements, as follows:
SQL>
When you want to quit or exit SQL*Plus, type EXIT
or QUIT
at the SQL prompt, as follows:
SQL> EXIT
To display a list of help topics for SQL*Plus commands enter HELP
INDEX
at the SQL prompt as follows:
SQL> HELP INDEX
From the list of SQL*Plus help topics, you can display help on an individual topic by entering HELP
with a topic name. For example the following displays help on the SQL*Plus COLUMN
command, which enables you to format column output:
SQL> HELP COLUMN
To enter and execute SQL statements or commands, type in the statement or command at the SQL prompt. At the end of a SQL statement, put a semi-colon (;) and then press the Enter key to execute the statement. For example:
SQL> SELECT * FROM employees;
If the statement does not fit on one line, type in the first line and press the Enter key. Continue entering lines, terminating the last line with a semi-colon (;). For example:
SQL> SELECT employee_id, first_name, last_name
2 FROM employees
3 WHERE employee_id >= 105 AND employee_id <= 110;
The output from the previous SELECT
statement would be similar to:
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -----------------------
105 David Austin
106 Valli Pataballa
107 Diana Lorentz
108 Nancy Greenberg
109 Daniel Faviet
110 John Chen
6 rows selected.
Note that a terminating semi-colon (;) is optional with SQL *Plus commands, but required with SQL statements.
SQL*Plus provides the DESCRIBE
to display a description of a database object. For example, the following displays the structure of the employees
table. This description is useful when constructing SQL statements that manipulate the employees
table.
SQL> DESCRIBE employees
Name Null? Type
---------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
The SQL*Plus SET
commands can be used to specify various SQL*Plus settings, such as the format of the output from SQL*Plus statements. For example, the following SET
commands specify the number of lines per page display and the number of characters per line in the output:
SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140
To enable output from PL/SQL blocks with DBMS_OUTPUT
To view all the settings, enter the following at the SQL prompt:
SQL> SHOW ALL
For information about the SQL*Plus SERVEROUTPUT
setting to display output from a PL/SQL program, see Inputting and Outputting Data with PL/SQL.
See Also: SQL*Plus User's Guide and Reference for information about setting up the SQL*Plus environment with a login file |
You can use a text editor to create SQL*Plus script files containing SQL*Plus, SQL, and PL/SQL statements. For consistency, use the .sql
extension for the script file name.
A SQL script file is executed with a START
or @
command. For example, on a Windows environment you could execute a SQL script as follows:
SQL> @c:\my_scripts\my_sql_script.sql
A SQL script file could be executed on a Linux environment as follows:
SQL> START /home/cjones/my_scripts/my_sql_script.sql
You can use SET
ECHO
ON
to cause a script to echo each statement that is executed. You can use SET
TERMOUT
OFF
to prevent the script output from displaying on the screen.
When running a script, you should include the full path name unless the script is located in the directory where SQL*Plus was launched.
The SPOOL
command can be used to direct the output from SQL*Plus to a disk file, which enables you to save the output for future review.
To start spooling the output to an operating system file, you can enter:
SQL> SPOOL my_log_file.log
If you want to append the output to an existing file:
SQL> SPOOL my_log_file.log APPEND
To stop spooling and close a file, enter the following:
SQL> SPOOL OFF
You can write queries that use variables to make your SELECT
statements more flexible. You can define the variable prior to running a SQL statement or you can prompt for a variable value at the time that the SQL statement is run.
When using a variable in a SQL statement, the variable name must be begin with an ampersand (&).
This topic includes the following topics:
For information on using bind variables in PL/SQL code, see Using Bind Variables.
In Example: Defining a Variable, including the variable &emp_id
causes the SQL statement to prompt for a value when the statement is executed. You can then enter the employee_id
that you want to display, such as employee Id 125.
Defining a Variable
-- prompt for employee_id in a query, you need to enter a valid Id such as 125 SELECT employee_id, last_name, job_id FROM employees WHERE employee_id = &emp_id;
When you run the previous SELECT
statement, the output is similar to:
Enter value for emp_id:
125
EMPLOYEE_ID LAST_NAME JOB_ID
----------- ------------------------- ----------
125 Nayer ST_CLERK
In Example: Prompting for a Variable, the variable &jobid
is defined prior to running the SQL statement and the defined value is substituted for the variable when the statement is executed.