SET TRANSACTION READ TFM

Recently I've been having to log on the production server as SYSTEM (don't ask why). Obviously, this is a risky thing to do, so I have taken to running

set transaction read only
/

at the start of the session. This prevents me accidentally dropping a key table due to a sudden moment of madness (or just because I have lost track of which PuTTY session is logged on to which database).

This morning I was monitoring the status of a job going through the system. It seemed to have been stuck on one stage for over half an hour. I was worried because this stage ought to have taken a few seconds. I checked the V$SESSION_WAIT view and there was loads of activity: a different wait event every time I queried on that session.

Of course, what I had forgotten was that setting the transaction to READ ONLY doesn't just prevent me executing DML or DDL [see update below - APC] in the session: it also sets the isolation level. So (in the words of the documentation) "All subsequent queries in that transaction only see changes committed before the transaction began." No wonder I wasn't seeing any changes in the status table data! Obviously the V$ views, being dynamic views on the X$ tables, have a back door through the isolation level. Which I suppose makes sense but it could confuse a stupid person.

One to file under Oracle Things I Shouldn't Forget.

Update

In his comment Hemant Chitale points out that SET TRANSACTION READ ONLY does not prevent DDL (because DDL issues an implicit commit and so ends the transaction). Actually in real life I really just worry about changing production data; I just used the DROP TABLE example for dramatic effect. But of course, this is no excuse for not writing something wrong in article, especially as the documentation I linked to makes that very point.

As an ironist I can only applaud the name I gave the article.

UKOUG 2007: Abstracts redux

Well, I have finished judging all the abstracts in my stack (214 of the blighters!). How are you all doing? According to the latest e-mail from the UKOUG back office there's still a big chunk of abstracts which nobody has evaluated. I would guess these are mainly in the Apps domain, but I could be wrong. As I mentioned previously it is important for as many people as possible to vote on the abstracts, so if you are in the eligible camp and still haven't checked in, please find the time over the next few days (judging closes Monday 2nd July, 10.00am BST).

This week I have been looking at the database server papers. The usual suspects - tuning, RAC, RMAN - are present in the usual numbers. But there are some new hot topics (several people submitting papers in the same vein) for the database too. They are:

• Virtualisation
  
• Data and Audit Vault
  
• Linux administration
  
• 11g New Features
  

Hmmm, there must be a good reason why we haven't had papers on that last one before now....

Evaluating the abstracts is an interesting exercise. We get to see the range of topics which people feel strongly about (because nobody bothers to volunteer to spend the time and effort to talk about something which bores them). And those of us who have also submitted papers get to size up the opposition.

UKOUG DE SIG 14-JUN-2007: The Correct use of SOAP

I didn't plan for this SIG to be streamed around Web Services and SOA. But I got offered three presentations which focused on this space. So I made a virtue of it, and requested my last two volunteers to shape their talks around the concept too. This was a bit risky but SOA is a mature concept now and people are actually implementing web services in anger, so it seemed like a good idea. In the end I'm not sure whether it paid off. On the one hand the turnout was quite low, even by the standards of the Birmingham SIG (which is the least-well attended of the three meetings we run). On the other hand pretty much everybody - delegates and speakers - seemed to like the idea of a coherent agenda. Most of the attendees were still there for the final session, which to be frank is a rather rare occurrence.

The presentations

The opening session was a SIG stalwart, Oracle's own Grant Ronald. One of Grant's recurring themes is the importance of extracting additional value from Forms apps migrated to the web by taking advantage of the additional capabilities of the application server. To this end he talked about including Forms applications in BPEL workflows. The actual mechanics of this are a snip: a JDeveloper wizard generates the Java client from the WSDL URL and the Forms Builder java importer wizard generates the PL/SQL wrapper for it.

My heart sank a bit when I saw that Grant's powerpoint included the orchestration of the Star Loans/United Loans demo: if I had a loyalty stamp for every time I've sat through that I'd be entitled to a free cappuccino by now. Fortunately for us Grant had actually worked up a new demo. Unfortunately for him this demo was rather ambitious. There were two laptops, one running Forms server and the other running a BPEL server, and a web service client calling an external web service (i.e. on the actual interweb) which sent an SMS message to his mobile phone. Even the simplest demos can go wrong, but that was just asking for trouble. All of which explains why Grant was a trifle stressed by the time the meeting opened. He had been wrestling with his set-up for a couple of hours whilst we stood around drinking coffee and relaxing after our journeys to BVP. In the end Grant just about got away with it.

Jonathan Elland from Oracle talked about E-Content Management, which turned out to be Enterprise Content Management. Yep, a new meaning for "e-"! This is set to be a major thing in Oracle EMEA. ECM is about managing unstructured information (documents, email, images, etc) which represent roughly 80% of the information owned by most organisations, and which tends to be unmanaged in most organisations. This is the "infoglut" (thank you Gartner). ECM consists mainly of putting this stuff in a database and managing it the way we manage structured information (data). There's more to than that of course, because the tools are different (MS Word rather than a data input form). Most users won't want to know about ECM, they won't want to go through additional hoops before they can read a report from Pat in Accounting. ECM is on the cusp of becoming a service like e-mail. It's infrastructure rather an application per se. At this point Jonathan mentioned Web 2.0 and Enterprise 2.0, thereby ensuring that the SIG was on the cutting edge. Anyway, ECM is a happening thing and the big vendors are buying up smaller niche companies with a view to offering complete content management packages. Oracle have just purchased Stellent, for the products and expertise rather the customer base. This will considerably boost Oracle's ECM profile, once they have got Stellent's products integrated into Oracle's architecture. So be prepared to hear lots about "Fusion Content Server" (before we've even got Fusion Middleware sorted). In the meantime some of the demos still haven't been branded with Oracle logos throughout.

After Coffee 2.0 it was time for David Rowe from Rocela to talk about securing web services. The first half of Dave's presentation dealt with the web security standards. The purpose of web services is to allow clients to communicate with servers in a loosely coupled fashion. This is done through SOAP, the Simple Object Access Protocol; the server has to specify all the information it requires, which the client has to include in the message header. However, the initial web service protocols didn't cover security, so transactions were anonymous, unauditable and in clear. No wonder early implementations went no further than checking the weather in Alicante. Then in 2004 OASIS published a series of web security standards. These specify protocols to meet the ISO requirements for secure systems: identification, authentication, authorisation, integrity, confidentiality, auditing and non-repudiation. The protocols are quite complicated. Being XML the end product is extremely verbose. Fortunately JDeveloper 10.3.1 provides wizards to generate any the necessary tokens. David was in the unenviable task of presenting a technical presentation when the techie person who wrote it can't actually make the meeting. So under the circumstances he did well with the demonstration. He rounded off with a discussion of OWSM, Oracle's Web Service Manager (and not Optimized Weapon System Management as you may have thought). It certainly looks as though secure web services are viable; now all I need to know is how to decide between all the different options. X.509? SAML? XML Signature? It's like they speak some kind of crazy moon language!

Oracle's Danny Roach talked about consuming web services in PL/SQL. This was a case study based on work done for a charity. The charity has a website with a shop and a CRM database to manage both the website's customers and the charity's members. The CRM database is a proprietary third-party product; the vendors would not allow the website to communicate directly with the datavase. So the process looks like this:

website -> e-mail -> human operator -> CRM database

However, the database vendor was prepared to expose certain functions through a web service interface which the website could then use. Danny's presentation covered the steps necessary to implement the service calls in PL/SQL using the UTL_HTTP package. Refreshingly the code was written by human beings rather than being generated through a wizard. This was a useful talk, because it shows how modern techniques can be used to solve old problems in fresh ways.

The afternoon was rounded off by Microsoft's Xen Lategan walking bravely into the lion's den. He was good-humoured about his company's "harmonious co-existence" with Oracle. This was the first time the DE SIG had hosted a talk from a Microsoftee and I think it's an experiment we need to repeat. Xen was talking about BizTalk server, which is the MS equivalent of BPEL. Indeed the entire Microsoft stack is full of boxes whose names are unfamiliar but which are equivalent to something in the Oracle or J2EE architectures. Along the way Xen explained the mystery of Microsoft's naming mechanism. The year in the product names - Word 2003, BizTalk Server 2006 - has nothing to do with the year of release. Rather it refers to the MS engineering compliancies to which the product has to conform. Making an internal QA conformance part of a product's branding reveals a lot about Microsoft's attitude towards the market. Anyway, Xen's presentation gave us an opportunity to compare Visual Studio, the .NET IDE, with Oracle's JDeveloper IDE. In terms of layout, they look quite similar, but VS's deep integration with the Windows OS gives it a slicker look and (probably) quicker responses times. The "Oracle.Net 3.0 Adapter experience" refers to the fact that prior to this latest release, BizTalk only supported an Oracle ODBC connection, which is a rather unsatisfactory way to manage enterprise-level applications. The new version has a connector which wraps Oracle's own .NET data provider. Apart from supporting transactions, this means the applications talk to the database directly through TNS rather than needing an ODBC DNS. Xen's demonstration showed how easy it is to switch a BizTalk service from writing to a MSSQL database to an Oracle database. As Xen joked, it's a very easy migration to make.

The wash-up

I think the themed day worked well. A couple of questions to Grant at the end of the first presentation were actually covered in later presentations. Similarly speakers in subsequent talks were able to reinforce or contrast points they wanted to make by referring to earlier sessions. During the day several delegates asked me whether I was going to run further themed days, because they were finding this one so useful. I am tempted. There are just two sticking points. I need to find themes which can be supported across an entire day without boring people And I need to find themes which will attract more than twelve people. I am open to suggestions.

UKOUG 2007: Judging the abstracts

The Call For Papers for the 2007 UKOUG Annual Conference has closed and the next phase has begun: judging the submissions. Unlike certain other conferences I could mention the judging process for the UKOUG is quite open. If you are on the UKOUG committee or if you submitted a paper you are entitled to act as a judge (you won't be allowed to mark your own abstracts). If you fall into either category and you haven't already registered you can do so on the conference website. Please do so: the more people who evaluate the submissions, the more useful the marks will be when it comes to making the final selection.

The marking categories have been expanded. Last year there were only four gradations and the majority of presentations were marked as 1. Which makes it hard to distinguish the very good from the run-of-the-mill. This year there are six grades, ranging from Must have to Don't bother, with some helpful variations in the middle range. So, with a bit of luck we'll get better differentiation of marks.

I have just been doing my bit. There are some very interesting submissions (and a few dogs). In the development stream the hot topics (the ones with the most submissions by different people) are:

• Application Express
  
• Incorporating AJAX into ADF applications
  
• Migrating client/server Forms apps to the web
  

Once again, not that much on PL/SQL or Java outside of building UIs. On the other hand this year had several submissions on process (as opposed to "cookbook" presentations) which is a hopeful sign.

I have tried to reflect the feedback from the DE SIGs, to select papers which I know address common concerns of the delegates, even though they might bore me. But after a while the abstracts for certain topics (such as AJAX and ADF) all start to read the same. And of course, my take on what constitutes a Must have presentation is idiosyncratic. That's why we need more judges.