Column name in user_ind_columns, all_ind_columns

Advertisement

The oracle data dictionary seems to assign a new system generated name (for example 'SYS_NC00008$') to index columns where the descend value = 'DESC';
SQL> select index_name, index_owner, column_name, descend from all_ind_columns
where descend = 'DESC';
INDEX_NAME INDEX_OWNER
COLUMN_NAME
DESC
MY_INDEX MY_OWNER
SYS_NC00008$
DESC
1 row selected.
is there a way to get the proper column name through a join to another table?
This is what I tried
SQL> select a.index_name, a.index_owner, a.table_name, b.column_name, a.descend
from all_ind_columns a, user_tab_columns b
where a.table_name = b.table_name and a.column_name = b.column_name and a.descend = 'DESC';
no rows selected
Is this fixed in 9i?
Thanks in advance,
David Wilson.

Advertisement

I get exactly the same results in both 9.0.1 and 9.2.0, so it is unchanged in 9i. However, it is not a bug, so it will not be fixed. The reason you get the system generated name with a DESC index is that Oracle implements it as a function based index.
If you really need the "real" column name, it is in the default$ column of sys.col$.
I suppose you could add that column to a custom version of the xxx_ind_columns views if you really needed it.
TTFN
John

October 11, 2015

Column name in user_ind_columns, all_ind_columns

The oracle data dictionary seems to assign a new system generated name (for example 'SYS_NC00008$') to index columns where the descend value = 'DESC'; SQL> select index_name, index_owner, column_name, descend from all_ind_columns where descend = 'DES

Rename column name

Can i rename column name of a table. If so how to do it????before 9i Renaming a column in table: ========================== This is possible by updating the "name" column of the col$. To reflect this change in the system views, run catalog.sql a

Column exist but ResultSet.getString(String) return invalid column name

With oracle 7 db the ResultSet.getString(String) works fine but after the upgrade to oracle 8 db it return invalid column name. ResultSet.getString(int) method works fine. Is this a bug in the driver? Thanks -RedaYes I'm 100 % sure that the columns e

Change the column names programatically

Hi All, is it possible to change the column name in a table programatically?? my req is like this..I ve a table which contains 60 columns. out of these 60 columns 53 columns signifies the 53 weeks of the year. so i ve to display the date in the colum

Lookup Column name

Hi All I am new to OIM. Can anyone tell me what is the difference between Lookup Column Name and Column Name property of a lookup field while writing lookup query?Hi, Lets take an example here in the post given below. Re: Conditional forms Column Cap

Migrating from Sql Server tables with column name starting with integer

hi, i'm trying to migrate a database from sqlserver but there are a lot of tables with column names starting with integer ex: *8420_SubsStatusPolicy* i want to make an offline migration so when i create the scripts these column are created with the s

Presentation variable in column name

Is there any way to add in column name presentation variable? Example *2007* =tmp_2ybefore *2008* =tmp_ybefore This variables are defined in prompt Sale *2007* -- Sale *2008* 1,22------------2,86 4,44------------3,29 Edited by: Micek on 2009.04.10 09

Switching from delimited to fixed width and had to retype 115 column names

Hi we run 2012 enterprise.  I just changed a delimited flat file over to fixed width and had to retype all 115 column names.  Is there a way to preserve at least some of what ssis already knows (eg col names) in the flat file connection when this kin

"write to measurement file" specify column names

I'm using "Write to measurement file" VI to create a TDMS file from 2 and possiby more data sources. Currenty the columns are titled "Untitled", "Untitled1", etc. I would like to specify column names. Can anyone point me to a

Having a column name as a variable within a query,Problem!!!!

I have a problem with a simple query shown below SELECT * FROM DisneyWHERE Upper(COLNAME) LIKE UPPER('%' || SEARCHSTRING || '%');SELECT * FROM Disney WHERE Upper(COLNAME) LIKE UPPER('%' || SEARCHSTRING || '%'); My problem, The colname variable is not

Invalid column name in IMGSimilar

Hi all, I have a question for the function IMGSimilar. I have a table musterbildsig, in which the generated signatures of 100 pictures are stored in the column msbild_sig. Each Signature in this table is to compare with all signatures in the column b

How to know primary key column name form a table name in sql query

Suppose I only know the table name. How to get its primary key column name from the table name? ThanksViews don't have primary keys though their underlying tables might. You'd need to pick apart the view to determine where it's columns are coming fro

Can the column names be changes dynamically in OBIEE presentation

Hi, Is thr any way through which I can change the column names depending on the value of a column. the table structure is Plan_name      Character1   Character2    Character3  .... so here according to plan_name the meaning of each character column c

Dict View name and column names

Hi, Oracle 11.2.0.1 Windows XP I wish to get the output of all the dictionary views with their name and column names something like this : VIEW NAME Name                                      Null?    Type VIEW NAME Name                               

Query to list tables with a particular column name in a schema!!

Is there any way to know? Thanks in advance.You can query the data dictionary views to get this information. For example untested select table_name from user_tab_columns where column_name = 'my particular column name';There is also all_tab_columns an

Passing column name dianamically in an insert query

i have a procedure hwre i used an inser query.eg insert into table (a,b,c) values(...) i need to pass a,b,c ie column names as parameter.depending upon the condition the column name will change.You don't necessarily need to use dynamic SQL; create or

Changing Alv Column name

Hi, I have a "viewcontaineruielement" which displays an ALV. Now i want to change the column names. Please give an detailed answer. ThanksHi Adil Ple refer this.   data: lr_alv_usage type ref to if_wd_component_usage, lr_if_controller type ref t

Help on Merge Statement ,I got 'ORA-00904: invalid column name' Error

Pls help In Oracle 9i i implement the following qry MERGE INTO jobs A USING (select order_no,jOB_SEQ_NO from jobs_dlt) B ON (A.ORDER_NO = B.ORDER_NO and A.JOB_SEQ_NO =B.JOB_SEQ_NO ) WHEN MATCHED THEN UPDATE SET           A.ORDER_NO= B.ORDER_NO ,     

SQLException: invalid column name

I created a view on a Oracle 8.0.5 database which joins three tables. One column of the view is built by subtracting the values of two table-columns by each other. On SQLPlus everything works fine, but when I run a SELECT-Statement over JDBC I got a

Why order by clause maintains column number values instead of column names

why order by clause maintains column number values instead of column names ?we can use oder by 1,2 as column number UGNo one said that it can't be used. What's your point? To OP: It can be written with the column's 'select list positional number' jus