July 24th, 2009 Matt
A colleague of mine recently asked me why I always started Oracle programs using a “/nolog” and then explicitly connecting rather than just passing in the username and password in one go. Some years back, I worked with an Oracle guru who showed me exactly why you shouldn’t, just by using one simple command in UNIX:
ps -ef
UID PID PPID C STIME TTY TIME CMD
oracle 4325 5180 0 12:00:04 ? 0:00 sqlplus system/manager
appluser 3118 3012 0 12:00:03 ? 0:00 sqlldr scott/tiger
appluser 26332 24101 0 11:00:15 ? 0:00 imp matt/matt full=y
As you can see – if you pass in the username and password, then anyone can easily find it out using ps!
So, how go you get round it? If you can’t connect from within the tool (as you can in SQL*Plus), you can use a password file, which you should then delete once you have done the job, e.g. “sqlldr parfile=$PWDFILE….” where the file contains “userid=matt/matt”.
In order to make it even more secure, then you could use temporary files for your password file, e.g.
PWDFILE=$(mktemp)
echo "userid=matt/matt" > $PWDFILE
sqlldr parfile=$PWDFILE ....
rm $PWDFILE
mktemp is a utility which will create a temporary file with a unique name.
Posted in Oracle, Technical | No Comments »
July 15th, 2009 Matt
I was playing about recently with some basic calculations and rounding within the Oracle database, using release 11.1.0.6 (and I’m sure it’s the same in earlier databases releases). I’d been busy with Dr. Kawashima’s Brain Training: How Old is Your Brain? on my Nintendo DS and doing the mental calculations, and was thinking about the basic calculation 1/3*3.
Now, following my PEMDAS (I had two years in an American school when I was younger – for those in the UK, think BODMAS) – the sum would be calculated as (1/3) * 3 so would give 1 as the result – try it in any calculator and you should get the same result.
Try it in Oracle, and you get the same:
SQL> SELECT 1/3*3 FROM DUAL;
1/3*3
-----
1
That’s all fine – now try a slight variation:
SQL> SELECT 'OK' FROM DUAL WHERE 1/3*3 = 1;
no rows selected
Hmmmm – so even though it returns 1, it doesn’t equal 1…. Is it true for all values?
SQL> SELECT * FROM DUAL WHERE 1/3*3=1;
no rows selected
SQL> SELECT level, CASE WHEN 1/level*level = 1 THEN 'OK' END ok FROM DUAL CONNECT BY level <= 20;
L O
———- -
1 OK
2 OK
3
4 OK
5 OK
6 OK
7 OK
8 OK
9
10 OK
11
12
13
14
15 OK
16 OK
17 OK
18 OK
19
20 OK
So, there you have it – a handful (and there are more) of examples where 1 !=1 in Oracle!!
Posted in Oracle, Technical | 1 Comment »
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 »
July 1st, 2009 Matt
Well, it’s been AGES since I last blogged about anything, either here or on my other personal blog – apologies, I’ve just been really busy over the past few months. I’m also working on a government project where I have no internet access, so my chances to catch up on anything out there have been limited, too.
Anyway, just a quick post to say that I’ve not forgotten about my blog, and to give you a couple of links to places that I have managed to read recently:
Tom Kyte’s blog @ http://tkyte.blogspot.com
Oracle WTF @ http://oracle-wtf.blogspot.com
I’ve also been reading some Ask Tom threads on “Top-N queries” which I was asked about in my interview for the current role. Tom has some great points about the futility of doing a COUNT(*) to indicate how many records a query would return for allowing users to page through the results, and what a waste of resources it is.
Anyhoo – must dash 
Posted in General Computing | 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 20th, 2009 Matt
Some years back, there was an internal consultation in Oracle Europe about whether we could write something to generate Workflows instead of using the workflow builder. One of the main flaws with the builder is that it only runs on a Windows machine – what do you do if you are running Linux? The only thing to do is to use an emulator to run the builder. Unless Oracle move the builder to something platform non-specific, such as Java, then this is always going to be a problem.
Sadly, this didn’t really come to anything – there wasn’t enough interest or time to put people on it, and unless a customer was paying, then it was never going to get done. We did look at writing something in XML that could then get loaded into the workflow tables, but it became quite tricky. It’s reasonably straight forward to work out the insert statements that you would need to call to load the core definition into the workflow datastore, since the tables identify which activity comes next in the process, and at one stage we did have something running that would allow this bit to work.
The part of the load that we found particularly difficult was the geometry and layout, which we couldn’t really work out in the time that we had available was the co-ordinate system for laying out the diagram correctly (something that is important for usability).
In the end, we gave up, since we also needed to come up with some mechanism to convert a flow from whatever mechanism people were starting with (Visio, Word, Powerpoint diagram, any third party tool, anything else!) into XML, to convert into an Oracle proprietary format, and since no-one had actually asked for it…
If anyone out there has actually worked on this and got it working, I’m more than interested in learning how it was done (or even why!), but there were some ideas that came out of the discussions – more about that in the next post though!
Keep reading,
Matt
Posted in Uncategorized | 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 »
