Oracle view question

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?

Thanks,
Sameer.

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.