Default value for parameters not passed to SQLPlus script

Question

Is there a way to set the parameter default value in sqlplus script without user input?

For example, I have SQL script sessions.sql:

  SET VERIFY OFF
 SET TERMOUT OFF
 DEFINE uname = '& 1'

 COLUMN search_uname new_value search_uname
 SELECT CASE WHEN '& uname' = '' THEN '%' ELSE UPPER ('& uname') END AS search_uname 
 FROM dual;

 SET TERMOUT ON

 SELECT sid, serial, username FROM v $ session WHERE username LIKE '& search_uname';

And I want to call it from sqlplus as follows:

  SQL> @sessions
 Enter value for 1:

        SID SERIAL # USERNAME
 ---------- ---------- ------------------------------
     56 20577 CONTEXT
 .....
 236 rows selected.

 SQL> @sessions ""

        SID SERIAL # USERNAME
 ---------- ---------- ------------------------------
     56 20577 CONTEXT
 .....
 236 rows selected.

 SQL> @sessions SDE

        SID SERIAL # USERNAME
 ---------- ---------- ------------------------------
        113 56675 SDE
        165 64881 SDE
 .....
 43 rows selected.

 SQL> 

I can pass an empty parameter value when I am asked to enter it, or I can pass an empty parameter after the script name through ". But this behavior is very annoying. Some IF DEFINED value" & 1 "will be very useful.

Do you have any tips or tricks on how this should be achieved in order to apply the WHERE clauses in sqlplus script wheter parameter is defined or not without unnecessary user interaction?

Decision

According to an article related to Martin I, the previous script was modified, which will work without the need for parameter values:

  SET VERIFY OFF
 SET TERMOUT OFF

 column 1 new_value 1
 SELECT '' "1" FROM dual WHERE ROWNUM = 0;
 define uname = '& 1'

 SET TERMOUT ON

 SELECT sid, serial #, username FROM v $ session 
 WHERE username LIKE UPPER (DECODE ('& uname', '', '%', '& uname'));

 undefine 1

Answer 1

Please read the "On SQL * Plus Defines" for the answer to your question.

Answer 2

For those who are not interested in stalking and viewing links that might go away at any time, quickly remove the patch here.

set termout on set serveroutput on set feedback off set verify off -- start column 1 new_value 1 noprint select '' "1" from dual where rownum = 0; define param = &1 "default" -- end begin dbms_output.put_line ( 'Param 1 value is &param' ); end; / exit 0 / 

Execution:

$ sqlplus -s SCOTT / TIGER @ORCL @ a.sql
The default value of parameter 1 is $ sqlplus -s POSF / POSF @ECMDB @ a.sql nondef
Parameter 1 is nondef