July 8th, 2009 Matt
I read this recently on Tom Kyte’s blog about three questions that he asks at interviews:
I have a table:
create table t ( ….., month number, ….. );
Month is always a number between 1 and 12.
I ask three questions about this table:
1) how many rows are in the table
2) how many rows BY MONTH are in the table (i want to know how many rows for month one, month two
and so on)
3) what MONTH has the most rows (and for a special bonus, tell me why this question is ambiguous)
At an interview for a role that I did at some stage, I was asked the following:
I have a table EMP with columns NAME, SALARY, DEPTNO. How would you write a query to give me the top three earners in each department?
I’m not sure if I got the answer right, but I got the gig 
Posted in Oracle, Technical | No Comments »
March 10th, 2009 Matt
I wrote this a while back on my old blog, and have only just got round to moving it here…
Some time ago, I started to look into the functionality behind the “Expedite” button in the Workflow monitor, just to see a bit more about what it actually did. This became more important with release 2.6.3 of Workflow, where the button was replaced with the “Rewind” functionality – which is in no way a substitute for it. Finally, I’ve got round to writing my findings up as a technical white paper.
The white paper makes (I think) interesting reading – it shows exactly how you can jump from one activity in a process to another one, whether they are linked together in the process flow or not. I’ve also written some code that allows you to jump to another activity based on the activity ID, or failing that, you can specify the name of the activity instance, item type and process name, and the code will jump to that point in the process flow for you.
A recent client of mine had exactly this requirement – based on some other process logic, they wanted to jump to a completely different point in the process. Whilst this is possible to do diagrammatically (and this is normally the best practice to employ, for sure), in certain circumstances it involves over-complicating the process flow a great deal. In the past, I worked with a customer who wanted to implement a notification at the start of the flow, which allowed the responder to jump to any other activity in the process, so that everything could be revisited at any time – after much more business analysis, we decided that this wasn’t something that they really wanted to do, it was just a management idea of “wouldn’t it be nice if…”, without any business need.
I’ve uploaded the following files as part of the white paper here:
The code is provided without any warranty, etc. etc. – please feel free to use it, but remember to give credit where credit is due
Posted in Oracle, Technical | No Comments »
February 26th, 2009 Matt
Just a quick post, since it’s been a while – I’ve been VERY busy recently, and am only now taking a decent break with the family.
Anyway – reading some of the R12 documentation yesterday, I saw that there is a new “enhancement” to Workflow Notifications in R12, which I suggest is the most useless enhancement for a while.
In R12, there is now a facility to use a document attribute as the subject of an email, as long as the subject that is generated is less than 240 characters long. The “idea” seems to be so that you can “easily” include code top make the subject of a Notification dynamic. Now, forgive me if I’m wrong, but couldn’t you do that in EVERY version of Workflow, by using a text attribute??
What benefit does a document for a subject line give you? I can think of only one – you no longer need to include a PL/SQL activity within the process to set the attribute that is used for the Notification subject. That’s the only one I can think of – and personally, I would rather see an explicit step in the process which says “Set Subject” (or the like) rather than hiding the place where the subject is determined.
I can’t see a situation where I will ever be looking to use a document API instead of setting a text attribute, but you never know – if someone can come up with a reason why you should use it, please let me know! 
Posted in Oracle, Technical | No Comments »
January 24th, 2009 Matt
One of the services that I’ve done for a while now through my company, is running design and implementation reviews – spending time with customers getting to understand what they are expecting their Workflow system to do, and then looking at their workflow functional and / or technical design document, or their code that has been written, and providing a detailed analysis of the strengths, weaknesses and opportunities for improvements. One thing that comes up time and time again is that generally the code that has been written to support a Workflow has been written without reference to the Workflow build standards that Oracle wrote some years ago – these have been out there for some time now, but here’s a brief list of what I normally find:
- Procedure signature does not conform to the recommended signature from Oracle;
- Use of hard-coded values for function mode and results rather than using WF_ENGINE constants;
- No logging, or if any is in place, it doesn’t use the Workflow supplied logging package;
- Missing or incorrect error handling.
One thing that this has really got me thinking was about how great it would be if there was some kind of framework that made it easier to write good Workflow code.
So, I sat down and put together a utility to provide well-formatted code, which provides a framework that you can plug in your specific logic. The most important thing that a workflow developer should be focusing on is getting the flow of the process right – here’s where the developer should be devoting their efforts
- Determining what activities are needed in the process;
- Deciding what level of granularity is needed
- Should you have an activity that does lots of processing, a sub-process, or a couple of activities in one process?
- Should you build a series of activities that perform simple steps, or use one activity which has a series of IF conditions in it?
- etc.
- Putting the right activities in the right order;
- Determining error conditions;
- Determining revisit conditions;
- Deciding whether business events can and should be used;
- Deciding whether to defer the process or not.
Instead of which, developers tend to be spending time on writing the PL/SQL to support the Workflow, and not enough time focusing on getting the process(es) right.
I wrote a utility which is designed to help shift this effort from PL/SQL developer onto becoming a Workflow developer. If you install the package attached to this post, a PL/SQL package is installed which can then be used to generate code to support your Workflow. You should define the Workflow process, and this code will then look for a specific Item Type and pick up the PL/SQL activity which you have defined and generate code for you. If you have left any of the activities blank, then the code will create a PL/SQL package with the name of your item type, and procedures within the package named as the internal name of the activity. The code adds logging to the procedures, error handling, and if the activity uses lookup codes, it will also generate the results at the end of the code for you.
It can also update your Workflow for you, if you set it to. So for each Activity where you have not defined the Activity function, the code will generate the code for you to amend to support the business logic, and then update the Workflow with the name of the package and procedure which has been generated to support the process.
The code is copyright TS Fifteen Ltd (the parent company of WorkflowFAQ.com), but I’m prepared to make the code available free for use. If you install the code, then it must include the module header which is supplied with the code. If you make any changes to the code, or have any suggestions for extra functionality, please contact me or add comments below. I will be writing a fuller user guide to add to the module, but here’s the current version.
Package Specification
Package Body
Posted in Oracle, Technical | No Comments »
January 14th, 2009 Matt
I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that (prior to 10g) you can only have 1000000 characters in the output. I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown.
So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.
Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table. You can then just select from the view to get the output back.
I’ve copied the code into the attached SQL file, just for ease.
Enjoy!
Matt
SQL script
Posted in Oracle, Technical | No Comments »
January 10th, 2009 Matt
I’ve just been asked to come up with a quick script for our DBAs to let them know who has been using the system recently. They want something that they can use to pick up the recent eBS users and their email addresses, so that they can let them know when the system is about to be bounced or shut down.
So, here’s the SQL:
SELECT DISTINCT ppf.full_name
, fu.user_name
, NVL(ppf.email_address,fu.email_address) email_address
FROM per_people_f ppf
, fnd_user fu
, fnd_logins fl
WHERE fl.start_time > SYSDATE - 2
AND fu.user_id = fl.user_id
AND ppf.person_id(+) = fu.employee_id
AND fu.user_name NOT IN ('SYSADMIN', 'GUEST')
/
Note, it will only pick up recent eBS users – it doesn’t look at database users who are logged in, so if there is other access to the system then you need to be aware that this isn’t going to be a definitive list. Good starter for ten though.
Posted in Oracle, Technical | No Comments »
January 4th, 2009 Matt
I was chatting with someone on a current project this week, who was asking about the best ways to get into Oracle eBusiness Suite. He was just after some kind of sandpit environment to play about in – he’s fairly technical but hasn’t got much Oracle exposure, and almost no eBS exposure.
So I said “oh, you want to have a look at Solution Beacon – they’ve got some instances.” His immediate response was “oh, I can’t remember the link, but I’m sure that I’ll read about it in your blog…”
So, here it is!
Solution Beacon have a few 11i and R12 eBusiness Suite systems running, that you can play about in – they refresh them periodically, and there’s no database login, but for finding your way around Oracle Apps, it’s a good starting point – plus you don’t need to install your own sandpit area to play in!
The links to the 11i environments can be found here.
Posted in Functional, Oracle, Technical | No Comments »
December 22nd, 2008 Matt
I was reviewing some code for an Oracle colleague of mine the other day before it was released to the general public, and one of my comments was that I preferred to use the ANSI syntax for joins that came into the database with release 9i.
The original developer then asked what the point was, since the code was always doing a full join anyway. One of the simple reasons that I like the ANSI syntax is because it is immediately obvious which parts of the WHERE clause are there to join tables together, and which parts are the “real” data selection criteria.
The place that I found best for explaining the syntax and what each means is the O’Reilly site here, which gives a great overview of what each type of join offers.
Posted in Oracle, Technical | No Comments »
December 18th, 2008 Matt
A thread on OTN recently (which I missed while I was ill) asked about displaying the history of a notification, and when I got round to it, I recalled the same thing being asked in September on Metalink here.
However, I knew that I’d addressed the problem somewhere, but didn’t know whether it was on OTN, WorkflowFAQ or on Metalink. So, to make sure that I can find it again, here’s the contents of the post from Metalink:
From: Dmitry Lidvansky 18-Sep-08 09:29
Subject: how to select notification history (?)
I need a select statement that returns notification history as it shown in notification:
http://www.4freeimagehost.com/show.php?i=b2d4948a2dbf.png (see image)
please help
it seems that WF_ITEM_ACTIVITY_STATUSES/WF_ITEM_ACTIVITY_STATUSES_H does not contain all neccessary information for example, Delegate action
From: Matthew Searle 18-Sep-08 10:39
Subject: Re : how to select notification history (?)
Hi,
This should do the trick:
1 CREATE OR REPLACE FUNCTION get_hist ( p_nid IN PLS_INTEGER
2 , p_disp_type IN VARCHAR2 DEFAULT WF_NOTIFICATION.doc_text ) RETURN VARCHAR2 AS
3 v_hist VARCHAR2(32767);
4 BEGIN
5 Wf_Notification.GetComments2 ( p_nid => p_nid
6 , p_display_type => NVL(p_disp_type,WF_NOTIFICATION.doc_text)
7 , p_hide_reassign => 'Y'
8 , p_hide_requestinfo => 'Y'
9 , p_action_history => v_hist );
10
11 RETURN v_hist;
12
13 END get_hist;
14*
APPS@SOLDEV2 on 18-SEP-2008 10:43:34> select get_hist(1574024) from dual;
GET_HIST(1574024)
————————————————————————————————————————
Action History
Num : Action Date : Action : From : To : Details
1 : 17-JUL-2008 12:03:45 : Submit : SYSADMIN : Searle, Matthew :
2 : 18-SEP-2008 10:21:00 : Request Information : Searle, Matthew : SEARLEM4B : Hello
From: Dmitry Lidvansky 19-Sep-08 10:10
Subject: Re : Re : how to select notification history (?)
3 v_hist VARCHAR2(32767);
that means notification history cannot be longer than 32767 ?
what if it will be so ?
From: Matthew Searle 22-Sep-08 11:50
Subject: Re : Re : Re : how to select notification history (?)
If it’s over 32767, then it will error
The API returns a VARCHAR2, so that’s the limit – I’ve not tested it to see what happens if it’s too long.
If you want something that will cater for something bigger than 32K then you will need to look at the underlying code and pick out the cursor(s) it uses, then use that instead.
So, if you need to know how to display the full history of a notification from the notification ID, that’s how to do it.
Posted in Oracle, Technical | No Comments »
December 14th, 2008 Matt
Generally, there is no benefit / possibility of spell checking code – I’ve included a number of code samples in my documents and then been frustrated that the spell checker keeps telling me that VARCHAR2 isn’t a word.
However, there are some places where it’s important to ensure that what you type is what you mean. For example, if you have code that references a business event, then you need to make sure that the event name is correct before you start including it in the code. It would be really bad, for example, if you spellt, say, the name of your company badly. Particularly if it was a BIG company that people may need to trust can type properly.
Oh well, at least (as this image shows from my 11.5.10 system) that it doesn’t happen to any large software providers, eh?
"Oracel" event
Posted in General Computing, Technical | No Comments »
