VSIZE

The VSIZE function returns the number of bytes in the internal representation of an expression.

SQL syntax

VSIZE(Expression)

Parameters

VSIZE has the parameter:

Parameter Description

Expression

Expression that is passed to the VSIZE function. The function returns the number of bytes in the internal representation of the expression.

Description

  • If the value of expression is NULL, NULL is returned. Otherwise, the data type returned is NUMBER.

  • The VSIZE function does not support LOB data directly. However, LOBs can be passed in as arguments through implicit data conversion.

Examples

Use the VSIZE function to return the number of bytes in the last_name column of the employees in department 10. 

Command> SELECT last_name, VSIZE (last_name) "BYTES" FROM employees
         WHERE department_id = 10 ORDER BY employee_id;
< Whalen, 6 >
1 row found.

This example illustrates how to use the VSIZE function on a column defined with the CLOB data type. This example first creates the vsize_varchar2 table with the col1 column defined with the VARCHAR2(200) data type. It then creates the vsize_clob table with the col1 column defined with the CLOB data type. The same string is inserted into col1 for each table. The VSIZE function is then used to return the number of bytes in the internal representation of the data in col1. For the vsize_clob table, the CAST function is used to cast the CLOB data type as the VARCHAR2(200) data type (for the col1 column). As illustrated, the VSIZE function returns the same result for the same query on the vsize_varchar2 table as on the vsize_clob table. 

Command> CREATE TABLE vsize_varchar2 (col1 VARCHAR2 (200));
Command> CREATE TABLE vsize_clob (col1 CLOB);
Command> INSERT INTO vsize_varchar2 VALUES ('This is a test to illustrate how to 
            use the VSIZE function on a column defined with the CLOB 
            data type');
1 row inserted.
Command> INSERT INTO vsize_clob VALUES ('This is a test to illustrate how to 
            use the VSIZE function on a column defined with the CLOB 
            data type');
1 row inserted.
Command> SELECT VSIZE (col1) FROM vsize_varchar2;
< 102 >
1 row found.
Command> SELECT VSIZE (CAST (col1 AS VARCHAR2 (200))) FROM vsize_clob;
< 102 >
1 row found.

This example illustrates the difference between the LENGTH and the VSIZE functions. The LENGTH function returns the length of SYSDATE. The VSIZE function returns the number of bytes in the internal representation of SYSDATE. 

Command> SELECT SYSDATE FROM dual;
< 2021-03-07 10:47:40 >
1 row found.
Command> SELECT LENGTH (SYSDATE) FROM dual;
< 19 >
1 row found.
Command> SELECT VSIZE (SYSDATE) FROM dual;
< 7 >
1 row found.