Wednesday, July 28, 2010

Methods, questions and answers.

Today is one of those days when you've got a couple of recent blog posts which intersect beautifully.

Cary Millsap is here discussing why the right method is more important than the right answer.
Steven Feuerstein inadvertently opened up a can of worms regarding the seemingly simple task of extracting a list of a user's tables from the data dictionary.

The fallout from Steven's question in the PL/SQL Challenge is that the term 'table' is fuzzy and has become fuzzier over the years. Views, object tables, XML tables, Materialized Views, tables that have been dropped but are sitting in the recyclebin. The various answers (except for one) offered for that question in the PL/SQL all worked in SOME circumstances, but not necessarily all circumstances.

You can appreciate the confusion if you have had to explain to someone that, if you grant privileges on a procedure, the data dictionary shows that in USER_TAB_PRIVS.

Personally, I'm more attracted to DBMS_METADATA.GET_DDL these days. I just wish there was a (built-in) function that took the object name (and optionally schema) and worked out the object type for itself.

Anyway, the only thing I can add to the discussion is that testing is the process of proving that your method is applicable to a defined set of situations. The wider the testing, the more situations you can be confident of being addressed successfully.

In pure mathematics and some scientific disciplines, you can devise theoretical proofs that prove the wider applicability of a method without individual testing. That's harder in implementation. Maths can prove that multiply X by two, then divide by two, you get X as the result. In a physical implementation, you might have to deal with rounding imprecision or maximum permitted values or buffer overflows....

No comments: