By Jonathan Gennick

Cover | Table of Contents

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. Most of this book consists of a SQL*Plus syntax reference. While I don't intend this book to be used as a tutorial for learning SQL*Plus, you will also find sections that quickly review the basics of selecting data from a database and formatting that data into a report. I've also included a chapter on SQL statement tuning showing you how to use Oracle's EXPLAIN PLAN statement and describing all the optimizer hints.

The purpose of this pocket reference is to help SQL*Plus users 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 Oracle SQL*Plus: The Definitive Guide, by Jonathan Gennick (O'Reilly & Associates, 1999).

Thanks to Debby Russell for her tireless editing. Thanks to Ken Jacobs and Alison Holloway of Oracle Corporation for pointing me to information on the new HTML features in release 8.1.6. Thanks also to Mike Sierra who did the text conversion, and Jeff Holcomb for the copyedit and for managing the production process.

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.

[ ]

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 that you need to know in order to interact with SQL*Plus. Here you will learn how to start SQL*Plus, enter commands, delimit strings, and name variables.

SQL*Plus is almost always invoked by issuing the sqlplus command from your operating system command prompt. On Microsoft Windows systems, you also have the option of selecting an icon from the Start menu. Early releases of SQL*Plus on Windows used executable names such as PLUS33 and PLUS80W depending on the specific release number and on whether the DOS version or the Windows version was to be invoked.

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

The Oracle SQL*Plus Pocket Reference is a quick-reference guide to SQL*Plus. Most of this book consists of a SQL*Plus syntax reference. While I don't intend this book to be used as a tutorial for learning SQL*Plus, you will also find sections that quickly review the basics of selecting data from a database and formatting that data into a report. I've also included a chapter on SQL statement tuning showing you how to use Oracle's EXPLAIN PLAN statement and describing all the optimizer hints.

The purpose of this pocket reference is to help SQL*Plus users 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 Oracle SQL*Plus: The Definitive Guide, by Jonathan Gennick (O'Reilly & Associates, 1999).

Thanks to Debby Russell for her tireless editing. Thanks to Ken Jacobs and Alison Holloway of Oracle Corporation for pointing me to information on the new HTML features in release 8.1.6. Thanks also to Mike Sierra who did the text conversion, and Jeff Holcomb for the copyedit and for managing the production process.

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.

[ ]

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 that you need to know in order to interact with SQL*Plus. Here you will learn how to start SQL*Plus, enter commands, delimit strings, and name variables.

SQL*Plus is almost always invoked by issuing the sqlplus command from your operating system command prompt. On Microsoft Windows systems, you also have the option of selecting an icon from the Start menu. Early releases of SQL*Plus on Windows used executable names such as PLUS33 and PLUS80W depending on the specific release number and on whether the DOS version or the Windows version was to be invoked.

Section 1.2.1.1: Syntax for the sqlplus command

The syntax used to invoke SQL*Plus looks like this:

sqlplus [[-S[ILENT]] [-R[ESTRICT] level]
   [-M[ARKUP] markup_options]
   [username[/password][@connect]|/|/NOLOG]
   [@scriptfile [arg1 arg2 arg3...]]] | - | -?

The -RESTRICT and -MARKUP parameters are new in release 8.1.6. The descriptions of the parameters follow:

-S[ILENT]

Tells SQL*Plus to run in silent mode. No startup messages will be displayed; no command prompt will be displayed; no commands will be echoed to the screen.

-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 will be read, but restricted commands won't be executed.

-M[ARKUP] markup_options

Allows you to specify the markup language to use when generating output. Except for HTML, all markup options are optional. The following are valid markup options:

HTML [ON | OFF]

Specifies the markup language to use and enables or disables the use of that markup language. In release 8.1.6, this is a mandatory option.

HEAD text

Specifies content for the <head> tag. The tag ends up being written as <head>text < /head>.

BODY text

Specifies content for the <body> tag. The tag ends up being written as <body

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 that 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 following the SELECT keyword, place the table name after the FROM keyword, and execute your statement. The following query returns a list of tables that you own together 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!

Formatting Reports

Content preview·Buy reprint rights for this chapter

SQL*Plus reports are columnar in nature. SQL*Plus provides you with the ability to 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"

Either single or double quotes may be used to enclose the heading text. The resulting heading will look 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 will look 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.

Specify display formats using 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 Section 1.6 section, later in this book, shows you 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—by using the command like this:

SET LINESIZE 60

The LINESIZE setting is used by SQL*Plus 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 be employed as a tool to help tune SQL statements. You can use SQL's EXPLAIN PLAN facility to get the execution plan for a statement into a table. Then you can query that table by using SQL*Plus to display that plan. If you don't like the plan that 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 the $ORACLE_HOME/rdbms/admin directory. Execute it as follows:

SQL> @e:\oracle\ora81\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(30)
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)

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

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.4 shows the format elements that may be used when formatting numeric output.

Table 1.4: 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.
MI	Adds a trailing negative sign to a number and may be used only at the end of a format string.
S	Adds a + or - sign to the number and may be used at either the beginning or end of a format string.
PR	Causes negative values to be displayed within angle brackets. For example, -123.99 will be displayed as <123.99>.
D	Marks the location of the decimal point.
G	Places a group separator (usually a comma) in the output.
C	Marks the place where you want the ISO currency indicator to appear. For U.S. dollars, this will be USD.
L	Marks the place where you want the local currency indicator to appear. For U.S. dollars, this will be the dollar sign character.
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.
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.
RN	Allows you to display a number using Roman numerals. An uppercase RN yields uppercase Roman numerals, while a lowercase rn yields lowercase Roman numerals. Numbers displayed as Roman numerals must be integers and must be between 1 and 3,999, inclusive.
DATE	Causes SQL*Plus to assume that the number represents a Julian date and to display it in MM/DD/YY format.

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 Command Reference

Content preview·Buy reprint rights for this chapter

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

Comment Delimiters (/*...*/)

/* comment_text comment_text comment_text */

The /* and */ delimiters may be used to set off a comment in SQL*Plus. Comments entered this way may span multiple lines. If you use /*...*/ in a script file, the comments will be 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 may be used to 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 is used to execute a SQL*Plus script file. For example:

@$ORACLE_HOME/rdbms/admin/utlxplan

@add_user "TINA" "SECRETPASSWORD"

Parameters

script_ file

Is the name of the file to execute. You may include a path as part of the name. If you do not specify a path, SQL*Plus will look in the current directory and then follow the SQL*Plus search path. The default extension is .sql.

argument

Is 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.

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 is used to execute the SQL statement or PL/SQL block that is currently in the buffer. For example:

SQL> SELECT * FROM dual
  2  
SQL>

D
-
X

ACCEPT

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 is used to get 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"

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

Return to Oracle SQL*Plus Pocket Reference