SQL & PL/SQL

you can use CLOB column.

Depends on where you are. Inside the SQL engine, varchar2 is limited to 4000 bytes (not characters - thus be careful of multi-byte charsets and total string size).

Inside the PL engine, a varchar2 can be up to 32KB in size.

Note that the following (a "+mixed+" SQL data type definition that can use PL methods & constructors) is invalid:

create or replace type TStrings is table of varchar2(32767);

This will compile successfully - but as the type is used by the SQL engine, the actual limit it 4000 and not 32767.

That aside, this definition does allow you to store a collection of varchar2 strings that (in theory) has no limit (with each line limited to 4000 bytes). Inside a SQL table, a column of this data type will be stored as a nested table. However, this is not really an ideal method of dealing with large strings.

The preferred way usually is to use the CLOB data type instead. It provides a far greater flexibility than dealing with a string collection. Also note that the old data type LONG has been discontinued, and is only supported for backwards compatibility purposes.

I always wonder why oracle has different size for VARCHAR2 in SQL and PL/SQL. Why they are not making VARCHAR2 in SQL as 32K. Any idea?

No idea. Probably a legacy thing. 4000 itself is not a special number ito 16 and 32 bit memory management - or related to the size of the size bytes.

Some languages, like (old) Pascal, uses the 1st byte as the size byte for the string. The largest value that can be stored is +$FF+ in a byte, which then limits the string to 256 characters (0..255). I believe a similar size-of-the-size bytes restricted a Unix file system in the old days to 2GB.

I do not see offhand that 4000 fit any of these cases.. but there's likely some kind of "+valid+" reason for it.

I think it has to do with the page size.

4000 is the max. available space in a 4K page (the remaining bytes that make up the full 4K are page management information)

This ensures that a VARCHAR column will always fit into a single page.

But then this is just a guess on my side...

castorp is on the right tracks...

When Ingres (the first relational database) was developed, the size of "pages" dictated how much information could be stored on a single row, and page sizes were dictated by physical disk block sizes. In the early versions the physical blocks were (if I recall correctly) addresses in 64K address spaces. These blocks had to store multiple rows and were split into 32 seperate rows (meaning a row could be referenced within a block, by a single byte with a couple of bits left for internal purposes (probably to mark whether a block was full/empty or contained data etc.). So, the early designs allocated 2K per row which was often sufficient for most applications at the time where data was being relationally split anyway. The rows could actually store 2000 bytes of information and the remaining 48 bytes were used for various internal information about the row (and I recall some were left "reserved for future use" too).

So the early days of relational databases were limited to 2000 bytes and most systems then were using single byte character sets, so the most any string could be was 2000 characters.

As time passed (for Ingres we're talking about when it moved from Ingres 6.4 to Ingres II back around the time of the millenium, so not that long ago), computer hardware had moved on drastically, and disks were using larger block sizes and able to store more information, and people wanted to store more in their rows on the database. Ingres II introduced new different page sizes that could be defined when tables were created, anything from 2K, 4K, 8K, 16K or even 32K page sizes allowing for a row to be anything up to 32K (allowing for internal overheads).

So, Ingres does allow for varchars up to 32K (32000 bytes):

E_US07DE CREATE or ALTER TABLE: invalid column format 'varchar' on column
    'x'.
    (Fri Feb 12 08:04:46 2010)

continue
* create table t_x (x varchar(32001))\g
Executing . . .

E_US07DE CREATE or ALTER TABLE: invalid column format 'varchar' on column
    'x'.
    (Fri Feb 12 08:04:52 2010)

continue
* create table t_x (x varchar(32000))\g
Executing . . .

continue
*

So, for Oracle, it is a legacy thing and it will be to do with page sizes and no doubt the internals of the SQL engine, and as we can see, Ingres is now far superior to Oracle {noformat}*hides behind sofa*{noformat}

And, for those wondering, Ingres does now support CLOB's and BLOB's

>
# LONG VARCHAR (or CLOB) – variable length character data more than 32,000 bytes and up to 2 Gig, you do not need to specify length
# LONG BYTE (or BLOB) – variable length binary data more than 32,000 bytes and up to 2 Gig, you do not need to specify length
>

;)

+<starts pelting Blu with empty beer cans, PL/SQL print outs and bad execution plans>+


;-)

{noformat}*ducks*{noformat}

Based on the 2Gb limit, I would say that it supports LONG and LONG RAW with some restrictions removed :-)

John