Oracle PL/SQL - CHAR and VARCHAR2 Variables Size

Assigning or Inserting Too-Long Values

If the value that you assign to a character variable is longer than the maximum size of the variable, an error occurs.

Demo

SQL>
SQL> DECLARE-- from ww  w .j a  v a  2s .  c  o  m
  2    c VARCHAR2(3 CHAR);
  3  BEGIN
  4    c :=  'abc  ';
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error:
character string buffer too small
ORA-06512: at line 4


SQL>

If you insert a character variable into a column, and the value of the variable is longer than the defined width of the column, an error occurs.

Demo

SQL>
SQL> DROP TABLE t;
SQL> CREATE TABLE t (c CHAR(3 CHAR));
SQL>-- from   w w  w  .  j  a  va  2 s  . com
SQL> DECLARE
  2    s VARCHAR2(5 CHAR) := 'abc  ';
  3  BEGIN
  4    INSERT INTO t(c) VALUES(s);
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-12899: value too large for column
"SYS"."T"."C" (actual: 5, maximum: 3)
ORA-06512: at line 4


SQL>

Related Topics