Tuesday, May 15, 2007

Announcement: UKOUG DE SIG 14-JUN-2007

It's just over four weeks until the second DE SIG of the year. It's the June meeting so it must be the Midlands; specifically the Oracle offices at Blythe Valley Park.

The agenda is focused on Web Services and Service Oriented Architecture. I didn't really plan it that way. It just so happened that the range of presentations I was offered seemed to coalesce around this space. There has been a lot of hype around SOA for several years. This intensified a couple of years ago when Oracle acquired Collaxa BPEL Server (now BPEL Process Manager). I think it's fair to say that the buzzstorm was met with a certain amount of scepticism from the more jaded amongst us. However, SOA and BPEL are still here, and probably most of us have at least considered using a web service to share data or functionality between disjunct systems. So now seems like a good time to catch up with what's going on.

Jon Ellard from Oracle kicks off the SIG with a presentation on their offerings in the E-Content Management arena. Then Grant Ronald continues his crusade to pull Forms users into the twenty-first century with a presentation on integrating BPEL with Oracle Forms. From the opposing camp we are going to have Xen Lategan from Microsoft talking on integrating applications with BizTalk; I'm not sure what the Oracle spin is on that one, but I'm assured there will be one. We have a yet-to-be-named speaker, this time from Rocela, presenting on securing Web Services. This is a very interesting area, because I think the apparent insecurity of web services has been a barrier to more widespread adoption. We have a third speaker from Oracle, Danny Roach (who has obviously got a taste for presenting) giving us a case study on embedding web services within web-based applications.

It would be nice to see lots of people in BVP on June 14th. Every organisation which has a UKOUG membership can send one delegate for free to this meeting, so petition your boss to attend. It's not a jolly, it's training on the cheap ;-) If your organisation is based in the UK and uses Oracle but isn't a member of the UKOUG, why the heck isn't it?

Update


Xen's talk on Microsoft BizTalk now has a title which makes plain the Oracle spin: BizTalk Integration and the Oracle NET 3.0 Adapter Experience. I hope to understand what that means by the end of the SIG ;)

Friday, May 04, 2007

You wouldn't let it lie!

The OTN forum is still seeing activity on the notorious long running thread URGENT URGENT PLZ READ B4 OTHERS VERY URGENT NO TIME WASTERS. OTN regular Simon Galaxy asks why.
"I think that sometimes the most irrelevant threads present the most posts.....!!!!!!!"

I don't think this is wholly fair. Some of the long running threads contain useful nuggets of learning and opinion for which they are worth mining. They usually start with a wacky theory or application architecture proposed by somebody is not familiar with Oracle in particular or RDBMS in general. Examples include the 'code class' rdbms, nulls and empty strings or the key-value pair design. Usually such threads die out quickly after the initial response. But sometimes, as with the threads cited above, the OP is possessed of evangelical wrongheadedness: they need to prove us wrong and we need to get them to see sense. That's where the momentum comes from. Eventually the posts snowball because they persistently feature in the Popular Discussions panel, so more people read them and respond. Threads like these become too unwieldy to follow, but often William Roberston and his gang fillet the best ones for posting on Oracle WTF.

On the other hand there are zombie threads which are just fuelled by stupidity, no - let's be generous - naivety. One such was the gone but not missed "plz send me the coding standards" thread, Somebody searching the web for PL%2FSQL+coding+standards finds a seemingly helpful post. Unfortunately they just respond to the OP without registering the thread's bloated number of posts or its original posting date. These can run on for years, although occasionally the OTN moderators do terminate them.

The NO TIME WASTERS thread is really just an opportunity for some old forum lags to let off some steam. And why not. It was a free shot and better than RTFM-ing a genuine but hapless seeker. The interesting thing about this thread is how many people who have never or only rarely posted to the OTN forums felt the need to weigh in with their opinion of the OP. That's the Tom Kyte effect. Whilst it's always nice to see have visitors it seems a pity that these espontaneos couldn't find something more worthwhile to do with their first post than abuse some dumb troll who had already been roundly abused by many others.

It's instructive to compare this thread with another recent one: Oracle is the devil itself . The opening post goes:
I hate this stupid database engine and all its relevant stuff. Microsoft SQL Server 2005 is the king! I could kill my boss, because I have to work with this ****! Hate it!

Obviously this chap's real problem is with his boss, who is making him use Oracle without giving him the necessary training and support. But he felt unable to express this to his boss, so he flames the OTN forums instead. The thing is, people engaged with him, calmed him down and eventually he resolved his issue. As you might have guessed, it was a bug in his .Net application and nothing to do with the underlying Oracle database. However, people continued posting to this thread, perhaps because it had a catchy name, and it became one of the Forum's more thoughtful examinations of the relative merits of MS SQL Server and Oracle as RDBMS products.

Thursday, May 03, 2007

Working under constraints

This lunchtime I answered a question on the OTN PL/SQL forum asking how best to do something without using triggers, when using triggers was patently the best way to satisfy the task at hand. As I occasionally do in these cases, I asked whether the ban on using triggers was a OuLiPo thing. Ouvroir de Littérature Potentielle was a school of literature in which arbitrary constraints are used to drive creativity. For instance La Disparition by Georges Perec is a novel which contains no words with the letter e. One OuLiPo site features this quote from Igor Stravinsky.
"The more constraints one imposes, the more one frees oneself of the chains that shackle the spirit... the arbitrariness of the constraint only serves to obtain precision of execution."

In a neat piece of synchronicity, Sean McGrath over at IT World wrote about design constraints in this week's E-Business column. He ponders the relative merits of writing baroque code to implement a business process exactly versus simplifying the business process in order to make it easier to automate.
"If your sense of an existing process is that it needs to be changed before it is computerized ... then you need to be careful what tools you put in the hands of those looking at the problem. Tools with constraints that focus attention on simplicity can be excellent catalysts for change."

A few year back Oracle had an interesting idea as part of its managed services proposition to Oracle eE-Business Suite customers. Don't bother customising Apps to fit your business. Instead, let Oracle use its consultants to migrate your business processes to fit "vanilla" Apps workflows. In return for which Oracle would guarantee a 5% year-on-year reduction in management charges for five years. Apaprently it was cheaper for Oracle to do BPR for free than to maintain and support heavily customised code. And as customised code is also likely to be more error-prone than factory standard code, the customers ought to have got more reliable systems for less outlay.

Oracle seem to have been quiet about this recently, so I don't know whether it's still in effect. Probably it's been swamped by the various Fusion initatives. I would be interested in knowing whether there was any great take-up of that offer. TUSC have an paper on the feasibility of "vanilla" E-Business Suite, which suggests that even very the simplest implemenations get complicated very quickly, especially in the area of data mapping and data conversion. Another beautiful idea killed off by an ugly reality?

Tuesday, May 01, 2007

Another use for INSERT ALL syntax

Over on the Pythian blog Babette Turner-Underwood discusses Oracle's multitable insert syntax. She's been using in on a data conversion project. The advantage of INSERT ALL is that it allows us to insert several different VALUES clauses in a single statement. The documentation shows two uses for multitable inserts:

  • transforming a single row of data from one table into several rows of data in a another table;
  • putting data from a single row into one or more tables, depending upon the values of certain columns (a poor man's partitioining).

And, as Babette notes, in data migration we sometimes need to split columns from one old table into more than one new table.

None of these uses are what we might call everyday. So Babette is correct to describe the multitable insert as "little known". However, there is one use for the syntax, which is slightly arcane but is a much more common situation: creating referenced records for foreign keys on the fly. Usually we would expect to have our primary key records before we create child records because they exist as entities in their own right: Customers, Products, etc. But there are others which are only brought into existence by the creation of child records. For instance, the canonical implementation of an order process requires two tables - Order_Headers and Order_Lines. Order_Headers really just acts as an intersection table between Customers and Order_Lines: we only need an Order Header record when a customer places an order for a specific item.

One way of handling this is to have foreign keys which are DEFERRABLE INITIALLY DEFERRED. This means that the foreign key is not enforced until the end of the transaction. So we can insert a Order_Lines record without having a Order_Headers record. This is only a temporary suspension: eventually we will need to create that parent record. Besides, this violation of relational theory will disappoint Hugh Darwen. It would be nice to be able to create the parent record at the moment when we create the child record, so we can enforce the foreign key immediately. Which is where the multitable insert comes in.

Here is a simple orders implementation, with an IMMEDIATE foreign key:

SQL> desc order_headers
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
CUST_ID NOT NULL NUMBER
STATUS NOT NULL VARCHAR2(3)

SQL> desc order_lines
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL NUMBER
LINE_ID NOT NULL NUMBER
PROD_ID NOT NULL NUMBER
QTY NOT NULL NUMBER
CREATED NOT NULL DATE
STATUS NOT NULL VARCHAR2(3)
SHIPPED DATE

SQL> insert into order_lines (order_id, line_id, prod_id, qty)
2 values (63, ordl_id.nextval, 5000, 2)
3 /
insert into order_lines (order_id, line_id, prod_id, qty)
*
ERROR at line 1:
ORA-02291: integrity constraint (A.ORDL_ORD_FK) violated - parent key not found


SQL>
.
What we want to be able to do is use the creation of the first order line to create the order header record too. The multitable syntax has several limitations. The two which are relevant here are: it must be driven off a subquery and we cannot use the RETURNING clause to retrieve the values of any sequence used in the insertion.

SQL> create or replace procedure new_order_line (
2 p_cust_id in customers.cust_id%type
3 , p_prod_id in products.prod_id%type
4 , p_qty in order_lines.qty%type
5 , p_ord_id in out order_headers.order_id%type
6 )
7 is
8 l_ord_id order_headers.order_id%type;
9 begin
10 if p_ord_id is null then
11 select ord_id.nextval into l_ord_id from dual;
12 else
13 l_ord_id := p_ord_id;
14 end if;
15
16 insert all
17 when p_ord_id is null then
18 into order_headers (order_id, cust_id)
19 values (new_ord_id, new_cust_id)
20 when 1=1 then
21 into order_lines (order_id, line_id, prod_id, qty)
22 values (new_ord_id, ordl_id.nextval, new_prod_id, new_qty)
23 select p_cust_id as new_cust_id
24 , p_prod_id as new_prod_id
25 , p_qty as new_qty
26 , l_ord_id as new_ord_id
27 from dual;
28
29 if p_ord_id is null then
30 p_ord_id := l_ord_id;
31 end if;
32 end new_order_line ;
33 /

Procedure created.

SQL>

I admit this is a slighty clunky procedure. My orginal implementation required only one IF statement, but had a single table INSERT statement for Order_Lines as well as the multitable statement. I'm sure if I had more time I could polish this code more. Anyway, let's call this procedure twice:

SQL> declare
2 n number;
3 begin
4 new_order_line(1000, 5000, 2, n);
5 new_order_line(1000, 5002, 1, n);
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from order_headers
2 /
ORDER_ID CUST_ID STA
---------- ---------- ---
80002 1000 NEW

SQL> select * from order_lines
2 /
ORDER_ID LINE_ID PROD_ID QTY CREATED STA SHIPPED
---------- ---------- ---------- ---------- --------- --- ---------
80002 250510 5000 2 30-APR-07 NEW
80002 250511 5002 1 30-APR-07 NEW

SQL>

Of course I could have impelmented this as two separate single table inserts, which would have avoided the some of duplication I mentioned. However there is another advantage to the multitable version. It is a single statement, so if any part of it fails, the whole thing fails:

SQL> declare
2 n number;
3 begin
4 new_order_line(1000, 5001, null, n);
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("A"."ORDER_LINES"."QTY")
ORA-06512: at "A.NEW_ORDER_LINE", line 13
ORA-06512: at line 4


SQL>
SQL> select * from order_headers
2 /

no rows selected

SQL>

This is neater because the logic of the business model is that we do not want an order header if there are no order lines for it.

We still don't have a constraint syntax which allows us to enforce parentage. And there is still no sign of a constraint which will allow us to enforce arcs. But there is always hope.

Oracle and AppForge: official at last

Oracle has finally posted a notice about its acquistion of AppForge on its site. As the various rumours made clear, Oracle have only bought the intellectual property and hired some former employees; it will not be supporting the existing applications. I was right about Oracle looking to strengthen its mobile offerings.

There is a set of FAQs for AppForge's customers, which doesn't actually answer the most frequently asked question of all, i.e. "what the flip do I do with my AppForge licences now?" So I will also point out (for the last time) my original post on this topic, which does have some useful advice for AppForge licencees posted by others in the same boat.