February 24, 2005
Soup for the Fevered Brain
Just back from a very enjoyable lunch with Ciaran, Alan and Charles. We were esconsed at Soup Plus and the conversation covered the usual geeky topics. Which was a nice break from this morning's endeavours of trying to reverse engineer Oracle's aged debtors report.
Even worse, I'd taken refuge from that rather tricky activity in a little light perusal of the Python Daily URL. Unfortunately today it mentions this cookbook recipe. That just about finished off my remaining cognitive juices. Is there any need for this kind of thing? I can see why it's admirable, I may even one day understand how it does what it does, but it's hardly adhering to the KISS principle is it?
Posted by Andy Todd at 12:44 PM | Comments (0)
February 18, 2005
Subversion and Squid - Working
Good news, I've managed to get working access to my Subversion repositories. Many thanks to the nice people at TPG who, when I called them up and told them exactly what to change, updated their squid configuration so that everything now works as expected.
I'm now going to try and scrounge a copy of Pragmatic Source Control using Subversion to make sure I'm doing everything properly.
Posted by Andy Todd at 08:45 PM | Comments (0)
February 16, 2005
Recommended Reading
One of my colleagues has just received his copy of Pragmatic Version Control Using Subversion and he is praising it to anyone who will listen around the office. If I can't wrestle the copy out of his hands I may have to take the drastic step of ordering my own.
I was rather startled to find from a straw poll (of people within shouting distance) that no one had read what I consider to be the three seminal computing texts. So I'm going to point them all at this blog entry and suggest they read;
- The Pragmatic Programmer by Andrew Hunt and David Thomas
- The Mythical Man Month by Frederik P. Brooks, jr.
- Peopleware by Tom DeMarco and Timothy Lister.
Posted by Andy Todd at 11:10 AM | Comments (2)
February 14, 2005
Subversion and Squid
I've recently signed up with the lovely people at TextDrive. One of the deal makers for me was that they allow you to host Subversion repositories. Finally, I can have a place to put all of my bits and bobs of code that aren't part of any particular project and therefore don't really belong on SourceForge.
Setting up my first repository was simplicity itself, thanks mainly to the patience of the sterling folk at the TextDrive Help Desk. I was undone when I tried to import a few files into my shiny repository. Whatever I tried ended in HTTP errors.
To cut a long story short the current theory is that it's because my ISP uses Squid as a proxy cache and it needs to be tweaked to support Subversion's extensions to HTTP.
I'll leave it to the reader to imagine the conversation I had with their technical support people ("No, I'm not running Internet Explorer ", "It's an HTTP header, you know, like PUT or GET. You spell it P-U-T, oh never mind, who do I send an email to?").
Suffice it to say I've still not got any access to my Subversion repository. Hopefully with a little prodding they will make the necessary changes and I will become a hero to all of TPG's customers.
Posted by Andy Todd at 02:20 PM | Comments (1)
Technology Backwater
The blogosphere is very excited about the new, shiny, Google Maps feature.
Sadly a search for where I live yields an unsurprising lack of results. No matter how many times I check the big sunburnt land down under hasn't made it into the "anywhere between the west and east coasts" world view of most American technology companies.
Update. The Accordion Guy includes a nice picture of the world and includes it at the bottom of this post to the technology blog "The Farm".
Posted by Andy Todd at 10:32 AM | Comments (1)
February 05, 2005
ORA-01445
Continuing my adventures with ANSI join syntax today I found an interesting feature.
I was writing a query to simplify a rather complicated data structure (are there any other kind in Oracle Applications?) I was being good and writing it in an iterative fashion. As I needed extra columns from extra tables I was simply adding them to my FROM clause. Everything was working fine until I added the last table - GL_CODE_COMBINATIONS.
As soon as I did that my query stopped working and I got an error message;
ORA-01445: cannot select ROWID from a join view wihtout a key-preserved table.
Which rather surprised me as there were no ROWID's in my query. for once, Google wasn't very useful. Luckily MetaLink, Oracle's support web site, came to my aid. Through the diligent research of Sunder Rajan in the DBA forum I found that it wasn't my fault really, but that Oracle (or at least Oracle 9.2) has a limit of 1050 columns in any query that uses ANSI joins. By my reckoning the tables in my FROM clause between them had about 1100 columns. I told you it was complicated.
As a workaround I changed the line;
LEFT OUTER JOIN gl_code_combinations gcc ON dist.dist_code_combination_id = gcc.code_combination_id
to;
LEFT OUTER JOIN (SELECT code_combination_id, segment3 FROM gl_code_combinations) gcc ON dist.code_combination_id = gcc.code_combination_id
And still got the error. After reducing the number of columns from another couple of tables though the error went away and the query returned the data I expected.
I mention this here because the MetaLink forums aren't publicly available and other people may fall foul of this pernicuous issue.
Of course having a query that accesses this many tables is a really bad idea because it's going to be a nightmare to maintain. Now that I've got the query working, and used it to build a view, I'm going to write some rather strenuous documentation.
Posted by Andy Todd at 04:19 PM | Comments (3)
February 03, 2005
ANSI Join Syntax in Oracle
I thought it was about time I dragged myself (kicking and screaming if necessary) into the late twentieth century and started using more standard SQL syntax. In particular I wanted to make my SELECT statements more portable by using the SQL 99 join syntax. As usual, the manual was worse than useless. Google came up with a few pointers though.
The first cab off the rank was this offering from the Oracle Technology Network. It is best described as brief and to the point.
Luckily, the ever reliable Mark Rittman came to my aid with this column explaining ANSI joins and why they should be used. In turn, he points to this piece at dbasupport.com. Between them they explain how to "properly" join tables together in your queries.
A common idiom that I, and I suspect many others, use isn't quite explained in any of the literature. I often need to perform two or more unrelated outer joins. For instance I want to get all of the rows from table A outer joined to any applicable rows in table B. But at the same time I also want to outer join table A to table C. In the brave old 20th Century with an Oracle database I would do this like;
SELECT a.column1, b.column1, c.column1 FROM table_a a, table_b b, table_c.c WHERE a.b_key_col = b.b_key_col (+) AND a.c_key_col = c.c_key_col (+)
From my initial reading it didn't seem obvious how to do this with SQL-99 join syntax. The only example I could find that got close was in the dba-support article. This shows how to outer join table a to table b and then outer join table b to table c. Close, but no cigar.
After a little experimentation I found out that it's actually quite simple. To outer join table a to table b and then in the same query independently outer join table a to table c you just do something like;
SELECT a.column1, b.column1, c.column1
FROM table_a a
LEFT OUTER JOIN table_b b USING (b_key_col)
LEFT OUTER JOIN table_c c ON a.c_key_col = c.c_key_col
The query parser is smart enough to release that the second LEFT OUTER JOIN is independent of the first one and to fetch the data appropriately.
Posted by Andy Todd at 02:50 PM | Comments (6)