Installing SQLDeveloper and SQLCL on CentOS

As is becoming usual in the UK, the nation has been left somewhat confused in the aftermath of yet another “epoch-defining” vote.
In this case, we’ve just had a General Election campaign in which Brexit – Britain’s Exit from the EU – played a vanishingly small part. However, the result is now being interpreted as a judgement on the sort of Brexit that is demanded by the Great British Public.
It doesn’t help that, beyond prefixing the word “Brexit” with an adjective, there’s not much detail on the options that each term represents.
Up until now, we’ve had “Soft Brexit” and “Hard Brexit”, which could describe the future relationship with the EU but equally could be how you prefer your pillows.
Suddenly we’re getting Open Brexit and even Red-White-and-Blue Brexit.
It looks like the latest craze sweeping the nation is Brexit Bingo.
This involves drawing up a list of adjectives and ticking them off as they get used as a prefix for the word “Brexit”.
As an example, we could use the names of the Seven Dwarfs. After all, no-one wants a Dopey Brexit, ideally we’d like a Happy Brexit but realistically, we’re likely to end up with a Grumpy Brexit.

To take my mind off all of this wacky word-play, I’ve been playing around with CentOS again. What I’m going to cover here is how to install Oracle’s database development tools and persuade them to talk to a locally installed Express Edition database.

Specifically, I’ll be looking at :

  • Installing the appropriate Java Developer Kit (JDK)
  • Installing and configuring SQLDeveloper
  • Installing SQLCL

Sound like a Chocolate Brexit with sprinkles ? OK then… Continue reading

Dude, Where’s My File ? Finding External Table Files in the midst of (another) General Election

It’s early summer in the UK, which means it must be time for an epoch defining vote of some kind. No, I’m not talking about Britain’s Got Talent.
Having promised that there wouldn’t be another General Election until 2020, our political classes have now decided that they can’t go any longer without asking us what we think. Again.
Try as I might, it may not be possible to prevent the ear-worm phrases from the current campaign slipping into this post.
What I want to look at is how you can persuade Oracle to tell you the location on disk of any files associated with a given external table.
Specifically, I’ll be covering :

  • getting the name of the Database Server
  • finding the fully qualified path of the datafile the external table is pointing to
  • finding other files associated with the table, such as logfiles

In the course of this, we’ll be challenging the orthodoxy of Western Capitalism “If You Can Do It In SQL…” with the principle of DRY ( Don’t Repeat Yourself).
Hopefully I’ll be able to come up with a solution that is “Strong and Stable” and yet at the same time “Works For The Many, Not the Few”…
Continue reading

Breaking the Rules – why sometimes it’s OK to have a standalone PL/SQL Function

It was late. We were snuggled up on the sofa, watching a Romcom and debating whether to go to bed or see it through to the bitter( well, sickly sweet) end.

Wearily, I made the point that in the end the film would follow Heigl’s Iron Law of Romcom which can be summarised as “Katherine always gets her man”.

Deb begged to differ. Her argument was that, for every Colin Firth, riding into the sunset with his Bridget Jones, there’s a poor( largely blameless) Patrick Dempsey whose immediate future includes long-evenings alone in front of the telly and shopping for microwave meals for one.
The point is that even the most rigid rules tend to have their exceptions.

The star of this post is the oft-quoted rule that PL/SQL program units should always be incorporated into a Package.
There are special cameo appearances by “Never use Public Synonyms” and the ever popular “Never grant privileges to Public”.

Continue reading

Automated Testing Frameworks and General Rule-Breaking in PL/SQL

If there’s one thing that 2016 has taught us is that rules (and in some cases, rulers) are made for breaking. Oh, and that it’s worth putting a fiver on when you see odds of 5000-1 on Leicester winning the League.

Having lacked the foresight to benefit from that last lesson, I’ve spent several months looking at Unit Testing frameworks for PL/SQL. In the course of this odyssey I’ve covered:

This post is a summary of what I’ve learned from this exercise, starting with the fact that many of the rules we follow about good programming practice are wrong…
Continue reading

Post-Truth PL/SQL

We’re living in a Post-truth age. I know this because I read it in my Fake News Feed.
Taking advantage of this, I’ve updated the definition of PL/SQL.
Up until now, it would be true to say that PL/SQL is a 3GL based on ADA that’s incorporated into the Oracle RDBMS.
Post truth, the definition is that PL/SQL is a 3GL that comes with it’s own built-in Oracle RDBMS.

By a stroke of good fortune, my son recently bought me a copy of Ghost in the Wires by Kevin Mitnick and William L Simon, which begins each chapter with an encrypted phrase.
If your anything like me, you’d spend a fair amount of time geeking over this sort of problem, most likely using some fashionable programming language to help solve the riddles with which you were presented.

In my house at least, PL/SQL is back in fashion…
Continue reading

Chasing your tail – with SQL*Plus and SQLcl

Do you remember the film Up where the dogs were always distracted as soon as anyone mentioned squirrels ?
Well, there I was, continuing my journey through the wonderful world of PL/SQL Unit Tests when suddenly, SQLcl !
Yes, Oracle have just released the first production version of SQLcl.
Since I first looked at an Early Adopter version of SQLcl there have been several enhancements. One of these, the REPEAT command, has the potential to implement functionality akin to the good old *nix tail -f command for Oracle Database tables.
It turns out that you may also be able to do something similar in SQL*Plus…
Continue reading