Avoiding prompts for undefined SQL*Plus parameters

Here's an interesting solution to a question I've seen asked quite a few times. This was posted on the OTN SQL and PL/SQL forum recently by "laurent schneider" from an unaccredited script he found. I Googled a bit and couldn't find any previous references to it so I thought I'd post it for others to see and find later.

COL p_1 NEW_VALUE 1 NOPRINT
COL p_2 NEW_VALUE 2 NOPRINT
SELECT 'x' p_1, 'x' p_2 FROM dual WHERE 1=2;

Apparently, SQL*Plus sees the query and if either &1 or &2 is undefined then it defines them and sets them to null for you. Then the query doesn't actually return a row so no values are overwritten. Now you can call this script with zero, one or two parameters and not get prompted.

--
Richard Kuhler