Sunday, December 20, 2020

How to prevent any prompting for input parameters in sqlplus if the script is invoked without any parameters (example taken from preupgrade.sql)

Rem The below code will prevent any prompting if the script is
Rem invoked without any parameters.
Rem

SET FEEDBACK OFF
SET TERMOUT OFF

COLUMN 1 NEW_VALUE  1
SELECT NULL "1" FROM SYS.DUAL WHERE ROWNUM = 0;
SELECT NVL('&&1', 'FILE') FROM SYS.DUAL;

COLUMN 2 NEW_VALUE  2
SELECT NULL "2" FROM SYS.DUAL WHERE ROWNUM = 0;
SELECT NVL('&&2', 'TEXT') FROM SYS.DUAL;
SET FEEDBACK ON
SET TERMOUT ON

The input parameter &1 was assigned value 'FILE', and &2 was assigned a value 'TEXT'. Quite useful construction.

Good Luck !

No comments:

Post a Comment