MacLochlainns Weblog

I was looking for a cool post to point my students to about overriding the ed tool in SQL*Plus but couldn’t find one. A number of posts showed how to set vi as the default editor in Linux or Unix but none showed how to replace Microsoft Notepad with something else. Instructions for both operating environments are here.

Linux or Unix:

This is simple because all you need to do is open a terminal session and type the following command:

# which -a vi

vi is typically a symbolic link to /usr/bin/vi, and it points to /usr/bin/vim in many cases, like Linux or Mac OS X. You can now add that to your SQL*Plus session interactively by typing:

SQL> define _editor=vi

You can set this in your Oracle Database 10g or 11g home, or in the Oracle Database Instant Client. It is found in the $ORACLE_HOME/sqlplus/admin/glogin.sql file, and example is noted at the end of this blog.

Windows:

This is actually quite easy but different releases of Windows provide different behaviors. Some of those behaviors provide alternatives that don’t work in all Windows releases. The off-beat approaches let you launch the alternate editor but they don’t always edit the active buffer. The ones I’ve chosen to show you should work in all Windows releases, but let me know if they don’t in your environment.

1. Install the editor(s) you want to use. I’ve installed and tested GVIM (a vi editor) and Notepad++ on Windows XP and Vista with Oracle Database 10g and 11g.

2. Add the directory path to these products to your system path. This takes four steps. First, you open your System Properties dialog box. Click the Environment Variables button to set an environment variable.

In the Environment Variables dialog box, you should select the PATH variable from the System variables list. Click the Edit button to change the PATH variable.

Add the following in the Edit System Variable dialog box. You should note that you use a semi-colon to separate path elements in Windows (not a colon like Linux or Unix). After you add the editor path, click the OK button. You can append any number of editors if you’ve got a bunch that you like to use.

You should now click the OK button on the Environment Variables and System Properties dialog boxes in turn. Now you can open any command prompt and type the executable name to run the program, like gvim72.exe.

3. The Oracle Database 10g and 11g expect the executable for the default or override ed (editor) utility exists in the %SystemRoot%\System32 directory, which is the C:\WINDOWS\System32 directory. Copy only the executable, like GVIM.EXE, to the C:\WINDOWS\System32 directory.

4. You can now interactively type the following each time you log into the database at the SQL command prompt:

SQL> define _editor=gvim

Alternatively, you can place that command in the following file:

%ORACLE_HOME%\sqlplus\admin\glogin.sql

It is run each time you login to the database. The file would look like this if you wanted to run gvim as your override editor, which means when you type ed to change the SQL buffer file. The SQL buffer file contains the last SQL statement executed. That file is named afiedt.buf, which stands for A File Eidtor Buffer (debunked by Niall in the comment, the AFI stands for AFI Advanced Friendly Interface). As pointed to by Laurent’s comment, you should change the file extension to take advantage of GeSHi (Generic Syntax Highlighter) for your code.

5. After you’ve done all that. If you’d like to include your USER name and TNS alias, you can run the following command interactively or put it in your glogin.sql script. Caution, this only works for Oracle 10g forward.

SQL> SET sqlprompt _user"@"_connect_identifier>

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

STUDENT@orcl>

The rules for setting the SQLPROMPT aren’t intuitive. You can only use one set of double quotes. In the preceding example, the quotes surround the @ symbol between two SQL*Plus macros, which are the _USER and _CONNECT_IDENTIFIER. There’s no magic in that symbol and you can replace it with another. When you want text before, in between, and after a macro or two, you surround the whole thing with double quotes, and allow a white space before macros or use single quotes around string literals.

The white space example works like this:

SQL> SET sqlprompt "SQL: _user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL: STUDENT at orcl>

The nested single quotes example works like this:

SQL> SET sqlprompt "'SQL:'_user at _connect_identifier>"

This sets the SQLPROMPT to the following for a user account named STUDENT at the standard orcl TNS alias:

SQL:STUDENT at orcl>

The single quotes around the SQL: lets you remove the space between the colon and user name. I’ve never seen a way to control case for the macro return values but there may be one. Perhaps somebody will add a comment about it. If you put more than two double quotes in the descriptor passed to SQLPROMPT environment variable, SQL*Plus raises an SP2-0735 error.

6. Here is a sample of the glogin.sql file:

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
--
-- Define the override or default editor.
define _editor=gvim
-- Set the edit file to allow GeSHI highlighting.
SET editfile=afiedt.sql
-- Set the SQL*Plus prompt to show user and TNS Alias.
SET sqlprompt "'SQL:'_user at _connect_identifier>"

Hope this helps a few folks stuck with Windows as the operating system for Oracle.

We’ve got an implementation of Blackboard running on Oracle 10g. The new version has a grade book redesign that uses AJAX to eliminate the cumbersome nature of its predecessor. It was interesting figuring out why grades entered wouldn’t show without restarting Internet Explorer, while Firefox and Safari worked fine. Another heart found thank you to Microsoft for failing to optimize JavaScript execution in their browser (all the way through to 8). Read on if you must use IE.

The issue is that Internet Explorer reads JavaScript including all whitespace at initial page load. There’s no attempt to compress it, arghhh. Naturally, that makes performance crawl a bit on initial page load. That wouldn’t be bad but Blackboard’s JavaScript changes DOM values during product use. Those changes go unseen if you navigate from the Grade Book and return to it. At least, they do when you’re using default IE settings. Unlike Microsoft’s solution to the problem of suppressing Mac purchases (an advertising gem from Apple), Microsoft didn’t spend more on marketing (after all the Windows platform is more or less captured by IE). They simply set the default value for page refreshes to automatic, which means once per active IE load into memory for JavaScript files. Then, it is Blackboard’s fault for how it implemented AJAX, right?

You can fix this default by taking one step. Open IE, navigate to Tools, Internet Options, and then click the Settings button in the Browsing history section. The form looks like:

When you click on the Settings button, you’ll come to the following screen. Click the radio button for “Every time I visit the webpage,” which will ensure you get a working grade book in Blackboard.

Click the OK button. You’re not done yet. You must shut down all IE sessions and re-launch the product for the changes to occur. If you think that was tedious, here we need to do it every time our corporate governance folks push an update to Windows because they reset IE to an incompatible set of values for Blackboard’s AJAX implementation.

I think one annoying error that I’ve suffered with on Microsoft Vista with Oracle 11g is the nmefwmi.exe error. I got a message from Microsoft saying it’s fixed. They refer to Oracle Bug #6596234, but it’s really fixed in the Oracle 11g patch (11.1.0.7.0). Therefore, don’t waste your time looking for a one-off patch on metalink.

You can ignore this message on Vista that says refer to Bug #6596234 … by the way it’s actually under patch #6890831. Only the Linux and Solaris ports are available as of today.