SQL & PL/SQL

I have several questions regarding space allocation for VARCHAR2 datatypes:

1) Based on the excerpts below, am I correctly concluding that Oracle allocates space for VARCHAR2 columns in a table differently than it does for VARCHAR2 PL/SQL variables?

2) Can someone provide code that demonstrates the behavior in the example (in bold) in the first excerpt? I've tried writing my own (see below), but the output from my code seems to show that a 500 byte string stored in a VARCHAR2(1999 BYTE) PL/SQL variable occupies only 500 bytes of space -- not 1999. Perhaps the result of calling VSIZE() on the PL/SQL variables doesn't indicate the true amount of space consumed by these variables??

Excerpt 1, from the Oracle 10g PL/SQL Reference manual:

"Small VARCHAR2 variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a VARCHAR2 that is 2000 bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a VARCHAR2 variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. *For example, if you assign the same 500-byte value to a VARCHAR2(2000 BYTE) variable and to a VARCHAR2(1999 BYTE) variable, the former takes up 500 bytes and the latter takes up 1999 bytes.*"

Excerpt 2, from the Oracle 10g Database Concepts manual:

"The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.

For example, assume you declare a column VARCHAR2 with a maximum size of 50 characters. In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only the 10 characters (10 bytes), not 50."

References:

[http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref732]
[http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3780]

declare
  l_varchar2_1999_var VARCHAR2(1999 BYTE) := NULL;
  l_varchar2_2000_var VARCHAR2(2000 BYTE) := NULL;  
  l_var_1999_vsize PLS_INTEGER := 0;
  l_var_2000_vsize PLS_INTEGER := 0;  
  l_str_len        PLS_INTEGER := 500;
begin
  -- Create a string of length l_str_len bytes
  for i in 1 .. l_str_len loop
    l_varchar2_1999_var := l_varchar2_1999_var || 'X';
    l_varchar2_2000_var := l_varchar2_2000_var || 'X';
  end loop;

  -- PL/SQL VARCHAR2 variables 
  select vsize(l_varchar2_1999_var), vsize(l_varchar2_2000_var)
    into l_var_1999_vsize, l_var_2000_vsize
    from dual;
  dbms_output.put_line('Bytes occupied by value in VARCHAR2(1999) variable: ' || l_var_1999_vsize);
  dbms_output.put_line('Bytes occupied by value in VARCHAR2(2000) variable: ' || l_var_2000_vsize);

end;

Bytes occupied by value in VARCHAR2(1999) variable: 500
Bytes occupied by value in VARCHAR2(2000) variable: 500
PL/SQL procedure successfully completed.

Edited by: user1983440 on Jun 12, 2009 7:54 AM - simplified code