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.

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).

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).