Tardate 2016

Oracle has released Oracle Database 11g R2 today - currently only the Linux version, with other OS to follow.

The 11gR2 documentation is not yet available on OTN or for download yet, but I note it is already available online if you want to stay up tonight to digest all that's new. Chris Kanaracus' PCWorld review is one of the first to hit the streets.

I've yet to digest all the changes, but in general I'd call this a "refinement" release after what's been a very solid initial 11g release. It is interseting to see the cloud features creeping in though, for example backup to Amazon S3.

11g R1 has now been out for about two years, and while technically it was the "polish" needed to round out the major shift to 10g, my personal experience is that 11g adoption has been pretty slow, and mainly the result of fresh installs rather than upgrades. This is to be expected given that most customers fit into one of two camps: those still stuck on pre-10g, and those who finally got it and moved to 10g (few of whom are yet keen to regroup for a move to 11g). Apparently, Oracle estimates about 10-20% of customers have implemented 11g which sounds about right.

As fitting my tradition (going back to a very old and tired joke), this means the tardate blog gets a +0.1 increment. w00t!

There's something cheekily enjoyable about getting all manner of 'non-human' things to tweet. I've heard of plants tweeting, but until I saw Lewis Cunningham's post announcing ORA_Tweet, I hadn't even thought of getting Oracle Database onto twitter.

Saturdays are good for little projects, so I thought I would add URL shortening service today;-)

Since twitter famously limits you to 140 characters, it is conventional to use a url-shortening service to include hyperlinks in your tweet. So my little play for today was to pair that idea up with ORA_Tweet.

There are a range of URL shortening services available including snipurl and tinyurl (here's a survey of services). I've been using is.gd for a while though, since it sports the simplest GET request 'api' you could imagine, making it great for ad-hoc programmatic use.

So I add an extra package called SHORT_URL which has just two functions:

 FUNCTION encode_url(
 p_url IN VARCHAR2 )
 RETURN VARCHAR2;

 FUNCTION encode_text(
 p_text IN VARCHAR2 )
 RETURN VARCHAR2;

encode_url the main wrapper around the http://is.gd call to get a short url for the one you provide.

encode_text is a more convenient function that takes a block of text, and will replace all the urls it contains with the appropriate shortened versions.

Then there's just one change to the ORA_TWEET package body:

45c45
< url => 'status=' || SUBSTR( short_url.encode_text(p_string) ,1,140));
---
> url => 'status=' || SUBSTR(p_string,1,140));

Now you can go wild with URLs in your database tweets:

BEGIN
 DBMS_OUTPUT.ENABLE;
 IF ora_tweet.tweet
 (
 p_user => 'twitter_username',
 p_pwd => 'twitter_password',
 p_string => 'ora_tweet v1.1 is complete! Now with url shortening ... see http://database-geek.com/2009/03/15/ora_tweet-tweet-from-oracle-a-plsql-twitter-api/' )
 THEN
 dbms_output.put_line('Success!');
 ELSE
 dbms_output.put_line('Failure!');
 END IF;
END;

Building on Lewis' original justification for building ORA_Tweet, you could for example include links to a report page or admin screen when your long-running process sends you its "I'm done" tweet.

That's if you need justification;-)

If you are interested, the source is up on my github account now: ORA_Tweet_With_Shorturls.zip

Looking at an Oracle Portal installation recently, I wanted to call the WebCache invalidation API from SQL.

$ORACLE_HOME/webcache/examples/invalidation.sql sounds like a good idea, until:

begin
invalidate('hostname.com',9451,'password1','http://hostname.com/page')
end;
Error report:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.BASE64_ENCODE", line 51
ORA-06512: at "SCOTT.BASE64_ENCODE", line 57
ORA-06512: at "SCOTT.INVALIDATE", line 38
ORA-06512: at line 2

Uh-oh. The base64_encode function included in the script is having trouble with the password. A quick look at the code...

create or replace function base64_encode
(
 p_value in varchar2
) 
return varchar2 is

 BASE64_KEY constant varchar2(64) := 
 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/';
 l_buffer varchar2(32767);
 l_len integer := trunc(length(p_value) / 3);

...

begin
 for i in 0..l_len loop
 l_buffer := l_buffer || encode_chunk(substr(p_value, i * 3 + 1, 3));
 end loop;

 return l_buffer;
end base64_encode;

Note l_len division by 3, then using it in the for loop. Yep, classic 0/1 base offset issue. Any password with a length of 3, 6, 9 etc characters breaks the code. Fixed with a -1:

 for i in 0..l_len - 1 loop
 l_buffer := l_buffer || encode_chunk(substr(p_value, i * 3 + 1, 3));
 end loop;

But that raises more questions. What is this base64 encoding function doing here anyway?

At some point in time it might have been required, but Oracle Database has had the standard function utl_encode.base64_encode for at least a few versions. It encodes RAW so there's a bit of friggin around with types:

select utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw('password1') ) ) as B64 from dual;

B64 
-------------
cGFzc3dvcmQx 

I did note the comment in invalidation.sql to the effect that:

-- this old example is replaced by 2 files, collectively known as the
-- PL/SQL invalidation API.
--
-- the 2 files are
--
-- wxvutil.sql which does similar things as what invalidate.sql did
-- wxvappl.sql which is a wrapper of wxvutil.sql
--
-- both files are located in the same directory.

Well, these files are not in the same directory (they are actually in ../toolkit), and what's the excuse for shipping broken examples anyway, even if they are old and obsolete?

If none of these are present, then I tend to presume the real DBA has long left the building. Even if you are using third-party alternatives, do you continue to re-evaluate the Oracle capabilities with each new release?

Jon Emmons' book is of course more focused than this. It perfectly fills a niche, with an approachable, practical and comprehensive coverage of shell scripting from a DBA's perspective.

I can see the ideal audience for this book is people who are reasonable familiar with Oracle administration but are new to shell scripting. This book will rapidly teach you all you need to know on the scripting side (and let you skip alot of stuff you can learn later).

In other words, if you are a DBA who has just been assigned to manage a Unix-based system for the first time in your career: get this book. Forget all the (great) general Linux/Unix/shell scripting books for now. Don't even think the Oracle docs will teach you what you need to know. Oracle Shell Scripting: Linux and UNIX Programming for Oracle (Oracle In-Focus series) is what you need!

If you are coming the other way though - an experienced Linux admin being told that from Monday you also need to manage an Oracle database - I'd say this book probably doesn't have much to teach you. There's much more you'd need to learn about Oracle first (after telling your manager he's crazy), and there are really no scripting tricks in the book that you shouldn't already know. The main benefit you get would probably be a few pages in chapter 6 that cover the tricks of using sqlplus in a shell script - all in one place rather than having to tease it out of the Oracle docs (or see this related question on stackoverflow).

Originally posted on It's a Prata Life.

WSJ blogger Ben Worthen on the HP Oracle Exadata Storage Server:

Oracle now has a device that is kind of like an iPod except that it is a lot bigger and a lot more expensive and not as cool.

"... priced at US4,000ドル per terabyte of storage, plus the database license costs" Oh my.

Stackoverflow quietly moved into public beta last week, and I'm stunned by how active it is already.

I'm looking at pages of really funky technical questions here that I haven't a clue how to answer ... and they all have at least one answer in response already.

There are even 106 questions in the "oracle" category.

If you haven't checked it out yet, Stack Overflow is simply a "programming Q & A site". As they say in the FAQ:

What's so special about this? Well, nothing, really. It’s a programming Q&A website. The only unusual thing we do is synthesize aspects of Wikis, Blogs, Forums, and Digg/Reddit in a way that is somewhat original. Or at least we think so.

I have big hopes for this site. The best developer communities I ever participated in were on the old network news/nntp, until it started getting overtaken by the web in the late nineties. Ever since then I've never really found an "optimal" community. It's either everyone (aka google), very specialist mailing lists, or web forums that tend to be too fragmented or low volume to be really useful.

I think this site has great promise to be a well-known meeting place for the world-wide developer community to collect and share knowledge. And I hope we see a huge "Oracle Community" presence (Open Metalink+Forums+Wiki 2.0).

There are two things that have really interested me about this site:

Firstly, it was started by Joel Spolsky and Jeff Atwood (Coding Horror). Nuff said.

Second has been the community engagement during the development process. They've had a podcast which I've been listening to for the past 21 weeks. It's a great fly-on-the-wall kind of experience, having the chance to listen to the developers discuss the site while they are still building it. I hope we hear more development done this way.

Do you have a question or maybe some answers? I really recommend everyone should take the plunge and test it out.

Any site that spawned a parody site even before it was launched can't be all bad!

Derek Dukes was on net@nite #53 the other week, and it was really interesting to hear him talk about dipity.

Dipity is an experiment in information organisation, with time being the primary dimension currently being explored. Similar in a way to MIT's SMILE widget, which I was investigating a while back for visualizing time-based information.

Dipity shows a great deal of promise, and I like its emphasis on self-discovery and organisation information if directed (rather than everything having to be painstakingly entered). It is certainly a fun way to get lost for a few hours and learn a whole lot of stuff you never set out to study (just go to the home page and start checking out different timelines!)

Ulrich has already worked up a history of Oracle Releases. Not complete, but a fantastic visualisation that would be worth supporting and maintaining!

NB: I'm posting a static image here for now, because the embed code doesn't seem to work in all browsers at the moment.

The Bloggers at Openworld announcement promised to make this years conference a very different experience than ever before. Especially for people like myself who weren't able to attend. I'd like to add my big vote of thanks to the OTN, AppsLab and Oracle bloggers, whose combined efforts really did make this a great (first?) "virtual" OpenWorld.

Blogging In Action
By my last count we had 59 "openworld07" articles tagged at del.icio.us (37 in the official OTN del.icio.us tag cloud, 43 in Eddie Awad's Oracle OpenWorld News Aggregator and 26 tagged via Technorati). And so far 452 photos at Flickr.

I especially enjoyed the detailed conference diaries by Dimitri Gielis, Nathalie Roman and Doug Burns. Eddie Awad did a great job of community reporting outside the conference halls. They are just a few I enjoyed - too many other bloggers to mention, but on behalf of all the "virtual attendees" I thank you all!

Best Post has however got to be Oracle World and the invasion of the sports jacket on what is now one of my favourite blogs by the Sartorially Orientated Architects!

Suggestion #1 for the Community - Get our Common Tagging act together Eddie Awad posted some great tips for publishing and following OpenWorld. To be honest though, the adoption has been mixed. I suspect partially because there wasn't enough publicity or official sanction given to the ideas. I only noticed Eddie's post today - after the conference is over!

The most "wired" conference I've attended was ix2007 in Singapore, where instructions for twitter and tagging posts via Technorati were prominently included in the registration materials. It would be great to see this getting more official attention at OOW08.

If you blogged about OpenWorld, it's not too late to tag your posts "openworld07" and register with Technorati. And if you are reading Openworld articles, keep tagging them on deli.cio.us!

Podcasts and Streaming
Justin did a bang-up job with the OTN TechCasts from OpenWorld (can't pick a highlight since they all had great people with really relevant stuff to say). And it truely is OpenWorld when you can catch the keynotes without even attending the conference.

Suggestion #2 - Publish a Podcast Feed for the Keynotes and it's not too late for Oracle to post some feeds for the video, highlights and full audio feeds - with properly tagged audo files! I'm happy to download them all individually, but it would have been even more convenient to be able to subscribe to the keynotes feed and have them pop up in my iPod as they are published.

And now .. the analysis
I guess the one thing we haven't seen a great deal of so far is analysis of what went down at OpenWorld. I'm sure that will surface over the next weeks and months. My attention was drawn to two key themes..

• Virtualization The announcement of Oracle VM may prove to be a blockbuster if it is widely adopted in practice, I think once it is fully integrated with Enterprise Manager. It promises to completely change the game for how we build and maintain applications and technology infrastructure - from the bare-metal up, from one vendor.


• Fusion Scorecard the years are ticking by since Oracle played the Fusion card. Vinnie Mirchandani is just one who is asking "Where are the Fusion Applications?" For the cynics, the organisational changes in development are just another indicator that Fusion is devolving into a Technology/BI story i.e. just futzing around the edges. The optimists however will point out that getting the technology platform right is essential, and is not even news (see for example Cliff Godwin's Fusion AppCast on the Fusion Strategy Office from Jul-07).
  The true test for Oracle will not only be the delivery of appropriately componentized Applications, but the business insight it can encapsulate in the platform (hence why it is more important than ever that Oracle Development usefully engage the community through events like OpenWorld and the customer advisory boards).
  It is one thing to enthuse about how bringing Web2.0 and social networking features to CRM will have sales people the world over swooning, and quite another to have a head-on collision with the "Real World"!

That's all for now. Again, thanks especially to Justin (OTN), Jake and Paul (AppsLab) and the many Oracle bloggers who turned Oracle OpenWorld 2007 into a global, networked event. Kudos!