Starting SQL*Plus and Getting Help, 2 of 3

Starting SQL*Plus Using the SQLPLUS Command

You use the SQLPLUS command at the operating system prompt to start SQL*Plus:

SQLPLUS [ [Options] [Logon] [Start] ]where:

Options	has the following syntax: -H[ELP] \| -V[ERSION] \| [ [-M[ARKUP] markup_option] [-R[ESTRICT] {1\|2\|3}] [-S[ILENT]] ]
	and markup_option has the following syntax: HTML [ON\|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON\|OFF}] [SPOOL {ON\|OFF}] [PRE[FORMAT] {ON\|OFF}]
Logon	has the following syntax: {username[/password][@connect_identifier \| / } [AS {SYSOPER\|SYSDBA}] \| /NOLOG
Start	has the following syntax: @{uri\|file_name[.ext]} [arg ...]

You have the option of entering logon. If you do not specify logon and do specify start, SQL*Plus assumes that the first line of the command file contains a valid logon. If neither start nor logon are specified, SQL*Plus prompts for logon information.

The following sections contain descriptions of SQLPLUS command terms:

Options

HELP Option

-H[ELP]

Displays the usage and syntax for the SQLPLUS command, and then returns control to the operating system.

VERSION Option

-V[ERSION]

Displays the current version and level number for SQL*Plus, and then returns control to the operating system.

MARKUP Options

-M[ARKUP]

You can use the MARKUP option to generate a complete stand alone web page from your query or script. MARKUP currently supports HTML 4.0 transitional.

Use SQLPLUS -MARKUP HTML ON or SET MARKUP HTML ON SPOOL ON to produce standalone web pages. SQL*Plus will generate complete HTML pages automatically encapsulated with <HTML> and <BODY> tags. The HTML tags in a spool file are closed when SPOOL OFF is executed or SQL*Plus exits.

The -SILENT and -RESTRICT command line options may be useful when used in conjunction with -MARKUP.

You can use SET MARKUP HTML ON SPOOL OFF to generate HTML output suitable for embedding in an existing web page. Output generated this way has no <HTML> or <BODY> tags.

In this release, you can use MARKUP HTML ON to produce HTML output in either the <PRE> tag or in an HTML table. Output to a table uses standard HTML <TABLE>, <TR> and <TD> tags to automatically encode the rows and columns resulting from a query. Output to an HTML table is now the default behavior when the HTML option is set ON. You can generate output using HTML <PRE> tags by setting PREFORMAT ON.

Use the SHOW MARKUP command to view the status of MARKUP options.

The SQLPLUS -MARKUP command has the same options and functionality as the SET MARKUP command. These options are described in this section. For other information on the SET MARKUP command, see the SET command in Chapter 8.

Note:

Depending on your operating system, the complete markup_option clause for the SQLPLUS command may need to be contained in quotes.

HTML [ON|OFF]

HTML is a mandatory MARKUP argument which specifies that the type of output to be generated is HTML. The optional HTML arguments, ON and OFF, specify whether or not to generate HTML output. The default is OFF.

MARKUP HTML ON generates HTML output using the specified MARKUP options, or in the case of SET MARKUP, options set by previous SET MARKUP HTML commands in the current session.

You can turn HTML output ON and OFF as required during a session. The default is OFF.

You enable the writing of HTML output with the MARKUP option, SPOOL ON, and you subsequently initiate writing output to a spool file with the SQL*Plus command, SPOOL filename. See SPOOL {ON|OFF} below, and the SPOOL command in Chapter 8 for more information.

HEAD text

The HEAD text option allows you to specify content for the <HEAD> tag. By default, text is:

<TITLE>SQL*Plus Report</TITLE>

If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <HEAD> tag. This gives you the flexibility to customize output for your browser or special needs.

BODY text

The BODY text option allows you to specify attributes for the <BODY> tag. By default, there are no attributes. If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <BODY> tag. This gives you the flexibility to customize output for your browser or special needs.

TABLE text

The TABLE text option allows you to enter attributes for the <TABLE> tag. You can use TABLE text to set HTML <TABLE> tag attributes such as BORDER, CELLPADDING, CELLSPACING and WIDTH. By default, the <TABLE> WIDTH attribute is set to 90% and the BORDER attribute is set to 1.

If text includes spaces, it must be enclosed in quotes. SQL*Plus does not test this free text entry for HTML validity. You must ensure that the text you enter is valid for the HTML <TABLE> tag. This gives you the flexibility to customize output for your browser or special needs.

ENTMAP {ON|OFF}

ENTMAP ON or OFF specifies whether or not SQL*Plus replaces special characters <, >, " and & with the HTML entities <, > " and & respectively. ENTMAP is set ON by default.

You can turn ENTMAP ON and OFF as required during a session. For example, with ENTMAP OFF, SQL*Plus screen output is:

SQL>SELECT DEPARTMENT_ID, CITY
  1  FROM EMP_DETAILS_VIEW
  2  WHERE SALARY = 12000;

With ENTMAP ON, SQL*Plus screen output is:

SQL&gt; SELECT DEPARTMENT_ID, CITY
  2  FROM EMP_DETAILS_VIEW
  3  WHERE SALARY = 12000;

If entities are not mapped, web browsers may treat data as invalid HTML and all subsequent output may display incorrectly. ENTMAP OFF allows users to write their own HTML tags to customize output.

As entities in the <HEAD> and <BODY> tags are not mapped, you must ensure that valid entities are used in the MARKUP HEAD and BODY options.

Note:

ENTMAP only has affect when either the HTML option is set ON, or the SPOOL option is set ON. For more information about using entities in your output, see the COLUMN command in Chapter 8.

SPOOL {ON|OFF}

SPOOL ON or OFF specifies whether or not SQL*Plus writes the HTML opening tags, <HTML> and <BODY>, and the closing tags, </BODY> and </HTML>, to the start and end of each file created by the SQL*Plus SPOOL filename command. The default is OFF.

You can turn SPOOL ON and OFF as required during a session.

Note:

It is important to distinguish between the SET MARKUP HTML SPOOL option, and the SQLPLUS SPOOL filename command.

The SET MARKUP HTML SPOOL ON option enables the writing of the <HTML> tag to the spool file. The spool file is not created, and the header and footer tags enabled by the SET MARKUP HTML SPOOL ON option are not written to the spool file until you issue the SQLPLUS SPOOL filename command.

SQL*Plus writes several HTML tags to the spool file when you issue the SPOOL filename command. The tags written and their default content are:

<HTML>
<HEAD>
<TITLE>SQL*Plus Report</TITLE>
<META name="generator" content="SQL*Plus 9.0.1">
</HEAD>
<BODY>

When you issue any of the SQL*Plus commands: EXIT, SPOOL OFF or SPOOL filename, SQL*Plus appends the following end tags and closes the file:

</BODY>
</HTML>

You can specify <HEAD> tag contents and <BODY> attributes using the HEAD and BODY options

PRE[FORMAT] {ON|OFF}

PREFORMAT ON or OFF specifies whether or not SQL*Plus writes output to the <PRE> tag or to an HTML table. The default is OFF, so output is written to a HTML table by default. You can turn PREFORMAT ON and OFF as required during a session.

Notes:

To produce report output using the HTML <PRE> tag, you must set PREFORMAT ON. For example:

SQLPLUS -M "HTML ON PREFORMAT ON"

SET MARKUP HTML ON PREFORMAT ON

MARKUP Usage Notes

Existing scripts that do not explicitly set PREFORMAT ON will generate output in HTML tables. If you want output in HTML <PRE> tags, you must set PREFORMAT ON.

Some SQL*Plus commands have different behavior when output is directed to an HTML table. Commands originally intended to format paper reports may have different meaning for reports intended for web tables:

PAGESIZE is the number of rows in an HTML table, not the number of lines. Each row may contain multiple lines. The TTITLE, BTITLE and column headings are repeated every PAGESIZE rows.
LINESIZE may have an effect on data if wrapping is on, or for very long data. Depending on data size, they may be generated on separate lines, which a browser may interpret as a space character.
TTITLE and BTITLE content is output to three line positions: left, center and right, and the maximum line width is preset to 90% of the browser window. These elements may not align with the main output as expected due to the way they are handled for web output. Entity mapping in TTITLE and BTITLE is the same as the general ENTMAP setting specified in the MARKUP command.
If you use a title in your output, then SQL*Plus starts a new HTML table for output rows that appear after the title. Your browser may format column widths of each table differently, depending on the width of data in each column.
SET COLSEP and RECSEP only produce output in HTML reports when PREFORMAT is ON.

RESTRICT Option

-R[ESTRICT] {1|2|3}

Allows you to disable certain commands that interact with the operating system. This is similar to disabling the same commands in the Product User Profile (PUP) table. However, commands disabled with the -RESTRICT option are disabled even if there is no connection to a server, and remain disabled until SQL*Plus terminates.

If no -RESTRICT option is active, than all commands can be used, unless disabled in the PUP table.

If -RESTRICT 3 is used, then LOGIN.SQL is not read. GLOGIN.SQL is read but restricted commands used will fail.

Table 7-1 shows the commands disabled in each restriction level.

Table 7-1 Commands Disabled by Restriction Level

Command	Level 1	Level 2	Level 3
EDIT	disabled	disabled	disabled
GET			disabled
HOST, !	disabled	disabled	disabled
SAVE		disabled	disabled
SPOOL		disabled	disabled
START, @, @@			disabled
STORE		disabled	disabled

SILENT Option

-S[ILENT]

Suppresses all SQL*Plus information and prompt messages, including the command prompt, the echoing of commands, and the banner normally displayed when you start SQL*Plus. If you omit username or password, SQL*Plus prompts for them, but the prompts are not visible. Use SILENT to invoke SQL*Plus within another program so that the use of SQL*Plus is invisible to the user.

SILENT is a useful mode for creating reports for the web using the SQLPLUS -MARKUP command inside a CGI script or operating system command file. The SQL*Plus banner and prompts are suppressed and do not appear in reports created using the SILENT option.

Logon

username[/password]

Represent the username and password with which you wish to start SQL*Plus and connect to Oracle. If you omit username and password, SQL*Plus prompts you for them.

If you omit only password, SQL*Plus prompts you for password. When prompting, SQL*Plus does not display password on your terminal screen. In silent mode, username and password prompts are not visible - your username will appear as you type it, but not your password.

@connect_identifier

Consists of an Oracle Net connect identifier. The exact syntax depends upon the Oracle Net communications protocol your Oracle installation uses. For more information, refer to the Oracle Net manual appropriate for your protocol or contact your DBA.

Represents a default logon using operating system authentication. You cannot enter a connect_identifer if you use a default logon. In a default logon, SQL*Plus typically attempts to log you in using the username OPS$name, where name is your operating system username. Note that the prefix "OPS$" can be set to any other string of text. For example, you may wish to change the settings in your INIT.ORA parameters file to LOGONname or USERIDname. See the Oracle9i Administrator's Guide for information about operating system authentication.

AS {SYSOPER|SYSDBA}

The AS clause allows privileged connections by users who have been granted SYSOPER or SYSDBA system privileges. You can also use either of these privileged connections with / and /NOLOG.

If you use this option, you need to quote the command arguments on many operating systems, for example:

SQLPLUS "/ AS SYSDBA"
SQLPLUS "SYSTEM/MANAGER AS SYSOPER"

/NOLOG

Establishes no initial connection to Oracle. Before issuing any SQL commands, you must issue a CONNECT command to establish a valid logon. Use /NOLOG when you want to have a SQL*Plus command file prompt for the username, password, or database specification. The first line of this command file is not assumed to contain a logon.

Start

@{uri|file_name[.ext]} [arg ...]

Specifies the name of a command file and arguments to run. The command file can be called from the local file system or from a web server. uri is only supported on Windows platforms in this release.

SQL*Plus passes the arguments to the command file as if executing the file using the SQL*Plus START command. If no file suffix (file extension) is specified, the suffix defined by the SET SUFFIX command is used. The default suffix is .sql.

See the START command in Chapter 8 for more information.

Setting Up the Site Profile

SQL*Plus supports a Site Profile, a SQL*Plus command file created by the database administrator. This file is generally named GLOGIN with an extension of SQL. SQL*Plus executes this command file whenever any user starts SQL*Plus and SQL*Plus establishes the Oracle connection. The Site Profile allows the DBA to set up SQL*Plus environment defaults for all users at a particular site; users cannot directly access the Site Profile. The default name and location of the Site Profile depend on your system. Site Profiles are described in more detail in the Oracle installation and user's manual(s) provided for your operating system.

Setting Up the User Profile

SQL*Plus also supports a User Profile, executed after the Site Profile. SQL*Plus searches for a file named LOGIN with the extension SQL in your current directory. If SQL*Plus does not find the file there, SQL*Plus will search a system-dependent path to find the file. Some operating systems may not support this path search.

Receiving a Return Code

If you fail to log in successfully to SQL*Plus because your username or password is invalid or some other error, SQL*Plus will return an error status equivalent to an EXIT FAILURE command. See the EXIT command in this chapter for further information.

Example 7-1 Starting SQLPlus*

To start SQL*Plus with username HR and password HR, enter


SQLPLUS HR/HR

To start SQL*Plus, as above, and to make POLICY the default database (where POLICY is a valid Oracle Net database connect identifier), enter


SQLPLUS HR/HR@POLICY

To start SQL*Plus with username HR and password HR and run a command file named STARTUP with the extension SQL, enter


SQLPLUS HR/HR @STARTUP

Note the space between HR and @STARTUP.

To start SQL*Plus with HTML ON, so that output can be captured in a file and then viewed on a web browser, enter


SQLPLUS -M "HTML ON" HR/HR

To start SQL*Plus with no access to the EDIT and HOST commands during the session, enter


SQLPLUS -R 1 HR/HR

Example 7-2 Displaying the SQLPLUS syntax

To display the syntax of the SQLPLUS command, enter


SQLPLUS -H


Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_identifier>] | / |/NOLOG
      <start>  ::= @<uri>|<filename>[.<ext>] [<parameter> ...]
        -H displays the SQL*Plus version banner and usage syntax
        -V displays the SQL*Plus version banner
        -M <o> uses HTML markup options <o>
        -R <n> uses restricted mode <n>
        -S uses silent mode

Options	has the following syntax: -H[ELP] \| -V[ERSION] \| [ [-M[ARKUP] markup_option] [-R[ESTRICT] {1\|2\|3}] [-S[ILENT]] ]
	and markup_option has the following syntax: HTML [ON\|OFF] [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON\|OFF}] [SPOOL {ON\|OFF}] [PRE[FORMAT] {ON\|OFF}]
Logon	has the following syntax: {username[/password][@connect_identifier \| / } [AS {SYSOPER\|SYSDBA}] \| /NOLOG
Start	has the following syntax: @{uri\|file_name[.ext]} [arg ...]

Starting SQL*Plus Using the SQLPLUS Command

Options

HELP Option

VERSION Option

MARKUP Options

MARKUP Usage Notes

RESTRICT Option

Table 7-1 Commands Disabled by Restriction Level

SILENT Option

Logon

Start

Setting Up the Site Profile

Setting Up the User Profile

Receiving a Return Code

Example 7-1 Starting SQL*Plus

Example 7-2 Displaying the SQLPLUS syntax

Example 7-1 Starting SQLPlus*