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.)

4 comments:

Chris Farmer said...

I had to change this line in the PACKAGE BODY SQL_PROCESSING to get it to compile

PROCEDURE execute_statement (p_type IN VARCHAR2 default g_type);

forms 10g ( 9.0.4 ), Other than that, thanks for a nice little tool to add to my every expanding sql*plus replacements.

Anonymous said...

Thanks my friend, for this nice tool..!

Gary Myers said...

Code now located at http://www.sydoracle.com/Codespace

Nikki Hilton said...

Thanks for update Gary, util_geocode.pkb download link not working properly can fix that one.

Query Forms