Oracle SQL Plus Pocket Reference

BUY THIS BOOK

Safari Books Online

What is this?

Looking to Reprint this content?

Tell a friend

Oracle SQL Plus Pocket Reference, Second Edition A Guide to SQL*Plus Syntax

By Jonathan Gennick

Cover | Table of Contents

Chapter 1: Oracle SQL*PlusPocket Reference

Content preview·Buy reprint rights for this chapter

The Oracle SQL*Plus Pocket Reference is a quick-reference guide to SQL*Plus and to commonly used SQL query and data manipulation statements. The purpose of this book is to help you find the syntax of specific language elements. It is not a self-contained user guide; basic knowledge of SQL*Plus is assumed. For more information, see my book Oracle SQL*Plus: The Definitive Guide (O'Reilly) and Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly).

Deborah Russell, Darl Kuhn, Ken Jacobs, and Alison Holloway all played a part in making this book a reality. For their assistance and support, I'm most grateful.

UPPERCASE: Indicates SQL*Plus, SQL, or PL/SQL keywords
lowercase: Indicates user-defined items such as table names
Italic: Indicates filenames, emphasis, introduction of new terms, and parameter names
Constant width: Used for code examples
Constant width bold: Indicates user input in examples showing an interaction
[ ]: Used in syntax descriptions to denote optional elements
{ }: Used in syntax descriptions to denote a required choice
|: Used in syntax descriptions to separate choices
_: Used in syntax descriptions to indicate that the underlined option is the default

This section covers essential information you need to know to interact with SQL*Plus. Here you will learn how to start SQL*Plus, enter commands, delimit strings, and name variables.

Invoke SQL*Plus by issuing the sqlplus command from your operating-system command prompt. On Microsoft Windows systems, use either sqlplus or sqlplusw depending on whether you want SQL*Plus to run in a command-prompt window or in its own window; you can also select an icon from the Start menu. (Early releases of SQL*Plus on Windows used executable names such as PLUS33 and PLUS80W.)

Beware of passing your password as a command-line argument to SQL*Plus. Such passwords may be easily visible to other users on Linux and Unix systems.

Section 1.2.1.1: Syntax for the sqlplus command

The syntax used to invoke SQL*Plus is as follows:

sqlplus [[-S[ILENT]] [-H[ELP]] [-V[ERSION]]   
   [-R[ESTRICT] level] [-L[OGON]]   
   [-M[ARKUP] "markup_options"]   
   [ [username[/password][@connect]|/        
       [AS {SYSDBA|SYSOPER}]]     
     |/NOLOG]   
   [@scriptfile [arg1 arg2 arg3...]]]

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

Deborah Russell, Darl Kuhn, Ken Jacobs, and Alison Holloway all played a part in making this book a reality. For their assistance and support, I'm most grateful.

UPPERCASE: Indicates SQL*Plus, SQL, or PL/SQL keywords
lowercase: Indicates user-defined items such as table names
Italic: Indicates filenames, emphasis, introduction of new terms, and parameter names
Constant width: Used for code examples
Constant width bold: Indicates user input in examples showing an interaction
[ ]: Used in syntax descriptions to denote optional elements
{ }: Used in syntax descriptions to denote a required choice
|: Used in syntax descriptions to separate choices
_: Used in syntax descriptions to indicate that the underlined option is the default

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Interacting with SQL*Plus

Content preview·Buy reprint rights for this chapter

This section covers essential information you need to know to interact with SQL*Plus. Here you will learn how to start SQL*Plus, enter commands, delimit strings, and name variables.

Beware of passing your password as a command-line argument to SQL*Plus. Such passwords may be easily visible to other users on Linux and Unix systems.

Section 1.2.1.1: Syntax for the sqlplus command

The syntax used to invoke SQL*Plus is as follows:

sqlplus [[-S[ILENT]] [-H[ELP]] [-V[ERSION]]   
   [-R[ESTRICT] level] [-L[OGON]]   
   [-M[ARKUP] "markup_options"]   
   [ [username[/password][@connect]|/        
       [AS {SYSDBA|SYSOPER}]]     
     |/NOLOG]   
   [@scriptfile [arg1 arg2 arg3...]]]

The -RESTRICT and -MARKUP parameters are new in Oracle8i. -HELP and -VERSION are new in Oracle9i. Here are the parameter descriptions:

-S[ILENT]

Tells SQL*Plus to run in silent mode. No startup message is displayed; no command prompt is displayed; no commands are echoed to the screen.

-H[ELP]

Causes SQL*Plus to display a short summary of this syntax. Prior to Oracle9i, use sqlplus - to get the help summary.

-V[ERSION]

Causes SQL*Plus to display version and copyright information. Prior to Oracle9i, use sqlplus -? to get version and copyright information.

-R[ESTRICT] level

Restricts what the user can do from SQL*Plus. The level must be one of the following:

1: Disables the EDIT, HOST, and ! commands
2: Disables the EDIT, HOST, !, SAVE, SPOOL, and STORE commands
3: Disables the EDIT, GET, HOST, !, SAVE, START, @, @@, SPOOL, and STORE commands

Level 3 also disables the reading of the login.sql file. The glogin.sql file is read, but restricted commands aren't executed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Selecting Data

Content preview·Buy reprint rights for this chapter

The SELECT statement is the key to getting data out of an Oracle database. It's also very likely the most commonly executed SQL statement from SQL*Plus.

The basic form of the SELECT statement looks like this:

SELECT column_list
FROM table_list
WHERE conditions
GROUP BY column_list
HAVING conditions
ORDER BY column_list;

The lists in this syntax are comma-delimited. The column list, for example, is a comma-delimited list of column names or expressions identifying the data you want the query to return.

Section 1.3.1.1: Selecting columns from a table

To retrieve columns from a table, list the columns you want after the SELECT keyword, place the table name after the FROM keyword, and execute your statement. The following query returns a list of tables you own with the names of their assigned tablespaces:

SELECT table_name, tablespace_name
   FROM user_tables;

Section 1.3.1.2: Ordering query results

You can use the ORDER BY clause to sort the results of a query. The following example sorts the results by table name:

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY table_name;

The default is to sort in ascending order. You can specify descending order using the DESC keyword. For example:

ORDER BY table_name DESC;

While it's redundant, ASC may be used to specify ascending order. The following example sorts the table list first by tablespace name in descending order and then within that by table name in ascending order:

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY tablespace_name DESC, 
         table_name ASC;

If you want the sort to be case-insensitive, you can use Oracle's built-in UPPER function. For example:

SELECT table_name, tablespace_name
FROM user_tables
ORDER BY UPPER(table_name);

For symmetry, Oracle also has a built-in LOWER function. LOWER converts a string to lowercase; UPPER converts to uppercase.

Section 1.3.1.3: Restricting query results

Use the WHERE clause to restrict the rows returned by a query to those that you need to see. The following example returns a list of any invalid objects that you own:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Inserting Data

Content preview·Buy reprint rights for this chapter

Use the INSERT statement to add new rows to a table. New in Oracle9i is the ability to perform direct path inserts and multitable inserts. For the examples in this section, I've added a column to the COURSE table shown previously in the section on Oracle9i table joins:

ALTER TABLE COURSE ADD (
   course_hours NUMBER DEFAULT 4);

To insert one row into a table, specify the list of columns for which you wish to insert a value and use the VALUES clause to specify values for the columns in your list:

INSERT INTO COURSE (course_name, period, course_hours)
VALUES ('French I', 5, DEFAULT);

The DEFAULT keyword is new in Oracle9i and is used in this query to explicitly request the default value for the COURSE_HOURS column. You can use the NULL keyword, available in all releases of Oracle, to explicitly insert a null value into a column.

You can omit the list of columns if you provide a value for each column in your table and if you provide those values in the same order in which the columns are listed when you DESCRIBE the table:

INSERT INTO COURSE 
VALUES ('French I', 5, DEFAULT);

I don't recommend this shortcut unless you are just typing in a one-off query interactively. It's safer to specify the column names.

Use the INSERT...SELECT...FROM syntax to insert the results of a query into a table. For example, the following INSERT statement creates a new row in the COURSE table for any currently undefined courses for which students are registered:

INSERT INTO COURSE (course_name, period)
SELECT DISTINCT course_name, period
FROM enrollment e 
WHERE NOT EXISTS (
   SELECT *
   FROM course c
   WHERE c.course_name = e.course_name
     AND c.period = e.period
   );

When using INSERT...SELECT...FROM to populate a table with a large amount of data, you may be able to improve performance by doing a direct path insert. Use the APPEND hint for this (see Section 1.10.4 later in this book). A direct path insert functions much like a direct path load:the database buffer cache is bypassed, and data is written directly to new extents in the datafiles.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Updating Data

Content preview·Buy reprint rights for this chapter

Use the UPDATE statement to modify column values in existing table rows.

A simple UPDATE statement takes on the following form:

UPDATE table_name
SET column_name = new_value,
    column_name = new_value,
    column_name = new_value,
    ...
WHERE selection_criteria;

For example, the following statement corrects a small problem with a course name; it changes the name to use an "I" (letter) instead of a "1" (digit):

UPDATE course
SET course_name = 'Spanish I'
WHERE course_name = 'Spanish 1';

Be careful with updates. If you omit the WHERE clause, your update will be applied to all rows in the table.

Rather than specify a new value in the SET clause for a column, you can specify a subquery that returns exactly one value (one column, one row). That value then becomes the new column value. For example:

UPDATE enrollment
SET period = (
       SELECT period 
       FROM course 
       WHERE course_name = 'English II'),
   course_name = (
       SELECT course_name 
       FROM course 
       WHERE course_name = 'English II'),
WHERE course_name = 'English II';

Setting the PERIOD and COURSE_NAME columns to their current values by way of a subquery doesn't make much sense. I did it only to show that you can use more than one subquery in an UPDATE statement.

Subqueries in UPDATE statements are often more useful when they are correlated. A correlated subquery is one in which the row returned depends on the current row being updated. For example, the following UPDATE statement uses a correlated subquery to reset all periods in the enrollment table to values taken from the COURSE table:

UPDATE enrollment e
SET period = (
       SELECT MIN(period)
       FROM course c
       WHERE c.course_name = e.course_name);

Note the use of the table aliases c and e to qualify the column names in the WHERE clause. I used the MIN function in this case, because some courses (Spanish I, for example) are offered in more than one period.

When using correlated subqueries, you can specify multiple columns in your SET clause; just be sure to enclose them within parentheses:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Deleting Data

Content preview·Buy reprint rights for this chapter

Use the DELETE statement to delete rows from a table.

A simple DELETE statement takes the following form:

DELETE FROM table_name
WHERE selection_criteria

All rows meeting the selection criteria will be deleted. For example, to delete the sixth-period Spanish I course, specify:

DELETE FROM course
WHERE course_name = 'Spanish I'
  AND period = 6;

Be careful with DELETE statements. If you omit the WHERE clause, you will delete all rows from your table.

You can delete all rows from a table by issuing a DELETE without a WHERE clause:

DELETE FROM course;

Deleting all rows like this exacts a price. The deletion of each row must be logged to the database redo log, and a copy of each row to be deleted must be written to a rollback segment (or to an undo tablespace) in case the transaction is rolled back. A more efficient mechanism for deleting all rows from a table is the TRUNCATE statement:

TRUNCATE TABLE course;

The TRUNCATE statement only requires one, short entry in the database redo log and generates no rollback or undo data. As a result, it's faster to truncate a table than it is to delete all rows using a DELETE statement. Beware, however! You cannot roll back a TRUNCATE statement. Once you issue it, all the data in your table is gone for good.

By default, TRUNCATE deallocates all extents assigned to the table. If you wish, you can save the extents for later use:

TRUNCATE TABLE course REUSE STORAGE;

This resets the table's high-water mark so that it no longer contains any rows, but all existing extents remain allocated to the table. Retaining the extents can be helpful if you plan to reload the table.

It's occasionally necessary to delete "duplicate" rows from a table. This need probably arises more often on test systems than on production systems. One approach to deleting duplicate rows is to arbitrarily delete all but the one with the lowest ROWID value:

DELETE FROM course
WHERE ROWID NOT IN (
   SELECT MIN(ROWID)
   FROM course
   GROUP BY course_name, period);

The GROUP BY clause in the subquery defines "duplicate" as two rows having the same course name and period. The subquery returns a list of ROWID values in which each value represents the minimum ROWID for a given combination of course name and period. Those rows are retained. The use of NOT IN results in all other rows being deleted.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Merging Data (Oracle9)

Content preview·Buy reprint rights for this chapter

A common data-processing problem is the need to take some data, decide whether it represents a new row in a table or an update to an existing row, and then issue an INSERT or UPDATE statement as appropriate. In the past, this has always been at least a two-step process, requiring two round-trips to the database. New in Oracle9i is the MERGE statement, which makes the process of inserting or updating easier and more efficient than before.

The general form of the MERGE statement is as follows:

MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;

In this syntax, data_source can be a table, view, or query. The condition in the ON clause is what Oracle looks at to determine whether a row represents an insert or an update to the target table.

Here is an example of a MERGE statement:

MERGE INTO course c
USING (SELECT course_name, period, 
       course_hours
   FROM course_updates) cu
ON (c.course_name = cu.course_name
    AND c.period = cu.period)
WHEN MATCHED THEN
   UPDATE
   SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
   INSERT (c.course_name, c.period, 
           c.course_hours)
   VALUES (cu.course_name, cu.period, 
           cu.course_hours);

When processing this statement, Oracle reads each row from the query in the USING clause and looks at the condition in the ON clause. If the condition in the ON clause evaluates to TRUE, the row is considered to be an update to the COURSE_HOURS column. Otherwise, the row is considered a new row and is inserted into the COURSE table.

The following example shows the effects of the MERGE statement on the data in the COURSE table:

SQL> SELECT * FROM course;

COURSE_NAME         PERIOD COURSE_HOURS
--------------- ---------- ------------
Spanish I                1
U.S. History             3
English II               4
French I                 5            4

SQL> SELECT * FROM course_updates;

COURSE_NAME         PERIOD COURSE_HOURS
--------------- ---------- ------------
Spanish I                1            3
U.S. History             3            3
English II               4            3
French I                 5            3
Spelling                 6            2
Geography                2            3

SQL>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Transaction Management

Content preview·Buy reprint rights for this chapter

Oracle implements several statements to help you manage transactions. By default, a transaction begins whenever you issue your first SQL statement. Once a transaction begins, you end it by doing one of the following:

Issue a COMMIT.
Issue a ROLLBACK.
Issue a DDL statement.

DDL statements (the ALTER and CREATE statements, for example) are special in that they implicitly end any open transaction. Thus, when issuing a DDL statement, it's possible to both begin and end a transaction with the same statement.

Use SET TRANSACTION to explicitly begin a transaction, especially when you want to specify transaction attributes such as isolation level.

SET TRANSACTION [attribute [,attribute...] 
    NAME 'transaction_name';

attribute := 
    {READ {ONLY | WRITE}
    | ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED}
    | USE ROLLBACK SEGMENT segment_name }

The READ COMMITTED isolation level is Oracle's default. It allows you to see changes made by other transactions as soon as they have been committed. Isolation-level SERIALIZABLE is more strict. With SERIALIZABLE, you can't modify any data that has been modified by others (but not committed before your transaction started). SERIALIZABLE also gives a consistent view of the data. You won't see changes committed by other users after your transaction begins. The following statement gives you a serializable transaction:

SET TRANSACTION
   ISOLATION LEVEL SERIALIZABLE
   NAME 'Jonathan''s Transaction';

READ ONLY transactions allow you to issue queries but not to change any data. READ ONLY transactions also provide read consistency. You don't see changes committed by other users during a READ ONLY transaction.

The USE ROLLBACK SEGMENT clause allows you to assign a transaction to a specific rollback segment. This is especially useful for large transactions, because you can assign those large transactions to a correspondingly large rollback segment. For example:

SET TRANSACTION 
   USE ROLLBACK SEGMENT large_batch;

SAVEPOINT allows you to established a named point in a transaction to which you can roll back if necessary.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Formatting Text Reports

Content preview·Buy reprint rights for this chapter

SQL*Plus reports are columnar in nature. The program lets you define column headings and display formats for each column in a report. You may also define page headers and footers, page and line breaks, and summary calculations such as totals and subtotals.

Specify column headings using the HEADING clause of the COLUMN command:

COLUMN employee_name HEADING "Employee Name"

You can use either single or double quotes to enclose the heading text. The resulting heading looks like this:

Employee Name
-------------

To specify a multiline heading, use the vertical bar (|) character to specify the location of the line break. For example:

COLUMN employee_name HEADING "Employee|Name"

The resulting multiline heading looks like this:

Employee
Name
---------

Headings of text columns are aligned to the left. Headings of numeric columns are aligned to the right. Use the JUSTIFY clause to alter that behavior:

COLUMN employee_name HEADING "Employee|Name" -
   JUSTIFY RIGHT
COLUMN employee_name HEADING "Employee|Name" -
   JUSTIFY CENTER

Use SET HEADSEP to change the line-break character to something other than a vertical bar. Use SET UNDERLINE to change the underline character to something other than a hyphen.

You can specify display formats with the FORMAT clause of the COLUMN command. For numeric fields, format specifications can be quite detailed—controlling the length, the number of decimal places, and the punctuation used in the number. For text and date fields, you can control the column width and whether the column wraps. The later section Section 1.11 shows how to format different types of data.

Page width is controlled by the SET LINESIZE command. The default width is 80 characters. You can change it—to 60 characters, for example—with this command:

SET LINESIZE 60

You can use the LINESIZE setting to center and right-justify page headers and page footers.

Page length is controlled by the SET PAGESIZE command. The default is to print 24 lines per page, and this includes the page header and page footer lines. The following command changes the page length to 50 lines:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Tuning SQL

Content preview·Buy reprint rights for this chapter

SQL*Plus can help tune SQL statements. You can use SQL's EXPLAIN PLAN facility to get the execution plan for a statement into a table. You can then query that table using SQL*Plus to display that plan. If you don't like the plan Oracle is using, you can add optimizer hints to your SQL statement that specify how you want the statement to be executed.

Before you can use the EXPLAIN PLAN statement, you need to create a plan table to hold the results. Oracle provides a script named utlxplan.sql to create the plan table, and you'll find it in your $ORACLE_HOME/rdbms/admin directory. Execute the script as follows:

SQL> @c:\oracle\ora92\rdbms\admin\utlxplan
Table created.

The resulting table, PLAN_TABLE, looks like this:

 Name               Null?    Type
 ------------------ -------- ----------------
 STATEMENT_ID                VARCHAR2(30)
 TIMESTAMP                   DATE
 REMARKS                     VARCHAR2(80)
 OPERATION                   VARCHAR2(30)
 OPTIONS                     VARCHAR2(255)
 OBJECT_NODE                 VARCHAR2(128)
 OBJECT_OWNER                VARCHAR2(30)
 OBJECT_NAME                 VARCHAR2(30)
 OBJECT_INSTANCE             NUMBER(38)
 OBJECT_TYPE                 VARCHAR2(30)
 OPTIMIZER                   VARCHAR2(255)
 SEARCH_COLUMNS              NUMBER
 ID                          NUMBER(38)
 PARENT_ID                   NUMBER(38)
 POSITION                    NUMBER(38)
 COST                        NUMBER(38)
 CARDINALITY                 NUMBER(38)
 BYTES                       NUMBER(38)
 OTHER_TAG                   VARCHAR2(255)
 PARTITION_START             VARCHAR2(255)
 PARTITION_STOP              VARCHAR2(255)
 PARTITION_ID                NUMBER(38)
 OTHER                       LONG
 DISTRIBUTION                VARCHAR2(30)
 CPU_COST                    NUMBER(38)
 IO_COST                     NUMBER(38)
 TEMP_SPACE                  NUMBER(38)
 ACCESS_PREDICATES           VARCHAR2(4000)
 FILTER_PREDICATES           VARCHAR2(4000)

The columns in the plan table vary from one release of Oracle to the next. This version of the plan table is from Oracle9

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

SQL*Plus Format Elements

Content preview·Buy reprint rights for this chapter

The COLUMN, ACCEPT, SET NUMBER, TTITLE, BTITLE, REPHEADER, and REPFOOTER commands allow you to control data formats using what is called a format specification. A format specification is a string of characters that tells SQL*Plus exactly how to format a number, date, or text string when it is displayed.

Table 1-3 shows the format elements that may be used when formatting numeric output.

Table 1-3: Numeric format elements
Format element	Function
9	Represents a digit in the output.
0	Marks the spot at which you want to begin displaying leading zeros.
$	Includes a leading dollar sign in the output.
,	Places a comma in the output.
.	Marks the location of the decimal point.
B	Forces zero values to be displayed as blanks.
C	Marks the place where you want the ISO currency indicator to appear. For U.S. dollars, this is USD.
D	Marks the location of the decimal point.
DATE	Causes SQL*Plus to assume that the number represents a Julian date and to display it in MM/DD/YY format.
EEEE	Causes SQL*Plus to use scientific notation to display a value. You must use exactly four Es, and they must appear at the right end of the format string.
G	Places a group separator (usually a comma) in the output.
L	Marks the place where you want the local currency indicator to appear. For U.S. dollars, this is the dollar sign character.
MI	Adds a trailing negative sign to a number and may be used only at the end of a format string.
PR	Causes negative values to be displayed within angle brackets. For example, -123.99 is displayed as <123.99>.
RN	Allows you to display a number using Roman numerals. An uppercase RN yields uppercase Roman numerals, while a lowercase r n yields lowercase Roman numerals. Numbers displayed as Roman numerals must be integers and must be between 1 and 3,999, inclusive.
S	Adds a + or - sign to the number and may be used at either the beginning or end of a format string.
V	Displays scaled values. The number of digits to the right of the V indicates how many places to the right the decimal point is shifted before the number is displayed.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: SQL*Plus Command Reference

Content preview·Buy reprint rights for this chapter

This section contains an alphabetic listing of all the SQL*Plus commands, with brief descriptions.

Comment Delimiters (/*...*/)

/* comment_text comment_text comment_text */

The /* and */ delimiters set off a comment in SQL*Plus. Comments entered this way may span multiple lines. If you use /*...*/ in a script file, the comments are displayed on the screen when the script is executed. For example:

/* SQL*Plus script written 7-Jan-2000 
   by Jonathan Gennick. */

Double Hyphen ( -- )

-- comment_text

The double hyphen can place a single-line comment in a SQL*Plus script. For example:

--Written 7-Jan-2000 by Jonathan Gennick

At Sign (@)

@script_file [argument...]

The at sign (@) executes a SQL*Plus script file. For example:

@$ORACLE_HOME/rdbms/admin/utlxplan
@http://Gennick.com/message.sql 2

Parameters

script_file: The name, or URL in Oracle9i and higher, of the file to execute. You may include a path as part of the name. If you do not specify a path, SQL*Plus looks in the current directory and then follows the SQL*Plus search path. The default extension is .sql.
argument: An argument you wish to pass to the script. You may pass as many arguments as the script requires. Arguments must be separated from each other by at least one space.

In Oracle9i Release 1, only the Windows version of SQL*Plus allows you to identify a script file by its URL. In Release 2, all versions of SQL*Plus support this functionality. URLs may be HTTP or FTP.

In iSQL*Plus, you must specify a URL with the @ command; you cannot specify a filename.

Double At Sign (@@)

@@script_file [argument...]

The double at sign is used within a script file to execute another script file from the same directory as the first. For example:

@@generate_emp_report
@@generate_pay_history_report '101'

The parameters for @@ are the same as for @.

Forward Slash (/)

A forward slash executes the SQL statement or PL/SQL block that is currently in the buffer. For example:

SQL> SELECT * FROM dual
  2  
SQL>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

This section contains an alphabetic listing of all the SQL*Plus commands, with brief descriptions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Comment Delimiters (/*...*/)

Content preview·Buy reprint rights for this chapter

Comment Delimiters (/*...*/)

/* comment_text comment_text comment_text */

/* SQL*Plus script written 7-Jan-2000 
   by Jonathan Gennick. */

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Double Hyphen ( -- )

Content preview·Buy reprint rights for this chapter

Double Hyphen ( -- )

-- comment_text

The double hyphen can place a single-line comment in a SQL*Plus script. For example:

--Written 7-Jan-2000 by Jonathan Gennick

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

At Sign (@)

Content preview·Buy reprint rights for this chapter

At Sign (@)

@script_file [argument...]

The at sign (@) executes a SQL*Plus script file. For example:

@$ORACLE_HOME/rdbms/admin/utlxplan
@http://Gennick.com/message.sql 2

Parameters

script_file: The name, or URL in Oracle9i and higher, of the file to execute. You may include a path as part of the name. If you do not specify a path, SQL*Plus looks in the current directory and then follows the SQL*Plus search path. The default extension is .sql.
argument: An argument you wish to pass to the script. You may pass as many arguments as the script requires. Arguments must be separated from each other by at least one space.

In iSQL*Plus, you must specify a URL with the @ command; you cannot specify a filename.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Double At Sign (@@)

Content preview·Buy reprint rights for this chapter

Double At Sign (@@)

@@script_file [argument...]

The double at sign is used within a script file to execute another script file from the same directory as the first. For example:

@@generate_emp_report
@@generate_pay_history_report '101'

The parameters for @@ are the same as for @.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Forward Slash (/)

Content preview·Buy reprint rights for this chapter

Forward Slash (/)

A forward slash executes the SQL statement or PL/SQL block that is currently in the buffer. For example:

SQL> SELECT * FROM dual
  2  
SQL> /

D
-
X

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ACCEPT

Content preview·Buy reprint rights for this chapter

ACC[EPT] user_variable [NUM[BER] | CHAR | DATE] [FOR[MAT] format_specification] [DEF[AULT] default_value] [PROMPT prompt_text | NOPR[OMPT]] [HIDE]

The ACCEPT command (not supported in iSQL*Plus) gets input from a user. For example:

ACCEPT user_password CHAR -
   PROMPT "Password: " HIDE
ACCEPT id NUMBER FORMAT "999.99"
ACCEPT report_date DATE -
   PROMPT "Date: " FORMAT "dd-mon-yyyy"

Parameters

user_variable: The name of the variable that you want to define.
NUM[BER] | CHAR | DATE: The type of data you are after.
FOR[MAT] format_specification: A format specification, which may be optionally enclosed in quotes.
DEF[AULT] default_value: Specifies a default value to assign to the variable.
PROMPT prompt_text: The prompt text displayed to the user.
NOPR[OMPT]: Indicates that you do not want the user to see a visible prompt.
HIDE: Causes SQL*Plus not to echo the user's response back to the display. This is useful if you are prompting for a password.

The syntax for the ACCEPT command has evolved significantly with the past few releases of SQL*Plus. The syntax shown here is valid for Oracle8i and higher. Not all clauses are available when using prior versions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

APPEND

Content preview·Buy reprint rights for this chapter

APPEND

A[PPEND] text

APPEND (not supported in iSQL*Plus) is an editing command that lets you add text onto the end of the current line in the SQL buffer. For example:

SQL> L
  1* SELECT *
SQL> a  FROM dual
  1* SELECT * FROM dual

The text is the text you want appended to the current line.

Use two spaces after the APPEND command if you want your appended string to begin with one space.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ARCHIVE LOG

Content preview·Buy reprint rights for this chapter

ARCHIVE LOG

ARCHIVE LOG {LIST | STOP | START [TO destination] | NEXT [TO destination] | ALL [TO destination] | log_sequence_number [TO destination]}

The ARCHIVE LOG command controls—or displays information about—archive logging. You must be connected as SYSDBA, SYSOPER, or INTERNAL to use this command. For example:

ARCHIVE LOG LIST
ARCHIVE LOG START
ARCHIVE LOG ALL TO /m01/oradata

Parameters

LIST: Displays information about the current state of archiving.
STOP: Stops log files from being automatically archived.
START: Turns on automatic archiving of redo log files.
NEXT: Manually archives the next log file group in the sequence, provided that it is filled. Use ARCHIVE LOG LIST to see the sequence number of this file.
ALL: Manually archives all log file groups that have been filled but not previously archived.
log _sequence_number: Manually archives a specific log file group, provided the group is still online.
destination: Specifies a destination for archived log files. If used with ARCHIVE LOG START, this becomes the destination for all log files as they are archived. If used with NEXT, ALL, or a specific sequence number, this becomes the destination for files archived by that one command. If you do not specify a destination when using ARCHIVE LOG START, the value from the LOG_ARCHIVE_DEST initialization parameter is used.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

ATTRIBUTE

Content preview·Buy reprint rights for this chapter

ATTRIBUTE

The ATTRIBUTE command formats attributes of an Oracle object type. For example:

ATTRIBUTE employee_type.employee_salary -
   ALIAS emp_sal
ATTRIBUTE emp_sal FORMAT "$999,999.99"
ATTRIBUTE employee_type.employee_salary -
   FORMAT "$999,999.99"

Issuing the ATTRIBUTE command with no parameters displays a list of all current attribute settings.

Parameters

object_type: The name of an Oracle object type.
attribute: The name of an attribute of the specified object type and the attribute you are formatting. If you stop here and don't supply any other parameters, the current display settings for this attribute are shown.

The ALIAS, CLEAR, FORMAT, LIKE, ON, and OFF clauses function just as they do in the COLUMN command.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

BREAK

Content preview·Buy reprint rights for this chapter

BREAK

BRE[AK] [ON {column_name | ROW | REPORT} [SKI[P] {lines_to_skip | PAGE} | NODUP[LICATES] | DUP[LICATES]...]...]

The BREAK command defines page breaks and line breaks based on changing column values in a report. It controls whether duplicate values print in a column, and it controls the printing of computed values such as totals and subtotals. Issuing the BREAK command with no parameters causes SQL*Plus to display the current break setting. Following are some valid BREAK commands:

BREAK ON ROW SKIP 1
BREAK ON dept 
BREAK ON dept SKIP PAGE

Parameters

column_name: Specifies a report column to watch. When the value in the column changes, SQL*Plus executes the specified break actions.
ROW: Causes SQL*Plus to break on each row.
REPORT: Specifies a report-level break and prints grand totals at the end of the report. SKIP PAGE is ignored if it is specified as a report break action, but, strangely enough, you can skip lines on a report break.
SKI[P] lines_to_skip: Tells SQL*Plus to skip the specified number of lines when a break occurs.
SKI[P] PAGE: Tells SQL*Plus to advance to a new page when a break occurs.
NODUP[LICATES]: Tells SQL*Plus to print a column's value only when it changes. By default, whenever you put a break on a column, you get this behavior.
DUP[LICATES]: Forces SQL*Plus to print a column's value in every line on the report, regardless of whether the value is the same as that printed for the previous record.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

BTITLE

Content preview·Buy reprint rights for this chapter

BTITLE

See TTITLE for descriptions of the command parameters. BTITLE functions the same as TTITLE, except that it defines a page footer instead of a page header.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CHANGE

Content preview·Buy reprint rights for this chapter

CHANGE

C[HANGE] /old_text[/[new_text[/]]

CHANGE (not supported in iSQL*Plus) is an editing command that allows you to do a search and replace on the current line in the SQL buffer. The CHANGE command can also delete text. For example:

SQL> l
  1* select dummy,smarty from duap
SQL> c /duap/dual/
  1* select dummy,smarty from dual
SQL> c /,smarty/
  1* select dummy from dual

Parameters

old_text: The text you want to change or delete.
new_text: The replacement text.
/: Commonly used to delimit the old and new text strings, but any other character may be used as long as it is not a number or letter—and as long as it is used consistently throughout the command.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CLEAR

Content preview·Buy reprint rights for this chapter

CLEAR

CL[EAR] {BRE[AKS] | BUFF[ER] | COL[UMNS] | COMP[UTES] | SCR[EEN] | SQL | TIMI[NG]}

The CLEAR command allows you to easily delete all column definitions, break settings, compute definitions, and so forth. For example:

CLEAR BREAKS
CLEAR COMPUTES

Parameters

BRE[AKS]: Deletes any break setting you may have defined using the BREAK command.
BUFF[ER]: Erases the contents of the buffer.
COL[UMNS]: Deletes any column definitions you may have made using the COLUMN command.
COMP[UTES]: Deletes any computations you may have defined using the COMPUTE command.
SCR[EEN] (not supported in iSQL*Plus): Clears the screen.
SQL: Erases the contents of the SQL buffer.
TIMI[NG]: Deletes any timers you may have created using the TIMING command.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

COLUMN

Content preview·Buy reprint rights for this chapter

COLUMN

COL[UMN] [column_name [ALI[AS] alias | CLE[AR] | ENTMAP {ON|OFF} | FOLD_A[FTER] | FOLD_B[EFORE] | FOR[MAT] format_spec | HEA[DING] heading_text | JUS[TIFY] {LEFT | CENTER | CENTRE | RIGHT} | LIKE source_column_name | NEWL[INE] | NEW_V[ALUE] user_variable | NOPRI[NT] | PRI[NT] | NUL[L] null_text | OLD_V[ALUE] user_variable | ON | OFF | TRU[NCATED] | WOR[D_WRAPPED] | WRA[PPED]...]]

The COLUMN command formats report output for columnar reports. Issuing the COLUMN command with no parameters gets you a list of all current column formats. For example:

COLUMN employee_name HEADING "Name" -
   FORMAT A20 WORD_WRAPPED
COLUMN employee_hire_date -
   HEADING "Hire Date" -
   FORMAT A12 JUSTIFY RIGHT

COLUMN commands are cumulative. Two COLUMN commands specifying two different settings for the same field are equivalent to one command specifying both parameters.

Parameters

column_name: The name of the column you are formatting. If it is a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here. Issuing the command COLUMN column_name with no further parameters displays the current format for that column.
ALI[AS]: Allows you to specify an alternate name for the column that is meaningful to SQL*Plus.
alias: Is an alternate name for the column that may be used in BREAK commands, COMPUTE commands, and other COLUMN commands.
CLE[AR]: Erases any format settings for the column in question.
ENTMAP {ON | OFF}: Controls whether characters such as "<" and ">" should be represented as "<" and ">" in HTML reports. The default is to use the ENTMAP setting specified with the SET MARKUP command or the -M command-line option.
FOLD_A[FTER]: Causes SQL*Plus to advance to a new line after printing the column.
FOLD_B[EFORE]: Causes SQL*Plus to wrap to a new line before the column is printed.
FOR[MAT]: Allows you to control how column data is displayed.
format_spec: A string that specifies the display format for the column.
HEA[DING]: Allows you to define a heading for the column.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

COMPUTE

Content preview·Buy reprint rights for this chapter

COMPUTE

COMP[UTE] [{AVG | COU[NT] | MAX[IMUM] | MIN[IMUM] | NUM[BER] | STD | SUM | VAR[IANCE]}... [LABEL label_text] OF column_name... ON {group_column_name | ROW | REPORT}...]

The COMPUTE command defines summary calculations needed in a report. You can use COMPUTE in conjunction with BREAK to calculate and print column totals, averages, minimum and maximum values, and so forth. These calculations are performed by SQL*Plus as the report runs. COMPUTE is a complex command and, to get results, must be used in conjunction with the BREAK command. For example:

BREAK ON project_id
COMPUTE SUM LABEL "Totals" OF hours_logged -
   ON project_id

BREAK ON project_id ON employee_id
COMPUTE SUM OF hours_logged -
   ON project_id, employee_id

Issuing COMPUTE with no parameters causes SQL*Plus to list all currently defined computations.

Parameters

AVG: Computes the average of all non-null values for a numeric column.
COU[NT]: Computes the total number of non-null values for a column.
MAX[IMUM]: Computes the maximum value returned for a column. Applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
MIN[IMUM]: Computes the minimum value returned for a column. Applies to columns of type NUMBER, CHAR, VARCHAR2, NCHAR, and NVARCHAR2.
NUM[BER]: Performs a function similar to COUNT but computes the number of all values, including nulls.
STD: Computes the standard deviation of all non-null values for a numeric column.
SUM: Computes the sum of all non-null values for a numeric column.
VAR[IANCE]: Computes the variance of all non-null values for a numeric column.
LABEL: Allows you to specify a label for the computed value. If possible, this label is printed to the left of the computed value.
label_text: The text you want to use as a label when the computed value is printed.
column_name: The name of the column you are summarizing. If it's a computed column, the expression is the name. If your SELECT statement aliases the column, you must use that alias name here.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

CONNECT

Content preview·