If you are using SQL*Plus, you are likely to use the input parameters. And if you omit one of them, SQL*Plus will show prompt for it, like this:
SQL> get test.sql 1 select 'Input variable 1 = &1' from dual 2 union all 3 select 'Input variable 2 = &2' from dual 4 union all 5* select 'Input variable 3 = &3' from dual SQL> @test var1 var2 Enter value for 3: 'INPUTVARIABLE1=VAR1' ----------------------- Input variable 1 = var1 Input variable 2 = var2 Input variable 3 = Elapsed: 00:00:00.01
It is allright, if all variables are needed, but what if we don’t want to press enter for all omitted variables or specify they(especially if script can be start in silent, non interactive mode) and want to use default values for omitted variables or these variables can be unnecessary?
Strictly speaking, there are many different techniques for solving it, see some of them:
[sourcecode language=”sql”]
SQL> get test1
1 col var1 new_value var1 noprint
2 col var2 new_value var2 noprint
3 col var3 new_value var3 noprint
4 set termout off
5 with any_splitting_technique as (
6 select *
7 from xmltable(‘ora:tokenize(.,",")[position()>1]’
8 passing ‘,’||’&1′
9 columns
10 i for ordinality
11 ,"." varchar2(30)
12 )
13 )
14 select
15 nvl("1",’default1′) var1
16 ,nvl("2",’default2′) var2
17 ,nvl("3",’default3’) var3
18 from any_splitting_technique
19 pivot (max(".") for i in (1,2,3))
20 /
21 set termout on
22* prompt var1 = &var1, var2 = &var2, var3 = &var3;
SQL> @test1 1,2,3
var1 = 1, var2 = 2, var3 = 3
SQL> @test1 1,2
var1 = 1, var2 = 2, var3 = default3
[/sourcecode]
[sourcecode language=”sql”]
SQL> get test2
1 set termout off
2 spool tmp.sql
3 def 1
4 def 2
5 def 3
6 spool off
7 col var1 new_value var1
8 col var2 new_value var2
9 col var3 new_value var3
10 with tmp as (
11 select ‘
12 @tmp.sql
13 ‘ params from dual
14 )
15 select
16 nvl(regexp_substr(params,’DEFINE 1\s+ = "([^"]*)’,1,1,’i’,1),’default1′) var1
17 , nvl(regexp_substr(params,’DEFINE 2\s+ = "([^"]*)’,1,1,’i’,1),’default2′) var2
18 , nvl(regexp_substr(params,’DEFINE 3\s+ = "([^"]*)’,1,1,’i’,1),’default3′) var3
19 from tmp
20 ;
21 col var1 clear;
22 col var2 clear;
23 col var3 clear;
24 set termout on
25 prompt var1 = &var1, var2 = &var2, var3 = &var3;
26 undef 1
27 undef 2
28* undef 3
29 .
SQL> @test2 1 2 3
var1 = 1, var2 = 2, var3 = 3
SQL> @test2 1 2
var1 = 1, var2 = 2, var3 = default3
[/sourcecode]
Last example also shows a very useful way to read file into a variable.
But i think, the best option for initializing parameters is the solution by Vladimir Begun:
SQL> get test3
1 set termout off
2 COLUMN 1 NEW_VALUE 1 noprint
3 COLUMN 2 NEW_VALUE 2 noprint
4 COLUMN 3 NEW_VALUE 3 noprint
5 SELECT '' "1", '' "2", '' "3" FROM dual WHERE 1=0;
6 SELECT nvl('&1','default1') "1"
7 , nvl('&2','default2') "2"
8 , nvl('&3','default3') "3"
9 FROM dual;
10 col var1 clear;
11 col var2 clear;
12 col var3 clear;
13 set termout on
14 prompt var1 = &1, var2 = &2, var3 = &3;
15 undef 1
16 undef 2
17* undef 3
18 .
SQL> @test3 1 2 3
var1 = 1, var2 = 2, var3 = 3
SQL> @test3 1 2
var1 = 1, var2 = 2, var3 = default3
So i can create 2 include files – for execution at the start and at the end of all scripts. I created directory “inc” for include files and files:
[sourcecode language=”sql”]
store set splus_restore.sql replace
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT ” "1", ” "5", ” "9", ” "13"
,” "2", ” "6", ” "10", ” "14"
,” "3", ” "7", ” "11", ” "15"
,” "4", ” "8", ” "12", ” "16"
FROM dual
WHERE 1=0;
set termout on;
[/sourcecode]
and
[sourcecode language=”sql”]
undef 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
@splus_restore;
[/sourcecode]
[sourcecode language=”sql”]
@inc/s_begin;
col table_owner format a12
col column_name format a30
col index_owner format a12
col index_name format a30
col "#" format 99
break on table_owner on table_name on index_owner on index_name on partition_name on mbytes on bytes on blocks
select
ic.table_owner
,ic.table_name
,ic.index_owner
,ic.index_name
,s.partition_name
,round(s.bytes/1024/1024) mbytes
,s.blocks
,ic.column_position "#"
,decode(ic.column_position,1,”,’ ,’)||ic.column_name column_name
from dba_ind_columns ic
,dba_segments s
where
upper(ic.table_name) like upper(‘&1’)
and upper(ic.table_owner) like nvl(upper(‘&2′),’%’)
and s.owner = ic.index_owner
and s.segment_name = ic.index_name
order by
1,2,3,4,8
/
clear break;
col "#" clear;
@inc/s_end;
[/sourcecode]
[sourcecode language=”sql”]
SQL> @get_indexes wrh$%tab%stat xtender
Wrote file splus_restore.sql
no rows selected
Elapsed: 00:00:05.79
SQL> @get_indexes wrh$%undostat
Wrote file splus_restore.sql
TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME PARTITION_NAME MBYTES BLOCKS # COLUMN_NAME
———— ————– ———— —————– ————– ——- ——- — ——————–
SYS WRH$_UNDOSTAT SYS WRH$_UNDOSTAT_PK 0 16 1 BEGIN_TIME
2 ,END_TIME
3 ,DBID
4 ,INSTANCE_NUMBER
[/sourcecode]