Within Reason

SQL*Plus and parameter handling: the "Right Way"

Posted by Ceri Davies Sun, 05 Feb 2006 22:15:00 GMT

A few months ago I complained about ropey parameter handling in SQL*Plus. I found a solution.

It's a pretty ropey solution to my mind, but it works and it is officially documented to be the best solution available, so it'll do for now.

I'll present it below, but I won't make any further comment for now.

Recap

The script I was using before, was named segmentsbytablespace.sql and read:

SELECT segment_name, segment_type, bytes/1024/1024 MB
   FROM dba_segments
   WHERE tablespace_name = '&1'
   ORDER BY bytes
/

I lamented the fact that this did strange and confusing things for the user if a parameter was not passed to it, i.e.:

SQL> @segmentsbytablespace
    Enter value for 1:

I was hoping for the users of my scripts to not have to read them to work out what 1 referred to.

The hack^Wsolution

You basically just hack right around it. This is, according to Oracle, "[p]erhaps the closest solution".

prompt Enter the tablespace name
set termout off
define tablespace_to_query = &1
set termout on
prompt Setting tablespace to &tablespace_to_query

SELECT segment_name, segment_type, bytes/1024/1024 megs
FROM dba_segments
WHERE tablespace_name='&tablespace_to_query'
ORDER BY bytes
/

-- Now undefine the parameters used, otherwise SQL*Plus will pass
-- them on to any other scripts subsequently run.
undefine 1
undefine tablespace_to_query