Saturday, October 27, 2007

I've finally gone 11g

Typical. I finally resort to rebuilding my laptop with linux and install 11g, and Oracle come out with the Windows version.

Okay, to be honest, the linux install wasn't just to load up 11g. My windows XP startup, after 18 months, was abysmal. So I wiped it, fully intending to re-install it from scratch. But. There's always a but. Acer don't give you Windows install disks, or at least not for me. Instead you get a hidden partition on the drive that is supposed to be able to recover it as of the factory state. And you can create a Restore disk.

The restore disk didn't. The recovery partition didn't either. This was possibly due to my earlier conversion of the standard FAT32 C and D partitions to NTFS.

So I tried to install XP off the disks I had for the desktop machine. But that doesn't work with the laptop licence key, so I faced a long talk with either Acer or Microsoft or both. Bugger that.

I've opted for Ubuntu. I've tried that before, but it was a good while ago. I'm more impressed this time. It picked up my wifi connection without much fuss, which Oracle's Enterprise Linux 4 failed to do. I tried bits and bobs with OEL but gave up. Given my router is stuck in the bottom of the wardrobe (don't ask), no wifi is a no go for me.

Ubuntu 7.04 (sorry, the gimmicky names are a bit too much for me) was on the DVD for one of this month's computer magazines, so was an easy choice. Howard's articles here and here provided the details. Thanks, Howard !
I've also added rlwrap as I can't cope with SQL*Plus without a history. And this page told me how get my APEX going. While APEX is installed with 11g by default, it still needs a couple of commands from the DBA before it is available from the browser. Since the apxconf script is executed AS SYSDBA, it's a shame they didn't put the grant for DBMS_SYS_SQL in there too. I feel it would have made the default database install that little bit safer, neutering the FLOWS_nnnnn account until Apex is specifically switched on. But maybe I'm a worry wart.

I understand that the 11g Apex is the same 3.0.1 that is available for the general download and use (give or take a bug fix), which I installed on my XE install on my desktop machine. My plan is to do a little playing around, developing a small application one evening per week.

I may still download the Windows 11g next week. I noticed the Linux version didn't give an option of 'Personal Edition', which is apparently Windows only. I've no idea why it is Windows only. In fact, I've got very little idea what the market for Personal Edition is, apart from being the cheapest fully supported Oracle database (and so, I think, the cheapest way to get Metalink access and tempting for an individual consultant).

Wednesday, October 24, 2007

An Oracle coder in a Teradata world

My blog goes through quiet patches. The current one is because I'm not doing any Oracle work, but am actually using Teradata. There's not much blogging about Teradata. I can't even recall a mention in Pythian's Log Buffer. Maybe they just don't talk much.

Using a different SQL syntax does make you think a little.
For example, Teradata doesn't have DUAL. Instead you can just write "SELECT current_date" and leave it at that. [Yeah, it doesn't have SYSDATE either.] But you can also write "SELECT table.column" too, rather than "SELECT column FROM table". You can even do "SELECT table1.column from table2" which is equivalent to "select table1.column from table1, table2" in Oracle.

So what you DON'T want to do is create a few working tables with very similar names and then set off a query like
"SELECT oct23.val, ref.desc FROM oct22 JOIN ref ON oct22.ref_id = ref.ref_id".

Like SQL Server, a teradata "database" is more like a schema in Oracle. Rather than do an ALTER SESSION SET CURRENT_SCHEMA=..., you can do a SET DATABASE ...
And just to add to the spice of it, you can actually set up a sort of path of databases in which it will look for the tables. So if you mistype your table name or alias, it can go looking for that name in all sorts of places.

I'll follow the practice of the terrible programmer mentioned by Tom Kyte, and program defensively. No SET DATABASE for me. I'll specify the schema (or database) myself, and use aliases for all the tables.

There's no ROWNUM in Teradata either. Instead you do a "SELECT TOP 5 * FROM table". But if you do "SELECT TOP 5 * FROM table ORDER BY column" it does the ORDER BY first, then returns the first five rows.

Like pretty much every-one but Oracle, Teradata differentiates between a zero-length string and a null value.

It does have a few niceties that I'd like Oracle to adopt. Firstly you can specify an expression alias in the GROUP BY clause.
"SELECT CASE
WHEN column1 BETWEEN column2 AND column3 THEN column4 ELSE column5 END val,
COUNT(*)
FROM table
GROUP BY val"
Boy, does that save some COPY/PASTE.

There's also a QUALIFY clause. You know how HAVING is like a WHERE for a GROUP BY ? Well QUALIFY is like a WHERE for an analytic function, so you can do something like
SELECT id, start_date, end_date
FROM table
QUALIFY rank() OVER (PARTITION BY id ORDER BY start_date desc) = 1
rather than making the whole thing an inline view.

Remember, the "S" in SQL stands for Structured, not Standard. It just ain't that portable.

The Teradata documentation is all PDF and I haven't found it as easy to navigate as the Oracle documentation set, but that might be more familiarity with where to look. I did upload all the PDFs into the XE database on my laptop so I could use Oracle Text to search them (using the Apex Document Library application). Is that cheating ?

Still blogging

After a brief consultation, Howard has been persuaded that Dizwell is a popular and useful resource, and settled for a spring clean instead.

He did suggest letting others have their blogs hosted on Dizwell. I do find Blogger has its drawback. Mostly it is difficult to include supporting material, such as code or trace files, as it only provides for images and anything else needs to be hosted elsewhere. I find this a bit surprising given that it is all Google and it seems that they wouldn't find it difficult to have a more seamless integration with googlepages or their Docs. I expect that it is because these stretch the concept of blogging beyond the norm.

But being hosted on Dizwell.Com would be a bit scary too. It reminds me of Kerry Packer, who used to own the Channel Nine television network in Australia. During a dinner party, one of the guests raised the topic of "Australia's Naughtiest Home Videos", being debuted on his channel. He took a look at the program and promptly rang the station and ordered them to "Get that shit off the air!". They did, with just half an hour of the program shown.

It comes down the "the buck stops here". Dizwell is Howard's brand/company and opening it up to others, as he did with the Wiki and later the Knowledge Base, puts that on the line. That's one reason I have respect for the corporate blogs from the likes of Pythian, Amis and LogicaCMG . It's a brave company that is confident enough to put its technical knowledge on show for all to gawp at.

I'm glad Howard is sticking around. And if he does find 'room-mates' for his blog, I look forward to reading them too.

PS. Talking of brave, I'll be following the MEEP blog closely too. It sounds like an interesting experiment. Actually educating someone in Oracle, rather than just giving them code and expecting them to absorb knowledge by osmosis. A radical idea.

Wednesday, October 03, 2007

Sydney Technology Summit

Well it isn't a conference, but Sydney does rate a Technology Summit at the end of this month.

There's an RSS feed here that will keep you updated of "in person" events in Australia or here for Sydney. You can pick up events in Melbourne and Perth too. But Perth doesn't rate a "Technology Summit" so they'll just have to live with Chris Muir's grandma.

I'm not quite clear what the summit involves. It says :

Leverage on Oracle’s next generation of innovation - Oracle Database 11g:

  • Performance and Scalability
  • High availability and disaster recovery
  • Self-management and automation
  • Security and compliance
  • Business Intelligence and data warehousing
  • Database application development

A bit vague as to whether it is pitched at the CIO, DBA or someone in between. Anyone have the answers ?

Building up the Oracle user community

AppsLab is asking what we want from them. They are the "think-tank developed to drive adoption of new web patterns and technologies across Oracle’s business and products"

I'm not sure where the OTN forum membership fits in their remit, or more likely in Justin's, but I'd like to see them built upon. Discussion forums are one of the oldest of all online communities (after mailing lists) and given the recent developments that have occurred in this field, it is surprising that so little is new in the OTN forums. There has been some recent development with the Ospace community for Apps users and some forums that are industry / interest based rather than "solve my problem". To be honest, I'm not convinced that the current forum mechanism is appropriate for that community feeling, but I could well be wrong. Private messaging may help, but that opens up a whole can of worms (eg for monitoring).

Apparently the forum software is an out-of-the-box implementation of Jive Forums, which limits what can be done on a structural basis. However the underlying data is accessible, and it is the member profiles I'd like to see opened up and encouraged. The Jive documentation and viewing an OTN user's profile indicate that "Occupation", "Location", "HomePage" and "Biography" go into the Public Profile (if set) and "Time Zone Id" and "Locale" are only used internally.

Anyone listing a homepage can reasonably supposed to be encouraging visitors to that page, and anyone can set up a blog or googlepages etc.. Biographies could be used to publish details about whatever Facebook-like application users may want to connect with.

So the first option is to let people search for people with homepages and biographies. That may not be built into Jive itself, but the Profile data can be replicated easily enough and an Apex application quickly built for this level of functionality. Then add in an RSS feed for new and changed homepages/biographies.

We instantly get a mechanism for any OTN member to launch their blog or resource page. Maybe they could get an icon, like the ACEs and ACE Directors. Being in Oz, I suggest we title them 'Mates'.

Some OTN forum members may not be the blogging type; they may be happier responsing to questions or are looking for help themselves. The 'Mate' search can work off that by adding in the potential to build linguistic and geographical contacts, using the Locale and Location values. Locale is chosen from a list and would be good for an initial filter option, with the free-text location as an option below that. That can encourage people in the same area to make contacts, perhaps form or join a user group or mentoring or even employment opportunities.