aditi

• May 24th, 2006

 

Length function is meant to return the length of the string.

length('abc')=3

vsize returns the size of the oracle datatype in bytes.

vsize('abc') = 3 as character occupies 1 byte.

jsridevi

 Profile Answers by jsridevi 

• Jan 22nd, 2010

 

Length counts number of characters.
vsize function counts number of bytes occupied by the given string or column or number.

RW Johnson

 Profile Answers by RW Johnson 

• Jun 28th, 2010

 

So what is the difference between characters and bytes. Well that depends upon the character set/ type description. So

declare
  s1 varchar2(20) ;
  s2 NVARCHAR2(20) ;
  l_size integer ;
  v_size integer ;
begin
  s1 := 'abc' ;
  s2 := 'abc' ;

  select length(s1), vsize(s1) into l_size, v_size from dual ;
  dbms_output.put_line( 'for s1 Length=' || to_char(l_size) || ' Vsize =' || to_char(v_size) );

  select length(s2), vsize(s2) into l_size, v_size from dual ;
  dbms_output.put_line( 'for s2 Length=' || to_char(l_size) || ' Vsize =' || to_char(v_size) );

end ;

Produces the following:
for s1 Length=3 Vsize =3
for s2 Length=3 Vsize =6