Upcoming Presentation

G'day All,

I'll be presenting in Melbourne soon - I'll be in town for a few days and hopefully making best use of my time!

My topic will be Conditional Compilation - something touched on briefly during Steven Feuerstein's recent visit.

Where: VicUrban, Level 12, 700 Collins Street Docklands, 3008 (Beside Southern Cross Station)
When: Tuesday, September 08, 2009 05:15pm - 06:15pm
RSVP: RSVP to president.vic@ausoug.org.au to advise your attendance by close of business on 07-SEP-2009.

If you're from Melbourne, have a interest in PL/SQL, I'd enjoy your company.

Syntax Highlighting for SQL

Today I decided I can be a choosey chap.

Unhappy with the basic <pre> syntax for pre-formatted text such as my SQL queries and output, I was on the hunt for a Syntax Highlighter to suit my satisfaction.

Part of this search entailed reviewing how other bloggers do it, but considering I read most of my feeds through Google Reader, I don't see as much of the magic.

I like the way Laurent Schneider formats his, but I think it may be internal and cumbersome.

I found a bunch of reviews here.

A common/popular one seems to be Syntax Highlighter by Alex Gorbatchev. I've applied this to my blog although I'm not entirely happy with it.

I don't know if it's my penchant for SQL*Plus, or my softer choice of highlight colours in TextPad, but I don't like the colour choice of the strings or certain keywords - I mean, pink for aggregate functions? I had a look at the source and it doesn't seem immediately obvious how to customise these. I need the .js/.css files externally hosted anyway because I can't host them in blogger nor Google Docs.

It looks cleaner without the line numbers - I think I'll only add those when I want to mention particular lines.

It also comes out quite small in Google Chrome - a browser I find myself using more and more - can't wait for XMarks to be available in Chrome.

I also tried Google Code Prettifier, althought it seemed to have minimal effect on SQL bar drawing a box around my code.

I don't know if I'm being picky or if there just isn't much out there for SQL. If anyone has any suggestions, I'd more than welcome them.

2009 AUSOUG Conference - Call for Papers

Hello Oracle Community,

The call for papers has been re-iterated for this year's AUSOUG 20:20 Foresight National Conference Series.

Always a good opportunity for networking, learning and a few fun & games, this year's conference series is ready for abstract submissions from young & old.

If you've never written a presentation before, I highly recommend it. If you have, then get cracking on the next one! I've (hopefully) got two great topics up my sleeve this year and I look forward to the experience.

The 2009 dates are:

• Perth from 10-11 November at the Burswood Convention Centre
• Melbourne from 16-17 November at the Sebel Albert Park Hotel

Visit www.ausoug.org.au/2020 for all the latest information.

Hope to see you in Perth and/or Melbourne!

Steven Feuerstein Perth Review - Session 2 - Writing Maintainable Code

Steven's second session remained light on for technical details, which was great for a Friday morning. He dealt with a topic that can be applied to any language and one that every developer faces on a day to day basis - the balancing act between deadlines and writing maintainable code.

As I write this I remember the old adage from Microsoft Secrets, and probably numerous software engineering books - every step a bug makes towards production, the exponentially more expensive it will be to fix.

Steven mentioned a few basic mantras and elaborated on each. I think this was well received by the audience and it always makes a difference to hear the argument from authority.
Without elaborating too much, some I managed to note down include:

• Construct and enforce coding standards - even to the extent of when to utilise SQL. Steven pointed us to a set of standards he follows and has put out for public comment.
• Personally, I'm in favour of using hungarian notation, including the datatype in the prefix of a variable, eg: ld_dob DATE;
• - Utilise your development tool's template facilities. Whether it be Toad, SQL Developer, Textpad... they have have facilities to generate say a package with all the stubs, exception handlers, comment headers etc you need to avoid the White Canvas of Death, as he calls a blank .sql file. This helps with the enforcement of coding standards.
• Encapsulate "stuff" to make it more readable
• define a package for constants, and don't hardcode ANY value. If there is a slim chance in 100 years it could change, it should be defined somewhere. This also goes for formulas such as a full name evaluation from numerous fields.
• Use pragma exceptions, so we see pkg_exceptions.e_collection_empty instead of -22166
• Use PL/SQL APIs to each table (elaborated later in this post)
• If you ever say Never or Always, it's a bug. Tom Kyte always says ;-)
• Boolean functions read best when they return a boolean expression such as
  RETURN (this='TEST');
as opposed to
  

  if this = 'TEST' then
  return true;
  else
  return false;
  end if;

• Functions in general should work like a funnel. This way you can avoid ORA-06503 - Function returned without a value. PL/SQL warnings can help with this. Steven's words with this comment obviously help describe what a funnel looking function looks like!
• Always (is this a bug?) return a subtype - never a specific datatype, ie: VARCHAR2(100)
• Visit Refactoring.com
• Use instrumentation/assertions. (Hint - I'll be elaborating on this later in the year)
• And the fastest way to get the job done is to take your time. Don't rush. Mistakes are made when people rush, and experts from any industry will tell you that if you take your time, the job will get done quicker with fewer mistakes. The best analogy I heard on this was (I think) from a lecturer at uni -
  "Two carpenters were asked to make the same cabinet. The first carpenter, and old man, spent the first 2 hours thinking - studying the plans, laying out his tools.
  The second carpenter, an apprentice, (put his cowboy hat on) and got cracking straight away - sawing wood, drilling holes, occasionally swearing and looking for his hammer. He looked over at the old man thinking (chuckling), crikey, I'm halfway through this cabinet already and he's still thinking about the job!
  The old man then starts making the cabinet. All the relevant bits of timber were within arms reach as he built the cabinet, every tool never more than a step away. Actual construction of his cabinet took 30 minutes. Meanwhile, four hours in, the apprentice is still putting 'finishing touches' on his cabinet (working out how to put the doors on straight).
  The old man beamed at his work. Every join flush, every screw lined up. When the apprentice finally finished his, he figured he'd do better next time. Practice makes perfect."
  

As for coding practices, I had a flick through the book Steven was giving away on the day, and it looks well worth the read. Perhaps a book like that should be a standard text in a university curriculum?

Now to my first major criticism for the day. The concept of PL/SQL APIs to every table. I noticed Tim Hall also describes this sort of utopian development environment where all DML (including SELECT) for a table is done via PL/SQL APIs and any results returned via associative arrays. As I understand it, utopia is like the speed of light, or absolute zero - theoretically you can never reach it. And I think the reason in this particular case is the balance required between a tuned application and an abstracted/encapsulated one. To his credit, Steven himself said his focus is on PL/SQL performance and integrity, he's never been a SQL tuning fanatic.

Steven also mentioned Tom Kyte's mantra of "if it can be done solely in SQL, then do it in SQL". If there is no need to introduce PL/SQL for a solution, then don't.
E-Business Suite has been utilising table APIs for years, and quite successfully. It creates a well placed barrier and internalises all the business logic, turning say the insertion of a new HR employee into a procedure call whose black box touches a dozen tables to manage the setup. It also allows customisations to be neater and separate from the main install.

Software development theory aside, Steven did demonstrate a very nifty facility in his Toad suite to automatically generate all the typical APIs that may be required for a set of tables. Very tidy indeed.

There are many ideals in this session that need to be reiterated to every developer, and every newbie (I feel I should stipulate I think this is not a derogatory term, we should all remember we were all there once) should also cover it thoroughly. 4/5 for this session. Points lost because I think Hungarian notation should be in the major standard ;-), and further elaboration should be made on the utopian ideal of PL/SQL APIs - although time is always pressing for such a theological debate.

Steven Feuerstein Perth Review - Session 1 - Code Therapy for Software Developers

Based on conversations I've had with various people since Friday's session, it seems his day seminar was a success. There was a touch over 100 people in the auditorium with a fairly wide distribution of positions, skills & industry. Many thanks for AUSOUG and the members responsible for bringing Steven to our remote city (after the Sydney/Melbourne legs) - some of them only had minimal to see Steven present; and thank you Branka for that wonderful food! (I can be easy to please) It's just a shame it was a rainy weekend in Perth.

I'd like to take the opportunity to do a small review on each of the four sessions, although most of my comments will probably tie together scatterings of comments throughout the day.

I think his four topics were well selected. While obviously separate presentations in their own right, they were cohesive enough that you could see strong relationships between them throughout the day.

1. Coding Therapy for Software Developers
2. Writing Maintainable Code
3. High Performance PL/SQL
4. Automated Testing Options for PL/SQL

Even before Chris Muir introduced him, he took the time to demonstrate two old yet simple games that he believes a good developer should be comfortable with, at the same time obtaining good audience participation.
Mastermind he likened to the debugging mind, where given various states of being, how that suits with the expected solution. It was a very good analogy and reminded me how I used to enjoy that game.
The other was The Set Game, something I stumbled across a few years ago, possibly via a comment on his website. It reflected a consistent tenet of his that development is closely tied with pattern matching.

His first session, entitled Coding Therapy for Software Developers, I personally found to be the most enjoyable session of the day. I think we need to see more of this style of presentation at these events - unfortunately they seem to fit better with the evangalist/guru types with years of experience. Not only was it a sidestep away from the usual technical presentation, it helped remind us why we enjoy our jobs and how we can modify/affirm our behaviours and appreciate our career choice.

He recommended a number of books during the presentation, and I noticed quite a few people note them down. Steven did give a good impression of enthusiasm for these books, and I will certainly be on the lookout for them. For those who missed them:

Christopher Alexander - Pattern Language - Timeless Way of Building - Steven mentioned that day to day problems already have (good) solutions and if we can harness the concepts of pattern matching we can utilise the better, efficient solutions more frequently. One of the development perspectives here being templates and modularisation/encapsulation.

Jeff Hawkins - On Intelligence - For many years engineers have been trying to build intelligent machines, and apparently this book explores the topic well.

Daniel Gilbert - Stumbling on Happiness - An interesting look at how our minds work, and why we delude ourselves in thinking we know what we all want to make us happy.

Steve McConnell - Code Complete - A more technical book mentioned later in the day discussing best practices in the art and science of creating software. I remember thoroughly enjoying the art vs science debate of development at university, I'm sure this will be a good read.

In fact listening to his comments that morning, I have a sneaking suspicion he's a fellow evolutionist - and I would quite happily have recommended a wonderful book I'm reading at the moment.

One of my hand written notes referred to a conversation about why birds don't look sick until they are near death. A pattern of evolution and adaptation - but I can't remember how it related to software development! Oops.

He certainly mentioned to avoid caffeine, eluding to memory - based on my understanding anything learnt while under the influence of caffeine means we have a better chance of remembering it only when we have the same amount of caffeine in our system. Not really a state of being that we should desire 24-7.

Steven is a charismatic speaker, and I'm sure his fairly generic American/Chicago accent has helped his success in the presentation theatre. I recall a conversation with Lynn Munsinger on this concept.

So for me, 5/5 for the first session. Stay tuned for my thoughts on the other three. I'd certainly be happy to see the same session again, and more like it.

Global/Local Oracle Community Networking

Recently while doing a little web surfing & blog investigating, I signed up to Oracle Mix and Oracle Community (my profile and profile).

It's sometimes difficult to get familiar with a new forum/website, and at the moment it seems a little overwhelming. However the idea for the two sites suggest good potential - I was wondering if anyone out there could provide thoughts on their experiences?

1. Do you find it useful?
2. Have you used it as an effective tool?
3. What positives/negatives have you found?
4. Do you have a preference?
5. Are you happy to stick with an RSS blog feed?

g_input := 'welcome';

Subquery Factoring Clause - WITH

Through general experience I've found the WITH statement either underutilised or people haven't been aware of it's existence, although that seems to be changing - it has been around since 9i.

For me, two typical examples come to mind.
1) I was demonstrating recently an example of using SUBSTR & INSTR together to extract certain parts of a string. I built up the expressions step by step, then to test the example with a slightly different string, I would normally be forced to do a search and replace. Depending on the example, this can be annoying.

select 'halls head 6065'
      ,substr('halls head 6065',4,5) guess
      ,instr('halls head 6065',' ',-1,1) pos_of_space
      ,substr('halls head 6065',1,instr('halls head 6065',' ',-1,1)-1 )||'*' suburb
      ,'*'||substr('halls head 6065',instr('halls head 6065',' ',-1,1)+1 )||'*' postcode
from dual;

Instead I can put the string I want to play with in a simple WITH statement, and change the "column name" at will.

with test as (select 'halls head 6065' suburb from dual)
select suburb
      ,substr(suburb,4,5) guess
      ,instr(suburb,' ',-1,1) pos_of_space
      ,substr(suburb,1,instr(suburb,' ',-1,1)-1 )||'*' suburb
      ,'*'||substr(suburb,instr(suburb,' ',-1,1)+1 )||'*' postcode
from test;

Other times recently I have used the WITH statement to essentially encapsulate a unit of work. I had extracted a SQL statement from some PL/SQL and wanted to run it with my own restrictions, but leave the original untouched - this allowed me to test without worrying if I've disrupted the original join behaviour.

WITH original as ({copy of embedded sql})
select *
from original
where {my own restrictions}

These are ad hoc reasons, there is also a potential performance benefit from using the WITH clause.

I'm sure I've seen an example where using the WITH clause provided a performance benefit, but memory is fallible so I thought I'd see what the documentation had to say.
It says it will give the optimiser more choice about what to do, and that we can improve the query by using the WITH syntax.
After comparing explain plans from the example straight out of the documentation, I wasn't convinced. So I ran timings over different versions of the database - 1000 iterations each. I was surprised at the results (sounds like tabloid journalism, doesn't it?):

Without the clause

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING
SUM(sal) >
  (
    SELECT SUM(sal) * 1/3
    FROM emp, dept
    WHERE emp.deptno = dept.deptno
  )
ORDER BY SUM(sal) DESC;

With the clause

WITH summary AS
(
 SELECT dname, SUM(sal) AS dept_total
 FROM emp, dept
 WHERE emp.deptno = dept.deptno
 GROUP BY dname
)
SELECT dname, dept_total
FROM summary
WHERE dept_total >
  (
    SELECT SUM(dept_total) * 1/3
    FROM summary
  )
ORDER BY dept_total DESC;

--9i
.3 secs
.0003 secs per iteration
2.9 secs
.0029 secs per iteration
--10g
1.1 secs
.0011 secs per iteration
4.06 secs
.00406 secs per iteration
--11g
1.17 secs
.00117 secs per iteration
7.46 secs
.00746 secs per iteration

Not only has it not performed better, its comparative performance has deteriorated as versions go up. So while we have removed duplicate code and enhanced the readability of our code, this example shows it's not necessarily more efficient.

It may just be a poor example to illustrate the performance benefit of this feature. It shows while a documented effect may be true in some circumstances, you must always test your particular scenario on your framework to get accurate comparisons. For further comment on this paradigm, I highly recommend reading this. Twice.

Interval Issues

Date functions have been ubiquitous within our database for years. For the most part date calculations are robust and can solve many problems.

Oracle even caters for a drift noted in the 8th century fixed in the 16th century - due to some issues with the dates chosen for Easter by the catholic church.

select date '1582-10-04' + 1 gregory from dual;

GREGORY
-------------------
15-10-1582 00:00:00

Let's take for instance one method of adding one month to a given day.
Here I add a month to the last day of August:

select add_months(last_day(date '2009-08-01'), 1) end_of_sept from dual;

END_OF_SEPT
-------------------
30-09-2009 00:00:00

And I safely get the last day of September (which has one less day).

Likewise, the documentation states:

For example, the MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month.

So these three expressions will return slightly different results

select months_between(last_day(date '2008-02-01'), (date '2008-02-01')) is_28_days
      ,months_between(last_day(date '2009-08-01'), (date '2009-08-01')) is_30_days
      ,months_between(last_day(date '2009-09-01'), (date '2009-09-01')) is_29_days
from dual;

IS_28_DAYS IS_30_DAYS IS_29_DAYS
---------- ---------- ----------
.903225806 .967741935 .935483871

However if you're working with intervals here is a little trap to watch out for.

select last_day(date '2009-08-01')
+ INTERVAL '1' MONTH end_of_sept
from dual;

+ INTERVAL '1' MONTH int
*
ERROR at line 2:
ORA-01839: date not valid for month specified

Interestingly if you subtract one month interval from the end of September you get 30th August. Similar behaviour occurs when subtracting from a leap February. It seems an interval of one month is considered as 31 days, but it can't allow properly for smaller months.
The same error will occur however if you attempt to subtract from a month such as July.

select last_day(date '2009-09-01')
 - INTERVAL '1' MONTH end_of_aug
from dual;

END_OF_AUG
-------------------
30-08-2009 00:00:00

The 11g documentation states:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error...
SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL;
...
The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date.

Personally I can't quite grasp why there should be a difference between interval arithmetic and functions such as months_between, but just be aware if you need to be pedantic with your dates.

Perhaps this behaviour with intervals will be modified in a future release?

Steven Feuerstein to visit Perth

If you haven't seen the AUSOUG broadcast, PL/SQL guru Steven Feuerstein will conduct a seminar in East Perth on 14th August 2009.

If not already, what better time to become a member of your local user group?

The agenda is currently available through the AUSOUG website here.

Hope to see you there!