|
|
|
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.
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.
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))
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.
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.
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.
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:
Enter your script using '&' and '&&' as the prefix for variables. Do not use DEFINE, or UNDEFINE. Click the Execute button to execute the script. iSQL*Plus prompts you for values for all the substitution variables in your script. At the end of script execution, any double ampersand values contained in the script remain defined. This means that you will not be prompted to enter values for these variables again, until they have been undefined, or you log out of iSQL*Plus. If this is not the behavior you want, then use a single ampersand as the prefix for variables in your script. You are always prompted for value substitution for variables created with a single ampersand prefix. If you use DEFINE to define variable values in your script in this mode, the defined values override values entered in the Variable Substitution screen.
Enter your script. Ensure that you include SET DEFINE OFF as the last command in your script to make sure that iSQL*Plus does not prompt for variable values when the script is executed again. Enter SET DEFINE ON, and DEFINE all variables with '&' or '&&' before they are referenced. You can also use COLUMN ... new_value or COLUMN ... old_value to instantiate substitution variables. Click the Execute button to execute the script. iSQL*Plus does not prompt you for values for substitution variables. In this usage, be aware that iSQL*Plus substitutes a NULL value for any variable you do not DEFINE in the script.
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:
Displays the substitution variable names.
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.
Click the OK button to execute the script in the Input area with the substitution variable values you entered.
Click the Cancel button to return to the Work screen without executing the script in the Input area.
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.
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.
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.
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.
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.
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.
Use the dropdown list to select the system variable you want to set.
Click the Go button to navigate to the Quick link choice.
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.
Click the OK button to set all system variables that you have changed.
Click the Cancel button to return to the Preferences screen without making any changes.
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.
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.
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.
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.
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
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.
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.
Enter a valid iSQL*Plus Server URL in the form:
http://machine_name.domain:7777/isqlplus
This field is mandatory.
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.
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.
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.
Click Add to add a new iSQL*Plus Server. The cursor is positioned in the mandatory URL field.
Click Remove to delete a selected iSQL*Plus Server definition.
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.
Click Browse to find an application you want to associate with the .SQL file extension when you double click.
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.
Click OK to save your changes and close the Configure iSQL*Plus Servers dialog.
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.
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:
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.
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.
To modify an iSQL*Plus Server, perform the following steps:
The modified iSQL*Plus Server name appears in the context menu.
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:
The iSQL*Plus Server is removed from the context menu.
