​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking

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'



$ exit


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