|Oracle® Database Express Edition 2 Day Developer Guide
10g Release 2 (10.2)
Part Number B25108-01
This section provides an introduction to SQL Command Line (SQL*Plus), an interactive and batch command-line query tool that is installed with Oracle Database Express Edition.
This section contains the following topics:
For information about running SQL language statements, see Chapter 3, "Using SQL".
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 Command Line to generate reports interactively, to generate reports as batch processes, and to write the results to a text file, to a screen, or to an HTML file for browsing on the Internet.
This section describes SQL Command Line (SQL*Plus), a command-line utility to run SQL and PL/SQL.
This contains the following topics:
Note:Before starting SQL Command Line, make sure that the necessary environmental variables have been set up properly. See Oracle Database Express Edition 2 Day DBA for information about setting environmental variables for SQL Command Line.
When prompted, enter the username and password of the user account (schema) that you want to access in the local database. For example, enter
HR for the username and
my_hr_password for the password when prompted.
You can also include the username and password when you start SQL Command Line. For example:
If you want to connect to a database running on a remote system, you need to include a connect string when starting SQL Command Line. For example:
After you have started SQL Command Line, the
SQL> prompt displays as follows:
SQL> prompt, you can enter SQL statements.
When you want to exit SQL Command Line, enter
EXIT at the SQL prompt, as follows:
SQL> HELP INDEX
From the list of SQL Command Line Help topics, you can display Help about an individual topic by entering
HELP with a topic name. For example, the following displays Help about the SQL Command Line
COLUMN command, which enables you to format column output:
SQL> HELP COLUMN
To enter and execute SQL statements or commands, enter 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, enter the first line and press the Enter key. Continue entering lines, and terminate 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 is 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 Command Line commands, such as
DESCRIBE o r
SET, but required with SQL statements.
SQL Command Line provides the
DESCRIBE command 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
SQL> DESCRIBE employees
Name Null? Type
---------------------------------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
The SQL Command Line
SET commands can be used to specify various SQL Command Line settings, such as the format of the output from SQL
SELECT statements. For example, the following
SET commands specify the number of lines for each page and the number of characters for each line in the output:
SQL> SET PAGESIZE 200
SQL> SET LINESIZE 140
To enable output from PL/SQL blocks with
DBMS_OUTPUT.PUT_LINE, use the following:
SQL> SET SERVEROUTPUT ON
To view all the settings, enter the following at the SQL prompt:
SQL> SHOW ALL
For information about the SQL Command Line
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 Command Line environment with a login file
A SQL script file is executed with a
@ command. For example, in a Windows environment, you can execute a SQL script as follows:
A SQL script file can be executed in a Linux environment as follows:
SQL> START /home/cjones/my_scripts/my_sql_script.sql
You can use
ON to cause a script to echo each statement that is executed. You can use
OFF to prevent the script output from displaying on the screen.
When running a script, you need to include the full path name unless the script is located in the directory from which SQL Command Line was started, or the script is located in the default script location specified by the
SQLPATH environment variable.
To start spooling the output to an operating system file, you enter the
SPOOL command followed by a file name. For example:
If you want to append the output to an existing file:
To stop spooling and close a file, enter the following:
SQL> SPOOL OFF
You can create queries that use variables to make
SELECT statements more flexible. You can define the variable before running a SQL statement, or you specify that the statement prompts 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 section contains the following topics:
For information about using bind variables in PL/SQL code, see "Using Bind Variables With PL/SQL".
You can use
& to identify a variable that you want to define dynamically. In Example A-1, including the
&employee_id variable causes the SQL statement to prompt for a value when the statement is executed. You can then enter a value for the
employee_id that corresponds to the employee information that you want to display, such as employee ID 125. Note that you can use any name for the variable, such as
Example A-1 Prompting for a Variable Value in SQL Command Line
-- 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 = &employee_id;
When you run the previous
SELECT statement, the output is similar to:
Enter value for employee_id:
EMPLOYEE_ID LAST_NAME JOB_ID
----------- ------------------------- ----------
125 Nayer ST_CLERK
You can use
&& to identify a variable that you want to define dynamically multiple times, but only want to prompt the user once. In Example A-2, including the
&&column_name variable causes the SQL statement to prompt for a value when the statement is executed. The value that is entered is substituted for all remaining occurrences of
&&column_name in the SQL statement.
In Example A-3, the
&job_id variable is defined before running the SQL statement with the
DEFINE command, and the defined value is substituted for the variable when the statement is executed. Because the variable has already been defined, you are not prompted to enter a value.