Oracle GUI Clients

If you search for Oracle GUI Clients in Google, you turn up with nothing. After some more hunting, I found TOAD. I have been using the Free version of TOAD for a few weeks now but it is very annoying and has to be updated often. Also it can only make one connection at a time. The price of the full version > 800$!!
That’s an outrage, especially for us MySQL folks who can get SQLYog for free (which works perfectly well in Linux with wine).

A few days back I installed Ubuntu 7.04 on a new computer at work and was hunting for an Oracle client and I found TOra, an open source Oracle Client. I still haven’t got it connecting to Oracle from Ubuntu and the Windows version won’t even run. Hunting for ways to make it work, I found SQL Developer – which is made by Oracle themselves and is free. If only Oracle would have mentioned this on the download page for InstantClient, it would have saved me a few hours of searching, tinkering and complaining about a lack of choice in Oracle clients.

SQLDeveloper is a cross platform Java Oracle client and it is free and does everything that TOAD does as far as I can tell. No more TOAD for me.

Gripe 3, 4 with Oracle – concat + magic quotes

Concat:

Most of the database queries I run at some point are used to generate HTML and rather than go through the results of the query and add HTML to it in PHP or Ruby code, I like to get this out of the query itself.
So in MySQL my queries often look like this:

select concat(‘<a href=pagename.php?varid=”‘, varname, ‘”>’, varid, ‘</a>’) as varlink, …..

However for the same result in Oracle, which only allows two items in the concat function,
this turns into:

select concat(‘<a href=pagename.php?varid=”‘, concat(varname, concat(‘”>’, concat(varid, ‘</a>’)))) as varlink, …..

If there are multiple such items, the query becomes extremely ugly with too many brackets to keep track of.

Magic Quotes:

My next complaint about Oracle is no magic quotes – string values must be in single quotes, no double quotes allowed.

Gripe 2 with Oracle – Additional Columns in Group By

In the MySQL world, I can do this:
select a, b, c, max(d) from e group by a

In the oracle world that results in:
ORA-00979: not a GROUP BY expression

To overcome this, you guessed it, nested queries again:
select a, b, c, d from e, (select a, max(d) as d from e group by a) e2 where e.a=e2.a and e.d=e2.d
(There might be potential problems with this but that is beyond the scope of my gripe 🙂 )

Gripe 1 with Oracle – Pagination with queries.

Oracle 10g logoTwo months ago I moved to the Department of Procurement Services at the University of Virginia. Now I get to use Oracle for the first time and it is an adjustment being used to MySQL. I had high expectations from Oracle hoping my life as a Web and Database Application Developer will be easier. So far dealing with Oracle hasn’t been fun.

First order of business was to create pagination for some database results. Being used to the very convenient limit m,n of MySql, I set out to find the equivalent in Oracle – oops! – there is none. So I go about searching and find this – On ROWNUM and Limiting Results – a bunch of inconvenient ways to achieve the same result. Even Postgres has Limit n, Offset m. Come on Oracle – it is high time to implement this – three deep nesting is not nice for ordered pagination.