A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Getting an inline piece of sqlplus (called from a command file) to recognise a passed-in parameter and also avoid the
“Enter Value For 1:” prompt if no parameter is given.
The excerpt below is from a VMS command file and shows the use of this technique.
$! A trick to allow passing a parameter to an inline bit of
$! sqlplus code. Although the file i_dont_exist.sql doesn't
$! (or shouldn't) exist the bit of SQL after it will still
$! run and recognise the P1 parameter if one is supplied.
$! The 3 lines after the sqlplus command are another trick
$! to avoid the "Enter Value for 1:" prompt you would
$! normally get if you didn't supply a parameter. If no
$! parameter is passed in it defaults to NULL
$ sqlplus myuser/mypass @i_dont_exist 'p1
column 1 new_value 1
select null "1" from dual where rownum = 0
select * from dual where 1 = '&&1'
If no parameter is given, the &&1 defaults to NULL otherwise it uses the passed-in parameter. This avoid getting the "Enter Value For 1:" SQLPLUS prompt