Well I Never - Followup 1

OK, so I've managed to grab some time during the day to experiment, and I've got things to post. For now I've just got the time for this...

Turns out that William Robertson was quite right, the TO_CHAR 'too many declarations' issue has gone away (certainly by the time it reached 9.2), and I never even noticed!

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:53:25 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> SELECT TO_CHAR( 'CHARACTER' ) FROM DUAL
  2  /

TO_CHAR('
---------
CHARACTER

SQL> SELECT TO_CHAR( NULL ) FROM DUAL
  2  /

T
-


SQL>

Second up (also in 9.2) the first suspicion I had was quite right... the following doesn't work.

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:42:49 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE OR REPLACE PACKAGE test_pkg IS
  2    --
  3    FUNCTION cannot_be_overloaded RETURN NUMBER;
  4    FUNCTION cannot_be_overloaded RETURN VARCHAR2;
  5    --
  6  END test_pkg;
  7  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
  2    --
  3    FUNCTION cannot_be_overloaded RETURN NUMBER IS
  4    BEGIN
  5      RETURN 0;
  6    END cannot_be_overloaded;
  7    --
  8    FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
  9    BEGIN
 10      RETURN 'Character';
 11    END cannot_be_overloaded;
 12    --
 13  END test_pkg;
 14  /

Package body created.

SQL> SELECT test_pkg.cannot_be_overloaded FROM DUAL
  2  /
SELECT test_pkg.cannot_be_overloaded FROM DUAL
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'CANNOT_BE_OVERLOADED' match this
call

SQL> DECLARE
  2    vn_number  NUMBER;
  3    vc_character  VARCHAR2(100);
  4  BEGIN
  5    vn_number := test_pkg.cannot_be_overloaded;
  6    vc_character := test_pkg.cannot_be_overloaded;
  7  END;
  8  /
  vn_number := test_pkg.cannot_be_overloaded;
                        *
ERROR at line 5:
ORA-06550: line 5, column 25:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 28:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored

SQL>

However, my second suspicion was off the mark (at least in 9.2). Almost certainly this is related to the change in behaviour to TO_CHAR described above,

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:45:47 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production

SQL> CREATE OR REPLACE PACKAGE test_pkg IS
  2    --
  3    FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER;
  4    FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2;
  5    --
  6  END test_pkg;
  7  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
  2    --
  3    FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER IS
  4    BEGIN
  5      RETURN pn_number;
  6    END can_be_overloaded;
  7    --
  8    FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2 IS
  9    BEGIN
 10      RETURN pc_varchar;
 11    END can_be_overloaded;
 12    --
 13  END test_pkg;
 14  /

Package body created.

SQL> SELECT test_pkg.can_be_overloaded( 0 ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED(0)
-----------------------------
                            0

SQL> SELECT test_pkg.can_be_overloaded( 'WORD' ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED('WORD')
--------------------------------------------------------------------------------
WORD

SQL> SELECT test_pkg.can_be_overloaded( '100' ) FROM DUAL
  2  /

TEST_PKG.CAN_BE_OVERLOADED('100')
--------------------------------------------------------------------------------
100

SQL> DECLARE
  2    vn_number     NUMBER;
  3    vc_character  VARCHAR2(100);
  4  BEGIN
  5    vn_number     := test_pkg.can_be_overloaded( 0 );
  6    vc_character  := test_pkg.can_be_overloaded( 'WORD' );
  7    vc_character  := test_pkg.can_be_overloaded( '0' );
  8    vn_number     := test_pkg.can_be_overloaded( TO_NUMBER( '0' ) );
  9    vn_number     := test_pkg.can_be_overloaded( TO_CHAR( 0 ) );
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL>

Cheers to everyone who commented on the last post... it's led me to check out a few things that I might not have bothered with and I reckon I'll be looking a little deeper in the next few days. Contrived examples of where named parameter notation could go wrong are called for I think ;-)

Well I never

Good to be reminded that there's always something that you don't already know. And that's especially true of Oracle. I'd never suspected that some of those things are pretty fundamental, like the fact that package functions and procedures can be overloaded! I'd always assumed that since standalone functions and procedures can't, that the same was true of packages. Turns out that assumption was all wrong...

I.E.
This doesn't work:

CREATE FUNCTION cannot_be_overloaded RETURN NUMBER IS
BEGIN
  RETURN 0;
END cannot_be_overloaded;
/

CREATE FUNCTION cannot_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
  RETURN pc_varchar;
END cannot_be_overloaded;
/

But this does!

CREATE OR REPLACE PACKAGE test_pkg IS
  --
  FUNCTION can_be_overloaded RETURN NUMBER;
  FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2;
  --
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
  --
  FUNCTION can_be_overloaded RETURN NUMBER IS
  BEGIN
    RETURN 0;
  END can_be_overloaded;
  --
  FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2 IS
  BEGIN
    RETURN pc_varchar;
  END can_be_overloaded;
  --
END test_pkg;
/

I'm sure there are gotchas in there, and I'm not really sure it's actually that useful (I've gone 8 years without it ;-) ), but still... how did I miss it? What else have I missed?

Update - aside: Why does Oracle allow overloading in the package, but not with standalone. I'm guessing, but probably because not allowing the standalone overload makes things like the 'DROP PROCEDURE' command a lot simpler to use (care to specify which procedure with that name to drop?)
Probably because allowing the package procedures to be overloaded seemed like a god idea to someone in Oracle ;-o

Update: Just re-reading the bulk of the post... and now I'm blogging something that I've not tested (no Oracle at home). But I reckon that the following won't work:

CREATE OR REPLACE PACKAGE test_pkg IS
  --
  FUNCTION cannot_be_overloaded RETURN NUMBER;
  FUNCTION cannot_be_overloaded RETURN VARCHAR2;
  --
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
  --
  FUNCTION cannot_be_overloaded RETURN NUMBER IS
  BEGIN
    RETURN 0;
  END cannot_be_overloaded;
  --
  FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Character';
  END cannot_be_overloaded;
  --
END test_pkg;
/

Any calls to the functions would be ambiguous. Surely Oracle can't choose which one to use based on the type of the variable you're going to hold the value in... that would be a nightmare bit of compiler to implement. No no no!

Also, I reckon you'd have to take care with this:

CREATE OR REPLACE PACKAGE test_pkg IS
  --
  FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER;
  FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2;
  --
END test_pkg;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
  --
  FUNCTION can_be_overloaded ( pn_number   NUMBER   ) RETURN NUMBER IS
  BEGIN
    RETURN pn_number;
  END can_be_overloaded;
  --
  FUNCTION can_be_overloaded ( pc_varchar  VARCHAR2 ) RETURN VARCHAR2 IS
  BEGIN
    RETURN pc_varchar;
  END can_be_overloaded;
  --
END test_pkg;
/

Even if the above would compile (I don't know if it would), then if you were to call the above with:

  test_pkg.can_be_overloaded ( '100' );

I suspect that Oracle will throw a wobbler. The parameter passed could be treated as a number or a varchar2, meaning either function could be valid for passing.
The only reason I suspect the package would compile is that the call could become non-ambiguous with:

  test_pkg.can_be_overloaded ( pc_varchar => '100' );

I can see how this could get very knotty, positional parameters could make all kinds of function calls ambiguous.
Reckon I might try some experiments tomorrow...

Version Control and the Patch Runner

Commenting on my first post on upgrading databases, someone with the moniker 'gonen' asked a question:

"How do you handle the situation where one developer is checking in code that doesn't have to be released in current release?"

It's a good question, but it's a question mainly of version control rather than the patch runner itself. In fact, whilst the question was asked against the database patch runner post, it's interesting that the question doesn't mention the word 'patch', it uses the word 'code'. This is a general question that relates to any code, documentation, or other product of software development.

But, before I answer the question I'm going to turn it round a little and ask:

'Why do you have code that you want in version control that you don't want in the next release?'.

I can see three main answers to this one:

1 - The code is useless and so there's no reason to ever release it.

This is probably the most unlikely answer, but hey, it happens.
If this is the case, why are you keeping it? Commonly such code is kept because "it might be useful in the future and I don't want to throw it away".
Well, is it really likely to be useful? If it is historic code that is already is version control, then with most VC you can remove it and then still get it back at a later date if you need to. Proper version control software will record the fact that the file was deleted at a particular point in time but make sure the version exists in the historic versions. Otherwise you wouldn't be able to re-release only versions of your software that requires that code.
If it's new code that's not used anywhere, isn't yet in version control and isn't currently useful... why on earth would you want that in your system? It'll likely never get used, and merely hang around for years to come by which time people will be confused by existence but too scared to delete it.
The answer: If you don't need it, don't check it in. If it's already checked in, remove it. In either case, delete it, you don't need it.

2 - You're trying to fix a bug in the live system, but since you've made the original live release you've done a load more development. You're not ready to release the new stuff to live yet.

Or to put it another way. There was a live version of the system was released and since then Alex has been working on bug fixes. At the same time Ben's working on the new release and he new funky functionality. A few weeks in and Ben's been checking in his new stuff, but hasn't quite finished it. Alex gets given a critical bug to fix that must ship to the customer as soon as it's fixed. She can't commit and release the head of the trunk because it contains Ben's half finished stuff.

Ok. So when you made the live release you tagged up the release version in your version control didn't you. If not, you can always go back and do it, you just need to know the date you cut the release. And from now on you'll tag up everything that you release right?

The tagged version is the version you want to make your live bug fixes against, not the version at the head of the trunk. When you release a bug fix version you only want the bug fixes to be released, never the new functionality

To do this, you create a branch from the tag and do your bug fixing there. On that branch you now have your live version plus your bug fix. This branch doesn't contain any of the changes that you've made in the trunk since the live version was released. Of course, whenever you make a bug fix in the branch you make sure that the bug fixed is made in the trunk version as well, otherwise when your customer comes off the branch the bug will reappear.

3 - You have several people working on code at the same time, some on short term work, some on longer term stuff. Therefore have incomplete work you don't want to release yet.

For example, Alice has a long running bit of work and has been checking code into VC as she goes along. She hasn't really finished the job yet. Ben has been doing the same, but has managed to complete his. The trunk now contains a mixture of complete and incomplete work.

If you have several streams of concurrent long term development and often get to points where you need to release one stream without releasing another then branches can help again.
Rather than allow Alice and Ben to work directly on the trunk, give them a branch each. Known as 'task branches', each branch exists purely for the length of that task. Once the task is complete the developer merges their branch into the trunk and then discards the branch. When the next long term task arrives another branch is created and the work for that task done there

Going back to the example above:
Alice and Ben work on their own branches. As Ben finishes his work and commits into his branch we have the following situation: Ben's work is complete on his branch. Alice's incomplete work is on her branch. The trunk doesn't contain either Alice or Ben's work.
Now Ben's finished his work on the branch he merges it onto the trunk, effectively promoting it into the general release. Ben's task branch can now be discarded. The trunk can now be released to the customer.

Note that if Alice had finished her work first, then her work hits the trunk and the customer release can contain that bit of functionality instead of Ben's. It is not important which bit of work is finished first, which branch is discarded first. There is flexibility inherent in the system.

However, in this situation there may be another underlying problem. Ask yourself the question, would it be possible and be more efficient to have a single stream of work that gets completed quickly rather than multiple streams that each take time? That could engender a team attitude as well as focus the developers, testers, project managers, and business as a whole on a single task at a time. Yep, I know, that's not always possible...

So what about the patch runner then?

The patch runner can work very nicely in this branching structure, just as long as it's coded to deal with the fact that patches should only ever be installed once.

To quote myself, from that earlier post:

So, in summary, you check out a given tagged version of the application to run against an arbitrary database and run the patch runner. It loads the list of patches that are applicable for that tagged version. It runs over each patch in turn and checks if it has previously ran. If it has not, then it runs the patch.
By the time it reaches the end of the list it has ran all the patches, none of them twice. You can even run the patch runner twice in succession and guarantee that the second run will not change the state of the database.

Also, if the answer is in version control then the patch runner, the patch list and all the patches themselves need to be in version control. If they're not, then the fact that version control can answer your original question is itself a great reason for putting version control in place. Did you know that CVS and Subversion are both free... as is Tortoise (CVS and SVN). Download and install one of them, it'll take you all of 15 minutes to get a local version up and running and experimenting with.

Finally, if you want more information on version control then there are three books out there that you really should read. Well, you should read two out of three of them anyway...

Software Configuration Patterns - Steve Berczuk and Brad Appleton
Pragmatic Version Control using Subversion - Mike Mason
or
Pragmatic Version Control using CVS - Dave Thomas

And if you want more blog posts on version control then your man is Mike Mason... The go to guy on using Subversion in the real world.

Technorati Tags: Robert+Baillie, CVS, SVN, version+control, Oracle, upgrading, software, development, programming, database, DB, SQL, tortoise, patch

Merge triggers

Had a quick puzzler at work today that I thought I'd share... and the link to Ask Tom that solved it (before I could be bothered to delve into the test case myself).

Q: Which statement level triggers fires on a MERGE statement?

A: Since it is possible for either inserts or updates to occur during the statement, and since statement level triggers always fire even when no rows are processed, both the INSERT and UPDATE triggers fire every time, regardless of whether any inserts or updates occur.

Obvious when you think about it. Trouble is, how many our YOUR statement level triggers would work properly if both sets fired at the same time? There's no reason why they shouldn't, if you code for the possibility....

Tom (and Mikito and Kevin and others) explains here.

World Cup Trivia

Well, I'm finally back from my honeymoon, and with the wedding out of the way I may finally get some time to write blog entries again. But wait, hang on, it seems like there's a World Cup (footy that is) to take up almost all of my evenings. Ah well, no blogging just yet then!

Still, there's no matches on at the moment (not until Friday anyway) and so to quench my need for football I decided to go on a trivia hunt. And thus was born Honest Bob's World Cup Trivia facts... I can't guarantee the accuracy of any of these facts as all were researched on the internet ;-)

Of the 17 events (prior to Germany 2006), the hosts have won 6 times, with only Sweden being a losing host finalist (1958, losing to Brazil).

Argentina and Brazil are the only countries to win outside of their own continent. Brazil have managed to win on every continent the competition has been played:

• Asia: South Korea / Japan (2002)


• North America: USA (1994), Mexico (1970)


• South America: Chile (1962)


• Europe: Sweden (1958)

Not only that, but Brazil have managed to qualify for every world cup tournament.

However, they are the only team that have won the competition to have NOT won it as hosts.

Although a German team have won the competition 3 times, they've never managed it as a combined Germany, only ever as West Germany.

In total only 7 teams have won the competition out of 207 countries who have competed for qualification, and 78 who have made it to the world cup proper. The 7 winners are:

• Brazil (5 times)


• Italy (3 times)


• West Germany (3 times)


• Uruguay (2 times)


• Argentina (2 times)


• England (once)


• France (once)

The competition has been utterly dominated by European and South American teams, with only two teams outside of the two continents having made it to the semi-final stages. USA (1994) and South Korea (2002) both being hosts when they managed it.
The fastest goal in a world cup match was scored by Hakan Sukur (Turkey), 11 seconds after kick off against South Korea in the 2002 tournament.

However, that time is beaten when you also take into account the qualifying matches. David Gualtieri (San Marino) scored after 8 seconds against England in their ill-fated qualification attempt for the 1994 Finals. England needed to win by 7 clear goals and have Holland lose their match against Poland. England only managed to win 7-1, though it mattered little as Holland eventually won their game 3-1.

In the whole of that qualification group (10 games) San Marino managed to score only one other goal and conceded 46.

The 1950 World Cup was not decided by a final. Rather it was a league contested by 4 teams, with the match between Uruguay and Brazil (2-1) being the decisive match and therefore generally regarded as 'the final'.

The only person to have played both World Cup Football and World Cup Cricket is Viv Richards - West Indies at cricket (obviously) and for Antigua in their 1974 World Cup Qualifying matches, which ultimately ended in failure.