jump to navigation

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

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

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:

ora122> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYYAD';
Session altered.

-- select today
ora122> select sysdate from dual;

SYSDATE
-------------
29-JUL-2015AD

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

SYSDATE-24572
-------------
01-JAN-4712BC

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

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

ora122> @std1

31-DEC-4713BC

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…:

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

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.

eg:
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;

  type NUMBER is NUMBER_BASE;
  subtype FLOAT is NUMBER; -- NUMBER(126)
  subtype REAL is FLOAT; -- FLOAT(63)
  subtype "DOUBLE PRECISION" is FLOAT;
  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

  type VARCHAR2 is NEW CHAR_BASE;

  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;
  begin
    d := pessdt;
    return d;
  exception
    when ICD_UNABLE_TO_COMPUTE then
      select sysdate into d from sys.dual;
      return d;
  end;
--
--
-- 
  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;
  BEGIN
    t := pessts;
    RETURN t;
  EXCEPTION
    WHEN ICD_UNABLE_TO_COMPUTE THEN
      SELECT systimestamp INTO t FROM sys.dual;
      RETURN t;
  END;

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

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

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: , ,
2 comments

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: , ,
4 comments

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.