Xrhrft

Code of my stored procedure..

create or replace procedure test_1(p_table_owner in varchar2, p_table_name in varchar2, p_result out sys_refcursor)

AS

BEGIN

  OPEN p_result FOR

    select 

                     di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||

    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then ', unique, primary key' 

    when di.uniqueness='UNIQUE' then ', unique' end ||

    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'

     AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",                    

       case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"

                from dba_indexes di

                join dba_ind_columns ci on ci.index_owner = di.owner

                and ci.index_name = di.index_name



               where di.table_owner = p_table_owner

                 and di.table_name = p_table_name

                 and partitioned = 'NO'



              union all

              select 

                     di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||

    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key' 

    when di.uniqueness='UNIQUE' then ', unique' end ||

    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'

     AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",

         case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"

                from dba_indexes di

                join dba_ind_partitions dip on dip.index_owner = di.owner

                                           and dip.index_name =

                                               di.index_name

                join dba_ind_columns ci on ci.index_owner = di.owner

                and ci.index_name = di.index_name

               where di.table_owner = p_table_owner

                 and di.table_name = p_table_name

                 and di.partitioned = 'YES'



                 and dip.composite != 'YES'

              union all

              select 

                     di.index_name as "INDEX_NAME", case when di.index_type='IOT - TOP' then 'clustered' else 'nonclustered' end ||

    case when di.uniqueness='UNIQUE' and substr(di.index_name, 1, 3) = 'PK_' then', unique, primary key' 

    when di.uniqueness='UNIQUE' then ', unique' end ||

    case when di.uniqueness='NONUNIQUE' then '' else '' end || ' located on PRIMARY'

     AS "INDEX_DESCRIPTION",ci.column_name as "INDEX_KEYS",null as "INCLUDE_COLS",null as "INDEX_FILTER",null as "DATA_COMPRESSION",null as "ALLOW_PAGE_LOCKS",di.table_name as "TABLE_NAME",

        case when di.index_type='IOT - TOP' then 'CLUSTERED' else 'NONCLUSTERED' end AS "INDEX_TYPE"

                from dba_indexes di

                join dba_ind_partitions dip on dip.index_owner = di.owner

                                           and dip.index_name =

                                               di.index_name

                join dba_ind_subpartitions dis on dis.index_owner = di.owner

                                              and dis.index_name =

                                                  di.index_name

                                              and dis.partition_name =

                                                  dip.partition_name

                join dba_ind_columns ci on ci.index_owner = di.owner

                and ci.index_name = di.index_name

               where di.table_owner = p_table_owner

                 and di.table_name = p_table_name

                 and di.partitioned = 'YES'

                   and dip.composite = 'YES';

END;

Currently if i execute this on table(to get indexes information)..it displays me 2 rows for indexes(multi columns) like in below output..i.e it returns 2 seperate rows for index 24290...but i would like it to return only one row per index..In case of multiple columns, the INDEX_KEYS column should reflect all the columns as comma separated list. In this particular this column should appear as:
TESTSMALLINT, BIGINTTEST

SPROC should be compatible with versions lesser than 11g too..