Skip Headers

SQL*Plus User's Guide and Reference
Release 9.2

Part Number A90842-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

4
Starting SQL*Plus

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:

Starting Command-line SQL*Plus

To begin using SQL*Plus, you must first understand how to start and leave SQL*Plus.

Example 4-1 Starting SQL*Plus

This example shows you how to start SQL*Plus. Follow the steps shown.

  1. Make sure that SQL*Plus has been installed on your computer.
  2. Log on to the host operating system (if required).
  3. Enter the command, SQLPLUS, and press Return.


    Note:

    Some operating systems expect you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase.


    Keyboard icon
    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):

    Screen icon
    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:
  4. Enter your username and press Return. SQL*Plus displays the prompt "Enter password:".
  5. Enter your password and press Return again. For your protection, your password does not appear on the screen.

    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 Keyboard icon in the margin, it is prompting you to enter information at the command prompt line. Where you see the computer screen icon 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.

Shortcuts to Starting Command-line SQL*Plus

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

Keyboard icon
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.

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] 
| [ [-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]
[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:

@{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:

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.

LOGON Option

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

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.

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


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 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 &lt;, &gt; &quot; and &amp; 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.


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 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 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 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:

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 4-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 script. 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 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.

Start

@{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.

Getting Command-line Help

To access online help for SQL*Plus commands, you can type HELP followed by the command name at the SQL command prompt. For example:

Keyboard icon
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:

Keyboard icon
HELP INDEX

If you get a response that help is unavailable, consult your database administrator. See the HELP command for more information.

Installing Command-line Help

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:

The help script files are:

To install SQL*Plus help:

  1. Log in to SQL*Plus as the SYSTEM user with:
    Keyboard icon
    SQLPLUS SYSTEM/your_password

    where your_password is the password you have defined for the SYSTEM user.

  2. Run the SQL script, HLPBLD.SQL, from SQL*Plus with:
    Keyboard icon
    @$ORACLE_HOME/SQLPLUS/ADMIN/HLPBLD.SQL HELPUS.SQL

Exiting Command-line SQL*Plus

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.

Example 4-2 Exiting SQL*Plus

To leave SQL*Plus, enter the EXIT command at the SQL*Plus command prompt:

Keyboard icon
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".

Starting iSQL*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

  1. Enter the Uniform Resource Locator (URL) of iSQL*Plus in the Location field of your web browser, for example:
    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.

  2. Enter your Username, Password and Connection Identifier. See "Login Username and Password" and "Database Connection Identifier" for more information.
  3. Click the Login button. The iSQL*Plus Work screen is displayed.

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.

Getting Help in iSQL*Plus

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

Exiting iSQL*Plus

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.

Starting iSQL*Plus from a URL

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.

Examples

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

Login Username and Password

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

Keyboard icon
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.

Changing your Password

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.

Changing Your Password in iSQL*Plus

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 passwd.gif follows.

Text description of the illustration passwd.gif

Username:

Enter your Oracle database account username.

Old password:

Enter your current Oracle database account password.

New password:

Enter your new password.

Retype new password:

Enter your new password again to make sure you have entered it correctly.

OK

Click the OK button to change the password for your Oracle database account.

Cancel

Click the Cancel button to return to the Preferences screen without changing your password.

iSQL*Plus Expired Password Screen

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 passwdep.gif follows.

Text description of the illustration passwdep.gif

Multi-User Systems

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.

Single-User Systems

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.

Database Connection Identifier

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:

Connecting to a Remote Database as You Start SQL*Plus

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.

Connecting to a Remote Database Using the CONNECT Command

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.


Note:

Do not confuse the @ symbol of the connect identifier with the @ command used to run a script.



Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback