Oracle PL/SQL - Implicit Conversion Between Subtypes with Base Types in Same Family

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. Unconstrained Subtypes

Introduction

If two subtypes have different base types in the same data type family, then one subtype can be implicitly converted to the other if the source value does not violate a constraint of the target subtype.

In the following code, the subtypes Word and Text have different base types in the same data type family.

The first assignment statement implicitly converts a Word value to Text.

The second assignment statement implicitly converts a Text value to Word.

The third assignment statement cannot implicitly convert the Text value to Word, because the value is too long.

Demo

SQL>
SQL>--  w  w  w.ja  va 2s.co  m
SQL> DECLARE
  2    SUBTYPE Word IS CHAR(6);
  3    SUBTYPE Text IS VARCHAR2(15);
  4
  5    verb       Word := 'run';
  6    sentence1  Text;
  7    sentence2  Text := 'Test!';
  8    sentence3  Text := 'test test.';
  9
 10  BEGIN
 11    sentence1 := verb;
 12    verb := sentence2;
 13    verb := sentence3;
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error:
character string buffer too small
ORA-06512: at line 13


SQL>

Related Topic