Oracle PL/SQL - PL SQL Data Type VARCHAR2 Type

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. CHAR and VARCHAR2 Variables Size

Introduction

To fix the problems of fixed-length variables, Oracle introduced the variable-length data type VARCHAR2(N).

You can declare the maximum possible length of the string that can be stored in the variable.

The actual string will not be padded.

The maximum length of a string declared as VARCHAR2 is also 32,767 bytes in PL/SQL with a SQL restriction of 4,000 bytes. 

declare
   variable1_tx VARCHAR2(number of characters);
   ...

With VARCHAR2, you can be sure that you'll end up with exactly what you placed in the variable.

You can now detect the actual amount of data you're working with.

The following code takes the previous example and changes the data type:

Demo

SQL>
SQL> declare-- from   w  w w .  j av  a2 s  .  c  o  m
  2       v_tx VARCHAR2(5);
  3  begin
  4       v_tx:='A';
  5       DBMS_OUTPUT.put_line(length(v_tx));
  6       DBMS_OUTPUT.put_line('<'||v_tx||'>');
  7       v_tx:='A  ';
  8       DBMS_OUTPUT.put_line(length(v_tx));
  9       DBMS_OUTPUT.put_line('<'||v_tx||'>');
 10  end;
 11  /
1
<A>
3
<A  >

PL/SQL procedure successfully completed.
SQL>

Related Topic