Oracle Create Schema – multiple DDL statements in a single transaction

I was going to begin with some extended Brexit metaphor to illustrate the chaos and confusion that can ensue when you first encounter Oracle’s CREATE SCHEMA command.
Fortunately, the Dutch Government saved me the trouble :

Much as I’d like to believe that the Cookie Monster has finally overcome his Sesame Street type casting, I can’t help noticing that the Brexit Monster never seems to in the same room as this guy…

In Oracle, the term “schema” is used interchangeably with the term “user”. Creating a user in Oracle automatically creates a schema of the same name belonging to that user.
The process is so seamless that it’s almost impossible to tell where one begins and the other ends.
You may therefore be somewhat confused the first time you encounter Oracle’s CREATE SCHEMA command…

Advertisements

Using a Proxy User for Code Deployment in Oracle

“Proxy Users !” exclaimed Debbie.
“I say, that’s rather harsh don’t you think ?” came the rather startled reply from her boss.
Debbie sighed. They were in the midst of a discussion on the subject of how best to deploy database changes to multiple schemas.
“I meant”, she replied with iron patience, “that we could set up a proxy user to connect as each application owner in turn. That way, we wouldn’t have to grant those CREATE ANY privileges that get auditors so worried”.
“Oh, I see”, said Mike, who didn’t.
Not for the first time, Debbie wondered whether she had been lumbered with a less competent man as her boss simply in order to imbue this post with a semblance of social realism.
“I think”, she said, “that it’s time to move on to the techie bit.”
Debbie is right, as usual…

In order to make a change in Oracle ( or any database for that matter), you need at some point to connect to the database and run some SQL.
This is relatively straightforward if you are using the schema that is – or will be – the owner of the objects you are creating or changing.

However, this may not be possible if the account is identified externally or – in more recent releases – it’s a schema only account.
So, what is the best way to setup and use an account to make such changes in other schemas ?

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…

New Dog, Old Tricks – how to save yourself some typing with sed

We have a new addition to our household –

Teddy

Cute and fluffy he may be, but he’s got to earn his keep. He can start making himself useful by helping me with this post.

It begins one Friday afternoon when an urgent request lands on my desk with a large splat.

The requirement is that some csv files be uploaded into the Oracle 11g Datbasae serving the UAT environment to facilitate some testing.
There are around 20 files, each with a slightly different set of attributes.
The files are currently sitting on the on the Red Hat Linux Server hosting the database.
I have sufficient OS permissions on the server to move them to a directory that has a corresponding database object in the UAT instance.
Nevertheless, the thought of having to knock out 20-odd external tables to read these files might leave me feeling a bit like this…

Fortunately, a certain Lee E. McMahon had the foresight to predict the potential risk to my weekend and wrote the Stream Editor (sed) program…

Read Only Access for providing backend support for an Oracle Application

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…

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