Starting iSQL*Plus | Browser-Based SQL*Plus

Table of contents:

Which Port to Use?

Starting i SQL*Plus and connecting to a database is relatively easy. The most difficult part is to remember the URL to type into your web browser.

3.1.1 Starting the iSQL*Plus Server

If you're the DBA, you may need to worry about starting the i SQL*Plus server. The concept is similar to starting an Oracle Net listener. The i SQL*Plus server is what clients running web browsers connect to when they enter the i SQL*Plus URL into their web browsers. Figure 3-1 illustrates.

Figure 3-1. iSQL*Plus architecture

figs/sqp2_0301.gif

To start the i SQL*Plus server in Oracle Database 10 g on a Unix or Linux system, log in as the Oracle software owner and issue the isqlplusctl start command:

oracle@gennick02:~>

isqlplusctl start

iSQL*Plus 10.1.0.2.0

Copyright (c) 2004 Oracle. All rights reserved.

Starting iSQL*Plus ...

iSQL*Plus started.

Once you've started i SQL*Plus on your server, clients will be able to freely connect and use i SQL*Plus via their web browsers. You can use the isqlplusctl stop command to shut down the i SQL*Plus server, although you'll normally want to leave i SQL*Plus running for as long as your database is open .

To start Oracle Database 10 g 's i SQL*Plus server under Windows, go to the Services control panel, look for the Oracle service with a name ending in "iSQL*Plus", and start that service.

You can also use isqlplusctl start to start the i SQL*Plus server under Windows. Issue the command from the Windows command prompt. You may need to leave open the command-prompt window from which you issue the command, but I did not find that to be the case when running Oracle Database 10 g under Windows XP. You may want to investigate the use of the Windows start command, as in start isqlplusctl start .

In Oracle9 i Database, iSQL*Plus starts and stops along with the HTTP Server. Under Unix, use the commands apachectl start and apachectl stop . Under Windows, use Apache -k start and Apache -k shutdown .

If you're using the Oracle9 i Database version of i SQL*Plus, and you start the HTTP Server while logged in as the Oracle software owner, then any user connecting through i SQL*Plus will be able to connect as SYSDBA. Thus, you should start HTTP Server as some other user.

3.1.2 Using a Supported Browser

To connect to a database through i SQL*Plus, you need to be using a web browser that supports HTML 4.0 Transitional or higher. Oracle's i SQL*Plus documentation specifically mentions Netscape Navigator 4.7 or later, and Microsoft Internet Explorer 5.0 or later. These are the browsers Oracle officially supports. However, you should be able to use any reasonably current browser. I've had good luck using the Opera web browser Version 7.23, Mozilla 1.4, and Konqueror 3.0.3.

3.1.3 Connecting to a Database

You start i SQL*Plus in your web browser by entering a URL. In Oracle Database 10 g , the default URL takes the following form:

http://server.domain.com:5560/isqlplus

Thus, to connect to i SQL*Plus running on the database server named gennick01. gennick .com , you would enter the following URL:

http://gennick01.gennick.com:5560/isqlplus

5560 is the default port number used by i SQL*Plus in Oracle Database 10 g . Your DBA may have changed that port number. If you have doubts about the URL, ask your DBA. If you are the DBA, see the sidebar "Which Port to Use?".

Upon entering the correct URL, your browser will display the i SQL*Plus login page shown in Figure 3-2, which has been filled out to connect to a database through the net service name db01 . From here, you have two choices:

You can enter only a username and password to connect to the default database. Be aware that this default database is the one specified for i SQL*Plus on the server. If you have specified a default database at the client level, that has no effect here.

You can also enter a connect identifier , usually a net service name, to connect to some other database. If you use a net service name, that net service name is resolved on the i SQL*Plus server, not on your local machine.

The term connect identifier is synonymous with connect string . Oracle uses connect string in some of its documentation but uses connect identifier on the i SQL*Plus login page.

Figure 3-2. The iSQL*Plus login page

figs/sqp2_0302.gif

After entering your login information, click the Login button. You'll be logged in to your database and presented with the i SQL*Plus Workspace page shown in Figure 3-3.

Figure 3-3. The iSQL*Plus Workspace

figs/sqp2_0303.gif

Several things are worth noticing about this page:

The biggest element on the page ”you can't miss it ”is the text box into which you type SQL statements, SQL*Plus commands, and PL/SQL blocks.
Just above and to the right of the text box is a recap of your login information, minus the password.
There is a Logout button that you might mistake as being part of the page header. To log out, click the round icon with the key at the upper right of the page.

For the most part, operation of i SQL*Plus is fairly simple. Type a command, statement, or block into the text box and click the Execute button.

Which Port to Use?

As I mentioned, the hardest part of using i SQL*Plus might be remembering, or figuring out, the correct URL and which port to use. The following information might be helpful:

In the beginning, in Oracle9 i Database Release 1, the default port was 80, and you connected using a URL in the form http://server.domain.com/isqlplus .
Oracle9 i Database Release 2 changed the default port to 5560, and you used a URL such as http://server.domain.com:5560/isqlplus .
Using Oracle9 i Database, you can determine the currently used port number by looking in $ORACLE_HOME/Apache/bin/conf/httpd.conf (Unix/Linux) or %ORACLE_HOME%ApacheApacheconfhttpd.conf (Windows).
Using Oracle Database 10 g , you can determine the currently used port number by looking in $ORACLE_HOME/oc4j/j2ee/isqlplus/config/http-web-site.xml .
Using Oracle Database 10 g , if you (or your DBA) specify a non-default port number during installation, that port number will be recorded along with several other important port numbers in $ORACLE_HOME/install/portlist.ini .

3.1.4 Connecting as an Administrator

In Oracle Database 10 g , you can use a special URL to connect to i SQL*Plus in the SYSDBA or SYSOPER administrative roles. You can then proceed to use i SQL*Plus for administrative tasks such as starting and stopping your database.

When you connect to i SQL*Plus as a DBA, you must authenticate twice, as in the following process:

Enter a special URL that brings up an i SQL*Plus DBA login dialog.
Enter a username and password to identify yourself to i SQL*Plus as a DBA. This username/password has meaning only to i SQL*Plus and is unrelated to your database username/password.
i SQL*Plus takes you to a special version of the login page that includes a pull-down menu for choosing between SYSDBA and SYSOPER.
Authenticate to your database using your database username/password.

Before you can use i SQL*Plus as SYSDBA or SYSOPER, you must create a special username and password to use in the first step. This username and password is tied to the specific instance of i SQL*Plus to which you connect. Thus, if you connect to i SQL*Plus on gennick01.gennick.com to reach a database on gennick02.gennick.com, you must have an i SQL*Plus DBA password on gennick01.gennick.com.

3.1.4.1 Creating iSQL*Plus DBA users

To authenticate you as a DBA, i SQL*Plus uses a Java Authentication and Authorization Service (JAAS) provider named Java AuthoriZatioN , which Oracle calls JAZN. You must create a username/password within JAZN in order to use i SQL*Plus as SYSDBA or SYSOPER.

You can store i SQL*Plus DBA usernames and passwords within the Oracle Internet Directory. For details on doing that, refer to the chapter "Configuring SQL*Plus" in the Oracle SQL*Plus User's Guide and Reference (Oracle Corporation).

To create a JAZN user for i SQL*Plus, use the JAZN shell. Follow these steps:

Log into your database server as the Oracle software owner, or as a user having the DBA role.

Create a JAVA_HOME environment variable:

oracle@gennick02:~>

export JAVA_HOME=$ORACLE_HOME/jdk

You must invoke the JAZN utility from the correct directory. If you fail to do so, this process will also fail. Set your current working directory as follows :
```
oracle@gennick02:~>

cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
```

Invoke the JAZN shell for the "iSQL*Plus DBA" realm as the admin user using the following, rather horrendous command. Type it as one, long line. Type it exactly as shown, but supply your own password if you've changed it from the default of welcome :

oracle@gennick02:/oracle10g/product/10gr1/oc4j/j2ee/isqlplus/application-

deployments/isqlplus>

$JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_

HOME/sqlplus/admin/iplus/provider -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user 

"iSQL*Plus DBA/admin" -password welcome -shell

Issue an adduser command to create a new, i SQL*Plus DBA user. The following example creates a user named dude with a password of secret :
```
JAZN:>

adduser "iSQL*Plus DBA" dude secret
```

Grant the webDba role to the user you just created:

JAZN:>

grantrole webDba "iSQL*Plus DBA" dude

If this is your first time using the JAZN shell, change the admin password to something other than the default. The following example changes the admin password from welcome to secret :
```
JAZN:>

setpasswd "iSQL*Plus DBA" admin welcome secret
```
Exit the JAZN utility:
```
JAZN:>

exit
```

In order for i SQL*Plus to see the new JAZN user you've created, you'll need to bounce i SQL*Plus by issuing the command isqlplusctl stop followed by isqlplusctl start .

3.1.4.2 Authenticating to iSQL*Plus as a DBA

To access the DBA login page, append /dba to the URL you normally use to invoke iSQL*Plus. For example, if you normally use:

http://gennick02.gennick.com:5560/isqlplus

then to log in as a DBA, you would use:

http://gennick02.gennick.com:5560/isqlplus/dba

Entering the DBA URL should result in the login dialog shown in Figure 3-4.

Figure 3-4. Authenticating to iSQL*Plus as a DBA

figs/sqp2_0304.gif

Enter the username and password you created from the JAZN shell, and you should be taken to the login page shown in Figure 3-5. This login page will have an additional field, a pull-down menu from which you can choose to connect as SYSDBA or SYSOPER.

Figure 3-5. Logging in to Oracle as SYSDBA

figs/sqp2_0305.gif

Upon logging in to your database as SYSDBA or SYSOPER, you'll be taken to the same i SQL*Plus workspace page shown in Figure 3-3. The difference is that you will be able to execute commands such as STARTUP and SHUTDOWN.

Upon logging out of an i SQL*Plus DBA session, be sure to close your browser, unless you plan to immediately connect again in either the SYSDBA or SYSOPER role. Once you authenticate to i SQL*Plus as a DBA (as in Figure 3-4), you remain recognized as a DBA, and you retain the ability to choose SYSDBA and SYSOPER as long as your browser remains open.

3.1.5 Ending a Session

Figure 3-6 illustrates two ways to end an i SQL*Plus session. You can click the Logout button in the upper right of the page, or you can issue the SQL*Plus DISCONNECT command.

Figure 3-6. Disconnecting from an iSQL*Plus session

figs/sqp2_0306.gif

Clicking the Logout button returns you to the login page. Issuing the DISCONNECT command leaves you in a position to enter further SQL*Plus commands but neither SQL statements nor PL/SQL blocks. Frankly, I don't see much value in being able to issue SQL*Plus commands in i SQL*Plus while not connected to a database.

You can follow a DISCONNECT command with a CONNECT command to create a new database connection. However, if you're going to do that, you might as well enter the CONNECT command to begin with and skip the DISCONNECT step.

Introduction to SQL*Plus

Command-Line SQL*Plus