Oracle – for when it was like that when you got there

Main menu

Post navigation

Passing parameters in SQL*Plus – or What’s the opposite of a Chick Flick ?

Watching Iron Man 2 the other night, I was somewhat surprised by a brief appearance on screen of someone who looked remarkably like Larry Ellison.
No, he wasn’t the villain although – depending on what Oracle ends up doing to MySQL – he could be a candidate for the role in Iron Man 3.
As a result of riding this cinematic rollercoaster, I experienced two profound revalations.
The first is that, despite it’s poor relation status in the array of tools available to the Oracle developer, SQL*Plus can still be incredibly useful ( OK, I’ve always thought this – but I’m flashing my Poetic License here and no, it hasn’t expired yet).
The second is that the opposite of a Chick Flick must be a Bloke Buster. I know, I should really just stick to the programming stuff.
For those poor souls who seldom leave the safety of the IDE I should explain that SQL*Plus is Oracle’s command-line SQL environment.
Most of the time, it’s used for formatting output ( e.g. when generating slave SQL). However, it can also prove extremely useful when you want to pass parameters to scripts at runtime.

That’s better. At least we can run the script for different values without changing the script every time. However, it’s not exactly user-friendly. What happens if we want to run this test a few months later when we’ve forgotten all about it and need a handy hint to remind us of what value we need to enter :

Note here that we’ve also taken away that scruffy variable replacement text. This is done with the line set verify off.
Nice little prompt giving us a clue what to type. That’s much better.
Finally, say we wanted to script all our tests to run in one go without any of that command line interaction. Well, you can do that with SQL*Plus as well, simply by changing the test harness script to accept parameters by position :