The World Cup is finally over and “It’s Coming Home !”
For quite a long time, we English laboured under the illusion that “it” was football.
Fortunately for Scots everywhere, “It” turned out to be the World Cup which, like so many international sporting competitions, was conceived in France.
Another area that is often subject to flawed assumptions is what privileges are required to provide read-only access for someone to provide support to an Oracle Application.
So, for any passing auditors who may be wondering why “read only” access to an Oracle application sometimes means Write, or even Execute on certain objects…
It’s true, Oracle are giving away free stuff. “Oracle ?”, I hear you say, “as in Larry’s Database Emporium and Cloud base ?” The very same.
It’s been going on for quite a while and includes relatively hidden gems such as SQLDeveloper Data Modeler.
There is some confusion around this particular tool for a couple of reasons.
When it was first released (sometime around 2009 as I recall), Data Modeler was an additional cost option. However, that didn’t last long.
At present (and for a number of years now), it is available either as a completely standalone tool, or as a fully integrated component of the SQLDeveloper IDE.
Either way, it costs exactly the same as the SQLDeveloper IDE – i.e. nothing.
I can tell you like the price, want to take it for a spin ?
I’m going to focus here on using the integrated version of Data Modeler. This is because
I want to use it for small-scale modelling of the type you might expect to find when using an Agile Methodology
I’m a developer and don’t want to leave the comfort of my IDE if I don’t need to
What I’m going to cover is :
Viewing a Table Relationship Diagram (TRD) for an existing database table
Creating a Logical Data Model and Entity Relationship Diagram (ERD)
Generating a physical model from a logical model
Generating DDL from a Physical Model (including some scripting tweaks to suit your needs)
Using a Reporting Schema and pre-canned SQLDeveloper Reports to explore your models
This post is about introducing the features of Data Modeler in the hope that you may find them useful.
It’s not intended as a paragon of data modelling virtue.
Come to that, it’s not intended as a definitive guide on how to use this tool. I’m no expert with Data Modeler (as you are about to find out). Fortunately, there are people out there who are.
If, after reading this, you want to explore further, then you could do worse than checking out words of Data Modeler wisdom from :
“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…
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
*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… Continue reading →
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 →