What Is The Max Size of Varchar2 in Pl/Sql and Sql

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.

What is the size limit for a varchar2 PL/SQL subprogram argument in Oracle?

In PL/SQL procedure it may be up to 32KB

Futher information here:
http://it.toolbox.com/blogs/oracle-guide/learn-oracle-sql-and-plsql-datatypes-strings-10804

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.

Oracle not able to insert data into varchar2(4000 char) column

The problem is that the 4000 byte limit is a hard limit, regardless of whether the datatype is defined as VARCHAR2(4000 CHAR), VARCHAR2(4000 BYTE), or NVARCHAR2(4000). This means that multibyte characters will always have the chance of overflowing a max-size non-CLOB text column.

Oracle's table of Datatype Limits shows each of the VARCHAR2 variants as holding a max of 4000 bytes. And this is precisely the problem you have encountered.

You do have the option of increasing the max size for VARCHAR2 in your Oracle 12c database to 32k.

Here's how to do it: MAX_STRING_SIZE documentation

This is not something to be done without careful consideration: once you change your database to use extended VARCHAR2 strings you cannot go back.
Nevertheless, if your database is all your own and you like the idea of having 32K strings, then this feature was created specifically to address your situation.

Be careful to read the details of pluggable databases, container databases as they require different upgrade techniques. This is a change that cuts across the entire database so you want to get it right.

Why shouldn't I make all my PL/SQL-only VARCHAR2 32767 bytes?

It looks like this is one of the areas where the PL/SQL functionality has evolved over releases when Oracle has implemented different optimizations.

Note this also means some of the answers listed in the OP are also release specific even that is not explicitly mentioned in those questions/answers. When the time pass by and use of older Oracle releases ends (me daydreaming ?) that information will became outdated (might take decades thought).

The conclusion above is backed with the following quote from chapter 12 Tuning PL/SQL Applications for Performance of PL/SQL Language Reference 11g R1:

Declare VARCHAR2 Variables of 4000 or More Characters

You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

This issue is no longer mentioned in 11g R2 nor 12c R1 version of the document. This is in line with the evolution of the chapter 3 PL/SQL Datatypes.

Answer:

Since 11gR2 it makes no difference from memory use of point of view to use varchar2(10) or varchar2(32767). Oracle PL/SQL compiler will take care of the dirty details for you in an optimal fashion !

For releases prior to 11gR2 there is a cutoff-point where different memory management strategies are used and this is clearly documented in each release's PL/SQL Language Reference.

The above only applies to PL/SQL-only variables when there is no natural length restriction that can be derived from the problem domain. If a varchar2-variable represents a GTIN-14 then one should declare that as varchar2(14).

When PL/SQL-variable interfaces with a table column use %type-attribute as that is the zero-effort way to keep you PL/SQL-code and database structure in sync.

Memory test results:

I run a memory analysis in Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 with the following results:

str_size iterations UGA   PGA
-------- ---------- ----- ------
10       100        65488 0
10       1000       65488 65536
10       10000      65488 655360
32767    100        65488 0
32767    1000       65488 65536
32767    10000      65488 655360

Because the PGA changes are identical and depend only on iterations and not str_size I conclude the varchar2 declared size doesn't matter. The test might be too naïve though - comments welcome !

The test script:

-- plsql_memory is a convenience package wrapping sys.v_$mystat s and
-- sys.v_$statname tables written by Steven Feuerstein and available in the
-- code-zip file accompanying his book.

set verify off

define str_size=&1
define iterations=&2

declare
  type str_list_t is table of varchar2(&str_size);
begin
  plsql_memory.start_analysis;

  declare
    v_strs str_list_t := str_list_t();
  begin
    for i in 1 .. &iterations
    loop
      v_strs.extend;
      v_strs(i) := rpad(to_char(i), 10, to_char(i));
    end loop;
    plsql_memory.show_memory_usage;
  end;

end;
/

exit

Test run example:

$ sqlplus -SL <CONNECT_STR> @memory-test.sql 32767 10000

Change in UGA memory: 65488 (Current = 1927304)
Change in PGA memory: 655360 (Current = 3572704)

PL/SQL procedure successfully completed.

$

---

Related Topics