APEX and Privileges Granted through Roles

The mystery has finally been solved. England’s surrendering of the Ashes last winter was nothing to do with Australia being a much better cricket team. Thanks to Kevin Pietersen’s recently published Autobiography, we now know that the problem was that there were rather too many silly points in the England dressing room.
Moving swiftly on from that weak pun, the subject at hand can also be rather mystifying at first glance.

In a “traditional” Oracle Forms application, you would have one database user per application users.
Connections via the Application to the database would be done as the individual users.
It’s quite likely that database roles would be used to grant the appropriate privileges.

For applications using other web technologies, the application may interact with the database via a single account, often that of the Application Owner. Whether or not this is a good idea is probably a discussion for another time.

For now though, the question we’re asking is, how an APEX application connect to the database ?
On the face of it, it would seem that it’s pretty similar to the second of the two approaches above. APEX connects as the Parsing Schema (usually the application owner).
As Kevin will tell you, appearances can be deceiving…
Continue reading

Going dotty – Generating a Filename containing a parameter value in SQL*Plus

As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.

I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.

I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value. Continue reading

Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…
Continue reading

ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.
Continue reading

Oracle’s hidden documentation – Commenting the Data Model in Oracle

The Football (or Soccer, if you prefer) World Cup is almost upon us.
England have absolutely no chance so even the false hope that traditionally accompanies major tournaments won’t be around to spoil the enjoyment.
What makes this World Cup extra special is the fact that it’s taking place in Brazil – the spiritual home of the Beautiful Game.
The only previous occasion that Brazil hosted the tournament was in 1950, and it’s worth a brief look at what went on then, if only to provide the basis of the examples that follow.
Back in 1950, as now, money was a bit scarce.
Brazil agreed to host the tournament on condition that the format would be designed to maiximize the number of games played and therefore the gate revenue generated.
It is for this reason that the 1950 tournament is unique in World Cup history as the only tournament to be decided, not by a final, but by a round-robin “Final Pool”.
Then, as now, England travelled to Brazil. Unlike now there was a fair degree of confidence, not to say arrogance, about the prospects of the national team showing these foreigners how the game should really be played.
The Empire may have been slipping away, but it was still a widely held belief – in England at least – that God was an Englishman.
In the event, England managed to lose to an amatuer USA team 1-0 and then get sent packing by Spain. Continue reading