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:
Comma-separated params
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
[collapse]
With SPOOL and DEFINE
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
[collapse]
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:
inc/s_begin.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;
[collapse]
and
inc/s_end.sql
undef 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 @splus_restore;
[collapse]
get_index.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;
[collapse]
Sample output
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
[collapse]