ArtenSUITE Part 16: More SQL Comments

ArtenSUITE
A Development Journal
Part 16: More SQL Comments

In an earlier article I wrote about how I prefix every SQL statement with the Module, User ID and Date/Time. I do this by calling a function which sets these values and prefixes them as a comment to the beginning of the SQL statement.

The reason I do this is that I can view this information from within the application (by logging to a text file and then using a View SQL Window), but also this information is seen when looking at the statements in the database. Unfortunately from Oracle 10g these SQL comments are removed and not visible from within the database.

The way around this is to use the Hint syntax /*+ ... */. As you are not using valid hints they are ignored by Oracle, but not stripped from the SQL statement so are visible within the database. An example is shown below:

/*+ ArtenBASE / SJC / 1909208-11:00 */ select count(1) from bas_con_main

UPDATE: It turns out that using the above syntax causes each statement to be viewed as 'different' by the Oracle optimiser. Therefore any benefits of bind variables and caching are lost. This method can only be recommended for use during pre-production / development.

It seems there isn't a way (or at least, I haven't yet found one) in 10g to tag or comment the SQL statement in such a way that the comment is accessible from the database, as opposed to just within your application. Hence any logging of SQL statements with their associated comments must use the /* ... */ tags and be done on the client side.

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business