Friday, November 04, 2005

An Oracle Forms Ad-Hoc Query tool

If you are a Forms user, have you ever thought that it would be handy to have an SQL session running within a Forms session to examine data or do some tweaks ? Ever wanted a Forms version of TOAD ? Okay, this doesn't go that far, but it gives a chunk of that functionality.

A few years ago I wrote a form which allowed the user to enter a SELECT statement, hit a button and get the results back in a multi-record block. At the time I needed to see a bunch of data posted by a Form which hadn't been committed (and was throwing an error when it tried to commit). TOAD and SQL*Plus can't help much trying to show uncommitted data from a separate Forms session.

In the years since I've added bits, such as fetching DBMS_OUTPUT messages, and the Form evolved into a pretty handy tool. I say evolved because, if you look at the code, you'll see little evidence of intelligent design ;) . I have tried to put a few comments in, and kept the 'customizable' bits in obvious places.

I've previously posted it in Quest pipelines but now I've set up a blog where I can add small files (thanks alfablog), so I've posted it there as well. There's a Forms 6i FMB file, and a 4 page Word document describing most of what it can do. I have run versions under WebForms (6i,9i and 10g), though the layout needs some tweaking for that.

The blog item with the links is at Tadpole Form
The form can be taken directly from here with the document here.

No plans to actually move my blog at this time, but I will try to monitor both blogs if you want to post comments (bugs, requests for explanations etc.)