iSQL*Plus logo

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index

Using iSQL*Plus

Username and Password

When you start iSQL*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. You can change your password in the Change Password screen which is accessed from the Preferences screen. See Changing Your Password.

For further information about the default logins, see the Oracle9i Administrator's Guide.

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 35!sBxtR, you could enter

CONNECT HR/35!sBxtR

You can disconnect the username currently connected to Oracle without leaving iSQL*Plus by entering the iSQL*Plus DISCONNECT command.

Connection Identifier

To use iSQL*Plus to access data in an Oracle database, you must first connect to the database. When starting iSQL*Plus, you use a connection identifier to specify the database you want to connect to. The connection identifier 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.

Connecting to a Remote Database at Login

At login, you can specify the database to connect to in the iSQL*Plus 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.

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

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/35!sBxtR@connect_identifier

For more information, see the Oracle Net guide appropriate for your protocol, or contact your DBA.

Entering Statements

The iSQL*Plus Work screen is the main screen you work in. This is where you write, load, save and execute scripts.

Enter scripts in the Input area as you would in command line SQL*Plus. You can enter multiple commands at once, then click the Execute button to run them.

The script output can be:

You can use the Backspace and the Delete keys, and you can cut and paste using your web browser's edit keys to edit the statements in the Input area. You can also cut or copy scripts or statements from other applications such as text editors, and paste them directly into the Input area.

You can save scripts using the Save Script button. iSQL*Plus saves scripts to a text file. You can load scripts with the Load Script button. Saving and loading scripts may be useful when editing and testing scripts.

Click the Execute button to execute the contents of the Input area. The results of your script are displayed below the Input area. Use the History screen to access and rerun commands previously executed in the current session.

iSQL*Plus executes a SQL or PL/SQL statement at the end of the Input area, even if it is incomplete or does not have a final ";" or "/". If you intend to run iSQL*Plus scripts in the SQL*Plus command line, you should make sure you use a ";" or "/" to terminate your statements.

Remember that iSQL*Plus retains the state of your current system variables and other options from one execution to the next. If you use the History screen to re-execute a script, you may get different results from those previously obtained, depending on the current system variable values.

Loading Scripts

Editing complex scripts in another text editor with more sophisticated tools for search and replace, and other text manipulation, may be an advantageous way to prepare original scripts or variants of existing scripts which you can then load into iSQL*Plus for editing or execution.

You can load any SQL script into iSQL*Plus that you can access from your workstation. Make sure that scripts you load are valid SQL scripts.

Some web browsers may require you to create a MIME type or application association for files with a .SQL extension in order to load them into iSQL*Plus. See "Adding MIME Types" for information on how to create a MIME or application association.

Entering Substitution Variables

System variables specified with the SET command can affect iSQL*Plus behavior. SET DEFINE, SET ESCAPE, SET VERIFY ON, and SET CONCAT affect variable substitution behavior, and should be set in iSQL*Plus before attempting to execute a script. For further information about these SET options, see the SET command.

iSQL*Plus preprocesses a script for '&' and '&&' variables, and prompts the user for their values before sending the script to the SQL*Plus engine for execution. You should synchronize variable substitution with one of the two following options:

Substitution Variables Screen

When iSQL*Plus executes a script containing substitution variables, the Substitution Variables screen is displayed. For example, when you enter:

BREAK ON &SORTCOL
SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY > 12000
ORDER BY &SORTCOL

iSQL*Plus displays:


Text description of substit.gif follows.
Text description of the illustration substit.gif

Variable

Displays the substitution variable names.

Value

Enter a value for each substitution variable. For example, enter LAST_NAME in the sortcol field, and EMP_DETAILS_VIEW in the mytable field of the example script.

OK

Click the OK button to execute the script in the Input area with the substitution variable values you entered.

Cancel

Click the Cancel button to return to the Work screen without executing the script in the Input area.

Using SET and COLUMN Commands

iSQL*Plus remembers the state of your current SET command variables from one execution to the next. If you use the Back button on your web browser and then re-execute scripts, or if you re-execute scripts from the History screen, iSQL*Plus may display the results differently, according to any commands executed since the original script was executed. For example, a new SET PAGESIZE command may set column headings to appear in different positions.

The COLUMN command, and the SET LINESIZE and SET WRAP commands have an effect on the buffer sizes used by iSQL*Plus. If words in your script output include unexpected white spaces or are truncated, make sure the column size is large enough to accommodate the word, and that word wrapping is set to ON.

Setting User Interface Options

In the Interface Options screen, you can set the Input area size, where to output script results and the number of scripts to retain in the History. You access the Interface Options screen from the Preferences screen.


Text description of option.gif follows.
Text description of the illustration option.gif

Input Area

Enter the width in characters, and the height in lines that you want for the Input area. The default width is 70 characters, and the default height is 10 lines.

Output

Select one of the three options to set the output destination for script results generated from your session:

The Output setting returns to the default of Work screen every time a new session is started.

History

Enter the number of scripts to be stored in History. The minimum value allowed is zero, the maximum 100, and the default is 10. If you set an invalid value, the previous valid value is used.

Setting System Variables

You can set system variables for your iSQL*Plus session in the System Variables screen. You access the System Variables screen from the Preferences screen.

System variable settings return to their default values every time a new session is started.


Text description of variable.gif follows.
Text description of the illustration variable.gif

Quick link:

Use the dropdown list to select the system variable you want to set.

Go

Click the Go button to navigate to the Quick link choice.

System variables and options

There are checkboxes, radio buttons and text entry fields as required for each system variable. System variables are listed in alphabetical order.

The current state of system variables is displayed with the exception of the three system variables: CONSTRAINT, ROLE and TRANSACTION. While you can enter and set values for CONSTRAINT, ROLE and TRANSACTION through the associated text fields, the current state is never displayed.

OK

Click the OK button to set all system variables that you have changed.

Cancel

Click the Cancel button to return to the Preferences screen without making any changes.

Changing Your Password

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.

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

 

Exiting iSQL*Plus

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

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/isqlplus[?UserOpts]

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

    http://machine_name.domain/isqlplusdba[?DBAOpts]

or to invoke iSQL*Plus to generate the iSQL*Plus Server statistics report, use:

    http://machine_name.domain/isqlplusdba[?Statistics]

where

machine_name.domain is the URL of 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 hr, enter

    http://machine_name.domain/isqlplus?userid=hr/hr

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/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/isqlplusdba?userid=hr/hr@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/isqlplus?script="select * from employee_details_view;"&type=text&action=load

Configuring the iSQL*Plus Extension

The iSQL*Plus Extension for Microsoft Windows is accessed through extended menus in Microsoft Windows platforms. The iSQL*Plus Extension extended menu is accessed by right clicking files with the .SQL file extension.

Before you can use the iSQL*Plus Extension for Windows you must configure the available iSQL*Plus Servers that you want to use.

The Configuration Dialog

You access the Configuration dialog through the Configure option in the context menu. Open the Configuration dialog by right clicking a .SQL file in Windows Explorer, then select iSQL*Plus Servers, and then Configure. The Configure iSQL*Plus Servers dialog is displayed.

You can resize the column widths in the iSQL*Plus Servers area by selecting and dragging the column heading separator. You may need to expand column widths to enable fields to be selected. When the field is smaller than the text it contains, tool tips show the full text in the field, and prevent you from selecting it.

You can sort the list of iSQL*Plus Servers in ascending or descending field name order by clicking the field name column heading. You can also manually sort the server definitions by using the up and down arrow buttons on the right side of the list of iSQL*Plus Servers.


Text description of iseconf.gif follows.
Text description of the illustration iseconf.gif

URL

Enter a valid iSQL*Plus Server URL in the form:

http://machine_name.domain:7777/isqlplus

This field is mandatory.

Name

Enter a name for this iSQL*Plus Server. The name, if entered, is used in the context menu, otherwise the full iSQL*Plus Server URL is used.

Connect String

Enter a connect string to specify your username and password and the database you want to connect to. If you omit a username and password you are prompted for them. If you omit a database connection identifier or Oracle Net alias, you are connected to the default database.

Execute

Clear the Execute checkbox to change the action performed on the selected file. The default action is Execute, the alternative is Load. Execute runs the selected file in the iSQL*Plus Server and displays the results in your browser. Load opens the iSQL*Plus Server and loads the selected file into the Input area.

Add

Click Add to add a new iSQL*Plus Server. The cursor is positioned in the mandatory URL field.

Remove

Click Remove to delete a selected iSQL*Plus Server definition.

File Association:

Enter a path and arguments for the application you want to associate with the .SQL file extension when you double click. The default application is Windows Notepad. This definition does not affect the iSQL*Plus Server definitions.

Browse

Click Browse to find an application you want to associate with the .SQL file extension when you double click.

Language:

Select a language you want to use in the iSQL*Plus Extension from the dropdown list. If available, the Language setting only affects the iSQL*Plus Extension, not iSQL*Plus.

OK

Click OK to save your changes and close the Configure iSQL*Plus Servers dialog.

Cancel

Click Cancel to abort your changes and close iSQL*Plus Extension. Enter a valid password for the username. You are prompted one more time before your changes are lost.

When you first open the Configure iSQL*Plus Servers dialog, the OK button is unavailable and the Cancel button is labelled Close. Once you make changes, OK is enabled and Close is renamed Cancel.

Adding a Server

You must configure at least one iSQL*Plus Server before you can use the iSQL*Plus Server Extension. To add an iSQL*Plus Server, perform the following steps:

  1. Right click on a .SQL file in Windows Explorer.

  2. Select iSQL*Plus Servers to display the context menu options. Initially, there are two options:

  3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.

  4. Enter an iSQL*Plus Server definition in the iSQL*Plus Servers: area. When you first open the dialog, the following syntax model is displayed to show you the expected field contents:

    URL: << http://machine_name.domain:7777/isqlplus >>
    Name: << host >>
    Connect String: << username/password@connect_identifier >> 
    

    You must enter a URL for your iSQL*Plus Server, the other fields are optional.

  5. Click OK to save your new server definition.

The new iSQL*Plus Server Name appears in the context menu. When you select a server from the context menu, the full iSQL*Plus Server URL is shown in the status bar.

Modifying a Server

To modify an iSQL*Plus Server, perform the following steps:

  1. Right click on a .SQL file in Windows Explorer.

  2. Select iSQL*Plus Servers to display the context menu options. Initially, there is one unavailable option, No iSQL*Plus Servers, configured and one available option Configure.

  3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.

  4. Select the server you wish to modify from the list of available servers.

  5. Edit the server definition directly in the text fields and press Enter when you have finished editing each field.

  6. Click OK to save your changes.

The modified iSQL*Plus Server name appears in the context menu.

Removing a Server

Select the server you wish to remove from the list of available servers. The server's details will appear in the text boxes below. Click on the Remove button to remove it from the list of available servers.

To remove an iSQL*Plus Server, perform the following steps:

  1. Right click on a .SQL file in Windows Explorer.

  2. Select iSQL*Plus Servers to display the context menu options.

  3. Click Configure. The Configure iSQL*Plus Servers dialog is displayed.

  4. Select the server you wish to remove from the list of available servers.

  5. Click Remove.

  6. Click OK to save your changes.

The iSQL*Plus Server is removed from the context menu.

Previous Page
Previous

Next Page
Next

Table Of Contents
Contents

Index
Index