my recent reads..

Atomic Accidents: A History of Nuclear Meltdowns and Disasters; From the Ozark Mountains to Fukushima
Power Sources and Supplies: World Class Designs
Red Storm Rising
Locked On
Analog Circuits Cookbook
The Teeth Of The Tiger
Sharpe's Gold
Without Remorse
Practical Oscillator Handbook
Red Rabbit

Friday, May 25, 2007

The top 10 dead (or dying) computer skills

I try to avoid postings that just refer you to other blogs or articles, but I've succumbed. ComputerWorld's The top 10 dead (or dying) computer skills prompted a bit of nostalgia. I scored 91% [giving myself 1% for the time I bought a book on COBOL while at uni ... and had the good sense to take it no further than that!!].

OS/2 brings back memories, of which I was also reminded when I first checked out Google's code search and found some of my 1995 OS/2 code lying around! [NB: these days, I look at this code and shudder "Eek!... buffer overflow vulnerability!!" ... security just wasn't front of mind back then! ]. But it also reminds me of how much thought I put into the decision to adopt C++ on OS/2. It very much felt like "this is a decision that I'll live with for years". But 12 years later, in 2007, that decision-making process seems so naive and foreign. Now it is routine to dabble in a couple of scripting languages, some Java, even some C++. The right (or most fun) tool for the job, right?

If I could say "Programming Language Bigotry" is a skill (some people certainly practiced and honed it like it was), then boy am I glad it seems to be a thing of the past and perhaps it deserves to be #1 in this list!

After a brief post-dot-boom hiatus, the drammatic rate of evolution is certainly back, spurred on by Web 2.0 hype. The rate of technological change has indeed become so "normal" that a top 10 list hardly scratches the surface. Personally I would have voted for int 21h. I'm sure generations to come will have absolutely no idea what that means, but for me and presumably many others, I can sum up a year of computer science with that very phrase.

For many (myself included), To Be Alive is To Be Learning and vice versa. The new religion if you will. "Lifelong learning" or "learning for life" are too trite and miss the essential truth.

Other may say that to be continuously learning is to be in a perpetual state of childhood. Look at some of the toys we are learning about and maybe they have a point!

Postscript: I just re-listened to a WebDevRadio Episode 18 which reminded me that Coldfusion is not dead!! At least according to the guys at Mach ii..

Tuesday, May 22, 2007

Monitoring log files on Windows with Grid Control

The Oracle Grid Control agent for Windows (10.2.0.2) is missing the ability to monitor arbitrary log files. This was brought up recently in the OTN Forums. The problem seems to have been identified by Oracle earlier this year (Bug 6011228) with a fix coming in a future release.

So what to do in the meantime? Creating a user defined metric is one approach, but has its limitations.

I couldn't help thinking that the support already provided for log file monitoring in Linux must already be 80% of what's required to run under Windows. A little digging around confirmed that. What I'm going to share today is a little hack to enable log file monitoring for a Windows agent. First the disclaimers: the info here is purely from my own investigation; changes you make are probably unsupported; try it at your own risk; backup any files before you modify them etc etc!!

Now the correct way to get your log file monitoring working would be to request a backport of the fix from Oracle. But if you are brave enough to hack this yourself, read on...

First, let me describe the setup I'm testing with. I have a Windows 10.2.0.2 agent talking to a Linux 10.2.0.2 Management Server. Before you begin any customisation, make sure the standard agent is installed and operating correctly. Go to the host home page and click on the "Metric and Policy Settings" link - you should not see a "Log File Pattern Matched Line Count" metric listed (if you do, then you are using an installation that has already been fixed).

To get the log file monitoring working, there are basically 5 steps:

  1. In the Windows agent deployment, add a <Metric NAME="LogFileMonitoring" TYPE="TABLE"> element to $AGENT_HOME\sysman\admin\metadata\host.xml

  2. In the Windows agent deployment, add a <CollectionItem NAME="LogFileMonitoring"> element to $AGENT_HOME\sysman\admin\default_collection\host.xml

  3. Fix a bug in $AGENT_HOME\sysman\admin\scripts\parse-log1.pl

  4. Reload/restart the agent

  5. In the OEM console, configure a rule and test it


Once you have done that, you'll be able monitor log files like you can with agents running on other host operating systems, and see errors reported in Grid Control like this:


So let's quickly cover the configuration steps.

Configuring metadata\host.xml
Insert the following in $AGENT_HOME\sysman\admin\metadata\host.xml on the Windows host. NB: this is actually copied this from the corresponding host.xml file used in a Linux agent deployment.
<Metric NAME="LogFileMonitoring" TYPE="TABLE">
<ValidMidTierVersions START_VER="10.2.0.0.0" />
<ValidIf>
<CategoryProp NAME="OS" CHOICES="Windows"/>
</ValidIf>
<Display>
<Label NLSID="log_file_monitoring">Log File Monitoring</Label>
</Display>
<TableDescriptor>
<ColumnDescriptor NAME="log_file_name" TYPE="STRING" IS_KEY="TRUE">
<Display>
<Label NLSID="host_log_file_name">Log File Name</Label>
</Display>
</ColumnDescriptor>
<ColumnDescriptor NAME="log_file_match_pattern" TYPE="STRING" IS_KEY="TRUE">
<Display>
<Label NLSID="host_log_file_match_pattern">Match Pattern in Perl</Label>
</Display>
</ColumnDescriptor>
<ColumnDescriptor NAME="log_file_ignore_pattern" TYPE="STRING" IS_KEY="TRUE">
<Display>
<Label NLSID="host_log_file_ignore_pattern">Ignore Pattern in Perl</Label>
</Display>
</ColumnDescriptor>
<ColumnDescriptor NAME="timestamp" TYPE="STRING" RENDERABLE="FALSE" IS_KEY="TRUE">
<Display>
<Label NLSID="host_time_stamp">Time Stamp</Label>
</Display>
</ColumnDescriptor>
<ColumnDescriptor NAME="log_file_match_count" TYPE="NUMBER" IS_KEY="FALSE" STATELESS_ALERTS="TRUE">
<Display>
<Label NLSID="host_log_file_match_count">Log File Pattern Matched Line Count</Label>
</Display>
</ColumnDescriptor>
<ColumnDescriptor NAME="log_file_message" TYPE="STRING" IS_KEY="FALSE" IS_LONG_TEXT="TRUE">
<Display>
<Label NLSID="host_log_file_message">Log File Pattern Matched Content</Label>
</Display>
</ColumnDescriptor>
</TableDescriptor>
<QueryDescriptor FETCHLET_ID="OSLineToken">
<Property NAME="scriptsDir" SCOPE="SYSTEMGLOBAL">scriptsDir</Property>
<Property NAME="perlBin" SCOPE="SYSTEMGLOBAL">perlBin</Property>
<Property NAME="command" SCOPE="GLOBAL">%perlBin%/perl</Property>
<Property NAME="script" SCOPE="GLOBAL">%scriptsDir%/parse-log1.pl</Property>
<Property NAME="startsWith" SCOPE="GLOBAL">em_result=</Property>
<Property NAME="delimiter" SCOPE="GLOBAL">|</Property>
<Property NAME="ENVEM_TARGET_GUID" SCOPE="INSTANCE">GUID</Property>
<Property NAME="NEED_CONDITION_CONTEXT" SCOPE="GLOBAL">TRUE</Property>
<Property NAME="warningStartsWith" SCOPE="GLOBAL">em_warning=</Property>
</QueryDescriptor>
</Metric>

In the top TargetMetadata, also increment the META_VER attribute (in my case, changed from "3.0" to "3.1").

Configuring default_collection\host.xml
Insert the following in $AGENT_HOME\sysman\admin\default_collection\host.xml on the Windows host. NB: this is actually copied this from the corresponding host.xml file used in a Linux agent deployment.
<CollectionItem NAME="LogFileMonitoring">
<Schedule>
<IntervalSchedule INTERVAL="15" TIME_UNIT = "Min"/>
</Schedule>
<MetricColl NAME="LogFileMonitoring">
<Condition COLUMN_NAME="log_file_match_count"
WARNING="0" CRITICAL="NotDefined" OPERATOR="GT"
NO_CLEAR_ON_NULL="TRUE"
MESSAGE="%log_file_message%. %log_file_match_count% crossed warning (%warning_threshold%) or critical (%critical_threshold%) threshold."
MESSAGE_NLSID="host_log_file_match_count_cond" />
</MetricColl>
</CollectionItem>

A bug in parse-log1.pl?
This may not be an issue in your deployment, but in mine I discovered that the script had a minor issue due to an unguarded use of the Perl symlink function (a feature not supported on Windows of course).

The original code around line 796 in $AGENT_HOME\sysman\admin\scripts\parse-log1.pl was:
...
my $file2 = "$file1".".ln";
symlink $file1, $file2 if (! -e $file2);
return 0 if (! -e $file2);
my $signature2 = getSignature($file2);
...

This I changed to:
...
my $file2 = "$file1".".ln";
return 0 if (! eval { symlink("",""); 1 } );
symlink $file1, $file2 if (! -e $file2);
return 0 if (! -e $file2);
my $signature2 = getSignature($file2);
...

Reload/restart the agent
After you've made the changes, restart your agent using the windows "services" control panel or "emctl reload agent" from the command line. Check the management console to make sure agent uploads have resumed properly, and then you should be ready to configure and test log file monitoring.

Sunday, May 20, 2007

Validating Oracle SSO Configuration

A failing OC4J_SECURITY process recently had me digging out an old script I had put together to test Oracle Application Server Single-Sign-On (OSSO) configuration.

How and where the OSSO server keeps its configuration is a wierd and wonderful thing. The first few times I faced OSSO server issues I remember digging through a collection of metalink notes to piece together the story. It was after forgetting the details a second time that I committed the understanding to a script (validateSso.sh).

Appreciating the indirection used in the configuration is the key to understanding how it all really hangs together, which can really help if you are trying to fix a server config issue. Things basically hang together in a chain with 3 links:

1. Firstly, the SSO server uses a privileged connection to an OID server to retrieve the OSSO (database) schema password.

2. With that password, it can retrieve the SSO OID (ldap) server connection details from the OSSO (database) schema.

3. Thus the SSO Server finally has the information needed to connect to the OID server that contains the user credentials.

The validateSso.sh script I've provided here gives you a simple and non-destructive test of all these steps. The most common problem I've seen in practice is that the OSSO schema password stored in OID gets out of sync from the actual OSSO schema password. I think various causes of these problems, but the script will identity the exact point of failure in a jiffy.

Monday, May 14, 2007

Getting your Oracle Forum posts as RSS

In my last post I said one of my "Top 10" wishes for OTN was to be able to get an RSS feed of posts by a specified member to the Oracle Forums.

At first it may sound a bit narcissistic to have a feed that allows you to follow what you have written yourself!

It was my exploration of Jaiku that prompted the thought. Since "presence" is their big thing, I've been experimenting to see what's its like to have Jaiku aggregate all your web activity. So far it looks really cool - I love the interface. Must say that I'm not sure how useful Jaiku may turn out to be in the long run ... I suspect it works best if you have a whole lot of your friends also using it. NB: the Jaiku guys are particularly focused on mobile phones. Its not something I've tried yet because I think it would be a bit ex from where I live.

So Jaiku was the catalyst for me thinking about getting an RSS feed of my forum posts. Recently I've been trying to make an extra effort to contribute to the forums; frankly, they've always seemed a little quieter than I think they should be. So seeing any forums posts I make highlighted on Jaiku should be one of the neat indicators of my "web presence".

Problem is, while you can get a web page that lists your recent posts, and you can subscribe for email alerts when authors post, I wasn't able to find a way of getting an RSS feed for a specific author's posts.

So I created a little perl script that scrapes the HTML and generates an RSS feed (using XML::RSS::SimpleGen). I've packaged it as a CGI program on a server I have access to. That's what I registered on my experimental Jaiku site, and it works like a charm.

Until Oracle build this feature into the forums, feel free to take my oracleForumRSS.pl script and experiment away. Its pretty basic, but is generic for any forum user and ready to go. Sorry, but I'm not hosting it for direct use by others, so you'd have to find you own server with cgi or convert it to script that spits out a static rss xml page instead.

Post-script: Eddie Awad blogged on the "Easy Way" to do this using Dapper. Very cool, thanks Eddie!

Saturday, May 12, 2007

OTN Semantic Web - first look

My last post on the state of the OTN community was probably a little long on rhetoric. So I thought since the move of Oracle Blogs! to the much vaunted Semantic Web seems well underway, it would be worth taking a first look. The old site has taken a bit of stick for being, let's say, less than engaging.

The big improvement by using the Semantic Web is of course all the slicing and dicing it allows to hone in on posts of interest, with the blogger tag cloud giving instant feedback (and access) to the bloggers most active on a given subject.

I got to say though it doesn't take long before you realise this site needs a serious design and usability facelift. Urgently!

Maybe its easy to be critical with something new. Actually, no "maybe" about it...

OK, I won't nitpick too much. The biggest problem is that we've paid for all the slice & dice flexibility in the worst possible way ... all the content has been squeezed off the page!

If Oracle do nothing else, they should push all the filtering and clouds out of the way to make a nice, big section of the page available for the star of the show - the content. Use AJAX to make all the filtering available in an instant, but avoid the clutter. At that point, we have a decent replacement for the old blog site.

But for OTN to truely find its Web 2.0 mojo, I think the Semantic Web is probably laying some important foundations but its just the beginning.

My Top 10 OTN/Semantic Web Wishlist
OK, so quick brainstorm, and here's a selection of things I'd really love to see happen on OTN:

  1. Take Down This Wall ... between content types (not what Justin originally meant, I know)! The current layout of the Semantic Web page puts concrete delineation between content types (podcast, blogs, forums and other personally attributed content) at the top of the page. At first that may sound fine, but its locking us into a mindset and behaviour patterns that assume these are all distinct in terms of production and consumption. A destructive and divisive fallacy. Get rid of it, and make the content type just another filter.

  2. Long live the forums! These are places where you can actually have a conversation (instead of trying to have a conversation in blog comments). OK, so they look a bit dated, and the level of participation can be a problem. Nothing a lick'a paint can't fix, and use the Semantic Web to drive usage.

  3. Give Oracle's "celebrity" bloggers a personal forum that links neatly off their blog so that conversations can be usefully launched on the back of a blog post. What the heck, let everyone have a personal forum!

  4. Let me take an RSS feed of my forum post history. Its the kind of thing I'd put on my Jaiku page. At the moment, we can just watch via email.

  5. Let me take an RSS feed of any Semantic Web page I find/define (with all the current filtering etc).

  6. Drop the barriers to participation. The Semantic Web makes concerns of the "quality" of bloggers irrelevent. If they never post, they get buried. OTN registration should include an opt-in for an Oracle-host blog, or a link to an externally hosted blog.

  7. An OTN Community Site Badge. To promote participation, how about a logo/widget/javascripty thing that bloggers can put on their blog? It would both brand their blog as part of the OTN community, and also provide a link back to the OTN Semantic Web. It would be neat if it actually had a function (like clustermaps), but I can't think of anything useful right now.

  8. Invert the Semantic Web. OK, we're getting used to coming top down. But what if I *start* from someone's blog post? It could be a really neat thing to be able to then explore the Semantic Web from that point out ... to discover related or linked pages. Worth an experiment I think.

  9. Drop the dopomene theme. I'm in the Semantic Web, discovering fascinating information in ways that have never been possible before. This is really exciting! ... but the look and feel is blaring a very incongruous message that messes with your psyche. We need a better use of colour and graphics.

  10. ... and a dozen other insanely cool Web 2.0 things that I can't even imagine right now, and if I could I'd be well on my way to my first $1b.



Wow, I'm actually getting excited now...

No respect! Should Justin care?

Justin "Dangerfield" Kestelyn launched one of the most lively discussions the Oracle community has ever had with his "I Don't Get It" post some weeks back.

"In particular," he states, "Oracle gets zero credit in this community for its rather aggressive support of blogging (by employees and nonemployees), despite the fact that a rather large blogging community exists and has for some time"

Strangely, there seems to be pretty unanimous agreement that there is a large number of bloggers out there, and some very good ones at that. Vincent McBurney's blog made special mention of Nishant Kaushik, Rob Smythe and Steven Chan for example.

And if we also consider the OTN Podcasts (my favourites being the ones that feature interviews with the "names" like Tom Kyte and Wim Coekaerts), it seems to me pretty evident that we actually have a pretty healthy community of content creators.

But when I look back at what Justin actually said, he was referring specifically to the lack of credit from the Web 2.0 community.

I think the OTN team - and Justin in particular - have been doing a fantastic job with the blogsphere and podcasts. But is that enough to make a stir in the Web 2.0 scene? Maybe a year or two ago it would have, but not any more. Sadly (for Justin) it is now just all too routine.

How many "Web 2.0 firsts" can OTN really claim? The harsh reality is that to make a splash and get some respect in the Web 2.0 community, Oracle needs to do much much more. And I don't think its about content or whether our blog etiquette is any good. Leadership and innovation is the name of the game in two important areas:

  • How to build more effective social networks and find new and better ways for this to deliver real benefit to the community. At present, I'm not sure we even deserve the "community" moniker .. it feels more like a public swimming pool we all just happen to go to, rather than a forum (in the Roman sense) where we meet, discuss and debate.

  • Invent and apply cutting-edge Web 2.0 techniques and technologies to support this goal. Yes, this IS about technology;) The Web 2.0 community is incredibly dynamic and creative at this point. Take blogs for example. They've been around for a while. Long enough for people to discover that for some things they are really good, but in other ways they suck (like trying to have a "conversation" in comments). So we now have sites like twitter, tumblr, virb and jaiku all experimenting with different approaches and trying to push the envelope in meaningful ways. Its this kind of creative experiementation that we haven't seen Oracle doing in the past ... with the one recent exception being the semantic web (hopefully an indicator of more great things to come). If Oracle really wants Web 2.0 street cred, OTN should be the playground where it is seen to be exploring the outer limits of what is possible - some of which may find its way back into the Fusion Middleware product line.


One notion we must definitely reject is that somehow we need to coach all the Oracle bloggers into becoming Web Celebs. To do so totally ignores (and destroys) the value of diversity in the community. Personally, I identify five "kinds" of web presence we should embrace:

  1. Leadership and Product Management as a "conversation". These are the celebs and thought leaders engaging with the community, but very much with their corporate responsibility at the fore. Funny thing is, I had the impression Oracle was doing much better in this regard, but it doesn't hold up to inspection. Mark Wilcox is one of the few getting close. Perhaps commercial considerations actually make it a very hard thing to do without tipping the competition too much, or just sounding like a mouthpiece for marketing.

  2. Web 2.0 as Shared Memory. I think one of the completely understated revolutions going on. As I've blogged before, and epitomised by the likes of Alejandro Vargas, this is all about using the web to finally Get Knowledge Management Right. These tend to be boring as hell to try and follow unless they are right in your niche. Scenario: One day, you'll be sweating a problem. Ask google, and thank your lucky stars that there are people around like Alejandro.

  3. Living your professional life online. Probably the most common approach today on OTN. Its a diary, scrapbook and log. You may find some really good gems, but there's no harm in being obscure in this category... you're just one of the community and its often done more for your own personal benefit.

  4. The personal/social presence. And yes there is room for all those who are part of the community (because they work at Oracle for example) but just want to talk about baseball!

  5. The audience. Let's not forget the vast majority of people who are searching and reading, but will never do much more that perhaps post a question to a forum or maybe a comment on a blog. For a whole range of reasons there's no value or motivation for them to go further. Don't try and make them blog. It won't work. But should we do everything possible to make sure they are well served by the community ... yes!! Numerically, they ARE the community.


Justin finished his initial post with a somewhat flippant "...maybe I shouldn't even care!". But perhaps he unwittingly hit the nail on the head.

It's a truism in business that if you forget who your customers are, you are doomed. Similarly, if OTN becomes preoccupied with impressing the Web 2.0 community as its primary mission, I'm pretty sure they will find success "inexplicably" elusive (and prove that all of Justin's denials of it being a PR conspiracy are lies!!).

Success will come most easily if OTN focuses on serving its real constituency first - the Oracle community of employees, users and developers. Do that well, and if OTN is indeed pushing the boundaries, then the Web 2.0 cred will be the just reward.

I guess in a way its like being cool. Try to be cool and you'll fail. You just are (or not, as the case may be).

Friday, May 11, 2007

Do Oracle temp tables behave correctly under DBI?

Andon Tschauschev recently posted on perl.dbi.users concerning an apparent problem with temp tables "disappearing" between statements in the same session. He was using SQL Server via ODBC support.

The discussion and investigation continues, but it made me think to test if there's any similar strange behaviour with Oracle via DBI.

The temporary table model is somewhat different in Oracle, and centers around the "CREATE GLOBAL TEMPORARY TABLE.." statement. Temp table definitions are always global, but data is always private to the session, and whether data persists over a commit depends on whether the qualification "on commit preserve rows" or "on commit delete rows" is specified.

testOraTempTables.pl is a simple test script to check out the behaviour. Good news is that all seems to be a-ok. The temporary table definition is persistent across sessions, but data is not, and importantly (..the point of Andon's investigation..) data is preserved across DBI calls within the same session as expected.

Sample output from the test program:
C:\MyDocs\Testers2\perl\dbi>perl testOraTempTables.pl orcl scott tiger
[1st connection] connect to orcl {AutoCommit => 1}:
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit preserve rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have "on commit preserve rows" set:
select count(*) from t1 = 3
[2nd connection] connect to orcl:
[2nd connection] should be 0 rows because while the table definition is shared, the data is not:
select count(*) from t1 = 0
[2nd connection] disconnect:
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] should be 0 rows because this is a new session:
select count(*) from t1 = 0
[1st connection] drop the temp table: drop table t1
[1st connection] create global temp table:
create global temporary table t1 (x varchar2(10)) on commit delete rows
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 3 rows because we have autocommit off and not committed yet:
select count(*) from t1 = 3
[1st connection] should be 0 rows because now we have committed:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 1}:
[1st connection] insert 3 rows of data into it: insert into t1 values (?)
[1st connection] should be 0 rows because we have autocommit on and "on commit delete rows" defined:
select count(*) from t1 = 0
[1st connection] disconnect:
[1st connection] reconnect {AutoCommit => 0}:
[1st connection] drop the temp table: drop table t1
[1st connection] disconnect:

Thursday, May 10, 2007

Burning DVDs with unicode filename support

I have quite an eclectic music collection, which grew considerably last year when I spent quite a bit of time in Tokyo. My favourite Sunday afternoon haunt was HMV @ Times Square, checking out the Shunjuku South indie chart. A suitcase of CDs later, I finally got around to ripping my entire CD collection and adding it my old ripped vinyl collection. All nicely organised, named and categorised in iTunes.

Feeling very pleased with myself, I wanted to archive all my hard work onto a set of DVDs, only to find that my (Windows-based) recording software baulked at the Japanese, Korean and Chinese characters in the folder and filenames.

My first thought .. should just need to change the file system format? But it didn't take long to discover it wasn't so simple. In fact, of the half a dozen different disk burning softwares I ended up trying out (including most of the "major" names), all but one failed to handle my babel of disks correctly.

So I'd like to spread the word. VSO CopyToDVD was the ONLY product that worked. It costs a few bucks to buy, but I just downloaded the limited-time trial and it passed the unicode test with flying colours. I didn't really poke around all its features, but it seems to pack the lot. And the fact that I could download it and bang out a few DVDs in short order pretty much sums things up.

I'll be buying it, and if you also need to burn disks with unicode file/folder names then I can recommend you check it out too.

Wednesday, May 02, 2007

Getting environment variables on the Oracle database server

Say you have a connection to a remote Oracle Database server and want to get the ORACLE_HOME setting. Or any other environment variable for that matter. As far as I can see, Oracle doesn't provide any direct, supported way to do this.
In 10g however, there's an interesting procedure DBMS_SYSTEM.GET_ENV available which does the job:
set autoprint on
var ORACLE_HOME varchar2(255)
exec dbms_system.get_env('ORACLE_HOME',:ORACLE_HOME)

PL/SQL procedure successfully completed.

ORACLE_HOME
-----------------------------------------
D:\oracle\product\10.2.0\db_1

DBMS_SYSTEM is an undocumented/unsupported package. It mainly seems to be an internal utility function for debugging and event monitoring. The package itself is obfusticated, but we can discover a little about it from the data dictionary. The USER_PROCEDURES view lists the individual procedures available in the package:
select PROCEDURE_NAME from USER_PROCEDURES where OBJECT_NAME = 'DBMS_SYSTEM';
PROCEDURE_NAME
------------------------------
DIST_TXN_SYNC
GET_ENV
KCFRMS
KSDDDT
KSDFLS
KSDIND
KSDWRT
READ_EV
SET_BOOL_PARAM_IN_SESSION
SET_EV
SET_INT_PARAM_IN_SESSION
SET_SQL_TRACE_IN_SESSION
WAIT_FOR_EVENT

And USER_ARGUMENTS can tell us about the parameters. For example:
select OBJECT_NAME,ARGUMENT_NAME,POSITION,DATA_TYPE,IN_OUT
from USER_ARGUMENTS
where PACKAGE_NAME='DBMS_SYSTEM' and OBJECT_NAME='GET_ENV'
order by POSITION;

OBJECT_NAME ARGUMENT_NAME POSITION DATA_TYPE IN_OUT
------------- -------------- ---------- --------- ------
GET_ENV VAR 1 VARCHAR2 IN
GET_ENV VAL 2 VARCHAR2 OUT

Given an environment variable name (VAR), GET_ENV returns its value (VAL). These values are coming from the system environment that belongs to the Oracle server process. If you have a dedicated server config, the environment is inherited from the tnslsnr process that spawned the server process. If shared server, then the environment is inherited from whatever process (PMON? PSP0?) that started the shared server process.
So an interesting poke around in some Oracle internals, but there are lots of reasons why you shouldn't use this trick in any production situation!

  • It is undocumented and unsuppported. The "get_env" method seems to have appeared in 10g, but there's also no guarantee it will be present in any future versions.

  • There are better solutions. SQL client code shouldn't directly depend on server environment variables.

  • Remember it is instance specific, and may be misleading in a RAC environment.

Sunday, April 22, 2007

Synchronising two directory trees - update

I've just released an update to tree-sync-2.2.pl on CPAN. This fixes a few bugs with special character handling in filenames.

I've currently three main uses for the script:
  • On my Windows XP laptop, I use it to backup my working files to an external drive. For this I use the "fwdonly" mode so that the external drive copy is a perfect copy of what I have on the laptop.
  • Also on my laptop, I have a collection of files that I sync with the external drive in "full" sync mode (default). This means changes will be exchanged bidirectionally. This is useful because sometimes I will drop files into this area with the external drive connected to another machine.
  • Thirdly, I use it on my server to sync a collection of files from cvs into a web-visible area.


PS: As of Oct-2008, the tree-sync project is now on github. Use this if you want to contribute to development. Of course, releases will still be distributed for use on CPAN.

Find and tail the Oracle alert log

"Where's the alert log?" .. usually the first thing you want to know when looking at a new database.

Oracle's Grid Control solves this problem very well with it's web interface. But not always available.

Or in my case recently, where I had 6 databases setup on a single machine for various testing scenarios, I was getting tired of cd'ing all over the place, forgetting paths, or ending up with too many windows open for my own good. Getting well sick of this, I did a quick hunt for scripts to help but surprisingly didn't find much. So diving in, I created oraAlertLog.sh and now I'm happy;)

This script is for running on the database server. The Oracle environment must be set, and - at least for the first call - the database must be available so that the "background_dump_dest" parameter can be obtained. The script will cache the alert log location so that it will still work if the database happens to be down.

After getting this running I thought "duh!", should have been in perl so it would be possible to run on any platform supported by Oracle. Here's a version in Perl: oraAlertLog.pl. It requires Perl with DBI and DBD::Oracle .. the Perl distribution included with Oracle Database is fine for this (you just need to make sure the environment is properly configured).

Wednesday, March 28, 2007

Request header rewrites with Java servlet filters

A collegue and I have been looking at a setup with completely separate Oracle Portal (with SSO) and Oracle Collabsuite installations, and we wanted a simple way to have users automatically logged into Collabsuite after logging into Portal. If you are not familiar with Collabsuite, just think "J2EE" application.

Normally you would deploy a consolidated infrastructure, which makes this a no-brainer, but for various reasons we wanted to keep these two environments quite separate.

The details of how we did this are not really pertinent, but the bottom line is that we had everything sorted with one exception: the LDAP realm on Portal did not match Collabsuite. Everything was nicely working except the Collabsuite web applications keeled over, because the "osso-user-dn" request header set by the Portal SSO did not match Collabsuite.

If only we could hack/rewrite the osso-user-dn to fixup the realm part!

Now with Apache 2.0, this is probably quite easy by using the RequestHeaders directive in httpd.conf. That didn't exist in Apache 1.3, which unfortunately is what we are using.

This lead me to investigate what could be done at the J2EE level, and I discovered for the first time the servlet filter features in the Servlet API 2.3.

There are a few good tutorials floating around the web, such as Jason Hunter's JavaWorld article, but nothing I've found yet specifically demonstrates header rewrite.

Its pretty simple though. I've put together a demo RewriteRequestHeaderFilter with sources (download: RewriteRequestHeaderFilter-1.0-src.zip). It contains a complete demo site, but is also packaged and ready to insert into any arbitrary web application (just deploy the jar and fiddle the site's web.xml).

So, if you ever find yourself wanting to fiddle request headers in the J2EE environment and don't have "external" options, the RewriteRequestHeaderFilter could be just the ticket.

Postscript 2008-06-02: I've moved this to its own sourceforge project now.

Sunday, March 25, 2007

Who's bound to that port?

Recently wanted to track down the details of the process that had a specific port open. I checked out the O'Reilly Linux Server Hacks book, and hack #56 was pretty much what I wanted. I scriptified it somewhat as follows. Note that this only looks at tcp:
#!/bin/bash
port=$1
procinfo=$(netstat --numeric-ports -nlp 2> /dev/null | grep ^tcp | grep -w ${port} | tail -n 1 | awk '{print $7}')

case "${procinfo}" in
"")
echo "No process listening on port ${port}"
;;
"-")
echo "Process is running on ${port}, but current user does not have rights to see process information."
;;
*)
echo "${procinfo} is running on port ${port}"
ps -uwep ${procinfo%/*}
;;
esac

As you can see, this works by getting a little bit of process info from netstat, then using ps to get the full details. Download the script here: whosOnPort.sh

Sunday, February 25, 2007

A quiet revolution in KM?

Many thanks to Justin Kestelyn who recently highlighted Alejandro Vargas' Blog. Alejandro has been quietly putting together a powerful Body of Knowledge on RAC and related issues [still not a "Featured Employee Blog", but it should be!].

I must say, this was a Damascan Road event for me.

During the mid to late 90's, I was heavily involved in Content and Knowledge Management solutions for enterprises. We struggled with concepts of "Human Capital" and "Intellectual Property". In most cases, I was involved in helping companies setup the technical and organisational infrastructure to better empower "knowledge workers" to share, find and reuse information. The solutions of the day were corporate intranets, portals, search engines, mail, groupware and such-like.

I have been somewhat removed from that scene over the past 5 years, and it was only when seeing Alejandro's blog that it really struck me that a quiet revolution has been underway in Corporate Knowledge Management (KM).

The main problems we used to face in knowledge management were cultural, not technical. How to encourage participation? How to reward contribution and reuse? How to effectively organise and locate relevant information? How to measure and align with corporate goals? After years of practice (in both senses of the word), I came to the realisation that the most important contributing factor always came down to having a pivotal community member (not necessarily the formal "leader") around which others would cluster and be inspired to act.

Ironically, the solution to such cultural issues appears to have been technical if I am reading the megatrends correctly.

We saw the first inklings of progress with wikis, which facilitate rapid collective development of a body of knowledge, but now I realise that it is with blogs and podcasts that we are in the process of making a significant step-change.

Now blogs are nothing new. But predominantly we have seen them as a pop cultural phenomenon, creating our first issue of Web Celebs.

Alejandro's blog however epitomises the new breed of not-quite corporate blogging... blogging on a specific topic of interest that relates to their work, but done under personal editorial control. In the past (and still true today), Alejandro's employer would have provided corporate facilities to capture and share the kind of information he is publishing. I'm sure Alejandro still diligently complies with such mandated systems, but it is his blog which is having the most positive impact on the world.

So how is corporate knowledge management evolving with the advent of blogs and podcasts? To my mind, there are two important factors to note:


  • Accessibility If a tree falls in a forest and no one is there, does it make a sound? Similarly with knowledge management, what is the value of sharing if no-one uses your work? Corporate knowledge management systems were once able to deliver the optimal audience for your work (if you tried very very hard over a very very long period of time), but the rise of the internet and blogging in particular has changed that dynamic forever.

  • Attribution some would say "ego". One critical factor in the success of blogs is that the primary structural organisation is by attribution. It is your collection. Secondary organisation by news aggregators, semantic web or search engines cannot dilute the fact. The days of consigning your masterpiece into the black hole of corporate knowledge are fast receeding. For some, "ego" may be the prime motivation, but I believe it is usually a little more complex than that. Blogs tend to tell a story over time. Posts will be related. As a blog author, you will continually have you history of posts in front of you. Not only does this reinforce the sense of continuing narrative, it illuminates the "gaps" in your story and therefore compels further contribution to fill the void. Just as a novellist may feel compelled to complete their work no matter how unlikely it may be to find a welcoming readership, the blogger is likewise committed to continue what they start. Note that wikis are tapping into a fundamentally different attribution dynamic, one not so much governed by "ego", but by "tribal" aliegance.


"Information is power" is a hackneyed phrase. And I think it doesn't do justice to what is happening in the world today. It is too small town.

I prefer to think in terms of "potential accessibility x attribution = self-perceived value", and "referenced accessibility x attribution = actual value" i.e. power. And I think what we are seeing now is that the tools available (wikis and blogs in particular) are delivering a "value/power" formula that is starting to unleash an unprecedented wave of knowledge sharing and collaboration.

Implicit in the above is a decided shift in "editorial control". No longer is it a neat case of the knowledge worker submitting their work to the whims of the corporate machine. The blogger is in control. That is a significant challenge to the corporation, especially those for which information is the primary product they sell. Alejandro is perhaps fortunate to work a company that makes its money from selling software ... sales that his blogging supports and enhances. But a tax or legal firm? That's a different kettle of fish, since they primarily trade in "knowledge" i.e. expert advice.

So what response should we expect from corporations and developers of "km"/blogging software? Here are a few thoughts...


  • We need to see the modern tools (blogs, wikis and podcasts) incorporated as primary sources for corporate KM strategies. That means integration between blogging tools and the corporate KM systems such as customer care/help systems, search engines, semantic webs and intranet/internet portals.
  • Locking away such tools as "internal only" resources works against the accessibility imperitive. There are of course valid concerns over confidentiality. To accomodate this dilemma, metadata (and systems) need to support the concept of confidentiality. That is, employees should be able to selectively target internal, customer and public audiences when they post or broadcast. We should be able to adapt approval/moderation processes in accordance with confidentiality.
  • In a corporate setting, it doesn't take long before people start questioning the "business value" of all this blogging etc. Personally, I hav ea foot in both camps. On the whole, I believe very few people can justify blogging exclusively on company time. People like Tom Kyte for example [ if we consider Ask Tom a specialised kind of blog]. For most of us however, it's a shared "value capture" equation. Sure, the blogs/wikis etc may enhance our contribution to the business, but a large part can also be a personal development exercise in enhancing our long term career value. In crude terms, maybe that next job will be clinched on the strength of your blog. So just like I read professional books of my choice on personal time, that's when I blog too. As employees, we need to be realistic about this and manage our time accordingly.
  • For the corporations themselves, "value capture" can be a trickier proposition. And this depends on the nature of the business too. For a company like Oracle, having employees and users positively blog about its software can arguably give a major (but unmeasurable) sales boost by enhancing "brand value". Maybe we are not quite there yet, but I can imagine a day soon where the lack of an active blogsphere around your software product will make it almost impossible to sell. However to take an extreme example, consider CNN. What if all your reporters actively blogged too? Or if all Ernst & Young's accountants popped up on blogspot? It is harder to implicitly conclude there would be a positive impact on the bottom line. At this point, I see no other solutions than trying to achieve a suitable compromise. The softwares we use play an important role in providing the functionality to achieve that balance faster.



All of the above may be bleeding obvious to some and well discussed in the KM journals, but everyone needs their "Ah-ha" moment, and I just had mine.

Saturday, February 24, 2007

Securing your home router #2

Just listening to Security Now #80 in which Steve and Leo discuss a Javascript exploit that aims to compromise home routers with default password.

I strikes me that its a very short leap to combine this Javascript approach with the broken security implementation in certain routers that I blogged on recently. In fact, the Javascript approach overcomes the main limitation of the vulnerability hack which is that it required LAN access to your router to do anything more than mischief.

Wednesday, February 21, 2007

Generating CLOB/CDATA elements with XMLDB

Generating CDATA elements with Oracle XMLDB recently got a good airing in the XMLDB forums.

I won't reiterate the discussion there, but offer a summary and some sources.

It seems the current state of affairs is that if you need to generate large text elements with XMLDB you have two options:


  • use DBMS_LOB procedural code to manually construct a CDATA element, or
  • use XMLTYPE views to construct an XML-encoded element


In both cases you need to be careful not to do anything that casts or converts to varchar to avoid the inherent size limitations.

Note that the XML-encoding in XMLTYPE views is automatic, and I currently don't know how to tell it not to encode but rather quote as CDATA.

Some sources and examples:


  • clob-cdata.pl is a Perl script using DBI that demonstrates how to generate an XMLTYPE view over an arbitrary CLOB element, without using XMLSchema. In this case, the CLOB will be automatically XML-encoded [clob-cdata-nonschema.sql is just the plain SQL].
  • clob-cdata-schema.sql shows how you can do a similar thing, but using an XMLSchema definition.
  • clob-cdata-small.sql shows how you can create CDATA elements where the text size is small using the XMLCdata function

Letting strangers on your Wifi .. need a reason why not?

Sometime back I was hacking my wifi admin pages (to let me register a certain NTP server .. but that's another story), and in the process discovered how broken the security is on my device (an SMC SMC2804WBRP-G Barricade router).

Basically the security check - to make sure you are a valid, logged-in administrator - just redirects to the "action" page which does no further checking of your credentials.

It doesn't take a genius to figure out that if you just post directly to the "action" page you can probably bypass authentication. At least, that's what occured to me, so I tried it and (too my surprise nonetheless) it worked. Or didn't work, depending on your point of view!

To their credit(!), the routine to reset the admin password does require you to send the existing password, but other operations have no barrier.

Here's a simple Perl script that demonstrates how you can "own" an SMC router of this type. It basically lets you reset factory defaults, after which you know the admin password (smcadmin). The factory default has no wifi enabled, so to make any further use of the router you must be connected to a LAN port. But certainly one way to wreck your neighbour's weekend.

I reported this vulnerability to SMC and CERT, but haven't heard whether any action has been taken to fix this.

I also don't know how many other models or brands of routers are susceptible to the same fault. But take this as a warning (and the reason why I am posting this information) ... if you want to offer wifi services to others, make sure your device is not subject to this kind of flaw first!

Running Instant Client on Linux

I recently had cause to install and configure the Oracle Instant Client under Linux. As I've written before, it is a breeze to get a client up and running.

I did find however that the way the instant client deploys its files can break makefiles and so on if you are doing C/C++ development.

I wrote a simple script (see installInstantClient.sh) to install and cleanup an Instant Client and take care of a few things like:


  • move executables into a /bin subdirectory
  • move libraries into a /lib subdirectory
  • create links for commonly know library names
  • create a default network/admin/tnsnames.ora
  • suggest appropriate environment settings for your .bash_profile


Note that the script is written to explicitly handle the basic+sdk+sqlplus installation. If you want to use it for a different combination of kits it will need some simple modification.

Time to revamp PL/SQL?

Welcome the year of the pig! That maybe appropriate, because I can't help thinking that its 2007 already, and high time that Oracle gave a serious revamp to doddering PL/SQL. Doddering, you say? Well, yes. The past few years have seen incredible language innovation (read Ruby, Python, even JavaScript getting a new AJAXian lease of life) but PL/SQL seems to have been left by the wayside.

I do not know exactly what Oracle have in store for us with 11g, but I sincerely hope it addresses some of my major beefs, which I'd summarise as follows.

1. Give me CLOB-sized VARCHARs
I have a thumping server with a gazillion gigabytes of memory, so why do I spend so much time working around 4000 byte or 32k limits in PL/SQL? Or worse, have my app fail randomly in production when a certain bit of data slips the limit.

These are internal RDBMS implementation details that application programmers should not be concerned with. That's not to say that application programmers shouldn't be concerned about performance, just that they shouldn't be constrained by such arbitrary fundamental restrictions.

OK, so the 4000 byte limit is a SQL thing. But once I am manipulating string data in PL/SQL, if I need a Mb, then please Oracle let me use a Mb.

This is the 21st Century guys. We're not all dealing with simple accounting data. Handling large volumes of text is de rigeur. Text, not nameless objects, be it XML, HTML or just plain ASCII/Unicode.

2. Function-style DBMS_LOB interface
Partly as a result of (1), we often need to resort to DBMS_LOB for dealing with large text. Since it has a largely procedural interface, this usually means we need to drop into PL/SQL when in fact plain SQL would have been preferred.

Rather than deal with temporary LOBs etc, I'd prefer just a function-style interface so most of the LOB handling could be done inline with SQL.

3. Get over the VARCHAR limits with XMLType and XML-related Packages
OK, maybe just a variation on the same theme, but one of the most common situations where application programmers will run into varchar limits is when working with XML. To many of the various XML-related functions and packages are hamstrung by their lack of native support for CLOBs. In many cases, this means what can be elegantly programmed "in the lab" has no practical use because of these limits.

4. Better documentation - proper definitions, real examples
IMHO, most Oracle docs are written according the the "Anne Elk" school of documentation.

It can't get much worse than DBMS_XMLDOM, but let me take an example at random... open the PL/SQL Packages and Types ref and page down a few times. Let's look at DBMS_APPLICATION_INFO.SET_CLIENT_INFO. Parameter is client_info IN VARCHAR2, uhuh. Definition is "Supplies any additional information about the client application". HELLO? Did that actually help anyone who didn't already know what the parameter was for or how it was used?

Normally with most reference guides this is when you turn to the examples to "reverse engineer" the definition. But there are no examples as a rule in the reference docs.

You may find an example in User's Guides, but then again, you may not;) That needs to change.


OK, that's a few for starters. Got any other beefs? Please post a comment, I'd be interested to hear what you have to say.

Synchronising two directory trees

I've released an update to Chang Liu's tree-sync.pl script on CPAN (currently tree-sync-2.2.pl).

I had some problems with the original script, so this is a re-write that uses an algorithm based on File::Find module.

I've only been testing on Windows and Linux, but so far so good - it solves my immiedate issue which was to have a way of maintaining a synchronised "backup" copy of various filesystems.

PS: As of Oct-2008, the tree-sync project is now on github. Use this if you want to contribute to development. Of course, releases will still be distributed for use on CPAN.

Sunday, February 18, 2007

XSL Transforms in the database

Previously, I wrote on how to extract XPath refs from an arbitrary XML document. Well, you can actually do this inside a database too - specifically Oracle 9i/10g with built-in XMLDB support.

Say we have XML data and XSL templates stored in a simple table:

CREATE TABLE x1 (item varchar(25) primary key, xml xmltype);

Where the data is stored with item="data", and the XSL template to extract paths to text is stored as item="xsl-to-text", then our transform may be executed as simply as this:
select 
XMLTransform(
xml,
(select xml from x1 where item='xsl-to-text')
).getstringval() into v_out_text
from x1 where item='data';
dbms_output.put_line(v_out_text);

A full sample script is available here.

Extracting XPath refs from an XML document

I was inspired by a recent post in the XMLDB Forum to look at the question of how to extract a complete list of XPaths and the associated text node values from an arbitrary XML file. I looked into an XSLT approach which I'll describe here.

Say we have an XML file like this:
<?xml version="1.0" encoding="ISO-8859-1"?>
<Library>
<Books>
<Book>
<Author>
<Last>Perry</Last>
<First>Anne</First>
</Author>
<Title>Long Spoon Lane</Title>
</Book>
</Books>
<Members>
<Member>
<Name>Paul</Name>
<Joined>2005-11-01</Joined>
</Member>
</Members>
</Library>

And our objective is to produce a listing like this:
/Library/Books/Book/Author/Last():Perry
/Library/Books/Book/Author/First():Anne
/Library/Books/Book/Title():Long Spoon Lane
/Library/Members/Member/Name():Paul
/Library/Members/Member/Joined():2005-11-01

After some investigation and reference to sites like Path Tracing and the XSLT 1.0 spec I arrived at what I think is the simplest xsl possible:
<?xml version="1.0" encoding="windows-1252" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>

<xsl:strip-space elements = "*" />

<xsl:template match="text()">

<xsl:for-each select="ancestor-or-self::*">
<xsl:text>/</xsl:text>
<xsl:value-of select="name()" />
</xsl:for-each>

<xsl:text>():</xsl:text>
<xsl:value-of select="." />
<xsl:text>&#xA;</xsl:text>

<xsl:apply-templates/>

</xsl:template>

</xsl:stylesheet>

What is going on here?

Well, firstly note that we strip-spaces and then match on all text() nodes - this ensures we skip all the pure whitespace nodes.

The magic that generates the XPath is the the "for-each" over all "ancestor-or-self" elements which generates the XPath identifier. Then we simply add the text value on the end.

A variation on the XSL template that produces an XML structure instead of text is as follows. It really varies just in terms of output formatting:
<?xml version="1.0" encoding="windows-1252" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>

<xsl:strip-space elements = "*" />

<xsl:template match="/">
<items>
<xsl:apply-templates/>
</items>
</xsl:template>

<xsl:template match="text()">
<item>
<path>
<xsl:for-each select="ancestor-or-self::*">
<xsl:text>/</xsl:text>
<xsl:value-of select="name()" />
</xsl:for-each>
</path>
<value>
<xsl:value-of select="." />
<xsl:apply-templates/>
</value>
</item>
</xsl:template>

</xsl:stylesheet>

Tuesday, February 13, 2007

Handling namespaces with DBMS_XMLDOM

The PL/SQL package dbms_xmlgen has been around for a while now, but it sort of suffers from a lack of doc and examples. Getting it to handle namespaces properly is a good example - it seems like it should be a bit more intelligent than it actually is!

Let's say we wanted to generate this:
<?xml version="1.0"?>
<a:gadgets xmlns:a="uri:a" xmlns:b="uri:b" >
<b:phones/>
</a:gadgets>

Part of the solution would look like this (enough to illustrate some key points):
...
V_CURRENT_EL := DBMS_XMLDOM.CREATEELEMENT(doc => V_DOMDOCUMENT, tagName => 'gadgets', ns => 'uri:a');
DBMS_XMLDOM.SETATTRIBUTE(elem => V_CURRENT_EL, name => 'xmlns:a', newvalue => 'uri:a');
DBMS_XMLDOM.SETATTRIBUTE(elem => V_CURRENT_EL, name => 'xmlns:b', newvalue => 'uri:b');
V_CURRENT_CHILD_NODE := DBMS_XMLDOM.makeNode(V_CURRENT_EL);
DBMS_XMLDOM.SETPREFIX(n => V_CURRENT_CHILD_NODE, prefix => 'a');
V_CURRENT_NODE := DBMS_XMLDOM.APPENDCHILD(n => V_CURRENT_NODE, newChild => V_CURRENT_CHILD_NODE);
...
Here are the few guidelines I've "inferred" about DBMS_XMLDOM behaviour (note that this is all my reverse-engineered understanding, so I may be well off base!):

1. namespaceURI/ns params of createDocument, createElement methods just declare the namespace of the entity and have no practical impact on the generated xml

So the xml output would be the same even if you changed line 1 of the code above to:
V_CURRENT_EL := DBMS_XMLDOM.CREATEELEMENT(doc => V_DOMDOCUMENT, tagName => 'gadgets', ns => 'someotheruri');
NB: you can verify the namespace is internally altered using DBMS_XMLDOM.GETNAMESPACE

2. to set the xmlns attributes of the document, you need to explicitly create the attribute. You need lines 3 and 4 of the code above.

3. to cause a node or attribute to be serialised with a namespace prefix, you need to explicitly request it with the SETPREFIX call e.g.
DBMS_XMLDOM.SETPREFIX(n => V_CURRENT_CHILD_NODE, prefix => 'a');  


4. however, the namespace prefix will only be used in the serialised xml if you also set a namespaceURI/ns (from point 1).


5. there is no validation or verification that the namespace prefix you set (point 3) matches a namespace you have declared (points 1 and 2).


As you can see, DBMS_XMLDOM is really just a very thin wraper to programmatically generate XML at the lowest level. It leaves much of the "intelligence" for you to provide!

Bearing that in mind, you probably can think of many cases where using DBMS_XMLDOM is not the right answer, but is sort of brute force/naive.

If you have data in Oracle and want to produce some complex XML output, there are many smarter alternatives, such as defining an xmltype view over the source data and then doing an xsl transform into the desired document format. Aside from being much less tiresome than DBMS_XMLDOM, it has the benefit of separating presentation (the template) from the data. When you need to change the output format, you just change the template rather than hacking away at the DBMS_XMLDOM code.

Sunday, February 11, 2007

Complex SOAP::Lite requests - my rules for SOAP::Sanity!

Previously, I mentioned I'd come back to more complex request and response structures with SOAP::Lite.

Frankly, I haven't posted because I can't avoid the feeling that there's still a little more to unravel. Did I mention that good documentation is sparse? ;) Byrne and others have posted some good stuff (see for example the majordojo soaplite archive and this hands-on tour at builder.com), but mostly you'll find the experiences shared go along lines like "...after hacking around for a bit, I found that this worked...".

But is it possible to try and pin down at least a couple of guidelines for using SOAP::Data? Well, so far I can't claim to solving it all, but I am able to share a few anchors I've tried to plant for my own sanity!

My Rules for SOAP::Sanity


In the following "rules", $soap is a pre-initialised SOAP::Lite object, as in:
my $soap = SOAP::Lite->uri ( $serviceNs ) -> proxy ( $serviceUrl );

1. The value of a SOAP::Data element becomes the content of the XML entity.


It may seem bleeding obvious. Nevertheless, get this idea fixed in you head and it will help for more complex structures.

So if we are calling a "getHoroscope" service with the following request structure:
<getHoroscope>
<sign>Aries</sign>
</getHoroscope>

"Aries" is the value, i.e. the content, of the XML entity called "sign". Thus our request will look like this:
$data = SOAP::Data->name("sign" => 'Aries');
$som = $soap->getHoroscope( $data );

2. To create a hiearchy of entities, use references to a SOAP::Data structure.


In (1), the content of the entity was a simple string ("Aries"). Here we consider the case where we need the content to encapsulate more XML elements rather than just a string. For example a request with this structure:
<getHoroscope>
<astrology>
<sign>Aries</sign>
</astrology>
</getHoroscope>

Here "astrology" has an XML child element rather than a string value.
To achieve this, we set the value of the "astrology" element as a reference to the "sign" SOAP::Data object:
$data = SOAP::Data->name("astrology" =>
\SOAP::Data->name("sign" => 'Aries')
);
$som = $soap->getHoroscope( $data );


3. To handle multiple child entities, encapsuate as reference to a SOAP::Data collection.


In this case, we need our "astrology" element to have multiple children, for example:
<getHoroscope>
<astrology>
<sign>Aries</sign>
<sign>Pisces</sign>
</astrology>
</getHoroscope>

So a simple variation on (2). To achieve this, we collect the "Aries" and "Pisces" elements as a collection within an anonymous SOAP::Data object. We pass a reference to this object as the value of the "astrology" item.
$data = SOAP::Data->name("astrology" => 
\SOAP::Data->value(
SOAP::Data->name("sign" => 'Aries'),
SOAP::Data->name("sign" => 'Pisces')
)
);
$som = $soap->getHoroscope( $data );

4. Clearly distinguish method name structures from data.


This is perhaps just a style and clarity consideration. In the examples above, the method has been implicitly dispatched ("getHoroscope").
If you prefer (or need) to pass the method information to a SOAP::Lite call, I like to keep the method information distinct from the method data.

So for example, item (3) can be re-written (including some additional namespace handling) as:
$data = SOAP::Data->name("astrology" => 
\SOAP::Data->value(
SOAP::Data->name("sign" => 'Aries'),
SOAP::Data->name("sign" => 'Pisces')
)
);
$som = $soap->call(
SOAP::Data->name('x:getHoroscope')->attr({'xmlns:x' => $serviceNs})
=> $data
);

I prefer to read this than have it all mangled together.

That brings me to the end of my list of rules! I am by no means confident that there aren't more useful guidelines to be added, or that in fact the ones I have proposed above will even stand the test of time.

Nevertheless, with these four ideas clearly in mind, I find I have a fair chance of sitting down to write a complex SOAP::Lite call correctly the first time, rather than the trial and error approach I used to be very familiar with!

Safe OCCI createStatelessConnectionPool usage

I've been working a bit with Oracle's OCCI (the C++ API for Oracle Database), and stateless connection pools in particular.

I've noticed a particular behaviour that's important to be aware of when creating the connection pool (using the oracle::occi::Environment method "createStatelessConnectionPool"). The problem is that this call will fail if you have some kind of conenction or TNS error, leaving you with an unusable and invalid pool.

To give a concrete example, if you create a connection pool like this:
scPool = env->createStatelessConnectionPool(...

what I find is that if the database is down (for example), this call with throw a TNS error like ORA-12541: TNS:no listener, and the scPool object is invalid (but not a null reference).

if you attempt to use the pool thereafter, e.g.:
if (scPool) cout << "MaxConn=" << scPool->getMaxConnections() << endl;

then firstly "if (scPool) .." wont protect you (because its not null), and the getMaxConnections method call will throw an uncatchable segmentation fault (this is Linux x86 I'm using)

The workaround of course is to null your scPool if you catch the TNS error, and then if you want a robust application that must keep running even if the connection pool is not created, everytime you try and get a connection from the pool you should also first check to see if you have a pool object to use (and if not, try and create it again).

Tortuous to say the least!

I would have expected that the desired behaviour should be for createStatelessConnectionPool to return a valid connection pool even if connections are not possible at that point in time, and that for the TNS errors to be only thrown if and when you try and get a connection from the pool.

Anyone have a view? ... bug, ER or expected?

12-Feb, an update: I've since discovered that this behaviour is true only if you set a "minumum connections" >0 for the pool. If you set "minumum connections"==0, then the behaviour is as I would expect - the pool is created without error, but you may hit an error when attempting to get a connection from the pool.

Monday, January 29, 2007

Handling range parameters in Bash

I've come across a few occasions where I wanted to specify a "range parameter" for Bash scripts. Like "1..4" meaning do for 1,2,3 and 4.

Here's a simple trick that uses the (relatively obscure) variable expansion and substring replacement capabilities of the shell.
#!/bin/bash
v_range="1..3" # or you could have taken it as a script parameter
v_start=${v_range%%.*} # chomp everything from the left-most matching "."
v_end=${v_range##*.} # chomp everything up to the right-most matching "."

The repeated %% and ## basically mean you will get a "greedy" match, so you can say "1..4" or "1....5"; it doesn't matter how many repeats you have of the range delimiter. Of course, you can choose other delimiters such as a hyphen, as in "5-10", if you wish.

Now that you have extracted the start and end indices, you can then loop or whatever to your hearts content:
for ((a=v_start; a <= v_end ; a++))
do
echo "Looping with a=$a"
done

For more information on variable expansion, see 9.3 in the Advanced Bash Scripting Guide.

Postscript 5-Feb-2008:
We live and learn! Hat tip to buford for alerting me to the seq utility, which simplifies the iteration over a range, as in:
for a in `seq 1 10`
do
echo "Looping with a=$a"
done

You still need to determine the start and end values of the range, which is the whole point of the variable expansions approach posted here.

Wednesday, January 17, 2007

Generating a temp filename in Bash

Here's a function that simplifies the process of generating temporary filenames in shell scripts (I use bash). This function will generate a temporary filename given optional path and filename prefix parameters. If the file happens to already exist (a small chance), it recurses to try a new filename.

It doesn't cleanup temp files or anything fancy like that, but it does have the behaviour that if you never write to the temp file, it is not created. If you don't like that idea, touch the file before returning the name.

This procedure demonstrates the use of variable expansion modifiers to parse the parameters, and the $RANDOM builtin variable.

function gettmpfile()
{
local tmppath=${1:-/tmp}
local tmpfile=$2${2:+-}
tmpfile=$tmppath/$tmpfile$RANDOM$RANDOM.tmp
if [ -e $tmpfile ]
then
# if file already exists, recurse and try again
tmpfile=$(gettmpfile $1 $2)
fi
echo $tmpfile
}

By default (with no parameters specified), this function will return a random filename in the /tmp directory:

[prompt]$ echo $(gettmpfile)
/tmp/324003570.tmp


If you specify a path and filename prefix, these are used to generate the name:
[prompt]$ echo $(gettmpfile /my_tmp_path app-tmpfile)
/my_tmp_path/app-tmpfile-276051579.tmp

Tuesday, January 16, 2007

Why we love Tom

I just picked up my copy of Effective Oracle by Design to check some facts. It just took a few moments for it to remind me how much I enjoy and learn from Tom Kyte's work.

Why do we love Tom? I have a theory on this! It comes down to two factors:
  • Substance. In an industry overwhelmed by candyfloss powerpoints, its refreshing to read someone who lives in the details, yet manages to retain sparkling clarity. It's apple pie for the technically oriented: database administrators, developers, and perhaps even a few architects(!).
  • Uncompromising rigour. Never one to let an assumption go unchallenged or untested. If there was an IT version of MythBusters, he would be your Jamie to Dvorak's Adam (or vice versa if you like). Highly opinionated, his opinions are (annoyingly) based on hard facts. Sometimes you just want to jump on the keyboard and hack away to try and prove him wrong ... But that's good! He's got you thinking.

Monday, January 15, 2007

Indexing with Oracle TDE

Oracle Transparent Data Encryption allows data encryption to be declared in a database schema, meaning that anything persisted on disk is protected from prying eyes.

It is a simple matter to setup TDE by bascially configuring a wallet location in sqlnet.ora, setting the key, and then opening the wallet after database startup. There's a good tutorial for this on OTN.

Declaring data encryption is then simply a matter of using the ENCRYPT keyword in your schema defintion, for example:

CREATE TABLE T1
(SEQ NUMBER(15),
CCNUMBER CHAR(16) ENCRYPT USING 'AES128' NO SALT);
Indexing encrypted columns is covered in the Advanced Security Guide. It mentions specifically that you cannot create an index on a column that has been encrypted with salt (hence the 'NO SALT' above). There is another restriction that you cannot use encrypted columns in functional indexes, but I've yet to find this covered explicitly in the doco. You may be surprised to find out that this also means you get caught if you try to create an index with descending values, such as:

CREATE INDEX T1_AK1 ON T1 (CCNUMBER, SEQ DESC);

This will fail with the error "ORA-28337: the specified index may not be defined on an encrypted column". The reason for this is that the use of "descending" will be treated as a functional index.

Removing the "descending" qualifier allows a valid index to be built:

CREATE INDEX T1_AK2 ON T1 (CCNUMBER, SEQ);

Tuesday, January 09, 2007

Exercising Regular Expressions and Arrays in Javascript

I've been working a bit with AJAX, and that soon prompted me to brush up on my Javascript. In the past I'd never used it to do more than a little validation or form "glue". Now revisiting the language from a true programmer's perspective, I recognise that it is pretty capable!

Speak Good Singlish! is a sample that gives regular expression and multi-dimensional array handling a good workout. It's a Singlish translator implemented in Javascript. I can't remember what sparked the idea to use this as an example, but somehow I got thinking back to ole "Jive" and "Valley girl" translators that have been floating around the net for many years.

The RegExp object usage in the sample is fairly standard, but the array handling is not.

I'm using a 2D array for the "lexicon" - basically a list of regex matches and replacements. For example the following fragment. You'll note that the second dimension can contain either a simple string or an array (as the replacement element):
var slexicon = [
[ '\\benglish\\b', 'Singlish' ] ,
[ '\\bdo\\b', [ 'do', 'do until sian', 'do sure can one' ] ]
];
There's a core routine that iterates through the array and performs the substitutions. Where an array substitution is present, it uses a funtion inline to pick a random element from the array to perform the substitution:
// do the translation
for (var i = 0; i < slexicon.length ; i++) {
var slexiconRow = slexicon[i];
var theRegex = new RegExp( slexiconRow[0], "gim" );
var theReplacement = slexiconRow[1];
if (theReplacement instanceof Array ) {
dataOut = dataOut.replace( theRegex, function (match) {return randomElement(theReplacement)} );
} else {
dataOut = dataOut.replace( theRegex, theReplacement );
}
}
The randomElement function is simplicity itself:
// returns random element of an array
var randomElement = new Function("x", "return x[Math.floor(Math.random() * (x.length))]");
The HTML page contains the whole script inline, so it is easy to review in-place with a "view source". Enjoy!

Sunday, January 07, 2007

Simple Perl clients for Axis2 with SOAP::Lite

SOAP::Lite is an incredibly powerful little module that makes using Perl for SOAP clients a piece of cake (groan).

True to the spirit of Perl, it makes simple things simple. This is all you need to do to interrogate the "Version" Axis (1+2) service:

#!/usr/bin/perl -w
use SOAP::Lite;
print SOAP::Lite
-> proxy('http://localhost/axis2/services/Version')
-> getVersion()
-> result;


The output:
Hello I am Axis2 version service , My version is 1.1


The SOAP request that this generates is as basic as it gets, and as you can see most of it is dedicated to the envelope definition:
<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope
xmlns:xsi=
"http://www.w3.org/1999/XMLSchema-instance"
xmlns:SOAP-ENC=
"http://schemas.xmlsoap.org/soap/encoding/"
xmlns:SOAP-ENV=
"http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd=
"http://www.w3.org/1999/XMLSchema"
SOAP-ENV:encodingStyle=
"http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body><getVersion/></SOAP-ENV:Body>
</SOAP-ENV:Envelope>


The example does illustrate one very important capability of SOAP::Lite - the automatic marshaling of the response into a usable Perl structure. In this case, its just a string.

One of the difficulties with SOAP::Lite at present is the dearth of good documentation and examples. This does tend to make doing more complex operations feel like you are living on the edge, but that's where the fun begins for some I guess;) I will look at some more complex request and response topics in a further post.

AXIS Revisited on OC4j - AXIS 2

Apache Axis2 is the core engine for Web services. It is a complete re-design and re-write of the widely used Apache Axis SOAP stack to build on the lessons learnt from Apache Axis. Version 1.1 was released in Nov'06. My first simple test was to update my deployment on Oracle OC4J.
Happily, it deployed just as easily as the original Axis, as I reported in a previous post.

Deployment of the Axis2 webapp is a little different, but just as straightforward. This is what I did:
  1. Download the Standard Binary Distribution and explode into a local folder
  2. In the $AXIS2_HOME/webapp directory, use ant to build the war file: ant create.war
  3. This creates the $AXIS2_HOME/dist/axis2.war file
  4. edit $ORACLE_HOME/j2ee/home/config/application.xml to add <web-module id="axis2" path="file:/d:/axis2/dist/axis2.war"/>
  5. edit $ORACLE_HOME/j2ee/home/config/http-web-site.xml to add <web-app application="default" name="axis2" root="/axis2"/>

And that's it. Axis2 up and running in my OC4J home at http://localhost/axis2/

Friday, February 03, 2006

Time to upgrade: Linux on Dell 9150

Just recently I bit the bullet - the PCs I had at home no longer had the horsepower (memory mainly) to run the stuff I wanted (Oracle mainly). So I gave myself an early birthday present - a Dell 9150 with the Pentium D processor and 4Gb RAM. Very nice box - running the pre-installed XP was a scream.

The 250Gb HDD came fully allocated for Windows, but my intention was to get it dual booting Linux. There were a few issues, but nothing a few days of messing around and downloading/cutting CDs couldn't fix.

First, to repartition the HDD I used qtparted from a Knoppix boot CD. This worked with no problems at all - Knoppix even booted into a high resolution display and I was able to knock the XP partition down to 50Gb without reinstalling.

Things weren't so straightforward when I started the Linux install, where I was planning Red Hat. I first tried RHEL WS 4 and got nowhere because the installer booted into a graphics mode that the machine couldn't display. I couldn't be bothered persisting because I was really more motivated to go RHEL AS 3 (for compatibility with other systems I'm involved with). I had some update 1 CDs on hand (old I know), but nothing to lose so I proceeded to install. Bad idea - I had driver problems with everything from the Dell USB keyboard (only recognised after plugging/unplugging after boot, and always kicked kudzu into action); USB support in general; the Intel Pro100/1000 NIC (not supported by the e1000 driver); and the display driver (ATI Rage x600).

So nothing to do but spend (quite) a few hours downloading update 6. Luckily this proved to be worthwhile effort - after installing most of the issues are resolved. I still needed to update the e1000 driver, and my display is still running VESA compatible mode but that's OK for me since when booted into Linux I'm mainly just using it as a server. If I want the graphics, sound etc then that usually means I've booted back into XP to dive into Age of Empires III or somesuch;-)

So overall - I'm really happy with the 9150, although I must say where there were a few moments while dealing with Dell's terrible support for Linux on the desktop machines that I was wondering if I made the right buying decision. Dell really should get their act together in terms of supporting Linux on the "consumer" range.

Wednesday, December 14, 2005

Running Cocoon with Oracle OC4J

After testing AXIS , I quickly moved on to check the latest Cocoon 2.1.8 release. I'd tried getting earlier versions running with OC4J but given up due to the xerces, xalan and jdk certification mix - it was hell. Fortunately Cocoon 2.1.8 worked (almost) perfectly first time with Oracle Application Server Containers for J2EE 10g (10.1.2.0).

After building Cocoon, I simply used a manual installation with the standalone version, which simply required $ORACLE_HOME/j2ee/home/config/application.xml and $ORACLE_HOME/j2ee/home/config/http-web-site.xml to include the Cocoon webapp.

The only "fixup" required - and this is for a non-fatal SAX processing error - is to force Cocoon to use the XML parsers etc that are included in the Cocoon distribution instead of the default (Oracle) parsers included in the OC4J distribution. The fix is to force "search-local-classes-first. To do this edit $ORACLE_HOME/j2ee/home/application-deployments/<your Cocoon deployment path>/orion-web.xml. Uncomment the line:
<web-app-class-loader search-local-classes-first="true" include-war-manifest-class-path="true" />

Tuesday, December 13, 2005

Running AXIS with Oracle OC4J

AXIS 1.3 was released back in October, so I thought it was about time to test it out with Oracle Application Server Containers for J2EE 10g (10.1.2.0). Good news .. all went absolutely smoothly, no frigging about with jar files at all.
Since Axis is a simple web module, manually deploying to Standalone OC4J is straightforward. After putting the web application files in place, register the module in application.xml and the web app root in http-web-site.xml:

  1. copy the axis webapp directory into $ORACLE_HOME/j2ee/home/applications/axis
  2. edit $ORACLE_HOME/j2ee/config/application.xml to add <web-module id="axis" path="../applications/axis"/>
  3. edit $ORACLE_HOME/j2ee/config/http-web-site.xml
    to add <web-app application="default" name="axis" root="/axis"/>
At this point, I had the AXIS welcome page coming up nicely at http://localhost/axis/. For the next 5 minutes, I played around with some drop-in web services. Very cool and easy!

Sunday, December 04, 2005

Time to test out AJAX?

I see AJAX popping up in discussions ever more frequently these days, an apparent groundswell around the next great thing for solving the perennial issue of delivering a rich client experience over the web. So far I've just been reading and listening. As with all new acronyms, it takes a while to figure out just exacly what the hell its all about. My "Ahah!" moment came after reading an article at AdaptivePath.
I must admit it seems AJAX is more a pattern than a specification, and its by no means clear that AJAX is anything more that a fad. It's not hard to find some interesting and contrary points of view on the web - QuirksBlog for example.
Anyway, after all that reading, I'm itching to at least go for a test drive. Have to have some playtime soon ... and after listening to this webdevradio podcast, I think I may start out with a look at phAtJAX.

Friday, November 25, 2005

OCFS is making it into the Linux kernel according to the interview with Andrew Morton on kernel development in Linux Format. There's an interesting discussion of this activity with Oracle's Wim Coekaerts in the Inside Oracle's Linux Projects podcast.