Oracle Database Discussions

Hello,

I would appreciate it very much if you can help with the following question:

I'm writing a stored procedure that has to retrieve definition information of a function-based index on a column.
(This info is needed to restore the index definition after it has been dropped in order to change the column width.)

I'm using SQLTools 1.5.0.

The following query:

SELECT table_name, index_name, column_name
FROM dba_ind_columns
WHERE index_owner = 'ISH'
ORDER BY table_name, index_name, column_position;

displays

h6.
TABLE_NAME INDEX_NAME COLUMN_NAME
'AATEST' 'IDX_UPPER_AATID' 'SYS_NC00006$'

and SYS_NC00006$ appears to be a virtual column name.


The following query:

SELECT index_name, column_expression, table_name
FROM dba_ind_expressions
WHERE index_owner = 'ISH'
ORDER BY table_name, index_name, column_position;

displays the output needed:

h6.
TABLE_NAME INDEX_NAME COLUMN_EXPRESSION
'AATEST' 'IDX_UPPER_AATID' 'UPPER("AATID")'

but..., the 'but' here is that COLUMN_EXPRESSION is not a kind of a varchar but a type long with the following meaning /* Functional index expression defining the column */

CAST, CONVERT, TO_CHAR are not useful in this situation


So... how do I get the value of COLUMN_EXPRESSION as in the display of the last query?

Thanks,
Paul

Edited by: user13475178 on 14-dec-2010 3:09

Edited by: user13475178 on 14-dec-2010 3:11