Re: Oracle view question

sameergn_at_gmail.com wrote:
: Hi,

: The source table has a column which is varchar2(100).
: If I create a view new_view on the column as
: select substr(column_name, 1, 20) new_col from table,
: then "desc new_view" command shows size of new_col as 60 i.e. 3 times
: the intended size. Why is that? Is it because of multibyte characters?

It is certainly related to utf-8 (i.e. multiple-byte, though utf-8 is not the same as older "multibyte" characters).

Not sure if "because" is the right word.

One "solution" is to use "substrb" (look it up though, I don't have the manual with me). substrb takes bytes, not characters. IFF your data does not have multiple-byte characters then that will work. That will be true if you have nothing but ascii data, or, if the original character set was an 8 bit character set.

The last point brings up the issue that this could be due to a misunderstanding within oracle about what character sets are in use. If the original table was not utf-8, and if the view presents its data in the same character set as the table, then presumably oracle would not need to magnify the width of the column in the view. However, I haven't played with those details so I don't know exactly how the various settings would interelate (or even if a view can present data in an alternate character set than the table, never even thought about that one before!).

--

This space not for rent.