Home » SQL & PL/SQL » SQL & PL/SQL » varchar2 byte or char semantics (db any version)
varchar2 byte or char semantics [message #676299] Wed, 29 May 2019 03:18 Go to next message
John Watson
Messages: 8052
Registered: January 2010
Location: Global Village
Senior Member
I have never really bothered about this before: I've always accepted the default (which I know can be changed at various levels) of BYTE. However, I've just been asked about it and can't come up with an answer.

I may be wrong, but I think this is how it works:

BYTE is a defined maximum length. If using unicode AL32UTF8 (assume this from now on) it may not be possible to fit that number of characters in the column.
CHAR is a defined maximum number of characters, and the number of bytes they will take up could be more than that: worst case, four times as many. There will be errors if the number of bytes goes over 4000, even if the number of CHARs has not been exceeded.

For several releases the nvarchar2 character set has been fixed length AL16UTF16, so does not have a choice.

What I'm hoping for is a general rule (which would of course have any number of exceptions) as to when to use BYTE and when to use CHAR. Any advice?

--update: this is particularly in regard to APEX.

Thank you for any insight.

[Updated on: Wed, 29 May 2019 03:31]

Report message to a moderator

Re: varchar2 byte or char semantics [message #676301 is a reply to message #676299] Wed, 29 May 2019 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66688
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My opinion: always use CHAR unless you have a very good reason to not do so.
Clients always think on number of characters not bytes. When they I want a field of length 20, this is characters never bytes, they don't care about how many bytes Oracle will take.
Now, the DBA has to explain that VARCHAR2(4000/32767 CHAR) is up to 4000/32767 characters but there is a software limit of 4000/32767 bytes.

Re: varchar2 byte or char semantics [message #676305 is a reply to message #676301] Wed, 29 May 2019 12:19 Go to previous message
John Watson
Messages: 8052
Registered: January 2010
Location: Global Village
Senior Member
Thank you, that makes sense. I guess I'll have to consider changing the default in all new databases from now on.
Previous Topic: json filter unusual behaviour
Next Topic: How to write SQL Query to get desire output
Goto Forum:
  


Current Time: Mon Nov 18 17:31:49 CST 2019