Tony’s Oracle Tips

August 26, 2008

The new campaign – Oracle Documentation Improvement

Filed under: Uncategorized — tonyhasler @ 10:07 pm

Ok, Its time to take some action. The state of the documentation has been changing over the last few releases. On the one hand the outward appearance of the doc. set has improved. On the other hand the content seems to have deteriorated.

As I often do I will go off on a tangent before I have even started. Actually, the 11g documentation seems to have serious problems with IE7. Apart from the need to configure IE to allow active content, there seem to be cases where hyperlinks send you to the top of the page (instead of the relevant part) and also cases where CTR-F searches cause IE to hang.

However, the biggest issue is with the content, rather than the presentation of the documentation. In fact, the biggest whoppa that I have discovered to date is the javadoc for the JDBC API. This entire document seems to have been omitted entirely from the doc. set. It is available on OTN at:

However, there isn’t even a reference to it that I can find in the docset.

Anyway, to give credit where credit is due I have once sent feedback to Oracle to correct an error and on that occasion the error was swiftly corrected.  The error related to the 10.2 feature that allowed you to drop a datafile.  The documentation stated that you couldn’t drop the last file.  It should have said the first file.

I am trying again.  Before embarking on the JDBC Developer’s Guide and Reference (which has tons of issues) I am trying out the Data Warehousing Guide.  Chapter 22 covers the MODEL clause in SQL and there is a mortgage amortisation example that doesn’t work.  I have told them what the correction is.  Let us see what happens.

In the meantime, if you know of a problem please:

a) Check the 11g documentation and make sure it hasn’t already been corrected

b) let them know by e-mailing

c) Post a reply here.  I am keen to understand the scale of the problem.

August 11, 2008

SQL Puzzle – 1 – Paul and Sam

Filed under: SQL Fun — tonyhasler @ 10:28 pm

I returned from vacation to be set a logic problem by a colleague.  He suggested that though it is possible to write a program in SQL to solve the problem it would, in fact, be quite tricky to do so. Well that was a challenge I couldn’t pass up!
As it turns out my solution is a sterling example of how otherwise complex problems can be significantly simplified by two highly underutilised features of SQL: factored subqueries and analytic functions.  The COUNT function is an aggregate function that can be used as an aggregate function with the appropriate use of additional syntax.

Anyway, if you want to have a go yourself here is the problem:


There are 2 integers, a and b, between 2 and 100, with a<=b.

Paul is only told their product (a*b), and Sam is only told their sum (a+b).

When Paul and Sam first meet each other, the following conversation is overheard.

Paul: I don’t know what the numbers are

Sam : I knew that. I don’t know what the numbers are either

Paul: I do now!

Sam : So do I!

So what are the numbers?

if you would like to see one solution see the next page

Blog at