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…

Easy Listening with Datapump in the SQLDeveloper DBA Module

There are a number of ways to transfer data between Oracle Databases, one of which is to use the PL/SQL Datapump API – DBMS_DATAPUMP.
If you wish to avail yourself of this utility but find the syntax a bit fiddly, you always have the option of getting SQLDeveloper to do (most of) it for you.
What we’re talking about here is how to persuade the SQLDeveloper DB module to :

• Create and execute a custom Datapump export job
• do most of the work creating an import of a subset of the exported data

…

Debbie Saves Christmas – Database Development in a Devops Wonderland : Re-runnable DDL

Debbie felt a shiver run down her spine. To be fair, that wasn’t much of a surprise since Lapland at this time of the year does tend to be a little chilly.
However, it wasn’t the weather that was the cause of her discomfort. Someone high up in the IT Department of her employer, The National Elf ( aka Santa’s Grotto) had decided that Continuous Integration was the way to go and had decreed that it should be used forthwith across all projects and technologies in the Company.
This included the application that Debbie was responsible for.
Written around 15 years ago, this Stock Control Application had already survived one major database upgrade but was now resolutely “stuck” on Oracle 11g.
The thing about so many modern software development techniques is that they were based on the premise that code was file based. Of course, this was also true ( or at least, true enough) for some database objects, but tables were a little different.
You couldn’t simply “replace” a table like you could any other program as doing so would destroy any data in that table. For this reason, any changes required to tables for a mature application such as this would be applied by means of DDL ALTER statements.
Of course, there are tools around for this sort of thing. Liquibase, FlexDeploy – these were just two of the tools that Debbie had no chance of getting approval to use in the face of a bureaucracy that made the Vogon Civil Service look like it was following Extreme Programming.
If she was going to get her changes through by her Christmas Eve deadline, she would have to get creative…

utPLSQL 3.0 – How to have your cake and eat it

“You can’t have your cake and eat it !” This seems to be a regular refrain from the EU in the ongoing Brexit negotiations.
They also seem to be a bit intolerant of “cherry picking”.
I’ve never really understood the saying, “You can’t have your cake and eat it”.
What’s the point in having the cake unless you are going to eat it ?
Fortunately, I’m not alone in my perplexity – just ask any Brexiteer member of the British Cabinet.
For those who want to make sense of it ( the saying, not Brexit), there is a handy Wikepedia page that explains all.

When it comes to Unit Testing frameworks for PL/SQL, compromise between cake ownership and consumption is usually required.
Both utPLSQL 2.0 and ruby-plsql-spec have their good points, as well as some shortcomings.
Of course, if you want a more declarative approach to writing Unit Tests, you can always use TOAD or SQLDeveloper’s built-in tools.

Recently, a new player has arrived on the PL/SQL testing scene.
Despite it’s name, utPLSQL 3.0 appears to be less an evolution of utPLSQL 2.0 as a new framework all of it’s own.
What I’m going to do here, is put utPLSQL 3.0 through it’s paces and see how it measures up to the other solutions I’ve looked at previously.
Be warned, there may be crumbs…

ORA-06592 and the Case of the Happy Australians

Another Ashes Tour to Australia has come and gone and the home team once again hold The Urn.
For any non-cricket fans, I should probably explain.
Every four years, England sends their Men’s and Women’s Cricket Teams to Australia on a goodwill mission.
The object of the exercise is to make Australians feel good about their country as their teams inevitably triumph.

These recently concluded contests provide the theme for the illustration of the less-than-straightforward circumstance surrounding the ORA-06592 error which follows.
When encountering this error, you’ll probably see something like

ORA-06592: CASE not found while executing CASE statement

06592. 00000 -  "CASE not found while executing CASE statement"
*Cause:    A CASE statement must either list all possible cases or have an
           else clause.
*Action:   Add all missing cases or an else clause.

Despite this apparently definitive advice, you don’t always need to cover any possible case, or include an ELSE clause…

Private Functions and ACCESSIBLE BY Packages in 12c

My recent post about PLS-00231 prompted an entirely reasonable question from Andrew :

“OK so the obvious question why [can’t you reference a private function in SQL] and doesn’t that defeat the objective of having it as a private function, and if so what about other ways of achieving the same goal ?”

I’ll be honest – that particular post was really just a note to self. I tend to write package members as public initially so that I can test them by calling them directly.
Once I’ve finished coding the package, I’ll then go through and make all of the helper package members private. My note was simply to remind myself that the PLS-00231 error when compiling a package usually means that I’ve referenced a function in a SQL statement and then made it private.

So, we know that a PL/SQL function can only be called in a SQL statement if it’s a schema level object or it’s definied in the package header because that’s the definition of a Public function in PL/SQL. Or at least it was…

In formulating an answer to Andrew’s question, it became apparent that the nature of Private functions have evolved a bit in 12c.

So, what I’m going to look at here is :

• What are Private and Public package members in PL/SQL and why you might want to keep a package member private
• How 12c language features change our definition of private and public in terms of PL/SQL objects
• Hopefully provide some up-to-date answers for Andrew