When dealing with dates in a program, I’ll tend to err on the side of caution and explicitly do the conversion from a varchar to a date, specifying the format so there’s no chance of anything unexpected happening if the program should run in a session with a different NLS_DATE_FORMAT from that I’m using.
However, if for example, I need to do a one-off data fix, sometimes, I just can’t be bothered with all that typing. Continue reading →
Sundays – a day of rest. Certainly true for me. Sunday morning is a time for lazing around leafing through the colour supplements and thinking about nothing in particular. Sunday 23rd October was a little bit different.
Wide-awake at 8 am ( I didn’t know that there was such a time as 8am on a Sunday), like several million others, I was wondering what would confront the All Blacks – the Gallic flair with which France had swept aside England or the Gallic shrug with which they had surrendered to Tonga ?
Look, I’m not really a New Zealander. Yes, I was born in Auckland but both my parents are English and I’ve lived most of my life in England. However, like anyone with a connection to the Land of the Long White Cloud, there is a part of my soul, however small, that takes the form of a Rugby ball.
At the end of the match, I was able to join my “fellow” Kiwis in, not so much paroxysms of joy as a huge collective sigh of relief.
On the whole though, I’d rather not have to see Sunday morning from that early on. So, if there is, for example, something that needs to run on my database on a Sunday morning, I’d rather the database just did it without my intervention.
What I plan to do here is :
set up a scheduler job
explore the ways in which we can control whether a class of job runs on a given database
stop jobs running on database startup
teach the scheduler how to tell the time – especially in terms of daylight saving
Whilst in Montreal recently, Deb and I made a pilgrimage to the Circuit Giles Villeneuve, home of the Canadian Grand Prix. When not in use, the track is open to the public. It’s divided into two lanes – one for people to walk and cycle down down, and a one for people to drive down.
You can just imagine flying round in an F1 car. You come out of the excruciatingly slow L’epingle hairpin and build up to top speed as you tear down the Casino Straight. Ahead lies the final chicane before the start/finish line. A tricky right left combination with the treacherous curb on the inside of the last turn ready to spit the unwary into the Wall of Champions on the opposite side of the track.
At over 300 kph you start to think about spotting your braking point. Suddenly, this comes into view….
What do you think this is, a race track ?
… and now you know what it’s like to be a programmer, who has channeled raw inspiration through his or her dancing fingers to produce a thing of beauty and elegance…only to run into the QA person pointing out that the commas are in the wrong place according to page 823, paragraph 2 sub-section e of The Coding Standards.
Often measured in weight rather than the number of pages, Coding Standards documents are often outdated, arbitrary and just plain wrong.
On the other hand, their absence can cause much heartache, not least to those poor souls in support who are trying to maintain code where the Agilista philosophy of Code over Documentation has been taken to the ultimate extreme.
What follows is an attempt to make sense of the Coding Standards conundrum.
I’ll look at what I think a Coding Standards document should contain, and what it shouldn’t.
Then I’ll give some suggestions as to standards for Oracle SQL and PL/SQL which you can either embrace or throw rocks at, depending on your preference.
Before all of that however, I feel the need for some serious catharsis… Continue reading →
I met up with an old friend recently. Deb and I were in Toronto, hometown of a certain Simon Jennings.
Apart from being a top bloke, Simon was also my first mentor in the mysterious ways of SQL, a sort of Obi-Wan Kenobi to my Anakin Skywalker, but without the light-sabers.
Ah those heady days when the world was young…and I first discovered ROWNUM.
That pseudo column was one I used often when investigating the structure and data within tables. It was also, the source of some confusion. Continue reading →
This week’s missive is coming to you from the netbook. Deb and I have pushed the boat out this year and we’re currently in Canada for our holiday.
This has nothing at all to do with Oracle External Tables, but does explain the flavour of the examples that follow. Continue reading →
The tension was palpable on the bridge of The Enterprise. The hulk of the giant Teredation Cruiser filled the View Screen.
With baited breath they awaited the response of the Teredation Captain to the message they had just transmitted.
Suddenly the image on the screen changed from that of the menacing warship to Simon, Lord High Hatter of the Teredations. In a voice which hinted at an unaccustomed uncertainty, he exclaimed,
“What is this PL/SQL of which you speak ?”
OK, so maybe it wasn’t the bridge of the Enterprise so much as in the beer garden at the Nut and Squirrel. The question, however, is pretty much accurate.
In an attempt to distract himself from the sad news that Claude Gnapka had finally left Luton for Walsall, Simon posed the following programming problem :
He needed a SQL query to return the first working day on or after the 23rd of the month, together with the first working day of the following month. He wanted both dates to be returned in the same row.
The catch ? Simon works on Teradata which, owing to a glitch in the Universal Translator ( or something), doesn’t have anything like PL/SQL or T-SQL built in. Continue reading →
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 →
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 →
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 →
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)