DBMS_APPLICATION_INFO – Are we nearly there yet ?

Deb has come to the conclusion that, when on a long car journey, I’m not a great passenger.
“Are we nearly there yet ?” I enquire politely…usually around five minutes into a four-hour journey.
“No, not yet”, comes the patient reply.
“Are we almost nearly there yet ?”
“No, I’ll let you know when we are”, she responds with iron patience.
A few minutes pass…
“Are we almost nearly almost there ?”
At this point, I’m usually offered the option of walking the rest of the way.
It’s the same with long-running programs on the database. I want to know how far it’s gone and how far it’s got to go.
Oh, what Deb would do for an in-car equivalent of DBMS_APPLICATION_INFO.

Deb would probably have some sympathy with the DBA who gets a call from a user who has a process running and is, essentially, asking “are we nearly there yet ?”
Fortunately, with a bit of foresight and the judicious application of a little DBMS_APPLICATION_INFO magic, the answer to this question is right there in V$SESSION, or even V$SESSION_LONGOPS. Continue reading

The Oracle Database – Where’s the “Upgrade Now” button ?

Regular readers of this blog ( hello Mum) will have noticed that I’ve been a bit quiet lately.
Having tired of my life of leisure, I now have a proper job and have consequently been a little bit busy.

I have though, had time to reflect on the question of Oracle database upgrades and why they are never ever standard.

“We’ve decided to upgrade the database”. Those words are bound to strike equal amounts of anticipation and abject terror into the heart of any DBA…or at least, any DBA who has gone through an upgrade before.

To be fair, it’s not the upgrade itself that is going to cause the problem, it’s all of the misconceptions that come with it.

The CIO/IT Manager/Project Manager is almost bound at some point to come out with one of the following gems :

“We don’t need to test, the application code will still work in exactly the same way”
“We need to upgrade/move our servers/Application/Operating System anyway so we can do it all at the same time to minimize downtime.”
“According to the salesman Oracle can now make the tea, so it must be true.”

My personal favourite is from a person who shall remain nameless :

“We want to be fast-followers. X team is upgrading their database in July so we should be done by June”. Continue reading

PL/SQL Injection – The Doctor Will See You Now

Following on from my recent post about SQL Injection I had an excellent comment from Gary.

Whilst we’ve been able to establish that the PL/SQL solution we implemented does not suffer the same vulnerability to injection as the concatenated SQL statement, this does lead us to a further question – does using PL/SQL automatically render us immune from injection attacks ?

For the purposes of this post, I’m going to leave PHP to one-side and concentrate on the PL/SQL side of the matter. Continue reading

Oracle, bind variables and SQL Injection – Keeping out unwanted guests

My son, Michael ( yes, it is the only name I can spell), is currently following in his father’s footsteps and studying Computer Science.
As is only natural, he does occasionally have the urge to rebel against all that his parents hold dear. In his case he’s rejected the path of light and Linux and has become … a Microsoft Certified Professional. Oh the shame. Where did I go wrong ?
All of which links, if somewhat tenuously, to the subject at hand. When he took his first steps into the world of programming, we had a look at PHP ( as part of a LAMP set-up, naturally).
In one of the introductory manuals, we came across an example of how to authenticate web users against a database.
The author was clearly trying to introduce various language concepts and would certainly not claim that his example was intended for production use. However, with a bit of tweaking for use against an Oracle database, it does offer a very clear illustration one area of the potential vulnerabilities of web applications to SQL Injection attacks. It also offers the opportunity to illustrate a major benefit of using bind variables in queries against Oracle – i.e. protection against SQL Injection.

I know that a fair few people who stumble across this site are new to Oracle and want to play around with Oracle XE. These people are also usually pretty experienced in other technologies (hi Wayne, hope you’re still enjoying all that sunshine).

So, the purpose of this post is to :

  • Illustrate the way in-line SQL statements can be injected
  • Show how this can be countered in an Oracle database by use of bind variables
  • Have a look at letting Oracle handle user authentication
  • Celebrate the visionary genius of Messrs Young, Young and Johnson. “For Those About to Rock” was not merely an album of raucous Blues-based Heavy Metal, but a prophecy about the potential pitfalls of Web Application development.
  • Oh, and give you the chance to laugh at my PHP prowess ( or lack thereof)

Continue reading

Database Design – Denormalization, Codd and the Copa America

Deb’s quite keen on the story of Goldilocks and the Three Bears ( although my version does end up with Goldilocks being charged with breaking and entering and criminal damage). How is this fairy tail linked to Database design ? Well, a good database should ideally be to Goldilock’s taste – not too fre-form and ad-hoc, not too rigid Third Normal Form, but just right.
To demonstrate this, we’re going to do a quick tour through the first three normal forms, give an airing to an ancient Geek joke, and relive past footballing glory. At this point Scottish readers will be relieved that I plan to demonstrate the concept of denormalization without referring to 1966. Instead, I will take as my example, the apogee of Columbian Footballing achievemnt, the 2001 Copa America. Continue reading

Custom Reports in PL/SQL Developer

Having played around with PL/SQL Developer, one of the frustrations I’ve found is that the extensibility of the tool relies on you being able to create dlls.
Not being a Microsofty, I’ve found it a bit limiting when compared with SQLDeveloper, which allows some fairly significant add-ons by the simple application of a bit of XML.

Don’t get me wrong, I quite like PL/SQL Developer, not least because it allows you to run SQL*Plus scripts pretty much unedited.

Now, I wanted a way of displaying the information held in my CRUD application without having to go to the trouble of typing the statement in each time.
The answer to my problem – the PL/SQL Developer custom report.

Creating the Report

In PL/SQL Developer, go to File / New/ Report Window.
This is where you type in your SQL statement.

As with SQL*Plus, any runtime parameters are prefixed by an ‘&’.
The way you name and define these parameters is a little different however.
In my case, I want to give my variables a name that will show up when the user is prompted to enter them at runtime. Both of the variables are mandatory, and both should be converted to uppercase.

The end result is a query that looks like this :

SELECT object_owner, object_name, object_type,
       create_flag, read_flag, update_flag, delete_flag
FROM crud_owner.db_crud
WHERE table_owner = '&<name="Table Owner" required="yes" uppercase="yes">'
AND table_name = '&<name="Table Name" required="yes" uppercase="yes">'
ORDER BY 1,2,3

There are a (bewildering) number of configuration options for the report, but I’m quite happy with the default output so I simply have to save the report in a file with a .rep extension.

Adding the Report to the Menu

Back at the main menu, select Tools / Configure Reports…
In the Configure Reports dialog box, click on the yellow folder icon and navigate to where you saved your .rep file.
Make sure that the Report as main menu item checkbox is checked then click OK.

When you next open the Reports menu, you should see your new report at the bottom of the list.

I don’t think I’ll ever learn to love PL/SQL Developer ( or any other Oracle IDE for that matter), but at least this sort of thing makes life a little more bearable.

Top-N Queries in Oracle – SQL at the sharp-end of the season

It’s coming up to that time of year once more. Games are running out, permutations are being permutated, bottoms are beginning to squeak.

The question on everyone’s lips ( well, Simon’s actually) is, who will fill the Play-Off places in the Conference ?

For those of you not acquainted with the lower echelons of English Professional Football ( or Soccer, if you really must), The Conference is the fifth tier of English football and the pinnacle of non-League. Each year, the winners gain automatic promotion to the promised land of the Football League with those finishing second to fifth playing-off for the right to join them.

All of which gives me the perfect opportunity to forsake my old habits and bring my SQL bang up-to-date. Continue reading

ANSI SQL Huh, What is is good for ? Er, Outer Joins Actually

When Oracle first introduced ANSI syntax to become ANSI SQL 92 compliant, the general idea was that the traditional Oracle syntax would do exactly the same as this new fangled ANSI stuff.
You wouldn’t have to start coding LEFT INNER JOINs everywhere and you could pretty much go on your way unmolested by so much syntactic furniture.

This was the approach I’d followed quite happily for many years. Sure, I had moved away from DECODE toward CASE as I think the code tends to be more easy to follow, But all that extra typing to code an ANSI Join – a waste of valuable typing molecules….or so I thought.

Recently, I came across a situation where I needed to outer join a table to two other tables.
Now, in honour of the man who brought this to my attention – a Business Analyst…and an Irishman…on the day that Ireland beat England, yes, at cricket…(thanks Gavan)… Continue reading

Nesting Quotes in SQL

Recently, I came across one of those situations where you need to get some data from a Production instance and transform it in some way. The catch is that you only have read access on the database in question. All of the usual methods of extracting data are blocked by either an extremely long and tedious battle to get the required access or are prohibited by various security policies.
I will need to do this quite regularly, so it looks like I’ll have to write some slave SQL to extract the data and then load it into a non-production database, where I have more privileges.
Once again, I’m going to have to struggle to remember the new Oracle Q quote syntax.

But before all that, a brief history of quotes.

In the beginning, there was the escape character. In SQL*Plus, this happens to be a single quote … Continue reading

ORA-1810 and Other Dating Disasters

During a recent difference of opinion, my girlfriend said to me “Ooohhh, you’re so logical!” The atmosphere was not improved by the fact that she had to then explain to me that this wasn’t a compliment.
All of which has nothing to do with the purpose of this post (although it may help to solve the mystery as to why I was single for so long).

The other day, someone asked me over to have a look at why they were getting an error when running a particular query. Now, as quizzes seem to be en vogue in the Oracle world at the moment ( have a look at that nice Mr Feuerstein’s site ), here’s a quick one for you. See if you can spot what’s wrong with this query Continue reading