Contribute  :  Calendar  :  Advanced Search  :  Site Statistics  :  Directory  :  Files to download  :  Links for developers  :  Privacy policy  :  Web Resources  :  Polls  
    MacOSX Guru Site Information, tips and tricks, FAQ for Mac OS X developers    
 Welcome to MacOSX Guru Site
 Tuesday, December 11 2018 @ 08:33 AM UTC

ORACLE LONG TO VARCHAR2 conversion

   
ORACLEyes, ... 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)
 

What's Related

Story Options

ORACLE LONG TO VARCHAR2 conversion | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
 Copyright © 2018 MacOSX Guru Site
 All trademarks and copyrights on this page are owned by their respective owners.
Powered By Geeklog 
Created this page in 0.07 seconds