Re: Oracle complex SQL query development tools?

From: Michael Krolewski <vandra_at_u.washington.edu>
Date: Tue, 25 Aug 1998 23:54:44 -0700
Message-ID: <35E3B133.4B0A8C2F_at_u.washington.edu>


There are several tools.

I saw TOAD mentioned. It is very good for freeware

A better tool is SQLNavigator by ??? It is around $800 per developer. My personal experience in using it, $800 is cheap if you are doing real work.
It is reasonably robust and complete. It is very good at looking at the schema, etc; creating and modifying the schema and developing PL/SQL

If you want the manufacturer, write me at mikkro_at_hbsi.com. I will forward it.

Jacqui Caren Jacqui Caren wrote:

> I have been looking for a few weeks for a tool to
> assist one of our developers in the construction
> of large multi table select queries. I have to
> admit total defeat here :-(. I found some tools
> but none of any quality or "fitness-for-purpose".
>
> Overview
> --------
> The developer makes extensive use of decode etc
> and (select) queries often fall into the 1000's
> of lines. He spends a large amount of time
> manually designing these and a (good) tool to
> allow rapid design and modification of complex
> queries would produce a reduction in his stress
> levels - which would be good for all :-)
>
> Commercial and free/shareware suggestions are
> both appreciated. Please don't tell me that such
> large complex queries are bad practise
> - the size and complexity of the queries are
> due to application realm (large complex
> reports against telecoms call/billing data).

Actually you are problably doing too much decode ifthe sql is 1000's of lines. Table driven selection is
what you need. -- I have written telecom reporting. Billing is basically still billing.

In addition one can develop your own functions to reduce the apparent complexity of any particular field. In the case of complex billing amounts ( first n seconds on rate, next m seconds another, if throug one carrier versus another, etc), PL/SQL is the best way to go. You can extend the system by using the PL/SQL tables to assist creating tables without continually requerying the system.

Similarly PL/SQL is available to support selection and integration of multiple tables into a composite.

My biggest challenge was the system was 24X7 and the database had a shorter lifetime before backups. Restarting the database and detecting the need for a restart was somewhat challenging. Think of the all the possible places the system is in when shutdown occurs?!

Mike Krolewski

> - I am the wrong person to try and convince :-)
>
> If I find nothing of value I will probably end up
> writing something in perl/DBI/tk - based on prior
> developed software, but this would be a act of
> last resort...
>
> Thanks,
> Jacqui
>
> p.s.
> If people respond by email I will summarise
> back to this group...
>
> --
> Email: Jacqui.Caren_at_ig.co.uk http://www.ig.co.uk/
> Fax : +44 1483 419 419 http://www.perlclinic.com/
> Phone: +44 1483 424 424 http://www.perl.co.uk/
> Paul Ingram Group Ltd,140A High Street,Godalming GU7 1AB United Kingdom
Received on Wed Aug 26 1998 - 08:54:44 CEST

Original text of this message