I am a Siebel developer when I am not doing anything else.

And, since most of the times I don’t do something else, and at those times I cannot gainfully employ myself doing Siebel work,  I end up doing something stupid. No, not any action remotely related to the song by Frank Sinatra. But write some useless tools that no one should use.

So, here comes the “Quick Query Tool”.

Problem #

Being an application developer, whether you like it or not, I care about the application more than the database that the application relies on.

My concern with the database is in all glory when making changes to the data model, getting data from database and comparing with the front-end, and updating data.

During development of Siebel applications these database operations becomes a vital tool to find out how things look in the back-end.

There are more than a few things that can go wrong, and all of us appreciate the need to get data, compare, and be done with it.

Solution #

The solution is of course simple.

All I need is TOAD.

Or, SQL Developer for people like me who will not have anything to do with tools that require hard dollars.

Unfortunately, for “people like me”, there are plenty of environments that restrict the tools that can be used. Database tools are deemed secondary.

I end up using SQLPlus - the excellent command line tool that does nothing but executes queries.

Don’t get me wrong here - I do take pride in my command line abilities. But frankly, SQLPlus sucks for any operation that requires you to work with more than 4 fields in one SQL.

One of my colleagues got me the same problem. And, I thought the solution was simple enough. Just use MS Excel / MS Access - the two default programs that no organization dares to oppose.

Both Excel and Access have wizards that can connect to popular databases through Microsoft/Thirdparty drivers. Since they are quite easy to copy/paste from, it makes my job of comparing DB with application so much easier.

Support for Database Access from Excel/Access #

There is no problem with accessing the database if the size of the table is less, structure is simple enough, and you have a fast enough connection to the database.

If anyone of these things are lacking, you are in for a rude shock.

Query Builder, which looks straight from a 80s desktop, takes for ever to load up. And when it does load, it takes for ever when you click on anything other than “close” button.

The database connection is created each time, and I was never able to get the data in Excel to do anything productive.

Quick Query Tool #

As I mentioned in the beginning of the post I look at tools that can waste my weekend time. The “Quick Query Tool” was born because I had nothing better to do.

Quick Query Tool is a simple macro. The tool is *really* simple -

• Establish a ADODB connection to Oracle database. User id, passwords, database name can be stored in the worksheet
• SQL can be stored in the worksheet
• Replace the parameters in SQL with the parameter values
• Execute the SQL
• Get the results in the Excel sheet

Don’t tell me that I did not warn you - it is really stupid to do this. Isn’t it?

You will see here that it is kinda helpful for Siebel developers.

You easily plugin the Siebel query, input the parameter values (this is “:1”, “:2” etc. - Siebel still does these strange things), and see the results in the worksheet. Since the database connection will not be closed after execution, the query results become faster after the first query.

Of course there is a limitation to how many records you can bring - hard-coded in the worksheet. You also cannot insert/update from this worksheet. There are too many things that can go wrong while doing updates, and I am not that jobless to code this in.

As a bonus with this tool you get to save the commonly used SQL statements, and get “contextual” help to use the tool effectively. Ha ha..!

For what it’s worth you will find the “Quick Query Tool” workbook in sourceforge. Download, play around with the macro, and know more about “how not to solve a problem”. You can also easily change the connection to use any other driver and connect to DB2, or any such legacy databases(!) as well.

Download from SourceForge

Find something fishy, strange, or helpful about this post? Comment!