Interesting Times

One a day before the Socceroos faced they final match in the World Cup (they won, but still did't make the next round), interesting times were occurring in the political arena back home.
Australia's Prime Minister Kevin Rudd  (seen here, eating his own earwax) is about to be deposed. Australia follows the Westminster system of government. Local areas elect a member of parliament, and the parliament chooses the Prime Minister from within its ranks. Party politics inevitably gets involved so the Prime Minister is invariably the leader of the party with the most seats in the House.

The Labor party currently has the most seats, at least in the main House of Representatives. We will ignore the Senate, elected by a peculiar form of proportional representation and described by one former Prime Minister as "unrepresentative swill".

Having decided that the Prime Minister, elected less than three years ago after overturning a long running Liberal party administration, isn't the best choice to lead the party to the next election, the deputy leader is challenging for the job. Kevin Rudd isn't going quietly. He'd like at least a chance at the election he would have called within the next six months.

In an hour or so, we will find out whether Australia will get its first woman PM in Julia Gillard .

We've had women as party leaders before. The most recent example has been the Democrats party, now practically dead after committing the unmentionable act of actually keeping an election promise. Back in 1998, The Liberals proposed a wide-ranging Goods and Sevices Tax (GST) . The Democrats, under Meg Lees , campaigned on a GST that excluded food . When they got the balance of power (ie their votes were needed to secure passage of the Bill), they forced that amendment. The shock of a party actually following through on an election promise was such that practically no-one voted for them again. After a brief sojurn under a different woman leader (Natasha_Stott_Despoja ) , they vanished from parliament.

So the topic of conversation this morning will be either the Socceroos or politics. As a Pom (with dual citizenship) I may stick with the former.At least to encourage the Aussies to support the team playing Germany which flogged the Aussies 4-0, which was responsible for the poor goal difference that kept the Aussies from second spot. Oh, and the team playing the Germans next will be England.

Well its either that or support the Kiwis who still have a chance of qualifying if they can beat Paraguay. Though Nuno can support Portugal, I guess.

Stackoverflow and StackExchange

Like Tony Andrews, I am a big fan of the StackOverflow Q&A engine. I'm just not as good as him :)

If you haven't tried it, go ahead. Here are the questions tagged Oracle .

• I like that it uses OpenId so I don't have another username/password to remember.
• I like that code formatting is so simple. 
• I like the simply numbered 'reputation' and the baadges you can collect.
• I like the fact that all the questions, answers and comments are Creative Commons licensed and you can download the whole lot.
• I like the RSS feeds based on tags.

The only drawback is that the site is aimed at programming. It has a sister site, serverfault.comwhich is more for admins. But that is mostly for system admins. There is a third site in the trilogy called superuser.com  aimed at, well, superusers. I guess that Apex websheets might end up fitting in there.

In database work there is a lot that can fall in either a developer or DBA camp. Performance tuning is the prime example. There is a limit to what can be achieve by tweaking SQL and at some point it becomes beneficial to address more fundamental issues through indexes or storage options. And some places have DBAs or 'the Oracle guy' who does everything, and at the other end of the spectrum who may have someone who's sole domain is backup/recovery with a separate 'architect' who has visions dancing in his head (maybe of data models but possibly of sugar plums).

So is there room for a similar site dedicated to Oracle or dedicated to databases in general ? One site to rule them all ?

The people behind stackoverflow are opening up stackexchange 2.0. People get to vote for potential new sites. And there are two proposals up and running, one for Oracle and one for Databases. If you are interested in either of these, follow the link and click the FOLLOW button.

Then, if you want, come up with some sample questions. You can get some ideas from other forums or what the people around you always end up asking.

Once a proposal is sufficiently defined, and has enough committed supporters, it can be launched and then its just a matter of driving questioners to it.

I should point out that OracleOverflow started up under the StackExchange 1.0 model (ie paid for, in this case by RedGate who are better know in the SQL Server field) but didn't really get the momentum. I don't believe this site meets the conditions for migration. If some of the premium members of the database community (Oak table members, ACEs, high-profile bloggers, oracle-l members etc) can voice their support for this, then it shouldn't have any problem getting off the ground.

Spread the word please.

Long intervals, PL/SQL Functions and subtypes.

I was doing some work with the interval day to second datatype recently. I hit a problem and thought it worth documenting my solution.

Generally, when you create a PL/SQL function or parameter, the scale/precision/size of the parameters (including the return value of a function) is undefined.

For example, you say FUNCTION show_price RETURN NUMBER or FUNCTION show_name RETURN VARCHAR2. You are not allowed to have FUNCTION show_price RETURN NUMBER(10,2) or FUNCTION show_name RETURN VARCHAR2(20), and the length/precision/scale is supposedly derived from the calling environment.

This new fangled INTERVAL DAY TO SECOND datatype is a bit odd, in that it has a default precision of 2 for the days.  That is, it can only store a number of days up to two digits (99 days). So if you have a function with a INTERVAL DAY TO SECOND return type, it will balk at a value of 9999 hours (over 400 days), and throw a ORA-01873 precision error, even if the precision of the variable in the calling environment is sufficient.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/

ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 7
ORA-06512: at line 11

You are not allowed to specify the precision directly as part of the datatype returned by the function.

DECLARE
  v_int INTERVAL DAY (4) TO SECOND(0);
  FUNCTION hhmm_to_interval return INTERVAL DAY (4) TO SECOND IS
    v_hhmm INTERVAL DAY (4) TO SECOND(0);
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;
end;
/
ORA-06550: line 3, column 49:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
to

You can use a PL/SQL SUBTYPE however.

DECLARE
  subtype t_int is INTERVAL DAY (4) TO SECOND(0);
  v_int t_int;
  FUNCTION hhmm_to_interval return t_int IS
    v_hhmm t_int;
  BEGIN
    v_hhmm := to_dsinterval('PT9999H');
    RETURN v_hhmm;
    --
  END hhmm_to_interval;
BEGIN
  v_int := hhmm_to_interval;

  dbms_output.put_line(v_int);
end;
/
+0416 15:00:00

PL/SQL procedure successfully completed.

The documentation is a bit skimpy here though. It states that where a parameter is based on a subtype, a NOT NULL constraint on the subtype is applied to the parameter. It also states that for numbers and VARCHAR2 it does NOT apply the size/scale/precision constraints (though it will apply a range constraint for a numeric type).

I added a comment for the documentation with the hope that the INTERVAL datatypes get a specific mention.

A comment by stezgr on the same issue raised on the stubbisms blog pointed out a built-in DSINTERVAL_UNCONSTRAINED subtype for this. When searching on that, Google brings up Steven Feuerstein's Oracle PL/SQL Programming book which describes using that built-in as the solution.