Oracle iSQL*Plus Overview

This post is a bit different from my usual ones. It's specifically aimed at classmates in one of my classes I'm taking right now where we are learning Oracle Database. I found out the school has an iSQL*Plus web access license and it's publicly accessible (so you can use it from home), so anyone can use it any time.

iSQL*Plus is a web interface to SQL*Plus. Pretty much everything will work in iSQL*Plus that will work in SQL*Plus, but not quite everything.

Login and Use

 Logging in to iSQL*Plus is similar to logging in to SQL*Plus, except you use your web browser. You'll navigate to the iSQL*Plus web server and enter your username, password and connect identifier (if applicable, for the school iSQL*Plus server it is). It works with any browser. I've used it in the latest releases of Chrome, Firefox, and Opera with no problem. The login screen looks like this:

Once you've logged in, you'll be presented with a nice web interface. Here's me executing a simple command:

 As you noticed, by default it'll display the output as standard HTML of your commands below the enter field. Also by default it'll paginate the output, so you only see a number of lines at a time, 24 by default. This can be changed in preferences, but first on the main workplace UI:

Your SQL Commands stay in the box until you clear it by either manually deleting it or using the "Clear" button.

The "Execute" button executes all the commands in your textbox, by default showing the output below.

The "Load Script" button will take you to a page where you can browse for a *.sql file to upload. After you've located it, press the "load" button and it'll put the contents of the file in the text box. It's kinda pointless since you can just copy and paste the contents faster most of the time.

The "Save Script" button will auto-generate a file called "myscript.sql" and present a download prompt, which is pretty nifty.

The "Cancel" button will terminate any running commands, useful if your commands are being unresponsive.

You may notice a "History" tab on the top-right corner. This will store a history of your last few executions of commands. You can then reload them, which is useful if you accidentally hit the "clear" button. Do note: the History is cleared upon logout.

Preferences

 Preferences are where you can change various default settings. Most notably are:

In Interface Configuration:

1. The amount of scripts to save in "History" from the current session (by default 10).
2. The default size of the script input text box (pointless in modern browsers that let you expand it yourself)
3. Whether to display the output below the text box or generate a downloadable html file (this is the closest equivalent to spooling, which isn't available in the iSQL*Plus interface)
4. Option to have everything display on one page or multiple pages (and set the number of lines per page)

In Script Formatting:

1. Option to have line numbers (kinda nifty)
2. Option to display commands in the output by default (equivalent to running SET ECHO ON at the top of every execution)

There are many other options too. Here's Oracle's page on iSQL*Plus preferences and the equivalent SET commands

Differences Between iSQL*Plus and SQL*Plus

Most things in SQL*Plus will also work in iSQL*Plus, as I've noted. One notable exception is spooling. If you try to spool you'll see this:

spool on 
SP2-0850: Command "spool" is not available in iSQL*Plus 

Any other command that can't be run will similarly kick up an error like that and the rest of your script will execute properly. Spooling doesn't work because, obviously, you cannot set a save location on the server from your web browser. that'd be a security nightmare! The closest equivalent is to have the output generate an isqlplus.html file instead of displaying the command results below the text box.

By far, my favorite feature is that you can edit multiple lines very easily in iSQL*Plus and recall history. It's much nicer in that respect than SQL*Plus is. Likewise, the commands don't disappear after execution, so if you made a small mistake, it's easy to fix and re-execute. Very nice.

The other nice thing is any user can set preferences for many set commands, which can save you time.

The main downside is that there are no TNS alias, so your connect identifier has to be the full, proper connect identifier normally contained in the TNS.ora file

You should also be careful of something: iSQL*Plus doesn't care if you don't end SQL commands with a semicolon. If you plan on using this elsewhere or submitting it, you'll need to make sure you include them, as otherwise it may not work for someone else.

One thing to note is that iSQL*Plus has a pretty aggressive timeout/auto-logoff setting. So if you just leave it open for some time, you'll probably be forceably be logged out, so do make note of that.

Wrap-Up

Well, that's pretty much all there is to iSQL*Plus. Just make sure to log out. It's a great way to practice your SQL off-campus.