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

    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…
    Continue reading

    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

    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 :

    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…

    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

    Changing the Hostname and IP Address on a CentOS server and re-configuring Oracle and ORDS

    I use VirtualBox quite a lot. Rather than going through the rigmarole of installing the software I need every time I want a new environment, I simply clone the VM I’ve already installed everything on.
    One drawback with this approach is that, because I’ve already configured the network settings on this baseline VM, I can’t run two clones concurrently as they both have the same hostname and IP address.

    What I’ll be covering here is :

    The approach I’ve taken is to execute each step on the command line without the need for any interactive input. Therefore, it’s possible to take the steps described here as building blocks for a bash script (or scripts) to accomplish these tasks.
    The exception is where I edit the contents of files. If you wanted to automate this, you can use something like…

    sed -i s/192.168.56.220/192.168.56.225/g file_to_edit
    

    …for the IP address and…

    sed -i s/frea./rincewind./g file_to_edit
    

    …for the hostname where file_to_edit is the file you want to change.

    If you’ve found your way here in search of simply changing the hostname and/or the IP address on a CentOS7 server, then you can just skip all the database related stuff and start right here.

    By the way, I’ve decided upon a new naming convention for my servers which makes use of Discworld characters. There may be the odd reference to this in what follows…

    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