Varchar2(CHAR) always in PLSQL?

If the DB is using utf8 as charset does it make sense to use varchar2 with byte semantics?

Consider a variable:

l_str varchar2(10 BYTE);

If I later assign values to it based on a query that will return the contents of a 10 char column I have no way of knowing how much bytes those characters will take, in utf8 that might be more than one byte per character.

So when using a multibyte character set shouldn't I always use the following?

l_str varchar2(10 CHAR);

Or to put it another way, is there any reason why you should use varchar2(10 byte) or varchar2(10) in PLSQL?

EDIT: The only reason I can think of for using byte is if you know for sure how many bytes the characters stored will need. So in this case you will allocate less memory.

Answers


Depending on the character set I think you just end up only getting half the space you might expect with a more restrictive set for instance. This is confirmed by the documentation

http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_sqltypes.htm#sthref367

id VARCHAR2(32 BYTE)

The id column contains only single-byte data, up to 32 bytes.

name VARCHAR2(32 CHAR)

The name column contains data in the database character set. If the database character set allows multibyte characters, then the 32 characters can be stored as more than 32 bytes.


I have AL32UTF8 charset with Oracle 11g. Oracle stores byte and char data with different lenght. e.g.--

create table t1(aa varchar2(1));
create table t2(aa varchar2(1 char));

Now execute--

select * from ALL_TAB_COLUMNS where table_name in ('T1','T2');

Check the DATA_LENGTH column where for byte datatype, it has 1 as value and for char it has 4 as value.

You may follow this link for ALL_TAB_COLUMNS columns description.


Need Your Help

301 Redirect rule to cover multple cases

regex apache .htaccess redirect

Is there any way I can do all the following ( and similar ) redirects with a rule

how to add two view controllers in UIPageViewcontroller

ios objective-c uipageviewcontroller

I m using UIPageViewController on iPad where I need to show a firstviewController in the first page and ContentViewController in the next page in landscape.