14

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
Improve this question

2 Answers 2

Reset to default
8

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Could you please explain why this works? I don't understand the point of the column 1... and select '' "1"... sections, but I tested it out and it doesn't work without them.
It seems that the column name doesn't actually matter. I can do column dummy_a new_value 1 noprint and select '' dummy_a from dual where rownum = 0 and get the same result. For that matter, I can replace '' with anything, even a numeric literal like 42, and it won't make a difference.
6

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
Improve this answer

1 Comment

Please provide answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.