What Is the Equivalent of Varchar(Max) in Oracle

What is the equivalent of varchar(max) in Oracle?

Varchars are limited to 4000 characters in Oracle. Other than that, you have to use a LONG or a CLOB. Prefer CLOBs. LONGs are the older equivalent.

From this Oracle documentation:

LOBs vs. LONG and LONG RAW

LOBs are different from the older LONG
and LONG RAW datatypes in many ways.

• The maximum size of a LOB is 4 Gigabytes versus 2 Gigabytes for LONG
  and LONG RAW.
• You can use random as well as sequential access methods on LOBs; you
  can only use sequential access methods
  on LONG and LONG RAW.
• LOBs (except NCLOBs) can be attributes of an object type that you
  define.
• Tables can have multiple LOB columns, but can have only one LONG or
  LONG RAW column.

Migration of existing LONG and LONG
Raw attributes to LOBs is recommended
by Oracle. Oracle plans to end support
of LONG and LONG RAW in future
releases. See Oracle8 Migration for
more information on migration.

VARCHAR(MAX) versus VARCHAR(n) in Oracle

Because it doesn't work in Oracle! You could declare all columns as VARCHAR2(4000) if you wanted, but it isn't recommended by any means. There will be no difference in storage or performance of the database, but:

• you lose a constraint on the sensible values that can be stored in the column
• some client applications will allocate 4000 bytes of memory to receive data from the column when (say) 10 is all it will ever contain.

VARCHAR max length is 4000 but only 2666 byte long Thai text can be stored

The problem

When describing a VARCHAR, you should supply a unit, e.g. VARCHAR2(200 BYTE) or VARCHAR2(200 CHAR). If you omit the unit, the default is BYTE (see Oracle Database Concepts, chapter Oracle Datatypes). This seems to be like a minor detail, but becomes quite severe, when you have multi byte character sets.

Situation up to 11g

Unfortunately there is a hard limit on the maximum size of a VARCHAR2 column. It is 4000 BYTEs (!) (see Oracle Database Reference, chapter Oracle Datatypes) up to Oracle 11g and . This is a hard limit and there is no way around this. The only way around this is a CLOB column.

Solution for 12c

The situation is different on Oracle 12c. There you can use the parameter MAX_STRING_SIZE = EXTENDED to lift the limit up to 32767 BYTEs (see Oracle Database Language Reference, chapter Data Types and Oracle Database Reference, chapter Initialization Parameters). So the obvious solution is: Upgrade to Oracle 12c, set MAX_STRING_SIZE = EXTENDED according to the documentation and alter your table definition. You might loose some indexes when altering your table, because previously to 12c not indexes could not hold VARCHAR2 values with more than 4000 BYTEs and there might still be some limitation. (I have to check the problem with the indexes and if it can be fixed by rebuilding the indexes).

Solution: Change database encoding

You could try to change your native database encoding (the way your database maps CHARs to BYTEs). For this you usually have to create a new database and provide an appropriate parameter for NLS_CHARACTERSET. This is a very big change in how your database operates and might have several side effects. If you ever try to add characters in a different encoding, you might be out of luck (i.e. you cannot store them in your database). So I would not suggest this solution.

Solution: Switch to CLOB

Usually it is not necessary to provide arbitrary queries on such big text fields. You might try to identify the queries selecting on the big text column and migrate them to Oracle Text on a CLOB column. But this is a very big change and might not be possible with your existing schema or your application. You might end up with a bunch of "INSTEAD OF" triggers and with some missing constraint checks (involving the newly created CLOB column).

Solution: Use XML

Instead of a CLOB you could try to store your string as a XML column. Maximum size for these is 4GB. It will hurt your performance, you will have to provide INSTEAD OF triggers and you might lose some constraints, but it could work for you.

Postgres equivalent of Oracle's VARCHAR2(N CHAR)?

Postgres doesn't make a distinction between "char length" and "byte length" when declaring the maximum length of a varchar column.

The limit is always specified as the number of characters.

So the equivalent of Oracle's VARCHAR2(31 CHAR) is VARCHAR(31) in Postgres.

There is however, no equivalent for Oracle's VARCHAR2(31 byte).

Equivalent of varchar(max) in MySQL?

The max length of a varchar is subject to the max row size in MySQL, which is 64KB (not counting BLOBs):

VARCHAR(65535)

However, note that the limit is lower if you use a multi-byte character set:

VARCHAR(21844) CHARACTER SET utf8

---

Here are some examples:

The maximum row size is 65535, but a varchar also includes a byte or two to encode the length of a given string. So you actually can't declare a varchar of the maximum row size, even if it's the only column in the table.

mysql> CREATE TABLE foo ( v VARCHAR(65534) );
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

But if we try decreasing lengths, we find the greatest length that works:

mysql> CREATE TABLE foo ( v VARCHAR(65532) );
Query OK, 0 rows affected (0.01 sec)

Now if we try to use a multibyte charset at the table level, we find that it counts each character as multiple bytes. UTF8 strings don't necessarily use multiple bytes per string, but MySQL can't assume you'll restrict all your future inserts to single-byte characters.

mysql> CREATE TABLE foo ( v VARCHAR(65532) ) CHARSET=utf8;
ERROR 1074 (42000): Column length too big for column 'v' (max = 21845); use BLOB or TEXT instead

In spite of what the last error told us, InnoDB still doesn't like a length of 21845.

mysql> CREATE TABLE foo ( v VARCHAR(21845) ) CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

This makes perfect sense, if you calculate that 21845*3 = 65535, which wouldn't have worked anyway. Whereas 21844*3 = 65532, which does work.

mysql> CREATE TABLE foo ( v VARCHAR(21844) ) CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)

Using MAX() in VARCHAR Field

You get D010 because alphabetically, D010 comes after D0012 or said another way, D01 comes after D00 and therefore anything that is D01x comes after anything that starts D00x.

What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte?

You can declare columns/variables as varchar2(n CHAR) and varchar2(n byte).

n CHAR means the variable will hold n characters. In multi byte character sets you don't always know how many bytes you want to store, but you do want to garantee the storage of a certain amount of characters.

n bytes means simply the number of bytes you want to store.

varchar is deprecated. Do not use it.
What is the difference between varchar and varchar2?

---

Related Topics