45

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

CLOB?

YourMomzThaBomb
  • 1,311
  • 2
  • 15
  • 21

7 Answers7

36

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.

cletus
  • 616,129
  • 168
  • 910
  • 942
  • 4
    Longs and CLOBS are evil. Most functions for varchar do not work with them nor are they meant to. – dacracot Jan 05 '09 at 23:03
  • 3
    Yes but if you want more than 4000 characters with Oracle you don't have much choice. – cletus Jan 05 '09 at 23:12
  • In later versions more of the VARCHAR functions (such as substr) work with CLOBs. Not all, sure, but you generally do different things with MB length strings than ones with a couple of dozen characters. – Gary Myers Jan 05 '09 at 23:42
  • You can generally use functions in DBMS_LOB to do what you want. I agree 4k is too short for varchar2 given the sort of hardware we have now. – WW. Jan 05 '09 at 23:49
  • 5
    To be exact, Varchars are limited to 4000 **bytes**, which is not more that 4000 **characters**, but may be less when storing multibyte character data. See [Oracle Documentation on Data Types](https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1835) – Michael Schaefers Jan 30 '16 at 16:30
12

As I understand it, a VARCHAR(MAX) data type is a SQL Server 2005 specific way of specifying a text field that can either be small (up to 8000 characters in SQL Server) or big (up to 2GB in SQL Server). The database handles the change in storage behind the scenes as the content grows from the small range to the large range.

There is no equivalent in Oracle.

You either have a smallish bit of text in a VARCHAR2 - which is up to 32767 bytes in pl/sql and up to 4000 bytes in SQL (i.e. in a table definition) - or you have a potentially very big bit of text in a CLOB (which is a specialised BLOB).

Nick Pierpoint
  • 17,641
  • 9
  • 46
  • 74
4

In PL/SQL, VARCHAR2 can be up to 32767 bytes. For SQL the limit is 4000 bytes (which may be less than 4000 characters if you are using a multi-byte character set).

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
0

The Oracle 11g Gateway translates a Varchar(Max) to LONG Not very handy and causing major problems for our loading of SQL Server data into Oracle.

See following url for more details:- http://docs.oracle.com/cd/B28359_01/gateways.111/b31049/apa.htm

Raymond de Vries
  • 1
0

An approach I have used in the past (MS SQL, prior to Varchar(max)):

Put two columns in the table, one smallish Varchar (255, for example), and another Text. Then build your application so that it will use the Varchar column for small data, leaving the Text null. If the data is larger than the Varchar, leave that null and store it in the Text. This way, small data doesn't take up its own page on the server. The tradeoff here is that all applications using the data have to agree to this scheme, and have logic to account for it. But it works well.

I presume the same is true in Oracle, just substiture Varchar2 for Varchar, and CLOB for Text. I don't pretend to know what the right size for the varchar should be - that's data dependent, and also depends on the rest of the columns in the table.

  • 1
    When you can't use varchar(max) or nvarchar(max) I believe a cleaner solution to having two columns in SQL Server is to use the 'text in row' table option. You can specify a maximum limit, from 24 through 7,000 bytes, for the length of a text, ntext, and image string that can be stored in a data row. See the help for sp_tableoption. e.g. EXEC sp_tableoption N'MyTable', 'text in row', '1000' – Ross Bradbury Mar 15 '10 at 16:19
0

AFAIK, there is no equivalent. The closest you get in ORACLE is the CLOB, which has the same limitations that TEXT had in SQL Server back in the 'bad old days'.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • You can't do most of the string manipulation functions with TEXT that you can with VARCHAR(MAX), such as finding individual characters in a string, pattern matching, etc.. with the standard SQL functions. – Dave Markle Jan 31 '09 at 16:18
-1

There is no equivalent in Oracle up to 11g-r2. If you need it, migrate to another DMMS which supports it.

bernd_k
  • 11,558
  • 7
  • 45
  • 64