Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

find out the Index columns based on the index name

user520824Dec 16 2015 — edited Dec 16 2015

[code]

Hello,

I need to write a query/procedure that takes index_name as input parameter and would like to return all the INDEXED columns as separated by comma

Eg,

create table test (col1 number, col2 number, col3 varchar2(20));

create index test_indx on test (col1, col2, upper(col3));

in this case, This index is having an expression. some of the indexes there can be an expression, not all the time.  Regardless I would like to return only the column name something like below

col1, col2, col3

My below query is not correct, it is not displaying the expression column so I would appreciate if you can get me the correct query. Thanks in advance

SELECT

       aic.column_name

  FROM all_ind_columns aic, all_ind_expressions aie

WHERE aic.index_name = aie.index_name(+)

   AND aic.index_owner = aie.index_owner(+)

   AND aic.column_position = aie.column_position(+)

   and aic.table_name ='TEST'

[/code]

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 13 2016
Added on Dec 16 2015
15 comments
29,317 views