Oracle as a Legacy technology

I recently read an entry in The Database Column which suggested that Oracle, and indeed every other RDBMS, should now be regarded as a legacy technology, since they are based on the fact that RDBMS technology is now 25 years old.

The article advocates the move to column-oriented databases , which are particularly relevant for data warehouses.  The idea (put simply) is that instead of selecting data from rows, it would be quicker for data warehousing technology to select columns from the tables, since only certain columns are typically required for data warehouse requirements.

Suggest that Oracle is a legacy technology, and you might think that the person suggesting it doesn’t know much about the industry.  However, the article was written by Michael Stonebreaker – who knows a thing or two about RDBMS technology.  Mike was a founder of Ingres, CTO of Informix and is currently professor at MIT .  Clearly, with that kind of background, this is someone who knows a lot about the background of RDBMS – certainly more that I do!

However, one thing that does spring to mind is one of Mike’s other ventures – he is also founder of Vertica Systems, Inc. who, would you believe it, just happen to sell column-oriented databases!  Now, I’m not arguing that a column-based approach is a method that should be considered for data warehousing solutions, but I would question the ascertation that there is something fundamentally wrong with Oracle that makes it unsuitable when compared with column-oriented databases.  One of the claims that Mike makes in his post is that the new design results in a performance improvement of 50 times better than a row-oriented system.  However, I would suggest that perhaps the root cause of the apparent slowness of Oracle is down to poor design, rather than a move to column-oriented technology resulting in such massive improvement.

It will be interesting to see whether there is any response that Oracle (or any other vendor) makes to this approach – I’m going to hedge my bets and suggest that a column-oriented database is probably a better thing for OLAP, whereas row-oriented is better for OLTP.

One thing I will give Vertica credit for is their wonderful slogan: "Lose Wait".

Determining Notification Response Values

If you are using email or the standard web pages to respond to notifications, then the system will automatically prompt you to let you know what the possible values for a response are. However, there are certain circumstances where this isn’t appropriate – a customer I worked with last year wanted all their notifications to be handled via an Oracle Form, which needed to dynamically create a drop-down list for each notification.

Since the Workflow Data Model isn’t really available anywhere yet (it’ll be in the book though!), it’s not immediately apparent to determine where you need to look to find out what the result is, so here’s how.

In order for a notification to use conditional routing, there needs to be an attribute called RESULT. This attribute has a type of LOOKUP and a subtype of RESPOND – i.e. this is something coming back into the Workflow process and the value corresponds to a lookup type. The FORMAT of this attribute contains the internal name of the lookup. All this information is stored in the WF_MESSAGE_ATTRIBUTES table.

In order to get to this table, we need to know what the message is that is linked to the notification. The WF_MESSAGE_ATTRIBUTES table is joined to the WF_MESSAGES table using the MESSAGE_NAME and MESSAGE_TYPE columns on the attributes table to join to the NAME and TYPE columns in the messages table.

Finally, the message needs to be linked to the notification, which is stored in the WF_NOTIFICATIONS table, which joins using the same columns as the join from the messages table to the attributes table. This way, for any notification that you are using, you can fetch a list of what possible responses to the notification are.

Here’s the code:
SELECT wfl.lookup_code
,      wfl.meaning
FROM wf_notifications        wfn
,    wf_messages             wfm
,    wf_message_attributes   wfma
,    wf_lookups              wfl
WHERE wfn.notification_id = 1035
AND   wfn.message_name = wfm.name
AND   wfn.message_type = wfm.type
AND   wfn.message_name = wfma.message_name
AND   wfn.message_type = wfma.message_type
AND   wfma.name = 'RESULT'
AND   wfl.lookup_type = wfma.format

Enjoy!

Delegating or Transferring Notifications

When a user receives a notification which requires a response, they have a choice in how they can respond to it. They can respond, transfer, delegate, or ignore the notification – so what does each mean?

Respond

This would be the “normal” expected behaviour of a user (if that’s possible to pre-empt as a developer!). They receive a notification which asks them to do something, and they respond to it. In this case, the Workflow engine will check whether there is a Post Notification Function (PNF) tied to the notification. If there is, then the PNF runs in RESPOND mode, which is the usual mode to run. Once the PNF completes (unless it returns a status of ERROR), the Engine will then transition down the path from the notification and continue processing. If the flow doesn’t contain a transition that it can use (or a default transition), then the process will have the status set to #STUCK as it can’t go anywhere from the notification.

Transfer

Transferring ownership of the notification means that the person who is designated as the assignee becomes the owner of the notification. This allows users to completely handover any responsibility for the notification to another person – they will be shown as the new owner. When the notification is transferred to another user, the Workflow engine again checks for a PNF, and runs the code in TRANSFER mode. If the user transferring the notification included a comment, it is at this stage that it is written to the notifications table. The new owner of the notification will now see the notification in their worklist, and it is their responsibility to respond, transfer, delegate or ignore the notification.

Delegate

Delegating the notification of the notification to another user means that the new user is responsible for handling it, but the notification still remains owned by the original recipient. When a user delegates the notification to another user, the system will update the notification with any comment provided by the original owner, checks for a PNF and runs it in FORWARD mode (note the internal name actually makes more sense than the display name!). The new recipient will see the notification in their worklist, but when they respond to it, the original owner remains constant.

Ignore

If the user doesn’t respond to the notification, if the flow has been built to include a timeout transition, then the Workflow background engine will take that transition once it has timed-out. If the process does not have a timeout, and the user chooses t ignore the notification, then the process will just sit, waiting, until someone tells it to continue…

Transfer or Delegate?

So does the method of reassigning a notification really make a difference – after all they appear (superficially at least) to perform the same functionality, in that a new person is now responsible for doing something with the notification.

The best way to consider this is to look at delegating or transferring notifications in some real-world examples. Firstly, consider an expenses processing system (this could be bespoke, Oracle Internet Expenses, or some other expense processing system). A report is sent to a user, asking them to approve or reject an expense claim before it can be processed further. Once the notification has been approved, the system verifies that the person responding to the notification has the appropriate authority to approve the expense report.

If the notification has been delegated, then the owner of the notification will still be registered as the owner of the notification. When the process verifies that the approver has the appropriate authority to approve the report, it is this user who is checked. If the notification has been transferred, then the owner of the notification changes to the new user. In this case, when the process validates that the responder has the appropriate authority, the new owner will be checked. Assuming that the original recipient has an approval limit of £500, here’s how the Workflow engine interprets the approval limit for delegating or transferring the notification:

Action Recipient Limit Approval Range
Delegate £0 Up to £500
Delegate £100 Up to £500
Delegate £1000 Up to £500
Transfer £0 £0
Transfer £100 Up to £100
Transfer £1000 Up to £1000

The second example to consider is Oracle Purchasing.  Within Purchasing, the workflow generates a notification and a document that is associated with the notification, which requires approval. If the notification is transferred, then the document is not transferred to the new user, and so an auditing capability is lost. Within Purchasing, notifications should never be transferred, only delegated. Indeed, the system now does not let you transfer notifications which require approval (see the Oracle Purchasing User Guide for more information).

Which leads nicely onto the final point that I want to make here:

Can I control whether users Transfer or Delegate notifications?

Within eBusiness Suite, you can. In standalone Workflow, you can’t as standard. If you need to manually include this functionality, then you should write a PNF which checks the mode – if the notification is being transferred instead of delegated (or vice versa), then you can error the PNF and get the user to change their reassignment method.

Within eBS, there is a profile which can be set called “WF: Notification Reassign Mode”. In some versions of the documentation, this is referred to as an “FND:” profile, but the name now starts “WF:” instead. The profile determines whether users are forced to use one reassignment mode or the other, or whether they should be given the option to determine which method to use.
The profile can be set at different levels, so you can enforce the Purchasing responsibilities to only allow Delegate, while Internet Expenses (for example) might only be allowed to Transfer notifications. You should set the profile to “Reassign” at Site level, which provides users with the choice, and then lock the reassignment rules down for each responsibility as required.

Finally, if you have used the “Grant Worklist Access” function within eBS, then the profile option is ignored, since this delegates the entire worklist to another user.

New format for TLDs?

I was reading recently about the ICANN decision to have a new range of top level domains (for a couple of hundred thousand dollars or so…), which would open the web further in terms of addressing.  The thought of a new range of web addresses could be interesting – for example a .nyc for things New York based etc. or just having .coke as the domain name (or .pepsi for that matter).

But one question that comes to mind is what would go in front of the dot in the new format?  So if Coke register .coke as the domain name, what would the website be?  It is just www.coke ?  Will users actually find the website – and more importantly, will search engines?

I’m not going to be rushing out to register .workflow by any means (since I’m a few hundred thousand short at the moment!) but also because I’m not convinced about the whole idea – I guess it’s a case of wait and see, but by that stage it could well be far too late…

In my search for more information about the whole concept, I found this interesing post , which seems to reflect my thoughts as well.

How not to ask for help!

I was looking at a post on AskTom this morning, which was looking to "Select entire rows with most recent date only.  No duplicates" and wondered how Tom would have approached it.  The question had been marked as for version 8.1.7 of Oracle.

However, looking at the query that the poster had included, there was a new word that seemed strange to me – readuncommitted.  I’d never seen it in an Oracle query before, so I looked at more of the code:

Select Jobstop.PODdatetime   Date_Time
,      job.routenumber       Route
,      Stop.Name             Location
,      Jobstop.PODName
,      Case When Jobstop.PODSign IS NOT NULL Then 'Electronic' End AS PODsign
,      JobStop.ArriveDateTimeSource    ArriveScan
,      Piece.Reference
From   Ordermain with (readuncommitted, INDEX=OrderDateOrderID)
Left Outer Join Customer with (readuncommitted) on Customer.CustomerID=Ordermain.CustomerID
Left Outer Join Job with (readuncommitted) on Ordermain.OrderID=Job.OrderID
Left Outer Join Jobstop with (readuncommitted) on Job.JobID=Jobstop.JobID
Left Outer Join Stop with (readuncommitted) on Jobstop.StopID=Stop.StopID
Left Outer Join Jobstoppiece with (readuncommitted) on Jobstop.JobstopID=Jobstoppiece.JobstopID
Left Outer Join Piece with (readuncommitted) on Jobstoppiece.PieceID=Piece.PieceID
Where Ordermain.Orderstatus IN ('N', 'A', 'I','P')
And Left(Ordermain.Service,3) NOT IN ('LEA', 'FUE', 'Wai', 'Mon')
And Ordermain.Orderdate between '9/21/07' AND '9/27/07'
And left(piece.reference,2)='TL'
And customer.customercode='331'
Order By Piece.reference, JobStop.PODdatetime

There’s no way that this could be a query for Oracle 8.1.7 – the ANSI join syntax didn’t come into the database until version 9i .   So, maybe it’s a 9i query then, and the poster got the version wrong (unlikely, but possible).  No Oracle query that I’ve seen uses the "readuncommitted" term – this would be a really bad thing to do, as far as I can see.

Hmm – different syntax, really bad ideas for data selection.  Then it hit me – it’s a SQL Server query!!

Lesson one in getting help – make sure you ask in the right place!

The Importance of Notification Links in Email

When a user receives a notification, there is usually an option to reassign / transfer / delegate the notification to other users. If they are using the standard Workflow worklists, then there is some in-built security here, in that only the recipient (or an administrator) should have access to the notification unless the whole worklist has been delegated to another user.

Responding to notifications via email is a different matter altogether. Assuming the email client renders the notification in HTML, the user should be presented with some system generated links to respond to the notification or reassign it. If they need to reassign the notification to a different user, then this is the mechanism that they should use. If they require more information, then this should be included as an option for the recipient to ask someone for their input. Assuming that the users use the links, the system is foolproof – reassign the notification via the link, or send it back to someone for comment using the link. Dead easy.

BUT…

Let’s be honest – can you really rely on your users doing everything the “correct” way? What happens if they just want to get more information quickly, so instead of clicking on a “more info” option, they just forward the email to the preparer with a quick note at the top for more information? They’ve just opened up a potential security loophole – the person that they sent the email to has now got a link in their mail which would allow them to respond to the notification if they wanted to! Imagine if this was an expense claim, or an expensive purchase order approval – the person who should have been approving it (i.e. a “trusted” employee) has effectively handed an opportunity to the preparer to approve the request themselves. The owner on the notifications table remains the same (the transfer / delegate functionality has not been used), so it will look like the right person approved it, when in reality, anyone could have done it.

So, what can we do to stop this happening?

There are a few things that you can do to help minimise the impact of this. Starting with the easiest to implement – don’t use email for your notifications. Email is fine for sending FYI notifications, but I don’t like the idea of relying on users to respond to a system via email My reluctance to embrace email with Workflow stems from working with less sophisticated email servers and clients. When I started Workflow development, HTML mail clients were nowhere near as common as they are these days – my email client through Netscape only displayed email in text format. Also, the Workflow mailer was mainly implemented using UNIX sendmail. If you have ever had the experience of trying to implement Workflow with a non-HTML email client, you should be able to relate to this quite well – if the email can’t generate the response template automatically for you, you are reliant on the user to enter the correct response, in the correct case, on the correct line, with the correct opening and closing quotation marks. However, I concede that this isn’t always possible – email is much more of a push of information from the system to the user, whereas the notification worklist relies on the user pulling information out of the system. Users like email, plus not all notification recipients are Workflow or eBS users, so can’t use the worklist.

Secondly, there is a certain amount of responsibility that lies with the user and the level of training that they have received. It should be a “simple” matter of user training to highlight the importance of using the generated links to respond to notifications. However, this is a fairly naïve thought – not everyone is going to get the same training, particularly as a system matures, and this can quite easily be overlooked.

Thirdly, you can check from the notification response who the response came from. When a notification comes in via email, the email address is stored in the system, and can be checked. A Post Notification Function could be used to check the email address against the owner role, and if there is a discrepancy, then it could reject the response. However, this isn’t quite as simple as it sounds – you also need to check whether the notification has been delegated, since the owner of the notification will not have changed on delegation.

The final check that you can implement to stop this being a security problem is that you can change the mailer configuration. AFAIK this will only work in the Java implementation of the Workflow mailer, but you can configure the mailer to disallow this. From the mailer configuration, there is a flag which you can set or unset as necessary called “Allow Forwarded Response”. If the flag is set, then the system will not accept responses which have not come from the original recipient of the notification email.

Personally, if possible, I would advocate the first option – if you require users to log on to the eBS, then this ensures that the respondent has the appropriate authority to do so, rather than relying on other configuration. If necessary, you can always send summary emails to remind people that they have open notifications in the system.

Workflow Notification Fails When Action History Becomes Too Long

This was something that was flagged in the forum a while back by Joe T, but I’ve been working with a client on 11.5.9 who has just hit the same problem.

There is a bug in 11.5.9 that means that occasionally a workflow notification will error with the following error
ERROR
Error - ORA-06502:
PL/SQL: numeric or value error ORA-06512: at "APPS.WF_NOTIFICATION", line 4995
ORA-06512: at line 5

This seems to happen when the action history for an item key exceeds a given limit – the breakpoint seems to be about 30 items, but it could be more or less (Oracle are no more or less specific on the threshold!). The error message appears instead of the actual notification – not something that you want your users to see!

Oracle have identified the bug that causes this as bug 4059291 – which can be resolved by applying patch 4946007. If you can’t apply the patch, then there is this workaround:

– Add an attribute called #HISTORY to the message causing the problem, and add &#HISTORY to the message body.

– If you want to view the history, then you can modify the file apwxwkfb.pls and change PROCEDURE GenerateExpClobLines:
l_line_accounting_enabled VARCHAR2(1);
to
l_line_accounting_enabled VARCHAR2(30);

Once you have made the change, bounce Apache and then re-test it.

This fix is approved by Oracle, and is documented in this note on Metalink.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close