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 Chen6 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 employeesName 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 DATEJOB_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 200SQL> SET LINESIZE 140To 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: 125EMPLOYEE_ID LAST_NAME JOB_ID----------- ------------------------- ---------- 125 Nayer ST_CLERKIn 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.