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

Running a “background” job in PL/SQL

Teddy has observed the recent General Election campaign with some interest and has concluded that he has what it takes to be the next Prime Minister.

It’s not just the haircut, which does now look extremely Prime Ministerial…

Politics is a robust business but Teddy’s more than capable of playing “ruff” :

He firmly believes in the need to streamline Government at Cabinet level, which has the incumbent Chief Mouser to the Cabinet Office a little nervous.
He’s also well used to being followed around by a “pooper scooper”. And not to put too fine a point on it, there’s more than one reason that he’s known as a “shaggy” dog.

If he’s going to make it in politics, Teddy knows that he doesn’t have time to waste waiting for that pesky long-running job he’s just started. Oh no, he needs to use his evenings to get on with building his power base.
Fortunately, Oracle facilitates detached execution of PL/SQL blocks by means of the DBMS_SCHEDULER package. Now, I know what you’re thinking, that’s going to involve a lot of fiddly setup with schedules and windows and chains and stuff. Well, you may be pleasantly surprised…

Upcycling and old TV to a Smart TV with an Amazon FireTV Stick

Once upon a time, a TV was simply something you plugged into an arial socket and used to watch whatever broadcasters were prepared to show at any given time.
With the rise of streaming, TVs have now become “smart”. Well, smartish. I mean, some of them use really slick software and have lots of Apps that enable you to stream from a variety of sources.
Others are a bit less sophisticated and have limited access to such Apps.
Not only that, the world is now full of the old type of “dumb” but perfectly serviceable TVs.
So, if you want to :

• Revitalise an old non-smart TV
• Make a new not-so-smart TV smarter
• Place a TV somewhere that’s not within easy reach of an arial socket

…then help may be at hand. There are a number of devices you can plug into a TV to beef up it’s smartness.
As these devices are streaming content over the internet, you don’t need an arial to make use of them.
Furthermore, they are a fraction of the cost of buying a new Smart TV.

What follows is an account of how I’ve setup an Amazon Fire TV Stick and used it to access the wealth of streaming content that’s freely available in the UK, as well as some of the main subscription services…

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.

Configuring External Tools in SQLDeveloper

In order to distract myself from the lunacy that is the ongoing UK General Election campaign, I’ve been continuing to spend some quality time with the Swiss Army Knife of the Oracle world, SQLDeveloper.
I know, conventional wisdom says that when you have a hammer, everything is a nail.
On the other hand, conventional wisdom is rather less forthcoming if instead you have a multi-tool of Central European origin.
Fortunately, SQLDeveloper pays no heed to conventional wisdom but allows you to configure callouts to other useful software utilities by means of it’s External Tools functionality.
What we’re going to look at here is :

• Adding a call to an external tool in SQLDeveloper
• Using macros to control the behaviour of an External Tool
• Configuration settings for common tools in Windows
• Configuration settings for common tools in Ubuntu

Using Git in SQLDeveloper

As I write, South Africa have just emerged victorious from the 2019 version of the Rugby World Cup having vanquished England in the final.
This explains both the code used in the examples that follow and the fact that I’m writing this to escape from the “commiserations” pouring into my phone from my Welsh In-Laws. Tell me, what is the Welsh for Schadenfreude ?
Continuing my SQLDeveloper appreciation fest, I’m going to look at the latest version of SQLDeveloper’s (19.2) level of integration with Git.
Specifically, what I’ll be covering is :

• Using the SQLDeveloper Files Tree to work with a local Git Repository
• Creating a branch
• Staging changes
• Commiting changes
• Comparing different versions of a file within a branch
• Comparing branches
• Merging branches
• Deleting branches

We’re going to do all of this without leaving the comfort of our favourite Oracle IDE…

Making yourself at home in SQLDeveloper

I had to configure a new work laptop recently. This task was complicated somewhat by many of the usual issues us techies face when working for a large organization.
Admin privileges are out of the question. Pre-packaged applications are out-of-date and modern open-source development tools tend to fall foul of seemingly arbitrary security policies.
As a result, I’ve had to rely even more than usual on the Swiss Army Knife of the Oracle database.
Fortunately, there’s more to SQLDeveloper than getting stones out of horses hooves. So much so, in fact, that I’ve decided to arbitrarily declare that it’s now SQLDeveloper Appreciation Month. This is largely because, in exploring it’s capabilities, I’ve found myself with enough material to knock out a number of posts over the next few weeks.
It seems appropriate to begin this journey with a look at how you can make SQLDeveloper feel a bit more homely.
How can you encourage this most configurable of tools to do all the things you find so helpful whilst at the same time curbing some of it’s less endearing habits ?
Quite often, the answer can be found in the Preferences Tree.

About now, I’d normally give you a list of topics I’ll be covering. First of all though, for anyone who has found their way here in a desparate search for how to tweak something specific in this tool, here are links to the relevant sections of the post :

• Changing the font
• Change the colour scheme
• Show line number in Code Editors
• Set code formatter defaults
• Create a custom code template
• Set defaults for DDL comparisons
• Set defaults for the SQLDeveloper export utility
• Set the default delimiter for flat-file data imports
• Open Object Viewer in Read Only mode
• Tweak completion insight
• Add a file type
• Customise keyboard shortcuts
• Specify a startup script
• Make your data stripy
• Setting the date format

Note that the steps for navigating from the menu to the Preferences tree is included in each of these sections.
If you’ve the time and inclination to read the post from the top, I hope that you’ll forgive the repitition. Oh, and you’ll probably also still want to know what I’m going to cover :

• Customising the Look and Feel of the Code Editor
• Getting SQLDeveloper to Format your code just the way you like it
• Saving yourself lots of typing with a code template
• Customising SQLDeveloper Utilities defaults because life’s too short to be ticking boxes
• Miscellaneous tweaks to make you feel more at home
• If all else fails, ask Jeff Smith

One more thing before we get started. This post is intended to show you where these settings are. The values I set them to are according to my preferences which may not be shared by everyone (anyone ?) even though they’re absolutely the right way to do things because…well…they just are OK ?!

Right then, from the Tools menu select Preferences…