ORACLE LONG TO VARCHAR2 conversion
14/07/18 12:49
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 from all_ind_expressions ; |
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 a compound index with 2 functions in it, like (UPPER(field1), field2 DESC) is some of your table)