Posting Reports from SQL*Plus

It used to be that all you needed to do with a SQL*Plus report was save it into a file, then you had everything you needed. (Or maybe just transfer the file to a web server afterwards.) These days, with blogs, wikis, etc., you might have to be a bit more creative.

For example, I was posting the results of a SQL*Plus report onto a wiki page. At the end of the SQL*Plus script, I had PROMPT commands to state the path of the report file and the URL of the wiki page. Then I'd copy and paste those items so I could open the report in an editor, open and edit the wiki page in a browser, and copy the contents of the file into the wiki edit box.

But if you're just doing the same thing every time, why not automate it? I hadn't made extensive use of the HOST command in SQL*Plus, so I held off a little longer than necessary. Here's the procedure I settled on:

Create a vim macro file that copies the contents of the edited file to the clipboard. (vim is a multiplatform vi clone with extra features like an extensive macro language, record/playback of keystrokes, and integration with features like the clipboard.) The procedure was something like:

vim -w clipboard.vi dummy_file.txt
[Then inside vim...]
"*yG
:q

That produces a macro file clipboard.vi with the keystrokes I did while editing the file. In this case, yanking the entire contents into the special buffer named *, which puts the results on the clipboard, then quitting.

Instead of just echoing the path of the report file, use the HOST command in SQL*Plus to run vim and load the report into the clipboard.

Instead of just echoing the URL of the wiki page, use the HOST command to open the default browser to that page, optionally using the URL that goes straight to the edit form.

-- Comment out the old PROMPT commands.
-- prompt Report is in: long_name_of_report.txt
-- prompt Wiki page is: http://...
-- Replace with fully automated HOST commands.
host vim -s clipboard.vi long_name_of_report.txt
host start http://...

Couple of wrinkles that I ran into along the way...

Although I use vim interchangeably between Windows and OS X, I'm finding that on OS X, the * buffer does not seem to replace the real contents of the clipboard; it gets retained somewhere between invocations of vim, but Cmd-V pastes the previous clipboard contents. Anyway, I use this technique primarily on Windows, where the * buffer really is the system clipboard.

On OS X, you would use 'host open ' to launch the browser, as opposed to 'host start ' for Windows.

I had the Cygwin command START.EXE in my Windows %PATH% where it was being run instead of the default Windows START command. For some reason it didn't like being run via HOST, even though other Cygwin commands like LS worked fine. I renamed it to CYGSTART.EXE to keep it out of the way.

While diagnosing my problem with HOST START, I ran across a blog post by Tanel Poder, who describes the technique of spooling the report in HTML format and then using HOST START to open it in a browser.

When you're using the Wikimedia markup format (i.e. the same as used on the Wikipedia site), you might find that SQL*Plus reports map naturally to the Wikimedia table markup.

When I'm faced with automating a braindead simple editing job, or even a more complicated situation involving searching, moving, and replacing variable amounts of text, my first instinct is to look for a solution involving a vim macro, rather than writing a Perl program. I have vim on all the same platforms as I do Perl, and vim lets me try out the solution interactively and "debug" the sequence of edits more effectively than Perl does.

Sometimes, this approach is looked down on as not "real" programming. I'm curious, what's the most complex or important business process you've ever run as an editor macro (vim, emacs, or other editor of your choice)?