Identify the reference column of an Oracle virtual column

Asked

Viewed 128 times

0

I have an index on Oracle in which one of the columns is DESC. I create it as follows:

CREATE INDEX XFT25TB ON FT25T (FT07CODEMP, FI16MODELO, FT07CODIGO, FT25ORDIMP, DESC)

However, when using Oracle’s data dictionary views, the index references a virtual column instead of referencing the column FT25ORDIMP, as below:

SELECT * FROM USER_IND_COLUMNS WHERE TABLE_NAME = 'FT25T' AND INDEX_NAME = 'XFT25TB';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                                                                      COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ -------------------------------------------------------------------------------- --------------- ------------- ----------- -------
XFT25TB                        FT25T                          FT07CODEMP                                                                                     1            22           0 ASC
XFT25TB                        FT25T                          FI16MODELO                                                                                     2             2           2 ASC
XFT25TB                        FT25T                          FT07CODIGO                                                                                     3            22           0 ASC
XFT25TB                        FT25T                          SYS_NC00006$                                                                                   4            34           0 DESC

I wonder if there is any way to get the column name FT25ORDIMP from the virtual column SYS_NC00006$. I found no way to make that reference directly.

sql oracle índices

  • https://decipherinfosys.wordpress.com/2009/02/12/check-for-column-expression-of-function-based-index-in-the-schema/ without access to Oracle for testing however

  • I didn’t know virtual could be indexed !

  • @Motta, in the case what is indexed is a physical column even, the Oracle itself that creates the virtual column and inserts in the index...

  • Another issue is also that I can see through the view USER_IND_EXPRESSIONS, the physical column of that item in the field COLUMN_EXPRESSION, but I can’t use this result in any WHERE, get as a string or anything, just view...

1 answer

0

I managed to solve creating a function in the bank, I leave here to whom it may interest.

CREATE OR replace FUNCTION COLUNAS_RAW_INDICES(p_tname IN VARCHAR2, p_cname IN VARCHAR2) RETURN VARCHAR2 AS l_cursor INTEGER DEFAULT DBMS_SQL.open_cursor; l_n NUMBER; l_long_val VARCHAR2(4000); l_long_len NUMBER; l_buflen NUMBER := 4000; l_curpos NUMBER := 0; BEGIN DBMS_SQL.PARSE(l_cursor, 'SELECT DATA_DEFAULT FROM USER_TAB_COLS WHERE ' || 'TABLE_NAME = :TableName ' || 'AND COLUMN_NAME = :ColumnName', DBMS_SQL.NATIVE); DBMS_SQL.DEFINE_COLUMN_LONG(l_cursor, 1); DBMS_SQL.BIND_VARIABLE( l_cursor, ':TableName', p_tname); DBMS_SQL.BIND_VARIABLE( l_cursor, ':ColumnName', p_cname); l_n := DBMS_SQL.EXECUTE(l_cursor); IF (DBMS_SQL.FETCH_ROWS(l_cursor) > 0) THEN DBMS_SQL.COLUMN_VALUE_LONG(l_cursor, 1, l_buflen, l_curpos, l_long_val, l_long_len); END IF; DBMS_SQL.CLOSE_CURSOR(l_cursor); RETURN RTRIM(LTRIM(REPLACE(l_long_val, '"', ''))); END COLUNAS_RAW_INDICES;

Thus, executing the command SELECT COLUNAS_RAW_INDICES('FT25T', 'SYS_NC00006$') FROM DUAL I can get like a VARCHAR2 the column I need.

Browser other questions tagged sql oracle índices

You are not signed in. Login or sign up in order to post.