A useful example of when to use vsize function instead of length function in Oracle?

Question

It seems vsize() and length() return the same results. Does anyone know of a practical example of when to use vsize instead of length?

select vsize(object_name), length(object_name) from user_objects

Result:

/468ba408_LDAPHelper    20  20
/de807749_LDAPHelper    20  20
A4201_A4201_UK  14  14
A4201_PGM_FK_I  14  14
A4201_PHC_FK_I  14  14

Answer 1

Well, Length() takes a character argument (CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB) whereas VSize() takes just about any data type, so if you pass Length() a noncharacter data type there has to be an implicit conversion.

Length is also sensitive to to character sets.

drop table daa_test;
create table daa_test as select sysdate dt from dual;
alter session set nls_date_format = 'YYYY-MM-DD';
select vsize(dt)  from daa_test;
select length(dt) from daa_test;
alter session set nls_date_format = 'YYYY-MM-DD HH24:mi:ss';
select vsize(dt)  from daa_test;
select length(dt) from daa_test;

... giving ...

drop table daa_test succeeded.
create table succeeded.
alter session set succeeded.
VSIZE(DT)              
---------------------- 
7                      

1 rows selected

LENGTH(DT)             
---------------------- 
10                     

1 rows selected

alter session set succeeded.
VSIZE(DT)              
---------------------- 
7                      

1 rows selected

LENGTH(DT)             
---------------------- 
19                     

1 rows selected

VSize is really of use IMHO in understanding internal storage requirements of data.

Answer 2

see: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1897591221788