Release 9.2
Part Number A90842-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| SQL*Plus User's Guide and Reference Release 9.2 Part Number A90842-01 |
|
This chapter describes how to start, login, and connect to a database, how to get help, and how to exit SQL*Plus.
Specific topics discussed are:
To begin using SQL*Plus, you must first understand how to start and leave SQL*Plus.
This example shows you how to start SQL*Plus. Follow the steps shown.
|
Note: Some operating systems expect you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase. |
SQLPLUS
SQL*Plus displays its version number, the current date, and copyright information, and prompts you for your username (the text displayed on your system may differ slightly):
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 1 16:29:01 2002 (c) Copyright 1986, 2002 Oracle Corporation. All rights reserved. Enter user-name:
The process of entering your username and password is called logging in. SQL*Plus displays the version of Oracle to which you connected and the versions of available tools such as PL/SQL.
Next, SQL*Plus displays the SQL*Plus command prompt:
SQL>
The command prompt indicates that SQL*Plus is ready to accept your commands. Throughout this guide, where you see the following keyboard icon 
in the margin, it is prompting you to enter information at the command prompt line. Where you see the computer screen icon 
in the margin, it is showing you what you should expect to see displayed on your screen.
If SQL*Plus does not start, you should see a message to help you correct the problem.
When you start SQL*Plus, you can enter your username and password, separated by a slash (/), following the command SQLPLUS. For example, you can enter
SQLPLUS HR/your_password
and press Return. You can also arrange to log in to SQL*Plus automatically when you log on to your host operating system. See the Oracle installation and user's guide provided for your operating system for details.
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] | [ [-L[OGON]] [-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] |
Logon |
has the following syntax: {username[/password][@connect_identifier | / } |
Start |
has the following syntax: @{url|file_name[.ext]} [arg ...] |
You have the option of entering logon. If you do not specify logon but do specify start, SQL*Plus assumes that the first line of the script 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:
-H[ELP]
Displays the usage and syntax for the SQLPLUS command, and then returns control to the operating system.
-V[ERSION]
Displays the current version and level number for SQL*Plus, and then returns control to the operating system.
-L[OGON]
Specifies not to reprompt for username or password if the initial connection does not succeed. This can be useful in operating system scripts that must either succeed or fail and you don't want to be reprompted for connection details if the database server is not running. This option is not available with the Windows graphical user interface SQL*Plus executable.
-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.
You can use MARKUP HTML ON to produce HTML output in either the 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 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 13, "SQL*Plus Command Reference".
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 13, "SQL*Plus Command Reference" 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> 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. |
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.
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 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.
Existing scripts that do not explicitly set PREFORMAT ON will generate output in HTML tables. If you want output in HTML 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:
-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.
-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 script. The SQL*Plus banner and prompts are suppressed and do not appear in reports created using the SILENT option.
username[/password]
Represent the username and password with which you wish to start SQL*Plus and connect to Oracle. If you enter your password on the command-line as part of the SQLPLUS command in the form,
sqlplus username[/password]
it could be viewable by anyone on your system. Some operating systems have monitoring tools that list all executing commands and their arguments.
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 Database 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/your_password 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 script prompt for the username, password, or database specification. The first line of this script is not assumed to contain a logon.
@{url|file_name[.ext]} [arg ...]
Specifies the name of a script and arguments to run. The script can be called from the local file system or from a web server.
SQL*Plus passes the arguments to the script 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 for more information.
To access online help for SQL*Plus commands, you can type HELP followed by the command name at the SQL command prompt. For example:
HELP ACCEPT
To display a list of SQL*Plus commands, type HELP followed by either TOPICS or INDEX. HELP TOPICS displays a single column list of SQL*Plus commands. HELP INDEX displays a four column list of SQL*Plus commands which usually fits in a single screen. For example:
HELP INDEX
If you get a response that help is unavailable, consult your database administrator. See the HELP command for more information.
The database administrator creates the SQL*Plus help tables and populates them with SQL*Plus help data. Before you can install SQL*Plus help, ensure that:
$ORACLE_HOME/SQLPLUS/ADMIN/
The help script files are:
To install SQL*Plus help:
SQLPLUS SYSTEM/your_password
where your_password is the password you have defined for the SYSTEM user.
@$ORACLE_HOME/SQLPLUS/ADMIN/HLPBLD.SQL HELPUS.SQL
When you are done working with SQL*Plus and wish to return to the operating system, enter the EXIT command at the SQL*Plus command prompt.
To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:
EXIT
SQL*Plus displays the version of Oracle from which you disconnected and the versions of tools available through SQL*Plus. After a moment you will see the operating system prompt.
Before continuing with this chapter, follow steps 3, 4, and 5 of Example 4-1 to start SQL*Plus again. Alternatively, log in using the shortcut shown under "Shortcuts to Starting Command-line SQL*Plus".
The Oracle HTTP Server and the iSQL*Plus Server must be running on the middle tier before you can run iSQL*Plus. See "Starting and Stopping the Oracle HTTP Server"
To run iSQL*Plus
http://machine_name.domain:port/isqlplus
where machine_name.domain is the URL, and port is the port number for the Oracle HTTP Server you want to use. The iSQL*Plus Login screen is displayed.
Each successful login is uniquely identified, so you can have multiple iSQL*Plus sessions running from the same machine, or from multiple client machines.
To run iSQL*Plus with SYSDBA or SYSOPER privileges, use the iSQL*Plus DBA URL:
http://machine_name.domain:port/isqlplusdba
When you are connected through the iSQL*Plus DBA URL, the Oracle HTTP Server authentication permits AS SYSDBA or AS SYSOPER connections through the DBA Login screen, or through a CONNECT command, but the Oracle9i username and password authentication may still prevent access.
The online iSQL*Plus Help provides help and syntax specific to iSQL*Plus. Click the Help icon to access the iSQL*Plus Help. It is displayed in a new browser window. iSQL*Plus Help is available in the following eight languages:
Brazilian Portuguese
French
German
Italian
Japanese
Korean
Simplified Chinese
Spanish
English is installed by default. For more information about language support in SQL*Plus, see Chapter 12, "SQL*Plus Globalization Support".
To exit iSQL*Plus, click the Logout icon. It is recommended that you always use the Logout icon to exit iSQL*Plus to free up system and server resources.
In iSQL*Plus, the EXIT command does not exit or quit your iSQL*Plus session, it halts the currently running script.
You can start iSQL*Plus and pass URL variables, SQL scripts and substitution variables by sending a request from a URL.
SQL scripts must be available through HTTP, HTTPS or FTP, or passed to iSQL*Plus as a URL variable. iSQL*Plus executes the script and returns the results in a web browser window, or loads the script into the Work screen.
You can invoke iSQL*Plus as a normal user, or with SYSDBA or SYSOPER privileges. You can also invoke iSQL*Plus to generate a report on iSQL*Plus Server settings and statistics.
The syntax to enter in your web browser's Location/Address field to invoke iSQL*Plus as a normal user is:
http://machine_name.domain:port/isqlplus[?UserOpts]
or to invoke iSQL*Plus with SYSDBA or SYSOPER privileges, use:
http://machine_name.domain:port/isqlplusdba[?DBAOpts]
or to invoke iSQL*Plus to generate the iSQL*Plus Server statistics report, use:
http://machine_name.domain:port/isqlplusdba[?Statistics]
where
machine_name.domain is the URL of the Oracle HTTP Server
port is the number of the port used by the Oracle HTTP Server
UserOpts is UserLogin|Script|UserLogin&Script
DBAOpts is DBALogin|Script|DBALogin&Script
Statistics is statistics={active|full}[&refresh=number]
and
UserLogin is userid=username[/password]connect_identifier
[@]
DBALogin is userid={username[/password]connect_identifier
[@] | / } AS {SYSDBA | SYSOPER}
Script is script=text[&type={url|text}][&action={execute|load}]variable
[&=value. . .]
If there is no userid URL parameter or if it has incomplete information, iSQL*Plus displays the login screen. If the URL parameter is complete and the login information is valid, iSQL*Plus connects and continues with the request.
SQL script parameters can be given in any order. If any script parameter begins with a reserved keyword, such as script or userid, iSQL*Plus may interpret it as a command rather than as a literal parameter.
If the URL parameter type is url, or if it is not specified, the script parameter is assumed to be the URL of a SQL script.
If the URL parameter type is text, the text in the script parameter is assumed to be the contents of the SQL script itself. There may be HTML character set restrictions on scripts passed using this method.
If the URL parameter action is execute, or if it is not specified, the SQL script is executed in iSQL*Plus.
If the URL parameter action is load, the script is loaded into the Input area of the Work screen, but it is not executed. A web browser may not be able to display large scripts in the Work screen, and as a result, scripts may be truncated.
See "iSQL*Plus Server Statistics" for an explanation of the Statistics syntax and functionality.
To log into iSQL*Plus with the username hr and password your_password, enter
http://machine_name.domain/isqlplus?userid=hr/your_password
To execute a script that is located at the URL http://machine_name2.domain/myscript.sql in iSQL*Plus, and prompt for username and password, enter
http://machine_name.domain:port/isqlplus?script=http://machine_ name2.domain/myscript.sql
To execute a script that is located at a URL, pass the username and password, login to a database, and pass parameters to the script to provide values for substitution variables, enter
http://machine_name.domain:port/isqlplusdba?userid=hr/your_ password@oracle9i%20as%20sysdba&script=ftp://machine_ name2.domain/script.sql&name=*&salary=12000
As the iSQL*Plus DBA URL is used, Oracle HTTP Server authentication is also required. As spaces are not supported, they have been encoded as %20 in this example.
To load a script into iSQL*Plus without passing the username and password, enter
http://machine_name.domain:port/isqlplus?script="select * from employee_ details_view;"&type=text&action=load
When you start SQL*Plus, you need a username that identifies you as an authorized Oracle user and a password that proves you are the legitimate owner of your username.
The database administrator (DBA) is responsible for creating your database account with the necessary privileges and giving you the username and password that allows you to access your account.
Default logins are created and displayed in messages during Oracle9i installation. The default login username/password combinations created are:
Default passwords and your database account password should be changed as soon as possible. See "Changing your Password" following. For further information about the default logins, see the Oracle9i Database Administrator's Guide.
Once you have logged in, you can connect under a different username with the SQL*Plus CONNECT command. The username and password must be valid for the database. For example, to connect the username TODD to the default database using the password FOX, you could enter
CONNECT TODD/FOX
In the command-line interface, if you omit the username and password, SQL*Plus prompts you for them. You also have the option of typing only the username following CONNECT and omitting the password (SQL*Plus then prompts for the password). Because CONNECT first disconnects you from your current database, you will be left unconnected to any database if you use an invalid username and password in your CONNECT command.
If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect.
If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.
You can disconnect the username currently connected to Oracle without leaving SQL*Plus by entering the SQL*Plus command DISCONNECT.
In the command-line interface, you can change your password with the PASSWORD command. See "PASSWORD".
Once you have logged in, you can connect under a different username with the CONNECT command. The username and password must be valid for the database.
For example, to connect the username HR to the default database using the password your_password, you could enter
CONNECT HR/your_password
You can disconnect the username currently connected to Oracle without leaving SQL*Plus or ending your iSQL*Plus session with the DISCONNECT command.
You can change your Oracle database account password in the Change Password screen. If you have logged in with DBA privileges, you can change the password of other users. You access the Change Password screen from the Preferences screen.
Text description of the illustration passwd.gif
Enter your Oracle database account username.
Enter your current Oracle database account password.
Enter your new password.
Enter your new password again to make sure you have entered it correctly.
Click the OK button to change the password for your Oracle database account.
Click the Cancel button to return to the Preferences screen without changing your password.
The Expired Password screen is automatically displayed on attempting to log into iSQL*Plus if your password has expired. Fill out the fields on the Expired Password screen as you would for the Change Password screen.
Text description of the illustration passwdep.gif
Each user must have a username and password to access the operating system. These may or may not be the same ones you use with SQL*Plus.
If only one person at a time uses your computer, you may be expected to perform the functions of a DBA for yourself. If you want to define your own username and password, see the Oracle9i SQL Reference.
To use SQL*Plus to access data in an Oracle database, you must first connect to the database. When starting SQL*Plus, you use a connection identifier to specify the database you want to connect to. The connection identifier is an argument to the SQLPLUS command you use to start a command-line connection, or it is entered in the Connection Identifier field on the iSQL*Plus Login Screen.
If you omit the connection identifier, you are connected to the default database. You must use a connection identifier to specify any database other than the default database.
The DBA is responsible for creating the databases you use and giving you the connection identifiers that allow you to connect to those databases. In the iSQL*Plus user interface, the database administrator can provide a restricted list of connection identifiers. These are accessed through a dropdown list which replaces the usual Connection Identifier field on the Login Screen.
The default database is configured at an operating system level by setting operating system environment variables, symbols or, possibly, by editing an Oracle specific configuration file. Refer to the Oracle documentation for your operating system for more information.
In the command-line interface, it is possible to start SQL*Plus without connecting to a database by using the NOLOG argument of the SQLPLUS command. This is useful for performing some database administration tasks, writing transportable scripts, or to use the editing commands to write or edit scripts.
Databases on other computers, or databases on your host computer other than your default database are called remote databases. You can access a remote database if it has Oracle Net configured and both databases have compatible network drivers. You can connect to a remote database in two ways:
To connect to a remote database when you start iSQL*Plus, enter the Oracle Net service name in your Login screen Connection identifier field. Leave the field blank to use the default Oracle database if one exists, otherwise enter an Oracle Net alias to specify a remote database you want to connect to. If you use an Oracle Net alias, it must be defined on the machine running the iSQL*Plus Server, which may not be the same machine from which you run your web browser.
To connect to a remote database when you start command-line SQL*Plus, include the Oracle Net service name in your SQLPLUS command in the following form:
SQLPLUS HR/your_password@connect_identifier
You can also use the full connection identifier, for example:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=www.oracle.com) (PORT=1521)))(CONNECT_DATA=((SERVICE_NAME=orashop.us.acme.com))
You must use a username and password valid for the remote database and substitute the appropriate service name for the remote database. Command-line SQL*Plus prompts you for a username and password as needed. The database you connect to is used until you CONNECT to another database, DISCONNECT, or exit SQL*Plus.
If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect. If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.
To connect to a remote database using the CONNECT command, include an Oracle Net connection identifier in the CONNECT command in the following form:
CONNECT HR/your_password@connect_identifier
For more information, see the Oracle Net guide appropriate for your protocol, or contact your DBA.
Command-line SQL*Plus prompts you for a password as needed, and connects you to the specified database. In iSQL*Plus, you are not prompted, but must supply the username and password with the CONNECT command.
If you log on or connect as a user whose account has expired, SQL*Plus prompts you to change your password before you can connect. If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.
When you connect to a remote database in this manner, you can use the complete range of SQL and SQL*Plus commands and PL/SQL blocks on the database.
The exact string you enter for the service name depends upon the Oracle Net protocol your computer uses. For more information, see CONNECT and the Oracle Net guide appropriate for your protocol, or contact your DBA.
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|
