Useful article on regex in Oracle 10g

cd has written an introductory article on regular expressions in the 10g database. As he doesn't have a blog (imagine!) he has posted it in the OTN PL/SQL forum. In this first article cd shows how we can use Oracle's regular expressions to determine whether a string contains a valid number.

For those who don't know, regular expressions are a way of searching text for patterns rather than literal matchings. It's a cool way of manipulating large amounts of text, including .CSV files and programs. I'll just mention a little utility I have found very useful: the Regex Coach. This nifty piece of freeware (written in Lisp!) provides us with a sandbox in which we can test regular expressions against a target piece of text to see what they will find. It handles replace too. Very useful if you are just starting to find your way around regex. Check it out.

Regex is a topic big enough for a whole book. The book I have found useful is the Mastering Regular Expressions) by Jeffrey E.F. Friedl and published by O'Reilly. Even though reading it does make me think I ought to know more about perl than I actually do.

Update

cd has subsequently published two further articles about Regular Expressions in the OTN forums:
Part Two deals with testing Regular Expressions.
Part Three looks at complex formats such as IP addresses and discusses the 10gR2 enhancements.

UKOUG Dev. Eng. SIG 19-SEP-2006

When I introduced the agenda yesterday morning I said I thought it was quite forward-looking, all about new stuff - new features, new tools, new architectures. It turns out it was really all about rebadging of old stuff and integration of existing applications. Very much a case of "back to the future". Hmmm. Still at least the SIG kicked off with something innovative. Julius Kisielius (UKOUG) ran a session of Speed Networking. This is a bit like Speed Dating, only without the exchange of bodily fluids. This worked quite well as an icebreaker and the buzz it generated lasted well beyond coffee time.

Ben Wooton (Oracle) presented on migrating workgroup databases to ApEx and XE. Workgroup databases is code for "Excel and Access". Ben fought hard to stop dissing Access all the time, but his key point is valid. Anybody who has done consultancy has been to places where the IT Dept has too process and not enough delivery and users have filled the gap with ad hoc lightweight databases. These breed like rabbits and frequently become essential to the business, without having any proper oversight, especially of data integrity and security. I was quite concerned but not surprised to hear Ben say that one of the sites where he had taken his crusade against such practices was a police force. Ben's talk gave a useful overview of the considerations for migrating from such jury-rigged environments to a consistent and coherent ApEx one.

Next up was David Rowe (Rocela) talking about Oracle Customer Data Hub. I vaguely knew that CDH came out of Applications but I hadn't realised it basically was E-Business Suite. Seriously, the first step in implementing Customer Data Hub is installing EBS. Ouch. I found this talk stimulating and useful. It was an architectural rather than heavily technical. The main point about data hubs is consolidating the data in all your systems so that you have a single view of Customer (or whatever type of party your business is interested in). This is not a one-off exercise but a continuous process of sharing, synchronisation and correction. As you might have guessed, the biggest issues here are not technical but procedural and cultural. It certainly showed me how far my current project still has to go before it even approaches being a proper data hub.

Forms: back like it never went away

After lunch Grant Ronald(Oracle) presented the headline new features for the next release of Forms (currently just version 11, marketing suffix yet to be decided). Many people I talked to during the speed networking session had come primarily to hear this talk. The focus of the next version is on administration, deployment and integration with other applications. Although there were a number of new features there wasn't much to set the pulse racing - useless diagnostic logging and tracing makes you hot. The only thing that I would have found useful in the days when I was a Forms developer is the ability to respond to external events. Several projects I worked on would have benefited from that.

So whilst I accept Grant's assertion that Forms is still a living and supported tool, I think the days when a new release of Forms was a major event have long gone. As a SIG chair this is disheartening because it means that the main theme for Forms presentations remains migrating from client/server to the web. So I'm thinking about running a SIG with a retro programming theme - client/server and character-based Forms, RPT, user exits and Pro*C. [1]

The final presentation of the day was supposed to be Susan Duncan (Oracle) talking about the development roadmap to Fusion. Unfortunately her VMWare demo blew up and sent her laptop into meltdown. I'm going to ask Susan to have another crack at doing her presentation next year, if yesterday's experience hasn't left her too traumatised. Anyway, Grant vamped gallantly for forty-five minutes on the future of Forms and E-Business Suite. Although EBS version 12 is scheduled to be on the "Fusion Stack" (basically the JDeveloper J2EE framework) it will still contain screens written in Forms, due to pressure of time. One interesting aspect of this process is that Oracle won't be producing tools like the FMA. EBS are not migrating their Forms screens to ADF and JSF, they are rewriting them. This is because the Fusion version of EBS has to incorporate elements of PeopleSoft, JD Edwards, Seibel and anything else Larry takes a fancy to, so straight migration is not a viable option. Grant rounded off with a quick showing of the new pre-beta rich client (JSF and AJAX) and a simple how to do Forms-type stuff in JDeveloper.

Summing up

Overall this is an interesting time to be in Oracle development. For a while back it seemed as though Oracle were turning their back on their Tools customers, what with the de-supporting of Forms client/server mode and the slow throttling of Designer. But during the last twelve months there has been a flurry of activity. JDeveloper continues to grow relentlessly. It will eventually be a sophisticated suite of tools aimed at a number of different user roles. The JDev team have learnt from Eclipse and have rebuilt JDeveloper as a plug-in framework,so different users can customise the IDE to their own needs. There's still some way to go in reducing the complexity of it; I frequently lose panels when I'm working in the IDE (although I'm getting better at finding them again). SQL Developer and ApEx are two new initiatives, although they are actually existing functionality productized and then marketed. Ben Wooton said a couple of times that he though ApEx is positioned wrongly as a replacement for workgroup databases, because it is actually more powerful than that. In the future the marketing of ApEx may change: there is currently not so much a steep learning curve as an actual discontinuity between building a simple application in ApEx and building an enterprise system using JSF and ADF. Oracle needs a development tool to bridge that gap. Funnily enough it ought to be Forms. We shall see.

In the meantime it seems certain that Oracle has final woken up to something Microsoft has known for a long time: the value of mindshare. Get developers building applications with your products and you can monetise things later. So Oracle have decided to love developers again. We can see this not just in the tools space itself but in the Oracle Develop initiative at Open World. Now if they could just bring themselves to address the question of modelling everybody would be happy...

Finally my heartfelt gratitude goes to all the presenters, to Julius at UKOUG for his galvanising e-mails and to everybody who attended. Tomorrow I can start worrying about the next SIG.

---

[1] At the moment I'm only joking but if people are genuinely interested in such an event then who knows?

Clarke's Three Laws of Office Life

First law: For every situation there is an equal and apposite Dilbert cartoon.

Second Law: The number of meetings required for any process is proportional to the quality of the biscuits provided at meetings.

Third Law: The bloke with the most annoying laugh is the one who finds everything funny.

Many office laws - The paper tray is always empty when you need a hard-copy right now - are just special cases of the more general Sod's Law. Whereas these are, I think, genuinely fresh insights. However, I'm not claiming originality. The First Law of Online Life is that somebody has always thought of it before you did.

At last the agenda for the DE SIG

or "Sisyphus Revisited"

or "Where Have All The Oracle Developers Gone?"

Finally the full agenda for the Development Engineering SIG is published on the UKOUG web site. It's quite close to the wire ( the meeting is 19th September) but at least we made it. At one point I thought I was going to have to postpone the SIG until after the Conference because it looked like I couldn't get enough speakers. When I started n July the process of assembling the agenda I had the promise of presentations from three people who had not been able to make the previous SIG. Alas none of them could make September either. So instead of having to find just two speakers I suddenly had an empty agenda to fill, over the summer holiday season. That we do have a full agenda is really down to great support from our Oracle Buddies, Grant Ronald and Stephen Wallace, and sterling organisation by Julius Kisielius in the UKOUG office.

In the end I think it is quite a good line up:

• APEX - Ben Wooton, Oracle
  
• Oracle Data Hub Offerings - David Rowe, Rocela
  
• What’s new in Developer Forms 11g - Grant Ronald, Oracle
  
• The future of Application Development in Fusion - Susan Duncan , Oracle
  

So lots for developers - something on Forms, something on Fusion, something on TAFKAH - and something for systems architects too.

There are three obvious points about this agenda.

One is the omissions. Nothing on JDeveloper, nothing on SQL Developer, nothing on esoteric topics like PHP or .Net. Well, we cannot cover everything in every meeting and the DE SIG has a very broad waterfront to cover. Also, I have to work with what I can get. The Lord knows I have tried to get a PHP on Oracle presentation. I have struck out with three different sources. Maybe next year it will happen.

Secondly, there are two talks about Forms (the talk on Fusion will outline how Oracle is moving its applications from Forms to the Fusion tool set, which is Java-based). I was forwarded an e-mail recently from someone observing of the presentation on Forms 11g, "what is there left to say about Forms?" Now Grant will disagree but I have a certain sympathy with this viewpoint: Forms has been around for a long time and once you've got over the hurdle of migrating from client/server to web there really wasn't much new stuff in Forms 10g. However, a large chunk of the UKOUG development community are using Forms and I owe it to them to include talks on the tool set. As long as Oracle is supporting and indeed extending Forms there is news for the SIG to convey. Anyway, I asked this person what topics they would like to see in a DE SIG agenda. Naturally I am still awaiting a response.

Lastly, the speakers. Three are from Oracle and the fourth is from Rocela, a consultancy founded by ex-Oracle employees (indeed I think David is ex-Oracle himself). Again this is question of working with what I get given. At every SIG I stand on my hind legs and solicit presentations from the audience but the actual presenters are almost always from Oracle itself, other consultancies and vendors. These are three classes of people for whom presenting is a core skill. If you work in sales, pre-sales or consultancy you have to be able to explain technical subjects to a variety of audiences in a fluent manner. Obviously I am profoundly grateful for the support the SIG receives from the chaps and chapeses in Oracle but it would be nice to hear from some more "real people".

Call for papers

I guess many in the DE audience don't feel the compulsion to present or are too shy to talk in public or just don't think they have anything to talk about. Over the years we have tried to introduce a couple of different formats to encourage new speakers - short slots talking about a particular little utility or tool which we've found useful, war stories about when some project went horribly wrong - but these haven't taken off. Which is a shame, because giving a presentation can be rewarding in its own right - just ask Bob Baillie. So if anybody reading this (in the geographical environs of the UK) does feel inspired to give a talk please contact Julius at the UKOUG office (or leave a comment here). And don't worry if you have not spoken in public before, we will provide support. Indeed the UKOUG offers free course in presentation skills to its members (although you don't have to be a member to present at a SIG).

Developer SIGs vs DBA SIGs

The lack of "civilian" presenters does give me concern because it is a symptom of a larger problem, namely the relative small audience we get at the SIG. Normally we get between the mid-teens (Birmingham) and the low thirty-somethings (London). These attendance figures have been fairly stable for years now and seem to bear little relation to the agenda's content. By comparison the DBMS SIGs regularly get audiences in three figures. On the face of it this is ought to be surprising. After all, there are a lot more developers than there are DBAs. We could take 'em easy in a straight fight. So why are the DBA-oriented SIGs so much stronger?

Well, for starters their target audience is easy to define and relatively homogenous in its interests. If you look at the agenda for the newly-founded Oracle on Windows SIG you see the topics you would expect - availability, performance tuning, monitoring. DBA stuff is DBA stuff, only the peculiarities of the OS change. There is a large corps of excellent non-Oracle speakers, many of them "names" in the community.

The Development Engineering (Tools) SIG is different. The need for brackets says it all. Our audience is diffuse, our remit broad, our focus fuzzy. The community is diffuse, split between many different programming tools and job descriptions. It is hard to put together an agenda that will appeal to enough people that they will take a day out of their working schedule to attend. A VB.Net code monkey is unlikely to be interested in an agenda of mainly Oracle Forms presentations with a single talk on ADO. This is exacerbated by the trend towards platform agnosticism: J2EE developers tend to regard databases with distaste: they are closed, proprietary, difficult, non-extensible, nasty things. The whole point of frameworks like Spring and Hibernate is to shield Java developers from the database, so why would they want to attend, let alone speak at, a meeting dedicated to the Oracle database?

(Lack of) Conclusion

How I solve this conundrum is a different matter. It's been acknowledged for years that the UKOUG is failing to reach the developer community, and this failure extends beyond just the DE SIG: papers in the Development stream for this year's Conference constituted a scant 4% of the total submissions. Perhaps the problem is that there is no "Oracle developer community", just lots of people who develop stuff that happens to run against Oracle databases.

Anyway, for the moment at least I can relax. I have a SIG meeting with a full agenda to chair. But I know the satisfaction of organising a successful SIG will last until approximately 20th September. Then it will be replaced by the sinking realisation that it has to be done all over again.

My OOW2K6SF agenda

I've been really impressed with the schedule for Open World this year. I know some people regard the conference as a marketing exercise on Oracle's part. And indeed it is. But it's so much more than that. It's chock full of interesting presentations. In particular Oracle have really made an effort to provide talks of interest to the developer community. So I think the presentations on offer more than justify all the keynotes. And anyway Collaborate didn't offer free tickets to the ACEs.

A couple of other people have posted their schedules, so here is mine. It is my blog and it is all about me.

---

Sunday, 22 October 2006

2:00 PM - "Scaling to Infinity: Partitioning Data Warehouses in Oracle"

Frankly I can't think of a better cure for jetlag than listening to Tim Gorman talk about database partitioning ...

3:30 PM - "Inside Oracle Business Intelligence Analytics Server"

... unless it's listening to an equally jetlagged Mark Rittman talk on some BI technology about which I know nothing whatsoever.

In the evening it's the Editor's Choice and ACEs dinner. I get two invites for this but I won't be claiming two dinners.

Monday, 23 October 2006

11:00 AM - "The Future of Database Technology, from Oracle Development"

If Mark is correct this will be something to do with 11g beta. Rather annoyingly it clashes with Bryn Llewellyn's talk on enhancements to PL/SQL in 11g. I guess this is a deliberate piece of scheduling but still rather annoying.

12:30 PM - "Developing PL/SQL Programs, Using Automated Unit Testing"

I suppose I really ought to turn up for this one.

3:15 PM - "Database Worst Practices"

Tom Kyte should give everybody a checklist on entry so we can tick off our sins as he enumerates them.

4:15 PM - "10 Things You Should NEVER Do in PL/SQL"

And that goes for Steven Feuerstein too. I expect I'll have to attend the OTN Night wearing a hair shirt and dragging round a large candle.

Tuesday, 24 October 2006

10:45 AM - "Using Oracle Advanced Queues to Facilitate Near-Real-Time Integration"

By the time I get to SF I will already have had a crack at this task but I'm hoping to pick up a few tips from Robert Corfman which I can bring back.

1:15 PM - "Bringing Open Source Software Development Practices and Principles Into Your Company"

Brian Behlendorf was one of the founders of Apache, so he should know what he's talking about here.

2:30 PM - "Developing Solutions to Real-World Problems with Oracle Content Database"

I'm all in favour of tackling real-world problems. I hope somebody from Metalink attends to discover how to properly tag documents with metadata to help searching ;)

In the evening it's the Bloggers' dinner. Once again Mark Rittman is organising this and he's settled on the Thirsty Bear Brewing Co, which is a block down from the Moscone Centre.

Wednesday, 25 October 2006

11:30 AM - "Oracle Forensics: Collecting Evidence After an Attack"

Another annoying schedule clash, as this is on at the same time as Vipin Samar's talk on securing our assets. In the end I have opted to find out how to discover whether anybody has hacked into my system rather than trying to keep the intruders out in the first place. Perhaps this is a bad call.

2:30 PM - "Data Design Reviews: Using Extreme Humiliation to Ensure Quality Data Models"

Kent Graziano wins my vote for the best presentation title of the conference. Fortunately Kent has the chops to produce insights to go with the smartarse titles.

4:00 PM - "Dynamic SQL in a Dynamic World"

As an old skool programmer I tend to distrust dynamic programming in anything except unusual circumstances: too unreliable, too slow, too risky. So I'm looking forward to hearing a different point of view from Dulcian's Michael Rosenblum.

Thursday, 26 October 2006

9:30 AM - "Don't Let It Happen to You: Encrypt Sensitive Information in Your Oracle Database"
More on security. An area I went to learn much more about.

10:30 AM - "Meet The Experts Panel w/ Tom Kyte"

I'm joining the inestimable Mr Kyte on the OTN sofa. Possibly an enormous act of hubris on my part.

11:00 AM - ""Best Practices for Upgrading to Oracle Database 10g Release 2"

Hoping for a few tips as this is an upcoming task which I'm facing at the end of this year.

And after that I'll be leaving on a jet plane.

---

As I have observed previously last year's attendance made OOW2K5 quite intense at times. This year I think the additional twelve thousand attendees will turn the Moscone Centre into a veritable pressure cooker. So at some point I shall slope off for an hour to check out SFMOMA. This is slapbang next to the Moscone Centre and is an ideal place to consider issues of process and design from a different perspective.