jump to navigation

Being part of the Oracle Scene… Quite Literally October 2, 2015

Posted by mwidlake in Oracle Scene, publications, User Groups.
Tags: ,
1 comment so far

I have some advice for you all.

Do not get drunk with people from Ireland who claim to be your friend and, if you do, agree to no suggestions they might make whilst you are drunk….

I am now deputy editor of “Oracle Scene”.


This is the magazine the UKOUG produce 3 times a year and you can click this link to see the latest issue. There are some very nice articles in there by the likes of That Jeff Smith (product manager of SQL Developer), Fanck Pachot, Philip Brown and prior (and up-coming) issues have contributions from the likes of Jonathan Lewis, Michael Abbey, Grant Ronald, Debra Lilley…

He sucked me into it by stages, a bit like anyone running an illegal street gang might. “Just keep a look out for us…. Just go in and nod when no-one is looking…. just hold this baseball bat for a bit…”. He got me drunk the first time and I agreed to attempt to write an article. He then got me drunk again and got me to agree to write a series of articles. I think I was actually sober when I agreed to review a few submissions. But I must have been very “happy” indeed when I said “Yes” to the being deputy editor…

Being serious, I’m actually very happy to help out though it’s one of those roles that is never going to garner me much notice in the community – but it is the sort of thing the community really needs. Just like people who volunteer to help organise user group meetings or chair sessions at a conference (more on that in a day or two) or help out with the administration of something behind the scenes. Those of us who present and blog and write get some reward in being noticed and recognized. But a lot of people do stuff with little or no recognition, doing it just to help the user community be a success. Maybe we should all keep an eye out for them and say thanks when we come across them.

What To Do at OOW15 (Social & Serious)? September 30, 2015

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

I’m going to OOW15 this year, my first Oracle Open World in 11 years I think. And despite the Prom Queen rejecting all my offerings, I will actually be sneaking in a presentation – which I am very happy about.

The European Oracle User Group (EOUG) get a few slots and two are being used on Sunday 25th, 13:30 – 15:15 for “More Than Another 12 on Oracle Database 12c” – 12 European experts all doing six minutes each on a couple of tid-bits on 12C, including Christian Antognini, Bjoern Rost, Brendan Tierney, Julain Dontcheff, Jonathan Lewis… plus Tim Hall and Maria Colgan if we can squeeze them in (thus “more then 12…”). It was a great success last year, so if you are going to OOW15 sign up to the session at this link to avoid disappointment. You can see more details by our organiser, Debra Lilley (thanks Debra), in her blog post about it.

So I know what I am doing for 6 minutes. What do I do for the rest of the time?

A main aim I’ll have is to try and meet up with loads of people I either only know via antisocial media or have not seen in years.

I am sure things have changed in over a decade and, I have to confess, I did not make the most of my last OOW experience. It was all rushed, I was pulled out there very last-minute (as part of being named Oracle Beta Tester of the Year by Oracle Magazine – get me :-) ) and they wanted me to be able to do some press stuff (it sounds grand – but there was not a lot of interest in me as I was utterly unknown, but I spent what seems like hours being available in case someone wanted to talk to me). To make it worse, I did not know many people out there who were not actually Oracle employees, and oracle employees found it hard to get into things as priority was given to attendees. I felt pretty alone amongst 45,000 people interested in the same Tech as me. I did not even realise I had to sign up for the top talks and by the time I knew, they were all full. I did stumble into some very good Other Talks by accident though.

So, what should I be doing? What great talks should I be signing up for and which fantastic social events should I be trying to get invited to/slotted into my agenda? I don’t even know if many events are by-invite-only…

After over a decade of doing other conferences (and helping organise a few!), I feel a bit like a conference newbie again…

All help for a lonely out-of-towner gratefully received!

Friday Philosophy – Antisocial Social Media and Sociopaths September 25, 2015

Posted by mwidlake in Friday Philosophy, humour, Twitter.
Tags: , , ,
1 comment so far

Twice, on consecutive days a week or so back, I realised that someone I knew on Twitter but did not know in real life… was someone I knew in real life. But I’d never made the connection! With one person it was not that their real name was missing from their twitter profile, it was just my brain had linked all I knew about them to their handle. In the other case, as far as I can see there is nothing to link their slightly obscure handle to their physical persona, despite the fact they are tweeting quite often about Oracle and also present at conferences. So that was simply not playing fair to hide their real identity in that way and I feel slightly aggrieved.

I’ve also had the experience of meeting someone in the flesh who treats me like an old friend, is being nice to me {possibly some sales-person-type I should back away from, I initially wonder?} but also knows a fair bit about me {Oh no! Creepy-stalker-type! Must-run must-run must-run} – before it dawns on me that this is actually Randolph Toddlepoddle who I have known online for 5 years, comments on my blog and I respond. But I’ve never met. And who is now wondering why I am being so unfriendly, am backing slowly away from them with a fixed grin on my face and scanning for exits. This has actually happened to me several times now. Thankfully only once with each person (I think – names & faces are not my forte}.

XKCD comic 741

I have a relationship with you lot? {shudder!}

The result is that I am sure that for some people I have two utterly separate relationships with them – the online one and the in-the-flesh one. (According to xkcd comic 741 I have a relationship with you as you read my blog. When can I meet the parents?).

Another aspect of social media I feel is a little tricky for me, personally, is keeping track of what people have said to me and things I’ve said I’ll do. I have a poor memory, I can barely remember conversations last week. With email I can file them away and find them later (mostly I just file them away and wonder why my email directory is so massive). But with Twitter and Facebook comments? OK, so you can search but it is slow and it is not great. Only today (as I type) I remember being given some advice by my friend Brendan about writing articles. I went and checked my email ( under “friends/Brendan” or maybe “ora600/articles”, I can’t remember). Nope. Could I have put it elsewhere in my email store of information and event? Nope, no where in my email I could find. Ahh, it was a twitter conversation. Damn. Now I need to step back and find it…

Maybe there is an app to tie all this stuff together for me but I would have to find it and learn it and the vendor will get bored or go bust in 2 years and I’ll lose the lot then. I’d rather mow the lawn.

Then there is the much-commented-on aspect of online comments where some people seem to sign up to a service or follow someone, just so they can be snide or criticise. No, this is not the usual rant about these phalluses (phalli?), It’s more that I don’t read user comments on the BBC web site much anymore as it lowers my already pretty sociopathic outlook on the human race (don’t get me wrong, many individual humans are wonderful animals – but as a pack they are a nasty and destructive species). It’s not that there are nasty or thoughtless people who put these comments up, we learnt there are people like that in the school playground (or even in the classroom – Mr Jenkins, you know who you are). It’s just that seeing what people can put on social media reminds me more about how dysfunctional people can be than meeting people in the flesh does. Being able to have some control over people you meet in the flesh means real people don’t tend to enhance my sociopathic tendencies as much as social media.

I follow a couple of “humorous” twitter accounts. They put the same stuff up all the time, sometimes it’s obviously fake and they “borrow” from each other like crazy. But it’s just a few tweets and if I find the repetitive nature of it or their take on humour gives me less amusement than annoyance, I can always do that “unfollow” thing. I am not in any way being forced to be exposed to it. I don’t have to start commenting all the time about “you got this from redit!!!” or “You spelt that wrong you moron” or “That’s not funny, I can tell it’s a paper bag on a baby”. I made the mistake a couple of weeks back of responding to one saying “Dude, thanks for pointing out the totally obvious, it had CLEAR passed me by”. Yeah, I told you I had sociopathic leanings – I went and did what they had done to annoy me, there was no need for me to read the comments if I knew it would annoy me.

Of course, I could just stop joining in; close my twitter account, delete the blog, remove my inconsequential presence on facebook. But then, I’m now in all these relationships (sometimes two or three times with the same person). How can I break up with so many people? :-)

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.


Get every new post delivered to your Inbox.

Join 206 other followers