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
There are times when I feel like Baldrick.
One moment I’m all boundless optimism and cunning plans and the next, I’m so confused I don’t know what my name is or where I live.
One such recent bout of existential uncertainty was caused by the error mentioned in the title of this post, or to give it it’s full name :
PLS-00231 : Function <function name> may not be used in SQL
It was late. In a corner of the Treasury however, a light burned brightly.
Phillip Hammond, Chancellor of the Exchequer, was still working.
“Spreadsheet Phil” was trying to formulate a plan to rectify the nation’s finances in the event that the Prime Minister was won over by the ebullient Mr Johnson and decided upon a “No Deal” Brexit.
Boris Johnson was currently based at the F.O. which, Phil considered darkly, was somewhat appropriate.
If only his predecessor had had the foresight to stick a couple of billion on Liecester City to win the league at 5000 to 1, The Country’s finances would be in much better shape.
In the midst of this gloomy reflection, he was struck by a sudden inspiration. The crowded half-hour that followed resulted in – what else – a spreadsheet.
The information therein could save The Economy. “Hmmm”, thought Phil, “this stuff is far too important to leave in Excel, I really need to put it into my Oracle database. I wonder if SQLDeveloper could help”… Continue reading →
Events have taken a worrying turn recently. I’m not talking about Kim Jong Un’s expensive new hobby, although, if his parents had bought him that kite when he was seven…
I’m not talking about the UK’s chief Brexit negotiator David Davies quoting directly from the Agile Manifesto and claiming that the British were “putting people before process” in the negotiations, although Agile as a negotiating strategy is rather…untested.
I’m not even talking about the sunny Bank Holiday Monday we had in England recently even though this may be a sign of Global Warming ( or possibly a portent for the end of days).
The fact is, we have an Ashes series coming up this winter and England still haven’t managed to find a top order that doesn’t collapse like a cheap deckchair in a light breeze.
On top of that, what started out as a relatively simple post – effectively a note to myself about using the row_number analytical function to overcome a recent performance glitch in a Data Warehouse Application – also seems to have developed an unexpected complication…
International relations seem to be somewhat tense at the moment with various World Leaders being publicly grumpy with each other.
To keep my mind off damoclesian digits dangling dangerously over Big Red Shiny Buttons, I really just want to hear some nice news to help me think happy thoughts.
I’d like to read about Luton Town because they’re doing quite well, or the England Cricket team winning a Test Series. I might even treat myself to a random Cat Video…
We had a few days of warm, sunny weather in Milton Keynes recently and this induced Deb and I to purchase a Garden Umberella to provide some shade.
After a lifetime of Great British Summers we should have known better. The sun hasn’t been seen since.
As for the umbrella ? Well that does still serve a purpose – it keeps the rain off.
Rather like an umbrella Oracle’s Edition Based Redefinition feature can be utilized for purposes other than those for which it was designed.
Introducted in Oracle Database 11gR2, Edition Based Redefinition (EBR to it’s friends) is a mechanism for facilitating zero-downtime releases of application code.
It achieves this by separating the deployment of code to the database and that code being made visible in the application.
To fully retro-fit EBR to an application, you would need to create special views – Editioning Views – for each application table and then ensure that any application code referenced those views and not the underlying tables.
Even if you do have a full automated test suite to perform your regression tests, this is likely to be a major undertaking.
The other aspect of EBR, one which is of interest here, is the way it allows you to have multiple versions of the same stored program unit in the database concurrently.
Generally speaking, as a database application matures, the changes made to it tend to be in the code rather more than in the table structure.
So, rather than diving feet-first into a full EBR deployment, what I’m going to look at here is how we could use EBR to:
decouple the deployment and release of stored program units
speed up the process of rolling back the release of multiple stored program unit changes
create a simple mechanism to roll back individual stored program unit changes
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)