Putting VALIDATE_CONVERSION front and centre, or possibly center

I recently had an interesting conversation with Suzanne, a native of Pittsburgh PA, about how the Americans and British spell some words differently.
Adding some local colour ( color) to the discussion, Suzanne recounted the story of when the US Postal service, seeking to save money on printing, proposed removing “superfluous” characters from place names.
Well, the burghers of Pittsburgh weren’t having any of that thank-you-very-much and so the City’s name remains unabridged to this day. The denizens of Harrisburg weren’t so fortunate.
Whilst we may be separated by a common language, as programmers who work with Oracle, Suzanne and I do share the challenge of loading data into tables when the format of that data may not be entirely what we were expecting.
If you’re fortunate enough to inhabit the sunlit uplands of Oracle 12c R2 and beyond, we’re about to explore the shiny new VALIDATE_CONVERSION function which promises to make your life that little bit easier.
For those languishing on 11g, we’ll explore how we might implement a similar function in PL/SQL.
Continue reading

Oracle and Alternative Facts – generating test data with DBMS_RANDOM

There’s a bug in DBMS_RANDOM.VALUE. It consistently fails to return the correct set of lotter numbers.
On the plus side, it is surprisingly useful when you find yourself in a situation which is all too common for a Data Warehouse developer.
There’s a requirement for a new feed into your Warehouse from another system. You’ve managed to agree the file specification – what data will be included in the file, datatypes etc, but the developers working on the upstream system won’t be able to start providing test files for loading for weeks yet. Meanwhile, you need to start writing your code to ingest the feed.
Fortunately, you have all the tools available to :

  • Generate lots of rows of test data
  • Generate random values for strings, numbers and dates
  • Use a set of pre-defined values randomly in your data generation

First of all, let’s take a look at…
Continue reading

Customizing DML in an APEX Interactive Grid

It should have been quite a relaxing Cricket World Cup final. After all, it was England v New Zealand. I was guaranteed to be on the winning side.
After several hours of nerve-shredding tension had failed to separate the teams England were awarded the trophy on the basis of dumb luck hitting more boundaries. The result was born with stoicism by the Black Caps, whose philosophy would, in other countries, be known as “Elite Niceness”. By a cruel twist of fate, Ben Stokes – England’s star all-rounder and Man of the Match – was actually born in Christchurch.
Oracle APEX has it’s own star all-rounder in the shape of the Editable Interactive Grid ( see what I did there ?)
As well as presenting information in the same way as an Interactive Report, it allows users to perform DML operations on the records it displays – provided it’s based on a single table.
What we’re going to look at here is how to base an Interactive Grid (IG) on a Query rather than a table whilst retaining the ability to perform DML operations on the displayed records. To achieve this, we’ll be customizing the PL/SQL that is executed when a DML operation is invoked in the IG.
Continue reading

Speed Dating – NLS_DATE FORMAT in Oracle

When dealing with dates in a program, I’ll tend to err on the side of caution and explicitly do the conversion from a varchar to a date, specifying the format so there’s no chance of anything unexpected happening if the program should run in a session with a different NLS_DATE_FORMAT from that I’m using.
However, if for example, I need to do a one-off data fix, sometimes, I just can’t be bothered with all that typing.
Continue reading

ORA-1810 and Other Dating Disasters

During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).

The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query Continue reading