Default value for paramteters not passed SQLPlus script

Question

Is there a way to set default value of paramter in sqlplus script without user input?

For example, I have an 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 invoke it from sqlplus like this:

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 only pass an empty value for parameter when I am asked to enter it, or I am able to pass an empty parameter after script name through "". But this behaviour is very annoying. Some kind of IF DEFINED "&1" will be very usefull.

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

Solution

According to the article linked by Martin I modified previous script to be working without aksing 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

For those who don't fancy chasing and perusing links that can go away anytime, here's a quick cut'n paste snippet.

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
Param 1 value is default
$ sqlplus -s POSF/POSF@ECMDB @a.sql nondef
Param 1 value is nondef

Answer 2

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

Quote:

SQL> COLUMN 1 NEW_VALUE 1
SQL> COLUMN 2 NEW_VALUE 2
SQL> COLUMN 3 NEW_VALUE 3
SQL> COLUMN 4 NEW_VALUE 4
SQL> SELECT '' "1", '' "2", '' "3", '' "4"
  2    FROM dual
  3   WHERE ROWNUM = 0
  4  /

no rows selected

SQL> DEFINE 1
DEFINE 1               = "1" (CHAR)
SQL> DEFINE 2
DEFINE 2               = "2" (CHAR)
SQL> DEFINE 3
DEFINE 3               = "" (CHAR)
SQL> DEFINE 4
DEFINE 4               = "" (CHAR)
SQL> 
SQL> REM ...but...
SQL> 
SQL> DEFINE 5
SP2-0135: symbol 5 is UNDEFINED