Oracle SQL
  • LICENSE

SQL*Plus tips. #1

Posted on March 29, 2013 by Sayan Malakshinov Posted in oracle, SQL*Plus, SQL*PLus tips 5 Comments

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]
And for example get_index.sql:
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]
As you see i can omit owner parameter and in this case it will search in all schemas.

SQL*Plus
« Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
SQL*Plus tips. #2 »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL