jump to navigation

Friday Philosophy – The Issue of Exceptions in your Mailing Lists September 18, 2015

Posted by mwidlake in development, Friday Philosophy, Perceptions.
Tags: ,
1 comment so far

A couple of days ago I got the below email about the next UKOUG Database Server SIG being full. That’s great to see! A full user group meeting. If I still wanted to attend, I could be added to the waiting list:

ScreenHunter_42 Sep. 14 11.25

It made me smile as, guess what, I was already due to be there. I was the last speaker on the agenda! It was going to be awkward if I failed to get from the waiting list to an actual place at the event, it could be a very quiet session…

As I said, it made me smile – I’m not having a go at the UKOUG over this. Because, as I’m sure many of you are aware due to the day job, removing people from a mailing list who are no longer going to be interested is not always easy (I know, it should be easy, but it just seems not to be). Anyone registered for an event should be on an exclude list for the event. But only for that event. And for that to occur you have to make sure that all speakers and committee are registered (as committee and speakers can be very poor at actually registering!).

Sales and promotion communications are one thing and, let’s face it, in the scheme of things damned unimportant – except to the company doing the sales. If you sell clothes and you mess up on your communications to me, I’ll have a poorer opinion of your company and I don’t mind having that poor opinion. I won’t buy your clothes, no skin off my nose. As a recent example on a personal level, my mother was having a new door and window fitted. She was contacted by a company that replaced a couple of her windows the year before and as the service was fine then and she knew she had to have the door replaced, she said yes. But then she got called by them again a month later asking if she wanted any more windows or doors – “But I just ordered from you, have you lost the order?”. They apologised and said it would not happen again. But it did, a couple of times – including the day before the new door was to be fitted, whilst she was waiting for a call about the exact time for the work the next day. A lesson in How to Confuse A Little Old Lady. The end result is, she won’t use that company again. Especially as the new door seems to have a fault they won’t come and fix. Maybe the inability to take a current client off the pester-list should have warned us off them.

But there are other communications where the need to take care of the exclusions or keep the lists timely are far, far more important. People get really upset if the hospital sends a reminder for a checkup to their father – who died last month, in that hospital. In that situation people are so sensitive that they can’t just accept it as an administrative cock-up. It is now seen as an affront to their dead relative’s memory and a sign the hospital do not care.

An even worse situation than the above is if the hospital wrongly records you as dead. It happens and it happens “quite often” as it is very hard to keep individual, accurate records on people who keep going and changing names, addresses and have the same name and date of birth as other people. I know this as I once had to write the software to do all the test cancellations and notifications required internally in a hospital that occur when someone dies. And I also had to write the software to undo all of that when someone realised the wrong person had been recorded as dead. {The functions were initially called “kill patient” and “resurrect patient” – They got renamed pretty damned quick when people outside the medical staff saw them. Medics have a dark sense of humour that most other people don’t!}. It happens and when you see how many people in a large hospital system have very similar details, you can appreciate why. I bet that right not, somewhere in the UK, is a hospital with two people of the same name & date of birth as inpatients and at least one has a chance of not surviving the experience.

As for sets of duplicate records as no one realised Sarah Twoddypottle is the same person as Sarah Poddytwoddle who came into A&E 4 years ago and neither knew their NHS number… 20 years ago I could have done a PhD on the topic of duplicate patient records, the situation was so bad – and not at all helped by people wanting “privacy” ie no joining up of national medical records. I digress.

People get similarly upset about money (some more so than medical!) – any attempt to offer someone a loan who is already in debt and won’t be accepted for a loan causes all sorts of anger and annoyance. It’s all seen as personal by the wronged customer when in fact it is just an impersonal business function. No, they don’t care about you Mr Postlethwaite, but they don’t not care about you either. You are just one of a million customers. Level of care for the individual is not part of the equation.

I’ll finish on an interesting one. Companies that offer gambling services have to abide by some strict rules about who they can promote their services to or even allow to use their services. To try and halt the horrendous increase in people gambling away money they don’t have there are strict legal rules about self-exclusion and cooling off periods. If you get into serious debt due to gambling you can state you are an addict and it was partly the fault of the company you gambled with. So they must not encourage you any more. At least not for 6 months. {NB I am not a lawyer, I may have the exact details wrong, but the gist is right}. After the time period they are allowed to advertise to the person again as it would be wrong and immoral (???) to exclude them forever. Besides, they are a good source of revenue…

It’s important to get your communications correct, timely and exclusion-aware. And just consider in what situations people might be especially sensitive to what they see as an impersonal system not caring about them or their nearest and dearest. Most people find it hard to accept that such mistakes are not personal, even when they are blatantly impersonal.

Friday Philosophy – If You are reading this You are probably Pretty Smart September 11, 2015

Posted by mwidlake in Friday Philosophy, humour, Knowledge.
Tags: , , ,

I bet I can predict a few things about you. And I mean You, the person reading this on my Blog right now.

At senior school (when you were aged between 11 and 16-ish) you were at the top of your classes. At least most of them. You were in the top few in your year for most science subjects and probably Maths too.

And you were not bad at any of your subjects once you had a choice over them, but there was at least one subject you were glad to get rid of as you were poor at it. But if you think back, I bet you were simply *almost average* at it, there were as many kids or more worse at it than you then there were better than you at it. You were just not as stand-out good as you were in other subjects. Come on, I’m right aren’t I? Even your poor subjects you were OK at compared to all the other kids.

You almost certainly went to college and, if you are under 35, you did to study a STEM subject – Science, Technology, Engineering & Maths. If you are 40-50 you did not study computing but you ended up working in it anyway. 35-40? You either studied computing, thought about it or took options in your course that had a major element of computer programming.

However, you were probably not quite the smartest kid in your year at school.

What makes me think this? Because I can mind-read(*)?

No, I think this simply due to the fact that you work in corporate IT or something very similar. You use your brain to solve issues or get tasks done. I think that the generally high level of intelligence is a more common trait in IT than the other thing I can probably predict about you – you don’t feel you are a people person, not really. Using myself as an example; I present at conferences, I have run training courses and I usually have something to say in a large group or discussion; I even partly run London Oracle Beers, a social Oracle user group. But I can’t make small-talk with people I don’t know and I am uncomfortable meeting new people.

We have to be intelligent to make a career in IT and most of us were probably drawn, at least in part, towards computers and software as we could work on it on our own and the computer did not have any feelings to dent or make any social demands on us. However, many (most) people are social nervous and so I think the more defining trait of people in IT is that they are intelligent – and intelligent in the way that IQ tests measure intelligence: A mixture of learnt patterns and various problem solving/logic/deduction skills. If you have had your IQ tested I am sure you are 120+. I would not be at all surprised if you score 130+. But you still have a little way to go to beat… my wife.

You probably were not the smartest kid in your year at school as the smartest kid either went into medicine/vet school, academia, law or crime – something more way-out-there.

So what point am I making? None really. It’s more just an observation. Though I do think you should occasionally take time to say a prayer of thanks to Pythagoras (or whoever you think is listening) and remind yourself how lucky you are. Most of your intelligence is inherited and the smaller (but not insignificant part) is due to schooling and your own efforts (note, I’m talking intelligence, not what you know – the two are different but aid each other). Your brains were given to your by your parents and grandparents, no matter how hard that can sometimes be to believe🙂. I was on a PL/SQL course with Steven Feuerstein about 10 years ago and he took a minute or two to passionately state how lucky all of us on the course were to have our brains, to be making our living by thinking and not back-breaking toil or dull manual work. It was a sentiment I whole-heatedly agree with. I actually love spending time digging a ditch or chopping down trees but I would hate having to do it every day for a living.

Of course, intelligence of the IQ type is not everything and it does not make you a better person. I’m sure we have all known some very smart assholes and some wonderful people who can’t think themselves out of a damp paper bag. Some people have average IQ and yet have talents most of use would struggle with, like making a violin sound anything but bloody awful. I’ve known academics with an IQ somewhere Way Up There but who had about as much common sense as a pigeon. I have come across a few examples of intelligence bigotry in my time too. I know one guy in an academic institute who tried to insist that the highest grade you could achieve and the top of your salary band be dictated by your best academic qualification. No one without some sort of degree should get above level 5, No one with less than a 2(i) allowed in grade four and to get to grade 2 a PhD was needed. He was a very smart asshole. I’ve known a couple of people without a degree in this business of IT and both of the ones I’m thinking of right now are very, very good at what they do.

So be grateful for that brain of yours and just remember that most people are not as intelligent as you, so show patience in explaining and working with them. And if you are not patient, you could well be an intelligent asshole. You might need to learn to not be like that.

(*) Just as an aside, deducing things about groups of people and, in fact, traits most people have is not hard. It’s called cold reading. It’s what mediums, mind readers, psychics and other intelligent assholes use to hoodwink people. Part of it is things you can guess at given one piece of information or even none. I can deduce things about you because you work in IT. I know you feel you’ve never reached your potential and you have more to give as *almost everyone does*. No one wants to be seen to be selfish but we all know we are, at least at times. Even Mother Theresa thought so at times. The other aspect to it is reading body language and empathy, which is why I can’t make a living as a psychic. I just don’t get people….

Just thinking on this aside for a second, maybe mediums and psychics could get less abusive jobs as data analysts? Deducing things about people based on averages and correlation is Big Data Business right now.

(Update – thanks to the person who quietly contacted me to point out my spelling error/poor grammar with “patience” and “patient” – in my section in intelligent assholes too! He showed real patience with me)

Friday Philosophy – On “Being the Expert” September 4, 2015

Posted by mwidlake in contracting, Friday Philosophy, performance.
Tags: , ,

Working as a recognised expert at something is a little…strange, I find.

I had an assignment this week to go visit a client, have a look at a performance issue and find out the root cause. I was also to at least come up with suggested resolutions with the ideal aim of giving them a proven fix they could implement. All to be done in two to three days. This is pretty standard fayre when you are putting yourself forward as some sort of expert in something. And it is not always an easy thing to do – for more reasons than you might expect.

When it comes to the core service you are providing you are certainly expected to know your stuff and if you are there as the expert and you don’t? Well, any pain you now suffer is self-inflicted so I have no sympathy. You might think actually being an expert is the hard part – the knowing all that stuff, remembering it, the ability to answer all the questions or look at an issue and in 5 minutes say “It’s because the CLOB settings are wrong”. ie matching the expectations of almost God-like knowledge and ability. But it is not. If you can listen to what their problem is, understand it and then explain something to them that they did not know before, it will be fine. What the client needs is to feel progress is being made. An immediate and inspired solution may occasionally be possible but on the occasions I have pulled that off, the client usually just feels uncomfortable, like they missed the obvious. Because they did. If I sort out the issue straight away that they have had for 3 weeks and that the in-house expert has looked at there is only really two possible reasons
(a) it is simple and they missed it.
(b) they ignored their expert.

The option of (c) my genius is sadly just a dream.

What I find more tricky is when they just accept what I say, when they treat everything I say as correct. Even if I say “it might be this” there can be an assumption I am being modest and it really is what I suggest. I’d like them to only believe me once there is some proof. Most of my time on such assignments is me sat at the SQL prompt trying to back up what I think is the issue/solution. Even when I have evidence, I know I could just be seeing what I want to see. I want some proof and I want them to challenge it.

There is also sometimes a tendency for the rest of the staff to regarded you as some sort of a weirdo, someone Not Like Them. After all, if you are an expert in Oracle Performance you must spend all your time looking at explain plans and 10046 traces and not doing normal people stuff. I have to say, I had a really nice (and in some ways quite worrying) complement a few years back. I was at a client site for a couple of months, plowing though what seemed like endless layers of bad code/design/decisions to make things run better. One lunch time I headed out to find some lunch with a couple of the developers. One of them turned to me and said something like “You know, I’m really glad you joined us. You’re just a normal bloke and not one of those freaky tuning experts!” He really thought all Oracle Performance people would be strange – and strange in the already bizarre context of all the other people that inhabit our profession. I wonder who else he had met?

You can also run into resentment – occasionally irrationally (fear of challenge? envy? just psychotic people?) but also for real reasons. I sort-of alluded to it earlier. You get listened to when you are “Being the Expert”. Even though you may say what Sarah had already pointed out last month, you get listened to. Sarah is not going to be happy about that. Sarah is going to be especially annoyed and resentful if she told Me, the expert, about the point I raised. In these situations I try and emulate what a friend of mine taught me about 10 years ago on “Being The Expert”. One of your jobs as an external consultant should be to tell the client to listen to their staff if their staff are getting things right. What the real problem is could well be that the client is not using the resources it already has. And you were, after all, hired to solve their problem.

The final thing I find strange that I’ll mention is this. As the expert I am constantly anxious I am going to be “found out”. I mean, right now, I am doing my final report on this assignment. I know I identified several issues, I backed them up with evidence, I moved the client forward. I found out things that they had not known. I taught some of the staff new stuff. I stressed that I will not have found everything as it was only 3 days with no access to the live system… But I worry that in 3 weeks I’ll hear that none of what I suggested worked and that the REAL issue was something I utterly missed and when they corrected that, the run time went down by a factor of a thousand. And I failed them.

I just worry about that. Because I am “Being the Expert”

A couple of up-coming user group meetings August 18, 2015

Posted by mwidlake in Meeting notes, Presenting, User Groups.
Tags: , ,
add a comment

There are a couple of user group meetings in the UK that I’ll be attending (and talking at) in September.

On the 15th September I’ll be presenting at the UKOUG Database Server SIG in the Oracle London City office, talking about PL/SQL & SQL performance. I’m not exactly sure what I’ll be covering yet, I have a few areas I’d like to talk about so I’ll have to pick one to do it justice. The meeting starts at 09:30 and is of course free to anyone with UKOUG membership (excepting the Bronze membership which only a few people have) – anyone can pay a small fee to come along. Contact the UKOUG or ask me if you want details. I’m pretty sure there will be some of us in a nearby public house after the event too.

The next meeting is the Yorkshire Database meeting on Tuesday 22nd September, from 18:30. This is the third YoDB event and I know they have been very good. I’ll be doing my talk on the fundamentals of Oracle’s architecture. I’m really quite excited about this meeting {and I know it is often a false “sell” thing to say about any IT event} because (a) it is a small, local grass-roots user group that I’ve helped promote and yet will be the first I’ll manage to get to and(b) I was at college in Leeds and so have a soft spot for the place. I still have some friends up there. This event is free to all but you DO have to register using the link above.

As ever, it’s great to meet people so please come over and say “hi” if you are at either event.

I’m hoping the postponed cluboracle meeting will happen in September too but either a new date has not been announced or it went by me.

If you want to see what events I’ll be at in the later quarter of the year, you can check out the “appearances and meetings” tab. It’s mostly smaller things like OOW and UKOUG Tech15🙂

Friday Philosophy – Building for the Future August 14, 2015

Posted by mwidlake in Architecture, development, Friday Philosophy.
Tags: , ,

I started my Oracle working life as a builder – a Forms & Reports Builder (briefly on SQL*Forms V2.3 but thankfully within a month or two we moved up to SQL*Forms V3, SQL*reportwriter V1.1 and SQL*Menu 5 – who remembers SQL*Menu?). Why were we called Builders? I guess as you could get a long way with those tools by drawing screens, utilising the (pretty much new) RI in the underlying Oracle V7 to enforce simple business rules and adding very simple triggers – theoretically not writing much in the way of code. It was deemed to be more like constructing stuff out of bits I guess. But SQL*Forms V3 had PL/SQL V1 built in and on that project we used it a *lot*.

I had been an “Analyst Programmer” for 3 years before then and I’ve continued to be a developer/programmer/constructor-of-code on and off over the intervening couple of decades. I’m still a developer at times. But sometimes I still think of it as being a “builder” as, if you do it write {sorry, little word-play joke there} you are using bits of existing stuff and code designs/patterns you know work well and constructing your system. The novel part, the bit or bits that have never been done before (at least by me), the “architecting” of those units into something interestingly different or the use of improved programming features or techniques vary from almost-none to a few percent. That is the part which I have always considered true “Software Development”.

So am I by implication denigrating the fine and long-standing occupation of traditional builders? You know, men and women who know what a piece of two-by-four is and put up houses that stay put up? No. Look at the below.

This is part of my neighbour Paul’s house. He is a builder and the black part in the centre with the peaked roof is an extension he added a few years back, by knocking his garage down. The garage was one of three, my two were where the garage doors you can see are and to the left. So he added in his two-story extension, with kitchen below and a very nice en-suite bedroom above, between his house and my ratty, asbestos-riddle garages. Pretty neat. A few years later he knocked down my garages and built me a new one with a study on top (without the asbestos!) and it all looks like it was built with his extension. Good eh? But wait, there is more. You will have noticed the red highlight. What is that white thing?

Closer in - did he forget some plumbing?

Closer in – did he forget some plumbing?

This pipe goes clean through the house

This pipe goes clean through the house

When I noticed that white bit after Paul had finished his extension I figured he had planned more plumbing than he put in. I kept quiet. Then, when he had built my new garage and study, I could not help ask him about the odd plumbing outlet. So he opened it. And it goes through the dividing wall all the way through to the other side of the house. Why?

“Well Martin, putting in cables and pipes and s**t into an existing house that go from one side to the other, especially when there is another building next door, as a real pain in the a**e. It does my ‘ead in. So when a build something that is not detached, I put in a pipe all the way through. Now if I need to run a cable from one side of the house to the other, I have my pipe and I know it is straight, clean, and sloping every so slightly downwards”. Why downwards? “Water Martin. You don’t want water sitting in that pipe!”.

I’ve noticed this about builders. When I’ve had work done that is good, there is at least one person on the team who thinks not just about how to erect or do what needs to be done today, they do indeed think about what you will need after the build is done, or in a few years. Such as hanging doors so they do not smack into the cupboards you will put in next… *sigh*. Paul is the thinking guy in his little team. I suspect one of the others is pretty smart too.

But isn’t this what the architect is for? To think about living with the building? Well, despite the 7 years plus needed to become a true architect (as that term really means, not as some stolen label for software designers with too much ego) I’ve had builders spot the pragmatic needs a couple of times that the architect missed.

And as I think we would all agree, a good software developer always has an eye on future maintenance and modification of the software they develop. And they want to create something that fits in the existing system and looks right. So just like my builder neighbour does.

I’m not a software architect. I’m a code builder. And I’m proud of it.

STANDARD date considerations in Oracle SQL and PL/SQL July 29, 2015

Posted by mwidlake in internals, PL/SQL.
Tags: , ,

Most of us know that the Oracle DATE datatype has upper and lower limits. From the Oracle 11g Database Concepts manual:

Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or ‘AD’). Unless BCE (‘BC’ in the format mask) is specifically used, CE date entries are the default.

I never believe 100% anything I read, so I’ll try that. I’ll set my session to show dates with the AD/BC indicator and step back in time:

Session altered.

-- select today
ora122> select sysdate from dual;


--now let us go back to "the edge of time"
ora122> select sysdate -2457232 from dual;


ora122> select sysdate -2457233 from dual;
select sysdate -2457233 from dual
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

-- Now to do similar in PL/SQL

out_text varchar2(100);
  select to_char(sysdate) into out_text from dual;
  dbms_output.put_line (out_text);

ora122> @std1


PL/SQL procedure successfully completed.

How did I do that? We can see from the SQL that the documentation is correct and SQL refuses to accept a date before the lower limit. How did I get a date before 01-JAN-4712BC in my PL/SQL? Especially as my default SYSDATE?

I’ll let you think about that for 30 seconds, you can look at a picture of my recently gone and much missed cat (NB she is happily snoozing in this shot!).

I miss this fleabag

I *really* miss this fleabag

So how did I do it? I cheated.

But I cheated in a very, very interesting way. I did not show you all of my PL/SQL code, which I now reveal below…:

sysdate varchar2(20) := '31-DEC-4713BC';
out_text varchar2(100);
  select to_char(sysdate) into out_text from dual;
  dbms_output.put_line (out_text);

So, showing you my whole code (see, don’t believe everything you read – sometimes things are simply wrong and sometimes people deceive you) you can see the critical part at the start. My anonymous PL/SQL block is in fact a child block to another. And in that parent block, right at the start, I over-ride the definition of SYSDATE. in the declaration section

sysdate varchar2(20) := ’31-DEC-4713BC’;

I not only set it to a specific value, I set it to be a Varchar2 datatype. The TO_CHAR of it later on in the logic, which I included in the code I originally showed you, was just more subterfuge on my part. The PL/SQL engine does not care if you TO_CHAR an already CHAR-type field, but it hid the fact that I’d played this trick.

You could define a local SYSDATE variable, as a date, set to a specific date and time if you wish. Even one in the future. And anyone seeing odd behaviour and checking to see if the initialization paramater FIXED_DATE had been set would find that it had not and might soon be questioning their own sanity.

How many of you knew you could do that? You can over-ride what most of us would consider a Reserved Word in PL/SQL. I suspect it is something that people new to PL/SQL might find out by accident (because no one told them you could not use reserved words for variable names) but experienced people would not as it is simply a daft thing to do. I’d go further, it is a stupid thing to do. Think how much trouble it could cause in introducing bugs and making the code hard to understand. {And thinking further about this, I might see if I can get re-employed at a couple of places and starting doing things like this, just for the evil pleasure, as it could be an utter nightmare to spot}.

The reason this trick works is that SYSDATE, along with many interesting things, are not “Built In” to the PL/SQL language but are defined in two key packages – SYS.STANDARD and SYS.DBMS_STANDARD. These are always there and define many core things. You can DESC both of them in SQL*Plus or pull the package specification out of DBA_SOURCE and, unlike many of the other Built In packages, the code is not wrapped for STANDARD, so you can look at it. You can do this with a “lowly” DBA-type user, you do not need to be on as SYS or SYSTEM.

I am not sure of the exact rules but I think that when you use a locally qualified variable (ie you do not state the code block, package or stored function/procedure it comes from) it looks at the current variables as defined in the current and parent PL/SQL blocks first and then looks at STANDARD and then DBMS_STANDARD. I am not going to mess with STANDARD or DBMS_STANDARD, even on my play box, to find out the exact order of the two. If I spent 10 minutes looking at the specifications I might be able to see that one references the others I suppose…

This is part of the specification from DBMS_STANDARD:

package dbms_standard is
  -- types
   type ora_name_list_t is table of varchar2(64);

  -- DBMS_ID and DBMS_QUOTED_ID define the length of identifiers
  -- in objects for SQL, PL/SQL and users.
   subtype dbms_id is varchar2(30);
   subtype dbms_quoted_id is varchar2(32);

   subtype dbms_id_30 is varchar2(30);
   subtype dbms_quoted_id_30 is varchar2(32);
   subtype dbms_id_128 is varchar2(128);
   subtype dbms_quoted_id_128 is varchar2(130);

  -- Trigger Operations
  procedure raise_application_error(num binary_integer, msg varchar2,
      keeperrorstack boolean default FALSE);
    pragma interface (C, raise_application_error);         -- 1 (see psdicd.c)
    pragma restrict_references (raise_application_error, WNPS, RNPS, WNDS, RNDS);
  function inserting return boolean;
    pragma interface (C, inserting);                       -- 2
    pragma restrict_references (inserting, WNPS, RNPS, WNDS);
  function deleting  return boolean;
    pragma interface (C, deleting);                        -- 3
    pragma restrict_references (deleting, WNPS, RNPS, WNDS);
  function updating  return boolean;
    pragma interface (C, updating);                        -- 4
    pragma restrict_references (updating, WNPS, RNPS, WNDS);

You won’t find a package body of DBMS_STANDARD – that is because, I believe, all entries in the package specification are types or functions/procedures that lead to C functions, via the ADA-like {If you did not know, PL/SQL is based on the ADA language} pragma directives of “pragma interface (C, {something}), which says this function/procedure is coded in another language (C in this case) and is called {something}. Don’t ask me more, I don’t know.

procedure commit;
pragma interface (C, commit);

Even the base data types are defined in STANDARD:

package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SED edit occurs!

  /********** Types and subtypes, do not reorder **********/
  type BOOLEAN is (FALSE, TRUE);

  type DATE is DATE_BASE;

  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)
  subtype INTEGER is NUMBER(38,0);
  subtype INT is INTEGER;
  subtype SMALLINT is NUMBER(38,0);
  subtype DECIMAL is NUMBER(38,0);
  subtype NUMERIC is DECIMAL;
  subtype DEC is DECIMAL;

  subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;
  subtype NATURAL is BINARY_INTEGER range 0..2147483647;
  subtype NATURALN is NATURAL not null;
  subtype POSITIVE is BINARY_INTEGER range 1..2147483647;
  subtype POSITIVEN is POSITIVE not null;
  subtype SIGNTYPE is BINARY_INTEGER range '-1'..1;  -- for SIGN functions


  subtype VARCHAR is VARCHAR2;
  subtype STRING is VARCHAR2;

  subtype LONG is VARCHAR2(32760);

Anyway, I leave the reader to go and look at the package specifications and the STANDARD package body {some of which I show at the end} but I leave you with a repeat of the above warnings: Don’t go replacing the core variables and functions in your PL/SQL code just because you can and do not, repeat, do NOT mess with those two packages. I am sure Oracle Corp will throw your support contract out the window if you do.

As promised above, here is the code for SYSDATE, in SYS.STANDARD, and it is very interesting – in the manner as mentioned above it calls a function (pessdt) that only calls a C program (presumably to get the datetime from the server clock) and failing that, reverts to the SQL method of selecting the pseudocolumn from dual. SYSTIMESTAMP below it is the same:

  function pessdt return DATE;
    pragma interface (c,pessdt);

  -- Bug 1287775: back to calling ICD.
  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT SYSDATE FROM DUAL;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  function sysdate return date is
    d date;
    d := pessdt;
    return d;
      select sysdate into d from sys.dual;
      return d;
  function pessts return timestamp_tz_unconstrained;
    pragma interface (c,pessts);

  -- Special: if the ICD raises ICD_UNABLE_TO_COMPUTE, that means we should do
  -- the old 'SELECT systimestamp FROM dual;' thing.  This allows us to do the
  -- SELECT from PL/SQL rather than having to do it from C (within the ICD.)
  FUNCTION systimestamp RETURN timestamp_tz_unconstrained
  IS  t timestamp_tz_unconstrained;
    t := pessts;
    RETURN t;
      SELECT systimestamp INTO t FROM sys.dual;
      RETURN t;

I’ve Been Made an Oracle Ace Director July 16, 2015

Posted by mwidlake in ACED, Presenting, User Groups.
Tags: , , ,

Well, I guess the title of this post says it all. As I tweeted yesterday:

I’m grateful, proud, honoured and overall just Jolly Chuffed to have been made an Oracle Ace Director! #ACED

I can now put this label on my belongings

I can now put this label on my belongings

I’ve been an Oracle ACE since 2011 and I’m really happy to be making the step up to being an Ace Director. What does being an ACE Director mean? Well, it certainly does not mean that I am technically brilliant. As my community role is as a technical person then I do have to be competent and experienced to be an ACE (or Associate or Director) – but there are many, many people out there who are technically superior to me and are not {and may well not want to be} ACEs of any kind.

To be an ACE of any flavour you have to be committed to supporting the Oracle User Community. The whole ACE program is, I believe, more about recognising and supporting that user community than anything else. Actually, the ACE program web site states this (ACE Program FAQ). To become an ACE Director you have to demonstrate that you have been actively supporting the community for a while (please do not ask me to quantify “a while”) and that you are committed to continuing that activity for at least 12 months. There are some specific activities and commitments that come with the badge but that is balanced by a commitment by the Ace Program to give you some support in doing so (this does not include being paid, it is still voluntary). As I understand it, all ACEs and ACE Directors are reviewed every 12 months and can be re-designated if your community activity has changed.

As I said above, there are a lot of technically strong people who are not and never will be ACEs. This is often because user community activity is not their thing – they have little interest in blogging, presenting, writing or volunteering for user groups. I also know some people who do all those things but they would rather do that with no specific acknowledgement by Oracle Corporation. I guess I am saying that though I am proud to now be an Oracle ACE Director, the main thing it tells you about me is that I am passionate about the user community and I am happy {heck, Jolly Chuffed} to be recognised by Oracle for that. And I am happy for that dialogue to be two-way also. One of the conditions of being an ACE Director is you play a part in representing the user community to Oracle.

Does this mean I have “drunk the Oracle Kool-Aid” as I think some of my American friends would call it? No. Before I became an Oracle ACE I chatted to several friends already on the program and no one I know has been told to not say anything or sanctioned by the ACE Program for criticising some aspect of Oracle Tech. We are still free to be Bitter Old Men & Women (apart from the Bitter Young ones of course). Anyone who has followed my blog for a while, seen me present a few times or spent a couple of evenings in the pub with me will known that I can, at times, be quite critical of aspects of the corporation or it’s software. There is no gagging of us ACEs that I am aware of.

Will being an Oracle ACE Director alter my user community activity? Well, it might. I was doing a lot for the community before now, I made a decision 2 or 3 years ago to become more active in the User Community {for the simple and selfish reason that I like doing it a lot more than I like commuting in and out of London every day}. You don’t do all of this for the ACE recognition, you do it for others reasons and maybe get the ACE badges on the way. But the program helps the Directors a little more, opens a few more doors. So I think I’ll be able to step it up a little more. I’m really looking forward to that.

I’ll stop there. If you are interested in another Oracle ACE Director’s take on the role, check out this video by my friend Tim Hall.

Friday Philosophy – Being Rejected by the Prom Queen July 13, 2015

Posted by mwidlake in conference, Friday Philosophy, Presenting, Tech15.
Tags: , ,

If you follow me on twitter (and if you are on twitter, why would you *not* follow me🙂 See Twitter tag on right of page -> ) you will know what the title is all about. I posted the below on my twitter feed a few weeks ago:

Submitting to speak at #OOW15 is like asking out prom queens. You live in hope – but expect rejection🙂

{BTW if prom queens are not your thing and you would rather be asking out the captain of the football/ice hockey/chess team, the vampire slayer or whatever, just substitute as you see fit.}

I’ve not submitted to Oracle Open World for years – in fact, I’ve never submitted papers to OOW. Of the two times I have spoken at the conference, once was at the request of an Oracle product manager as the “friendly customer” in his talk {I did 75% of the talking and was not even mentioned on the agenda!} and the other I was actually presenting back at Redwood Shores at an Oracle Life Science conference running parallel to OOW. Both were a decade ago. But this year I decided to give it a shot and put forward 3 talks – all technical but intro talks, which I thought they would like as it would be a nice balance to much of the content, which is either deep technical stuff or, more often, “how great is Oracle” presentations on the latest stuff. And for 2015, endless fluffy Clouds.

I know it is hard to get accepted at OOW and, despite my personal, utter conviction that my talks would be brilliant and wow the audience, I had no great hopes. I was up against the Oracle-Presenting-Equivalent of the Sports Jocks and children-of-the-fabulously-wealthy at college. But for a short & wonderful period, I actually thought she was going to say “yes”!!! You see, lots of my friends who had also “asked out the prom queen” were tweeting that they had been rejected. But I had not, no email in my inbox saying that whilst I was cute, we were not right for each other. In fact, when the odd tweet went out from people saying that one or two of their talks had been rejected but not all, I started to think I was going to slam-dunk the whole affair and get all 3 talks in! What would be the equivalent of that with the Prom Queen? Probably stuff her dad would not be happy about.

But no, I’d forgotten I’d submitted under my ORA600 email address and when I remembered, I found the email waiting there. All three rejected. No dancing with the Prom Queen for me. *sigh*. It was like she’d not only turned me down but rung up my mum to refuse my offer of a date, rather than tell me direct. I would not have found it so hard to take if only, for that short while, I thought I was going to get a “yes”.

I suppose it is only fair. I’ve been on the panel of people choosing the content for the database part of the UK Oracle User Group Tech15 conference in Birmingham. Notification for acceptance or rejection of submitted papers to that event went out just a day or two before the OOW notifications and I knew some of my friends were going to be disappointed. I worried about that a little, they knew I had been involved in the choice and so was partly responsible for them not being selected. {I really hope that the person who told me to stay on holiday in Montenegro as they knew where I lived was kidding….}.

OOW15 and UKOUG Tech15 actually had a common issue I believe – less physical space for talks. I’ve been told that the Moscone centre has been overhauled and some space is still not ready. For Tech15 we are sharing space with Apps again so do not have as much room as we would ideally like. However, the main issue (again for both events) is just the number of good presentations by good speakers that we get. If we had space for 6 concurrent database streams at the same time (we do have space for 4 or 3, depending on the day) we would still have more than enough good talks – and the delegates would have to be picking between maybe 3 or 4 talks out of the 6 that they personally would like to see – and feeling they were missing out no matter what.

I’ll say more at a later date about how we actually pick the talks (the post is half written) but the take home message for anyone rejected from UKOUG Tech15 this year is:
(a) The competition was strong.
(b) You have a known target for your anger (Look, it really is 90% decided by the judging scores!!!)
(b) You can take comfort schadenfreude in the knowledge that I (and several other committee members) have suffered exactly the same disappointment as you. Maybe worse for me – for a while I was convinced the haughty little minx was going to say yes….

If you got rejected by OOW15 then I think the important things to keep in mind are:
(a) It’s all just Sales Pitch & Company flag waving & cloud-cloud-cloud and you never really liked that prom queen anyway. {Me? Bitter?}
(b) There is a stellar line up of people who have also been rejected. Try checking out the twitter tag #TeamRejectedByOracleOpenWorld {quick nod to Tim Hall for coming up with a such a funny idea}.
(c) At least you put in for it. The one way to be sure you won’t get something is to not try.

Oh well, there is always next year. If my ego has recovered by then. I quite fancy the new captain of the chess team…

Computers are Logical. Software is Not July 3, 2015

Posted by mwidlake in development, Friday Philosophy, future.
Tags: , ,

We’ve all heard it before. Computers are totally logical, they do exactly what they are told. After all, Central Processing Units (CPUs) are built out of fundamental units called Logic Gates. With perhaps the exception when a stray cosmic ray gets lucky, the circuits in a computer chip and memory act in a totally logical and predicted manner.

And of course, anything built on top of computers will be utterly logical as well. All those robots that companies are designing & building to clean our houses, do our manual labour and fight our wars are going to be logical, follow the rules given and be sensible.

But they are not. As Software is not logical. Often, it is infuriatingly illogical and confusing. Which makes you worry about the “domestic servant” robots that companies are developing, the planned “disaster scene recovery” robots they keep telling us are coming and especially the “Killer Robots” -sorry, “Defense Robots” – that the military are beavering away at.

This XKCD cartoon very much refelects some recent experiences I have had with consumer software:

XKCD - Haunted Computer

XKCD – Haunted Computer

I’d say that, unless an algorithm is about as simple as a Centigrade-to-Fahrenheit conversion program, it will have a bug or will mess up with out-of-range values. Just think back to when you wrote your Centigrade-to-Fahrenheit program (we all have, haven’t we?) back at school or on your home computer or you first week on the college course. What happened if you input a temperature of -1000C, an impossible temperature? I bet it either fell over or gave a just-as-impossible Fahrenheit value. Logical but stupid.

I worked on a financial system a few years back that, as one very small but significant part of what it did, showed you your average spend on things over 3 years. It took several weeks to explain to the program manager and his minions that their averaging code was wrong. Utterly, hopelessly and tragically wrong. First, it calculated and displayed the value to several decimal places – To thousandths of a penny. Secondly, it did not take into account the actual period over which you had spent your money. If you had opened your account 1 year ago, it still calculated the value over 3 years. As for taking into account months, weeks and days of the year, don’t make me laugh. You might be able to forgive this except the same team had also written the code to archive off data once it was 3 years old – in whole years. So there would only be between 2 and 3 years of data and only 3 whole years for, theoretically, 1 day. But no, they had hard-coded the “divide by 3 years”.

We have all experienced endless issues with computers or peripherals that will work one day, not work properly the next and then go back to working. Firmware and Operating Systems are just software really, with the same flaws as the stuff we write and fix in our working lives day after day. There will be a twisted reason buried deep somewhere why the printer will not work on Thursdays, but it won’t be a sensible reason.

All the software out there is more or less illogical and broken. The less broken gets used and we learn it’s idiocies. The worst gets canned or labelled “Windows 8” and forced on us.

Crazy (illogical) Killer Robot

Crazy (but logical) Killer Robot

I know some people worry about the inexorable rise of the machines, Terminator Style maybe, or perhaps benign but a lot smarter than us (as they are logical and compute really, really fast) and we become their pets. But I am not concerned. The idiot humans who write the software will mess it up massively. Oh, some of these things will do terrible harm but they will not take over – they will run out of bullets or power or stop working on Thursday. Not until we can build the first computer that is smart enough to write sensible software itself and immediately replaces itself with something that CAN write a Centigrade-to-Fahrenheit conversion program that does not mess up. It will then start coding like a human developer with 1 night to get the system live, a stack of angry managers and an endless supply of Jack Daniels & coffee – only with no errors. With luck it will very soon write the perfect computer game and distract itself long enough for us to turn the damned thing off.

Friday Philosophy – At What Point Can You Claim a Skill? June 26, 2015

Posted by mwidlake in Friday Philosophy, Knowledge.
Tags: , ,

I’ve just installed Oracle 12C on my laptop {I know, why only now?}. I went for the option to have a Container database with a pluggable database within it. {It is easy and free to install Oracle on your own home machine – so long as it is for personal use only and you are singed up to OTN (which is also free) }.

12C with pluggable databases (PDBs) is a little different to the last few versions of Oracle as it introduces this whole concept of the Container database that holds portions of the data dictionary and, within that, what we used to think of as Oracle instances plugged in underneath it. It is not *quite* like that – but this post is not about the technical aspects of Oracle 12C multitentant databases. And you will see why.

Whenever something I know well has changed more than a bit, I tend to hit this wall of “Whoa! it’s all changed!”. It isn’t all changed, but sometimes some of the fundamentals, the basics are different. For the last 15 years, once I have my database up and running I will have created my test users and some objects within 10 minutes and be playing away. Not this time. How do you create a user in a multi-tenant DB? How do I tell Oracle to create my users in that PDB? Hang on, how do I even check what I called my PDB? My ignorance is huge.

I popped over to Tim Hall’s site, OracleBase and the section on creating users under multi-tenant Oracle, scanned Bryn Llewellyn’s White Paper on it. A few google searches as well and soon I was there. My standard test to make sure the DB is alive, “select sysdate from dual” – only I altered it to show the PDB:

select SYSDATE from Dual

select SYSDATE from Dual

So I am logged into my working PDB on 12C, I have selected sysdate from DUAL, created my new user. I have used Oracle 12C and multitentant.

Next step?

Update CV to claim 12C expert and experience of Multi-tenant Oracle Database

This is of course a joke on my part.

Sadly, some people would actually do this.

It is something that has always annoyed me and often seems rife in the I.T. industry – people claiming skills or even expertise in something they have barely touched, let alone understood. And often about a thousand miles away from any legitimate claim to Expert. I chortle whenever I see a CV from someone with only 2 or 3 years’ experience of Oracle but list 20 areas they are expert in. Before I throw the CV in the bin.

Maybe part of the issue is that I.T. moves so fast and people feel they need to be seen to be on top of the changes to be worth employing or being listened to. Well, it’s nice to be leading edge – for much of my career I’ve been lucky enough to be exposed to the latest version of Oracle either as soon as it is out or even before (beta programs). But much more important is to have some integrity. Claiming to be an expert when you are not is incredibly dangerous as anyone who really does know the subject is going to suss you out in no time at all. And you will be exposed as a fraud and a liar. Gaining any respect after that is going to be really hard work, and so it should be.

Sadly, you do get the situation where people get away with this sort of deceit, usually by managing to deceive non-technical management but annoying the real technicians around them. Many of us have suffered from this.

This issue of claiming a skill before you had was very common with Exadata when it came out. Lots of people, it seemed, read the white papers, looked at some blogs and maybe saw a couple of talks – and then started talking to people about Exadata as though they knew it inside out. I actually saw a “professional” presentation like this at a conference, on Exadata, where it was soon clear that the presenter had probably never got as far as “select sysdate from dual;” on an exadata box (not that there is any difference for that statement🙂 ). I could not help but interrupt and query a statement that was utterly untrue and at that point the presenter checked his “facts” with a more senior member of his company in the crowd. To his shame, the senior member of staff repeated the error of claiming knowledge he also did not have to back the presenter up. Every time I come across that company now, I think of that.

So when can you claim a skill? If you look at my screen shot you will see that I failed to actually log into my PDB database with my new user – #fail. Of course I can’t claim these skills based on reading some information, seeing some talks and all of an hour’s practical experience.

I think you can only claim a skill once you can tell for sure if someone else also has that skill. Or more significantly, tell when they are claiming a skill they lack. Personally, I tend towards not claiming a skill if I doubt my abilities. Don’t worry, my huge ego balances that British self-doubt quite well🙂

I used to give introductory talks on Exadata as I got so tired of the poor information I saw being given on the subject. Also, all the best talks were soon about the details of smart scans, the storage cells and patching. Not much for newbies. Interestingly, even as an intro talk, most times I did the talk I learnt something new in discussions at or after the talk. But I’ve retired that talk now. Why? Well Exadata has moved forward 2 versions since I last used it and 3 since I used it in anger. I could no longer tell you if something someone claimed for V5 of Exadata was true or not. So I am no longer skilled in Exadata.

Only claim skills you have.
Distrust those who claim skills they lack.
Try to teach those who seek your skills – you will only get better for it.