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


elmama
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

$ 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