Saturday, July 14 2018 @ 11:58 AM BST
Contributed by: Admin
yes, ... we know: don't use LONG, don't use LONG...
But LONG is used by lot of legacy meta-data tables in ORACLE... so in case you need to use their content as a VARCHAR2 (to concat, subset, ...) then you have no choice to have a way to convert them to VARCHAR2 in a way that can be used in a SELECT.
Here is a quick solution - using ALL_IND_EXPRESSIONS as data source - of course limited to VARCHAR2 MAXLEN (4000 char) but it's better than nothing...
select index_name, column_position, dbms_lob.substr(sys_dburigen(index_owner, index_name, column_position, column_expression, 'text()').getclob(),4000,1) as ce
Note that it's important that the list of fields passed to sys_dburigen leads to an UNIQUE row in the target table!
If not, you will get an ORA-19003 error, pretending a XML Schema is missing, which is quite annoying because this message has little connection the source of the problem, to say the least…
(if you don't believe me, remove column_position and have an compound index with 2 functions in it, like (UPPER(field1), field2 DESC) is some of your table)