oracle-databaseencodingvarchar2

Oracle-DB: Reliable way to calculate the length of a field in CHAR from all_tab_columns.data_length?

I've written a very simple database access layer that maps C# objects onto database tables and back. It uses the information in all_tab_columns in order to do some input validation (mainly length checking). It works ok on ISO-encoded databases but on UTF-8 it produces wrong results. I tried (data_length / LENGTHB('ä')) which sometimes seems to work and sometimes doesn't. I'm aware it's a dirty hack, but I haven't found a pattern yet. Is there a reliable way to calculate the CHAR length of a VARCHAR2 field from data_length?

Solution

  • I found the answer on my own. ALL_TAB_COLUMNS provides a field CHAR_LENGTH that contains the maximum amount of characters in the column. Example:

    SELECT column_name, char_length FROM all_tab_columns WHERE table_name = 'SOME_TABLE';