ORACLE LONG TO VARCHAR2 conversion

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 
                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 an compound index with 2 functions in it, like (UPPER(field1), field2 DESC) is some of your table)

0 comments



http://www.macosxguru.net/article.php?story=20180714115808392