Guido van Loon

In the line of great tools for the Oracle RDBMS, yet an other one: Schemester.

Schemester is a database modelling tool for Oracle with the capabilities of reverse engineering a diagram from a live database. It is also capable to find related tables referenced by foreign keys or references made to the table's primary key. I find this tool extremely useful when I have to find my way in an unknown database. And I use it to make prints of part of the database model.

It's a small single executable that's easy to take with you on the job. Not to worry about licences because it's freeware. I've seen Oracle consultants use this tool. It's a shame Oracle doesn't offer this little gem themselves.

I love the Microsoft SQL Server profiler tool. This tool can be used for a lot of things but I like to use it to trace which stored procedures are called from my application and the parameters values that where used. Today I needed something similar for Oracle. Unfortunately this seems not as easy as it is with SQL Server. This is why I don't like Oracle everything is made so difficult...

There is a tool, however, that can trace the sql statements passed to the Oracle RDBMS. It's called OraTracer and can be found on the following website: http://www.aboves.com.

It is not as advanced as the SQL Server profiler tool but then again it's free! Before we get too excited I must point out OraTracer does not trace connections made from ASP.NET or BizTalk. I'm still trying to figure out why. Perhaps it has something to do with these processes not running under the normal user account...

I like Microsoft Sql Server, I like it a lot. But there are times when you are forced to work with an other RDBMS like Oracle. I had to rewrite a stored procedure for Oracle using PL/SQL developer. One cannot do without this tool when working with Oracle! When I write complicated stored procedures for Sql Server I like to write the query as a select statement first. I can declare variables and use them in my select statement so I can easely convert the query to a stored procedure later:

I was looking for a similar approach for Oracle with PL/SQL Developer but had a hard time finding one. After some time fiddeling with declare statements and test scripts I found substitution variables. With PL/SQL Developer, in a Sql window, you can use so called substitution variables. By specifying &deptno in the SQL text, you will be prompted for a value for this variable. You can even set default values by specifying &<name="deptno" default="102">.