Tag Archives: SQL*Plus

SQL*Plus tips #4: Branching execution

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips Leave a comment

Today I’ll show a trick how we can use branching execution of SQLPlus scripts in SQL*Plus.
Although I previously showed the conditional execution of scripts and it really can be used for branching, but today I’ll show how to do it without splitting the script into several smaller scripts. In contrast to the conditional script execution, I’ll use a different method.

It is very simple, as usual – if you want to execute only one part of script, you can just comment out all unnecessary. So depending on the conditions, we can execute a script which will start a comment.

Suppose we need to create a script, which, depending on the input parameter will be required to execute a specific query.
See how this can be done:
1. “test.sql”:

def param = &1

@if &param=1
   select 1 from dual;
/* end_if */

@if &param=2
   select 2 from dual;
/* end_if */

@if &param=3
   select 3 from dual;
/* end_if */

2. “if.sql”:

col do_next new_val do_next noprint;
select 
      case 
         when &1 then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

3. “inc/comment_on.sql” contains only 2 chars:

/*

4. “inc/null.sql” is the same as in the previous examples – just empty file.

Ok, lets test it:

SQL> @test 1

         1
----------
         1


SQL> @test 2


         2
----------
         2


SQL> @test 3


         3
----------
         3

As you see, we got what we wanted. Please note that we have to close the multiline comments in the right places only(/* end_if */). So we cannot use in these parts another “*/”. But you can use it in another child scripts.

Same way we can make an analogue of switch/case:
“test2.sql”:

@switch &1

   @when 1 then
      select 1 from dual;
   /* end when */

   @when 2 then
      select 2 from dual;
   /* end when */

   @when 3 then
      select 3 from dual;
   /* end when */

/* end switch */
switch.sql 
def switch_param=&1

[collapse]

when.sql 

col do_next new_val do_next noprint;
select 
      case 
         when &1 = &switch_param then 'inc/null'
         else 'inc/comment_on'
      end as do_next
from dual;
@&do_next

[collapse]

Example:

SQL> @test2 2

         2
----------
         2

SQL> @test2 3

         3
----------
         3

SQL*Plus tips #3: Iterators and recursions

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips 8 Comments

We all used to iterators, recursions and branching in programming, but sql*plus does not have such commands. Today I will show how to do iterators/recusions.

Suppose we want to call a script 3 times. So it would be convenient if we can do it like: 

@iterate 3 @some_script

It is very easy: We can recursively call the script by reducing variable until it is greater than zero. and if a variable is zero, then call an empty “NULL” script.
iterate.sql:

-- initializing input parameters as i showed in previous tips:
@inc/input_params_init.sql;
-- for example max number of input parameters = 14:
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
def _ITER = "&1"

prompt ------   iteration &_ITER start: "@&2  -----------;
set termout off


col _SCRIPT    new_val _SCRIPT   noprint;
col _CONTINUE  new_val _CONTINUE noprint;
col _PARAMS    new_val _PARAMS   noprint;
select 
   replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
          ,'${ITER}'
          ,'&_ITER'
          )          as "_PARAMS"
  ,case 
      when &1 > 0 then 'iterate '||(&1 - 1)
      else 'inc/null'
   end               as "_CONTINUE"
  ,case 
      when &1 > 0 then '&2' 
      else 'inc/null'
   end               as "_SCRIPT"
from dual;
set termout on

@&_script            &_PARAMS
@&_CONTINUE &_SCRIPT &_INIT_PARAMS

As you see i also added option to pass iteration_number as input parameter, so we can call it like: @iterate 3 script.sql ${ITER}
And null.sql in “inc” directory is just empty file.

Lets test it:

SQL> $cat test.sql
select &1 a,'&2' b,'&3' c from dual;

SQL> @iterate 3 test.sql 1 2 3
------   iteration 3 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 3

------   iteration 0 start: "@test.sql  -----------

SQL> @iterate 2 test.sql 1 2 ${ITER}
------   iteration 2 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 2

------   iteration 1 start: "@test.sql  -----------

         A B C
---------- - -
         1 2 1

------   iteration 0 start: "@test.sql  -----------

Note that last string “iteration 0 start” just means that it is last empty call. I made this only for showing how we can do postprocessing after all iterations.
Ok, it works good, but with a little modification we can also add option to call such script with list of values too:

final iterate.sql 

@inc/input_params_init.sql;

set termout off
def _INIT_PARAMS = '"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
col _ITER_CURR new_val _ITER_CURR noprint
col _ITER_NEXT new_val _ITER_NEXT noprint
select
   case 
      -- simple N iterations:
      when translate('&1','x0123456789','x') is null 
         then '&1'
      -- list iteration:
      when substr('&1',1,1)='(' 
         then decode( instr('&1',',')
                     ,0,substr('&1',2,length('&1')-2)
                     ,substr('&1',2,instr('&1',',')-2)
                    )
   end "_ITER_CURR",
   case 
      when translate('&1','x0123456789','x') is null 
         then 
            case 
               when '&1'>1 then 'iterate '||('&1' - 1)
               else 'inc/null'
            end
      when substr('&1',1,1)='(' 
         then 
            case 
               when instr('&1',',')=0 or '&1'='()' then 'inc/null'
               else 'iterate '
                  ||'('
                  ||decode( instr('&1',',')
                           ,0,')'
                           ,substr('&1',instr('&1',',')+1)
                          )
            end
   end "_ITER_NEXT"
from dual;

set termout on
prompt ******   iteration &_ITER_CURR start: "@&2  ***********;
set termout off

col _SCRIPT    new_val _SCRIPT   noprint;
col _PARAMS    new_val _PARAMS   noprint;
select 
   replace('"&3" "&4" "&5" "&6" "&7" "&8" "&9" "&10" "&11" "&12" "&13" "&14" "&15" "&16"'
          ,'${ITER}'
          ,'&_ITER_CURR'
          )          as "_PARAMS"
  ,case 
      when nvl('&_ITER_CURR',0) in ('0','()') then 'inc/null'
      else '&2'
   end               as "_SCRIPT"
from dual;
set termout on

@&_script             &_PARAMS
@&_ITER_NEXT &_SCRIPT &_INIT_PARAMS

[collapse]

See how it works:

-- iterate by list of values  (7,3,10):
SQL> @iterate (7,3,10) test.sql 1 2 ${ITER}
******   iteration 7 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 7

******   iteration 3 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 3

******   iteration 10 start: "@test.sql  ***********

         A B C
---------- - --
         1 2 10

-- now with simple 2 iterations:
SQL> @iterate 2 test.sql 1 2 ${ITER}
******   iteration 2 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 2

******   iteration 1 start: "@test.sql  ***********

         A B C
---------- - -
         1 2 1

So if you call script with “iterate N …” it will iterate N times, and if you specify “iterate (X,Y,…,Z)” it will iterate by this list.

PS. About a branching i will wrote later…

SQL*Plus tips. #2

Posted on by Posted in oracle, SQL*Plus, SQL*PLus tips 10 Comments

I think you know the famous print_table procedure by Tom Kyte. It is really great, but a little hard(it requires create procedure or place it in anonymous block) and in last oracle versions we can do same with one simple query with xmltable/xmlsequence:

SQL> select *
  2  from
  3     xmltable( '/ROWSET/ROW/*'
  4               passing xmltype(cursor(select * from hr.employees where rownum<3))
  5               columns
  6                  col varchar2(100) path 'name()'
  7                 ,val varchar2(100) path '.'
  8        );

COL                            VAL
------------------------------ ------------------------------------------------------
EMPLOYEE_ID                    198
FIRST_NAME                     Donald
LAST_NAME                      OConnell
EMAIL                          DOCONNEL
PHONE_NUMBER                   650.507.9833
HIRE_DATE                      21-JUN-07
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50
EMPLOYEE_ID                    199
FIRST_NAME                     Douglas
LAST_NAME                      Grant
EMAIL                          DGRANT
PHONE_NUMBER                   650.507.9844
HIRE_DATE                      13-JAN-08
JOB_ID                         SH_CLERK
SALARY                         2600
MANAGER_ID                     124
DEPARTMENT_ID                  50

20 rows selected.

It is very easy, but for conveniency we need to add “rownum”:

SQL> select *
  2  from
  3     xmltable( 'for $a at $i in /ROWSET/ROW
  4                   ,$r in $a/*
  5                     return element ROW{
  6                                       element ROW_NUM{$i}
  7                                      ,element COL_NAME{$r/name()}
  8                                      ,element COL_VALUE{$r/text()}
  9                                      }'
 10               passing xmltype(cursor(select * from hr.employees where rownum<3))
 11               columns
 12                  row_num   int
 13                 ,col_name  varchar2(30)
 14                 ,col_value varchar2(100)
 15        );

   ROW_NUM COL_NAME                       COL_VALUE
---------- ------------------------------ ------------------------------------------
         1 EMPLOYEE_ID                    198
         1 FIRST_NAME                     Donald
         1 LAST_NAME                      OConnell
         1 EMAIL                          DOCONNEL
         1 PHONE_NUMBER                   650.507.9833
         1 HIRE_DATE                      21-JUN-07
         1 JOB_ID                         SH_CLERK
         1 SALARY                         2600
         1 MANAGER_ID                     124
         1 DEPARTMENT_ID                  50
         2 EMPLOYEE_ID                    199
         2 FIRST_NAME                     Douglas
         2 LAST_NAME                      Grant
         2 EMAIL                          DGRANT
         2 PHONE_NUMBER                   650.507.9844
         2 HIRE_DATE                      13-JAN-08
         2 JOB_ID                         SH_CLERK
         2 SALARY                         2600
         2 MANAGER_ID                     124
         2 DEPARTMENT_ID                  50

20 rows selected.

Now we can create simple script for it with formatting:

print_table.sql 

-- show output  
set termout on
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page

-- main query:
select *
from 
   xmltable( 'for $a at $i in /ROWSET/ROW
                 ,$r in $a/*
                   return element ROW{
                                     element ROW_NUM{$i}
                                    ,element COL_NAME{$r/name()}
                                    ,element COL_VALUE{$r/text()}
                                    }'
             passing xmltype(cursor( &1 ))
             columns
                row_num   int
               ,col_name  varchar2(30) 
               ,col_value varchar2(100) 
      );
-- disabling pause and breaks:
set pause off
clear breaks

[collapse]

Usage example:print_table example
As you see script works fine, but it is require to pass query as parameter, though sometimes it is not so convenient. For example if we want started query and later decided to show it with print_table. In that case we can create scripts with tricks from previous part:
print_last.sql 

store set settings.sql replace
-- saving previous query:
save tmp.sql replace

-- OS-dependent removing trailing slash from file, choose one:
-- 1. for *nix through head:
!head -1 tmp.sql >tmp2.sql

-- 2. for for *nix through grep:
--!grep -v tmp.sql >tmp2.sql

-- 3. for windows without grep and head:
-- $cmd /C findstr /v /C:"/" tmp.sql > tmp2.sql

-- 4. for windows with "head"(eg from cygwin)
--$cmd /C head -1 tmp.sql > tmp2.sql

-- 5. for windows with "grep":
--$cmd /C grep -v "/" tmp.sql > tmp2.sql



-- same setting as in print_table:
set termout on echo off embedded on pause on newpage 2
set pause "Press Enter to view next row..."
break on row_num skip page

-- main query:
select *
from
   xmltable( 'for $a at $i in /ROWSET/ROW
                 ,$r in $a/*
                   return element ROW{
                                     element ROW_NUM{$i}
                                    ,element COL_NAME{$r/name()}
                                    ,element COL_VALUE{$r/text()}
                                    }'
             passing dbms_xmlgen.getxmltype(
             q'[
               @tmp2.sql
             ]'
             )
             columns
                row_num   int
               ,col_name  varchar2(30)
               ,col_value varchar2(100)
      );
-- disabling pause and breaks:
set pause off
clear breaks
@settings.sql

[collapse]

Example: 

SQL> select * from hr.employees where rownum<3;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER      HIRE_DATE JOB_ID    SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        198 Donald               OConnell                  DOCONNEL                  650.507.9833         21-JUN-07 SH_CLERK         2600                       124            50
        199 Douglas              Grant                     DGRANT                    650.507.9844         13-JAN-08 SH_CLERK         2600                       124            50

Elapsed: 00:00:00.01
SQL> @print_last
Wrote file settings.sql
Wrote file tmp.sql

   ROW_NUM COL_NAME                       COL_VALUE
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
         1 EMPLOYEE_ID                    198
           FIRST_NAME                     Donald
           LAST_NAME                      OConnell
           EMAIL                          DOCONNEL
           PHONE_NUMBER                   650.507.9833
           HIRE_DATE                      21-JUN-07
           JOB_ID                         SH_CLERK
           SALARY                         2600
           MANAGER_ID                     124
           DEPARTMENT_ID                  50
Press Enter to view next row...

[collapse]

PS. if you will use xmltype(cursor(…)) on versions less that 11.2.0.3 you can get errors with xml rewriting. In this case you need to disable it:

alter session set events '19027 trace name context forever, level 0x1';

Update:
Vladimir Przyjalkowski rightly pointed out that such approach will be suboptimal in case of big amount of data:

A nice toy, but i’m sure it will not tolerate big data.

It is absolutely true, because at first, “xmltype(cursor(…))” aggregated data as xmltype, and only then xmltable returns data.
But I use print_table for small amount of data only, and in case of when i want to see sample of data, i usually add limitation by rownum, which also convenient because it automatically changes optimizer mode with enabled parameter “_optimizer_rownum_pred_based_fkr” (it is default):

SQL> @param_ rownum

NAME                               VALUE   DEFLT  TYPE     DESCRIPTION
---------------------------------- ------- ------ -------- ------------------------------------------------------
_optimizer_rownum_bind_default     10      TRUE   number   Default value to use for rownum bind
_optimizer_rownum_pred_based_fkr   TRUE    TRUE   boolean  enable the use of first K rows due to rownum predicate
_px_rownum_pd                      TRUE    TRUE   boolean  turn off/on parallel rownum pushdown optimization

However, we can easily change the our query to make it optimally with a lot of data too:

select row_num
      ,t2.*
from 
   (select rownum row_num
         , column_value x
    from table(xmlsequence(cursor( &1 )))
   ) t1
  ,xmltable( '/ROW/*'
             passing t1.x
             columns
                col_num for ordinality
               ,col_name  varchar2(30) path 'name()'
               ,col_value varchar2(100) path '.'
      )(+) t2;

Lets test it with pipelined function which will pipe rows infinitely and log count of fetched rows:

-- drop table xt_log purge;
-- drop function f_infinite;

create table xt_log(n int);

create function f_infinite 
   return sys.ku$_objnumset pipelined 
as
   i int:=0;
   pragma autonomous_transaction;
begin
   loop 
      i:=i+1;
      insert into xt_log values(i);
      commit;
      pipe row(i);
   end loop;

exception 
   when NO_DATA_NEEDED then
      commit;
end;
/
New version of print_table: print_table2.sql 
-- set arraysize for minimal value, so sqlplus did not fetch extra rows:
set arraysize 2
-- show output  
set termout on
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page

-- main query:
select row_num
      ,t2.*
from 
   (select rownum row_num
         , column_value x
    from table(xmlsequence(cursor( &1 )))
   ) t1
  ,xmltable( '/ROW/*'
             passing t1.x
             columns
                col_num for ordinality
               ,col_name  varchar2(30) path 'name()'
               ,col_value varchar2(100) path '.'
      )(+) t2;
-- disabling pause and breaks:
set pause off
clear breaks

[collapse]

And look final test:

SQL> select * from xt_log;

no rows selected

SQL> @print_table2 "select * from table(f_infinite)"
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         1          1 COLUMN_VALUE                   1
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         2          1 COLUMN_VALUE                   2
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         3          1 COLUMN_VALUE                   3
Press Enter to view next row...


   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
---------- ---------- ------------------------------ -----------
         4          1 COLUMN_VALUE                   4
Press Enter to view next row...

   ROW_NUM    COL_NUM COL_NAME                       COL_VALUE
         5          1 COLUMN_VALUE                   5
Press Enter to view next row...


6 rows selected.
-------------------------------
SQL> select * from xt_log;

         N
----------
         1
         2
         3
         4
         5
         6
         7

7 rows selected.

So we got what we wanted!

PS. Full scripts:
1. Simple: print_table.sql
2. For big queries: print_table2.sql

SQL*Plus tips. #1

Posted on by 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.