Decode column position : user_ind_columns « System Tables Views « Oracle PL / SQL

1. Oracle PL / SQL
2. System Tables Views
3. user_ind_columns

Decode column position

   
SQL>
SQL>
SQL> column table_name format a15 word_wrapped
SQL> column index_name format a25 word_wrapped
SQL> column columns format a40
SQL>
SQL> break on table_name skip 1
SQL>
SQL> select table_name,
  2       index_name,
  3  rtrim(
  4       max( decode( column_position, 1, column_name, '' ) )||','||
  5       max( decode( column_position, 2, column_name, '' ) )||','||
  6       max( decode( column_position, 3, column_name, '' ) )||','||
  7       max( decode( column_position, 4, column_name, '' ) )||','||
  8       max( decode( column_position, 5, column_name, '' ) )||','||
  9       max( decode( column_position, 6, column_name, '' ) )||','||
 10       max( decode( column_position, 7, column_name, '' ) )||','||
 11       max( decode( column_position, 8, column_name, '' ) )||','||
 12       max( decode( column_position, 9, column_name, '' ) )||','||
 13       max( decode( column_position, 10, column_name, '' ) )||','||
 14       max( decode( column_position, 11, column_name, '' ) )||','||
 15       max( decode( column_position, 12, column_name, '' ) )||','||
 16       max( decode( column_position, 13, column_name, '' ) )||','||
 17       max( decode( column_position, 14, column_name, '' ) )||','||
 18       max( decode( column_position, 15, column_name, '' ) )||','||
 19       max( decode( column_position, 16, column_name, '' ) ), ',' )
 20       columns
 21  from user_ind_columns
 22  where rownum < 50
 23  group by table_name,index_name
 24  /
TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------
EMP_CHANGES     SYS_C005784               MSGID

UPPER_ENAME     SYS_IOT_TOP_15816         X$ENAME,X$RID

DEPT_AND_EMP    SYS_C005826               DEPTNO
                SYS_C005827               EMPS

AQ$_EMP_CHANGES SYS_IOT_TOP_16251         MSGID,SUBSCRIBER#,NAME,ADDRESS#
_G

TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------


AQ$_EMP_CHANGES SYS_IOT_TOP_16249         MSGID,SUBSCRIBER#,NAME,ADDRESS#
_H


AQ$_EMP_CHANGES SYS_IOT_TOP_16254         SUBSCRIBER#,NAME,QUEUE#,MSG_PRIORITY,MSG
_I                                        _ENQ_TIME,MSG_STEP_NO,MSG_CHAIN_NO,MSG_L
                                          OCAL_ORDER_NO,MSGID



TABLE_NAME      INDEX_NAME                COLUMNS
--------------- ------------------------- ----------------------------------------
AQ$_EMP_CHANGES SYS_C005787               SUBSCRIBER_ID
_S


AQ$_EMP_CHANGES SYS_IOT_TOP_16247         NEXT_DATE,TXN_ID,MSGID
_T


COMPILE_SCHEMA_ COMPILE_SCHEMA_TMP_PK     OBJECT_NAME,OBJECT_TYPE
TMP


10 rows selected.

SQL>

   
    
  

Related examples in the same category