1

Good morning, I have a problem in plsql because I have to know how many char a str can contain(I use oracle 11). The code in plsql is this:

str VARCHAR2(32767):= NULL;

str is a string of maximum size 32767 byte.

But what is the max number of characters which this string can contain?

Thanks for the help.

Milen
  • 8,697
  • 7
  • 43
  • 57
user3868055
  • 61
  • 5

2 Answers2

3

I noticed your question was about the number of char you can store, not the number of bytes. The difference is here (see SO answer Difference between BYTE and CHAR in column datatypes):

Let us assume the database character set is UTF-8, which is the recommended setting in recent versions of Oracle. In this case, some characters take more than 1 byte to store in the database.

If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store, e.g. non-English characters.

By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes.

Sample about the difference between chars and bytes: http://mothereff.in/byte-counter

Also, that character length semantics do not affect the 4000 byte maximum (Oracle 11g, see doc Oracle doc) length for a VARCHAR2. Declaring a VARCHAR2(4000 CHAR) will allow fewer than 4000 characters if some of the characters require multiple bytes of storage.

Community
  • 1
  • 1
Nicolas R
  • 13,812
  • 2
  • 28
  • 57
  • 2
    Do you think you should have included attribution to [the answer you copied this from](http://stackoverflow.com/a/81492/266304)? – Alex Poole Jul 29 '14 at 08:52
  • Alex, my mistake, I added the reference. Should I have used duplicate in that case, as I added info linked to the Oracle version he mentioned and the test showing the difference between char count and byte length – Nicolas R Jul 29 '14 at 08:56
  • Thanks. I'm not sure the question is an exact duplicate as it's specifically about hitting the 32k limit, I think, and the behaviour at that point. – Alex Poole Jul 29 '14 at 08:57
2

As explained in the documentation (v11.2) varchar2 has maxsize respectively:

  • PL/SQL: 32767 bytes
  • SQL: 4000 bytes in SQL.

To be sure that, independently by character set, you store N charge, you have to specify:

str VARCHAR2(32767 CHAR):= NULL;

By the way the number of bytes needed to store a character will depend upon the character set.

Starting from version 12.1, VARCHAR2 is extended to 32767 bytes.

Luca Davanzo
  • 21,000
  • 15
  • 120
  • 146
  • You might add as a that in version 12c SQL VARCHAR2 length is possible to extend to 32,767 bytes. – user2672165 Jul 29 '14 at 07:57
  • `VARCHAR2(32767 CHAR)` doesn't mean you can have 32k multi-byte characters though; you're still limited to the overall 32k byte limit for the data type. For smaller sizes it makes a difference, e.g. `VARCHAR2(100 CHAR)` would allow 100 multi-byte characters so total storage may be more than 100 bytes; but not right at the top of the data type limit. – Alex Poole Jul 29 '14 at 08:47