3 Starting SQL*Plus

Secure External Password Store

As a command-line alternative for large-scale deployments where applications use password credentials to connect to databases, it is possible to store such credentials in a client-side Oracle wallet. An Oracle wallet is a secure software container that is used to store authentication and signing credentials.

Storing database password credentials in a client-side Oracle wallet eliminates the need to embed usernames and passwords in application code, batch jobs, or scripts. This reduces the risk of exposing passwords in the clear in scripts and application code, and simplifies maintenance because you need not change your code each time usernames and passwords change. In addition, not having to change application code also makes it easier to enforce password management policies for these user accounts.

When you configure a client to use the external password store, applications can use the following syntax to connect to databases that use password authentication:

CONNECT /@database_alias

Note that you need not specify database login credentials in this CONNECT statement. Instead your system looks for database login credentials in the client wallet.

Expired Password

In the command-line interface, if your password has expired, SQL*Plus prompts you to change it when you attempt to log in. You are logged in once you successfully change your password.

Expired Password Screen in iSQL*Plus

If your password has expired, the Expired Password screen is automatically displayed when you attempt to log in. Fill out the fields on the Expired Password screen as you would for the Change Password screen.

You are logged in once you successfully change your password. If you click the Cancel button, you are returned to the Login screen.

Changing your Password

In the command-line interface, you can change your password with the PASSWORD command. See PASSWORD.

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.

Net Service Name

Your DBA is responsible for creating the databases you use and defining net service names for them in the tnsnames.ora file. In iSQL*Plus, your DBA can also restrict the databases available to those shown in a dropdown list of net service names.

A net service name definition in the tnsnames.ora file has the syntax:

net_service_name= 
(DESCRIPTION= 
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) )
(CONNECT_DATA= 
(SERVICE_NAME=service_name) ) ) 

To use a net service name (alias), it must have an entry in the tnsnames.ora file on the machine running SQL*Plus, or for iSQL*Plus, the machine running the iSQL*Plus Application Server. An entry in tnsnames.ora is not required if you use a connection identifier.

SALES1 =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) )
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com) ) )

SQLPLUS hr@SALES1

Full Connection Identifier

Depending on your configuration, use the full connection identifier syntax like:

(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) )
(CONNECT_DATA=
(SERVICE_NAME=service_name) ) )

The SERVICE_NAME is the global database name entered during database creation. It combines a database name with a domain name. For example, the SERVICE_NAME sales.us.acme.com has a database name of sales and a domain of us.acme.com.

An INSTANCE_NAME is the name you give to the database instance during creation. It defaults to the SID you entered during database creation.

An Oracle System Identifier (SID) identifies a specific Oracle release 8.0 database instance.

You can optionally use an INSTANCE_NAME in place of the SERVICE_NAME phrase.

Use a SID in place of the SERVICE_NAME when connecting to an Oracle release 8.0 or earlier database.

SQLPLUS hr@(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) )
(CONNECT_DATA=
(SERVICE_NAME=sales.us.acme.com) ) )

Easy Connection Identifier

The easy or abbreviated connection identifier has the syntax:

[//]host[:port][/service_name]

sqlplus hr@sales-server:1521/sales.us.acme.com

connect hr@sales-server:1521/sales.us.acme.com

Connectionless Session with /NOLOG

In the command-line interface, it is possible to start SQL*Plus without connecting to a database. This is useful for performing some database administration tasks, writing transportable scripts, or to use SQL*Plus editing commands to write or edit scripts.

You use the /NOLOG argument to the SQLPLUS command to start a connectionless command-line session. After SQL*Plus has started you can connect to a database with the CONNECT command.

SQLPLUS /NOLOG 

Starting Command-line SQL*Plus

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

1. Make sure that SQL*Plus has been installed on your computer.

2. Log on to the 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.

   
   

   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 10.2.0.1.0 - Production on Thu Jun 2 16:29:01 2005 (c) Copyright 1982, 2005 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 Database 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 SQL*Plus command prompt indicates that SQL*Plus is ready to accept your commands.

If SQL*Plus does not start, you should see a message to help you correct the problem.

Getting Command-line Help

To access command-line help for SQL*Plus commands, type HELP or ? followed by the command name at the SQL command prompt or in the iSQL*Plus Workspace Input area. See the HELP command for more information. 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 fits in a standard screen. For example:

HELP INDEX

Starting the Windows Graphical User Interface

The graphical user interface can be started from the Windows menu, or from a Windows command prompt.

Starting the GUI from the Windows Command Prompt

1. Select Command Prompt from Programs->Accessories in the Start menu. A Command Prompt window appears.

2. Enter

   C:\> SQLPLUSW
   

   The SQL*Plus graphical user interface starts. You can optionally include your login username and password separated by a slash (/), and a database to connect to. However, for security reasons, it is recommended that you never include your password in plain text. You are always prompted to enter your password if you start the SQL*Plus GUI with:

   C:\> SQLPLUSW username@connect_identifier
   

   Otherwise enter the required information in the login dialog as described in Starting SQL*Plus earlier. The Oracle SQL*Plus application window appears.

Description of the illustration wingui.gif

Starting the iSQL*Plus Application Server

The iSQL*Plus Application Server must be running on the middle tier before you can start an iSQL*Plus session. A command-line utility and a Windows Service are supplied to start and stop iSQL*Plus on Windows.

The iSQL*Plus Application Server is started by default during Oracle Database installation.

To Start the iSQL*Plus Application Server on Unix

1. Start a terminal session.

2. Enter

   $ORACLE_HOME/bin/isqlplusctl start
   

   The iSQL*Plus Application Server is started.

To Start the iSQL*Plus Application Server on Windows

1. Select Services from the Start > Programs > Administrative Tools menu.

2. Locate the iSQL*Plus Windows Service, OracleOracleHomeNameiSQL*Plus.

3. Start the Windows Service.

Alternatively, you can start iSQL*Plus from a command prompt.

To Start iSQL*Plus Application Server from a Command Prompt

1. Start a command prompt session.

2. Enter

   %ORACLE_HOME%\bin\isqlplusctl start
   

   The iSQL*Plus Application Server is started.

To Test If the iSQL*Plus Application Server Has Started Correctly

1. Enter the iSQL*Plus URL in your web browser. The iSQL*Plus URL is in the form:

   http://machine_name:5560/isqlplus/
   

   iSQL*Plus uses HTTP port 5560 by default. If iSQL*Plus is not available on port 5560, read the $ORACLE_HOME/install/portlist.ini file to find the port on which iSQL*Plus is running.

2. Enter one of the following URLs from a web browser on the machine running the iSQL*Plus Application Server if you do not know the iSQL*Plus URL:

   http://127.0.0.1:5560/isqlplus/
   http://localhost:5560/isqlplus/
   

   The iSQL*Plus Login screen should be displayed.

3. Enter the same URL you used in step 2, without "isqlplus/" if the iSQL*Plus Login screen was not displayed in step 2. This is to establish whether the OC4J instance has started correctly:

   http://127.0.0.1:5560/
   http://localhost:5560/
   

   The OC4J default page should be displayed.

   If the OC4J default page is not displayed, the iSQL*Plus Application Server is not running. Also see Testing if the iSQL*Plus Application Server is Running.

To Check the HTTP Port used by the iSQL*Plus Application Server

To discover the HTTP port number used by the iSQL*Plus Application Server, search the $ORACLE_HOME/install/portlist.ini file on the Application Server. Also see Changing the iSQL*Plus Application Server Port in Use.

Stopping the iSQL*Plus Application Server

To Stop the iSQL*Plus Application Server on Unix

1. Start a command-line session.

2. Enter

   $ORACLE_HOME/bin/isqlplusctl stop
   

   The iSQL*Plus Application Server is stopped.

To Stop the iSQL*Plus Application Server on Windows

1. Select Services from the Start > Programs > Administration Tools menu.

2. Locate the iSQL*Plus Windows Service, OracleOracleHomeNameiSQL*Plus.

3. Stop the Windows Service.

To Stop the iSQL*Plus Application Server from the Command Prompt

1. Start a command-line session.

2. Enter

   %ORACLE_HOME%\bin\isqlplusctl stop
   

   The iSQL*Plus Application Server is stopped.

Once stopped, no iSQL*Plus sessions are possible through this server until the iSQL*Plus Application Server is restarted.

Starting iSQL*Plus

To start an iSQL*Plus session

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 Application 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 Connecting to a Database for more information.

3. Click the Login button. The iSQL*Plus Workspace is displayed.

Starting iSQL*Plus as a DBA

To start an iSQL*Plus session with SYSDBA or SYSOPER privileges, you use the iSQL*Plus DBA URL which has the form:

http://machine_name:port/isqlplus/dba/

To access the iSQL*Plus DBA URL, you must set up login credentials using the Oracle JAAS Provider, known as JAZN (Java AuthoriZatioN). See Enabling iSQL*Plus DBA Access for information on accessing the iSQL*Plus DBA URL.

When you are connected through the iSQL*Plus DBA URL, the Application Server authentication enables AS SYSDBA or AS SYSOPER connections through the DBA Login screen, or through a CONNECT command, but the Oracle Database username and password authentication may still prevent access.

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

You can start iSQL*Plus as a normal user, or with SYSDBA or SYSOPER privileges.

The syntax to enter in your web browser's Location/Address field to start iSQL*Plus as a normal user is:

http://machine_name.domain:port/isqlplus[/dynamic?UserOpts]

or to start iSQL*Plus with SYSDBA or SYSOPER privileges, use:

http://machine_name.domain:port/isqlplus/dba[/dynamic?DBAOpts]

where

machine_name.domain is the URL of the Application Server

port is the number of the port used by the Application Server

UserOpts is UserLogin|Script|UserLogin&Script

DBAOpts is DBALogin|Script|DBALogin&Script

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 user variable 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 Workspace, but it is not executed. A web browser may not be able to display large scripts in the Workspace, and as a result, scripts may be truncated.

http://machine_name.domain:5560/isqlplus/dynamic?userid=HR

http://machine_name.domain:5560/isqlplus/dynamic?script=http://machine_name2.domain/myscript.sql

http://machine_name.domain:5560/isqlplus/dba/dynamic?userid=hr@oracle10g%20as%20sysdba&script=ftp://machine_name2.domain/script.sql&name=*&salary=12000

http://machine_name.domain:5560/isqlplus/dynamic?script=select%20*%20from%20emp_details_view;&type=text&action=load

Getting Help in iSQL*Plus

To access iSQL*Plus Online Help, click the Help icon. Help specific to iSQL*Plus is displayed in a new browser window. It is available in eight languages:

Brazilian Portuguese French German Italian Japanese Korean Simplified Chinese Spanish

English is installed by default, and when the requested language is unavailable.

For more information about language support in SQL*Plus, see Chapter 11, "SQL*Plus Globalization Support".

For more information about language support in SQL*Plus, see SQL*Plus Globalization Support.

Exiting the Command-line User Interface

When you are done working with SQL*Plus and wish to return to the operating system, enter EXIT or QUIT at the SQL*Plus prompt, or enter the end of file character, Ctrl+D on UNIX or Ctrl+Z on Windows.

SQL*Plus displays the version of Oracle Database from which you disconnected and the versions of tools available through SQL*Plus before you return to the operating system prompt.

Exiting the Windows Graphical User Interface

You can exit the Windows GUI in the same way as you exit the command-line user interface, enter EXIT or QUIT at the SQL*Plus prompt.

You can also click Exit in the File menu to exit the Windows GUI.

Exiting the iSQL*Plus User Interface

To exit iSQL*Plus, click the Logout icon.

To free up system and server resources, it is recommended that you always use the Logout icon to exit iSQL*Plus.

In iSQL*Plus, the EXIT or QUIT command halts the script currently running, it does not terminate your iSQL*Plus session.

Options

The following sections contain descriptions of SQLPLUS command options:

SQL>PROMPT A > B
A > B

SQL> PROMPT A > B
A > B

</BODY>
</HTML>

Logon

username[/password]

Represent the username and password with which you wish to start SQL*Plus and connect to Oracle Database.

If you omit username and password, SQL*Plus prompts you for them. If you omit only password, SQL*Plus prompts for it. In silent mode, username and password prompts are not visible! Your username appears when you type it, but not your password.

@connect_identifier

Consists of an Oracle Net connect identifier. The exact syntax depends upon the Oracle Net configuration. For more information, refer to the Oracle Net manual or contact your DBA.

/

Represents a default logon using operating system authentication. You cannot enter a connect identifier 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 Oracle Database Administrator's Guide for information about operating system authentication.

AS {SYSOPER|SYSDBA}

The AS clause enables privileged connections by users who have been granted SYSOPER or SYSDBA system privileges.

/NOLOG

Establishes no initial connection to Oracle Database. 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.