10g iSQLPlus Enhancements

10g iSQL*Plus Enhancements

Oracle 10g iSQL*Plus has been enhanced to provide greater flexibility and manageability for end-user support. Following is a short summary of the new features that are now available for 10g iSQL*Plus.

iSQL*Plus Environment

Oracle Database 10g introduces major changes to the underlying iSQL*Plus infrastructure environment. Prior to Oracle 10g, iSQL*Plus used the Oracle HTTP server to enable communication and authentication between the client and the database layers, respectively. With Oracle 10g, the iSQL*Plus middle tier consists of a Java2 Enterprise Edition (J2EE) compliant application server and uses Oracle Containers for Java (OC4J) as the server engine. Along with these changes, there are also notable changes with the iSQL*Plus URL and the iSQL*Plus configuration files. When accessing iSQL*Plus, users will need to use the following URL: http://hostname.domainname:port/isqlplus. With Oracle 9i, the port had a default value of 80. Any time you wanted to check the configuration files for errors or change the default port number, you had to review $ORACLE_HOME/Apache/Apache/conf/httpd.conf, $ORACLE_HOME/Apache/Apache/conf/oracle_apache.conf, and the $ORACLE_HOME/sqlplus/admin/isqlplus.conf files. With Oracle 10g, the default port value is 5560 and users will need to review the $ORACLE_HOME/oc4j/j2ee/isqlplus/config/http-web-site.xml file for any port changes or errors.

iSQL*Plus Workspace

The iSQL*Plus workspace, formerly called the Work Screen, has been enhanced to provide a more robust working environment. The 10g iSQL*Plus workspace consist of the SQL Workspace, SQL History, and Load Script screens. From the main workspace screen, shown in Figure 20.1, you can enter and execute SQL scripts, load and save SQL scripts, view SQL output, access preferences, view the SQL history, and access the iSQL*Plus help files.

Figure 20.1. 10g iSQL*Plus Workspace overview.

When entering SQL from the main workspace screen, you can specify the number of lines of data returned from your query by using the SET PAGESIZE command. After the initial result set is displayed, you can click the Next Page button to view the next result set. By default, the page size is set to 24. You can change the settings by accessing the preferences, modifying the glogin.sql file, or using the SET PAGESIZE command before you issue your query in the iSQL*Plus input window (shown in Figure 20.2).

Figure 20.2. Specifying page size limits in the 10g iSQL*Plus Workspace.

From the History screen, shown in Figure 20.3, you can access a list of scripts that you previously executed during your session. You can either load scripts from the History screen to the Workspace Input screen or delete scripts from the History screen. If you select multiple scripts from the History screen, the scripts are loaded into the Workspace Input screen in the order they were displayed in the History list. It's also important to note that the script history is only available for the current session. The history is not saved over multiple sessions, nor can it be exported for future use.

Figure 20.3. List of previous scripts from the iSQL*Plus History screen.

When you click on the Preferences link in the top-right corner, you can access the Interface Configuration screen, shown in Figure 20.4, to control the look and feel of the iSQL*Plus interface. Starting with 10g Release 1 you can set number of scripts within the script history, set the size of the script input area, set the location where the output should be displayed (below the input area or in an HTML file), and specify whether output is displayed on a single page or spread over multiple pages. From here you can also set the number of rows on each page as well as customize text to be displayed at each page break.

Figure 20.4. Interface configuration options from 10g iSQL*Plus.

You can also access the System Configuration screen from the Preferences link. The System Configuration screen replaces the System Variables screen in the previous release of iSQL*Plus. It contains three links to configure script formatting, script executing, and database administration. The Script Formatting screen lists the configuration options that affect the way the report is displayed. The Script Execution screen lists the configuration options that affect the way scripts are executed. The Database Administration screen, shown in Figure 20.5, lists configuration options that affect database administration. The options include specifying whether to use default archive log file names during recovery as well as the archive log location for recovery.

Figure 20.5. Database administration options from 10g iSQL*Plus.

You can also access the Change Password screen from the preferences link, as shown in Figure 20.6. From the Change Password screen, you can change your iSQL*Plus password in the same fashion as you can by issuing the PASSWORD clause from the original SQL*Plus screen.

Figure 20.6. Change Password screen from 10g iSQL*Plus.

Privileged users can change the password for other users from the iSQL*Pluspassword screen.