SQL*Plus and parameter handling
Posted by Ceri Davies Fri, 23 Sep 2005 12:01:00 GMT
In my new job, I’ve inherited the dubious pleasure of babysitting some Oracle 9i databases. Though I am somewhat familiar with Oracle, I really only know enough to know that I don’t know enough, so when I wanted to see where all the space was going, having already identified the tablespace that I was interested in, I issued the following, perhaps naive, statement:
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE tablespace_name = 'WIBBLE_DATA'
ORDER BY bytes;
That turned out to be a lot of typing for something that I wanted to do a lot, so I decided to throw it in a script, named segmentsbytablespace.sql (yes, that *is* quite a long file name – your point?). The next logical step was obviously to parameterise the tablespace name. I simply cannot believe the can of worms that this opened.
If you’ve run a script in SQL*Plus before, you’ll probably know that arguments to scripts can be accessed by the positional parameters &1, &2, …, &n. So the script just becomes:
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM dba_segments
WHERE tablespace_name = '&1'
ORDER BY bytes
/
and is run like this:
SQL> @segmentsbytablespace WIBBLE_DATA
That would be too easy; there are a couple of problems with this.
The first is simply that if you leave out the parameter, SQL*Plus prompts you for its value, like this:
SQL> @segmentsbytablespace
Enter value for 1:
Enter value for what now? I’d like it to say Enter value for tablespace. It looks like it is impossible for me to do that (unless I throw that variable at the database server and ask if it is NULL or not; I’m not doing that). This issue is, at least, cosmetic.
The second problem is that running the script with an argument defines the same argument for the next invocation, i.e. in this example:
SQL> @segmentsbytablespace WIBBLE_DATA
SQL> @segmentsbytablespace
both scripts believe that they have been passed WIBBLE_DATA as an argument. I have to explicitly undefine the positional parameter 1 at the end of my script to avoid inadvertently passing it to another script executed in the same session.
For those who know SQL*Plus, you also benefit from this “feature” if you use the &&1 form of variable dereferencing in order to avoid prompting your users for the same value more than once.
Perhaps I missed something, but this is documented behaviour and looks like an accident waiting to happen to me.
