Fantasy Software Development Sprint 1- Coding like it’s 1889 !

Now we have our backlog, it’s time to finish our setup and then get on and knock out some code…or maybe not.
Remember, we’re using APEX as part of our technical solution and we’re going to let it do some of the heavy lifting, at least to start with.

What we’re going to cover here is :

  • The stories selected for the Sprint
  • APEX attribute setting required to insert records into a table containing an identity column
  • What our Physical data model looks like at the end of the Sprint

But first…

Why Fantasy Software Development – A quick recap

The reason for this series of posts is to explore how both the methodology used for development and later enhancements to the application affect the finished product.
The requirements for this initial version of the application cover Football only up to the end of the 1888/89 season. For the purposes of this exercise, we’re trying – for the most part – not to anticipate developments after that date. So, whilst we may reasonably take steps to future-proof our application, we’re not going to indulge in clairvoyance by predicting the advent of VAR, for example.


Continue reading

Fantasy Software Development – exploring Brownfield development with Oracle RAD

I just can’t get the hang of Fantasy Football.
I’ll do all the right things, like swapping out striker A because of that tough away game coming up and bring in striker B who is bang in form and playing against a team struggling at the foot of the table.
Then I’ll look on helplessly as striker A bangs in a hat-trick whilst striker B scores an own-goal and manages to get sent-off.
Therefore, I’ve decided to stop worrying about how my pretend football team is getting on and do something a bit more useful, like undertaking a pretend development project. Yep, welcome to Fantasy Software Development.
The primary reason for doing this is to explore how an application evolves over it’s lifetime.
A huge amount has been written about how to develop applications from scratch but not so much about how they grow once the initial release is in production and the enhancement requests start coming in.
In other words, how is it that an application which, if you were writing it from the ground up right now would look like this :

…actually looks like this :

This is quite a sprawling project and my intention is to tackle it through several posts. This one is simply to give and overview of where I might be headed.

Continue reading

Time flies when you’re having fun – The truth about Oracle Exception handlers (this week)

Oracle Twitter is a bit like Poker – you tend to take notice when Aces turn up.
I mention this because I’m indebted to Jacek Gebal, Sabine Heismath and Andy Sayer, whose discussion on this topic alerted me to the fact that PL/SQL exception handling had changed in a small but important way whilst I wasn’t looking.

Just in case you’re wondering whether that opening paragraph is a prelude to a post containing gratuitous references to a certain Motorhead song, all I can say is…if you like to gamble… Continue reading

Making the most of Oracle 18c XE – Pluggable Databases and the Oracle Eco-System

This was going to be a simple post about creating multiple Pluggable Databases (PDBs) in Oracle 18cXE.
But you know what it’s like, you get your lego database set out and then you spot those unusual shaped bricks… and the transparent ones… oh, that one has a wheel on…and get a bit carried away.
What follows is a guide on how to create three concurrent PDBs on an Oracle 18cXE database. However, I have taken the opportunity to investigate other areas of the Express Edition platform and so will be making use of Advanced Compression as well as Oracle Enterprise Manager Express.
In fact, I wouldn’t be surprised if SQLDeveloper put in an appearance as well.
I’ve also included a couple of the more notable members of Oracle’s small-but-perfectly-formed eco-system into the final design. On top of all that, I’ll be performing a magic trick to ensure that I only have to install each piece of software once, even though it may end up in multiple PDBs…
Continue reading

Running a shell script from inside an Oracle database using DBMS_SCHEDULER

As I write, various parts of the United Kingdom are still under variations of the theme of “lockdown”. Not that I’m stir crazy or anything but I’ve now decided it’s time to explore one way of escaping, from the confinements of my database, at least.

Specifically I’m going to :

  • create an OS user on the database server
  • create an OS group to allow both the new user and the oracle user to access to a common location
  • create a shell script owned by the OS user which writes to that common location
  • create a credential to access that user from inside the database itself
  • setup and run a scheduler job to execute a shell script as the new OS user
  • read the output file generated by the shell script from inside the database

For this exercise, I’m using my trusty 18cXE database running on CentOS… Continue reading

To Boldly gzip from PL/SQL

Lempel-Ziv. Sounds like something you might find on a starship. I mean, you can just imagine Scotty telling Kirk that they can’t go to warp because the Lempel-Ziv compressors are offline.
In fact, the Lempel-Ziv algorithm is what GNU’s Gzip program uses to compress files.
Fortunately, the UTL_COMPRESS package employs the same algorithm, which means that you can create archives using one tool that can be manipulated by the other…
Continue reading

Reading an OS file from inside an Oracle Database using an External Table

Two weeks into lockdown and some are looking to break the monotony by varying their diet…

For my part, I’ve decided to amuse myself by making a couple of “Top five” lists – the five best things about the lockdown and five things I’ve now finally got time to watch.

These lists are in text files are somewhere on my Database server and therefore provide the basis for the examples that follow.

When using Oracle, if you find yourself in a situation where you don’t have access to the Database Server file system, or would just rather use SQL to find what you’re looking for in a data set, you’re in luck.
The technique that used to be a neat way of viewing the database alert log from inside the database can now be re-purposed to look at any text file that’s located in a directory referenced by a Directory Object in the database.

What we’re going to look at here is :

  • Setting up an external table for reading from files
  • How to point the external table specific files and directories using EXTERNAL MODIFY
  • How achieve the same thing in 11gR2 databases with the help of DBMS_LOCK

First of all though, just in case you’re wondering….
Continue reading

Generating CSV files from PL/SQL the Very Easy Way.

This post is dedicated to Morten Braten and William Robertson.
Thanks to both of you for saving me a lot of time (and typing)…

XML, YAML, JSON. When it comes to transferring data between systems, they’ve all been flavour of the month at one time or another. However, good old CSV is still the format of choice when the batch window opens.
Not only is it simple, it adds comparatively little overhead to the size of the data file being generated.

OK – CSV used to mean “Comma-delimited” but these days it’s become synonymous with delimited data.
Whatever separator character you use, generating CSV is considerably easier these days…unless you’re using PL/SQL in a batch (as opposed to interactive) process.
That said, Connor MacDonald does have a clever way of utilising the functionality available in a command line tool such as SQL*Plus by means of a DBMS_SCHEDULER job.

If for some reason that won’t work for you, it looks like you’re going to have to hack out some custom PL/SQL to get the job done…or you could have a look at a couple of the options available in what other technologies would refer to as “the ecosystem”.
What I’m going to cover is :

  • The pain of hand-cranking delimited SQL
  • A solution offered in Morten Braten’s Alexandria PL/SQL Utility Library
  • An alternative solution made available by William Robertson
  • The final PL/SQL procedure
  • Continue reading