0
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
    index_name,
    index_description,
    ltrim(sys_connect_by_path(index_keys,','),',') as Index_Keys,
    include_cols,
    index_filter,
    data_compression,
    allow_page_locks,
    table_name,
    index_type    
 from 
  (
  Select t.*,  
    row_number() over (partition by INDEX_NAME order by INDEX_NAME) as rn
    from 
    (
     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 = 'CON$' AND
        di.table_name = 'CON$' 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 = 'CON$' AND
       di.table_name = 'CON$' 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,
           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 = 'CON$' AND
       di.table_name = 'CON$' AND
        di.partitioned = 'YES' AND
       dip.composite = 'YES' ) t      
)

For the following index definition:

1

Preferred Method

Your method is only valid for ancient versions of Oracle. Instead, you should use the LISTAGG() aggregate.

Documentation: https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030

Example code:

SELECT
  di.table_name
 ,di.index_name
 ,LISTAGG( ci.column_name,', ' ) WITHIN GROUP( ORDER BY ci.column_position ) column_name_csv
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_name   = 'CON$'
GROUP BY
  di.table_name
 ,di.index_name
;
0

Answer originally left in comments by ypercube

You probably need to provide an order by in the window function:

row_number() over (partition by INDEX_NAME order by INDEX_NAME)

There's a COLUMN_POSITION in dba_ind_columns I think.

So probably:

row_number() over (partition by INDEX_NAME order by COLUMN_POSITION) as rn

or even more simply, without any function: COLUMN_POSITION as rn.

You have to add the column_position column in the (t) subquery, too, in all 3 UNION parts. It can't find it otherwise.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.