PL/SQL Data Types

 PL/SQL Data Types

SQL Data Types

Different Maximum Sizes

 Additional PL/SQL Constants for BINARY_FLOAT and BINARY_DOUBLE

 Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE

• SIMPLE_FLOAT, a subtype of SQL data type BINARY_FLOAT

• SIMPLE_DOUBLE, a subtype of SQL data type BINARY_DOUBLE

CHAR and VARCHAR2 Variables

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.

SQL> 
SQL> set serveroutput on
SQL> 
SQL> DECLARE
  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> 

Similarly, 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 

SQL> 
SQL> DROP TABLE t;

Table dropped.

SQL> CREATE TABLE t (c CHAR(3 CHAR));

Table created.

SQL> set serveroutput on
SQL> 
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> 

To strip trailing blanks from a character value before assigning it to a variable or inserting it into a column, use the RTRIM function,

SQL> 
SQL> DECLARE
  2    c VARCHAR2(3 CHAR);
  3  BEGIN
  4    c := RTRIM('abc  ');
  5    INSERT INTO t(c) VALUES(RTRIM('abc  '));
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> select * from t;

C
---------
abc

SQL> 

Declaring Variables for Multibyte Characters

 When declaring a CHAR or VARCHAR2 variable, to ensure that it can always hold n characters in any multibyte character set, declare its length in characters—that is, CHAR(n CHAR) or VARCHAR2(n CHAR), where n does not exceed FLOOR(32767/4) = 8191.

Differences Between CHAR and VARCHAR2 Data Types

• Predefined Subtypes

The CHAR data type has one predefined subtype in both PL/SQL and SQL—CHARACTER.

The VARCHAR2 data type has one predefined subtype in both PL/SQL and SQL, VARCHAR, and an additional predefined subtype in PL/SQL, STRING.

• Blank-Padding

• The value that you assign to a variable is shorter than the maximum size of the variable.

• The value that you insert into a column is shorter than the defined width of the column.

• The value that you retrieve from a column into a variable is shorter than the maximum size of the variable.

Example 3-1 CHAR and VARCHAR2 Blank-Padding Difference 

SQL> 
SQL> DECLARE
  2    first_name  CHAR(10 CHAR);
  3    last_name   VARCHAR2(10 CHAR);
  4  BEGIN
  5    first_name := 'John ';
  6    last_name  := 'Chen ';
  7   
  8    DBMS_OUTPUT.PUT_LINE('*' || first_name || '*');
  9    DBMS_OUTPUT.PUT_LINE('*' || last_name || '*');
 10  END;
 11  /
*John      *
*Chen *

PL/SQL procedure successfully completed.

SQL> 

• Value Comparisons

The SQL rules for comparing character values apply to PL/SQL character variables. Whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2, nonpadded comparison semantics apply; otherwise, blank-padded semantics apply.

LONG and LONG RAW Variables

Oracle supports the LONG and LONG RAW data types only for backward compatibility with existing applications. For new applications:

• Instead of LONG, use VARCHAR2(32760), BLOB, CLOB or NCLOB.

• Instead of LONG RAW, use BLOB.

You can insert any LONG value into a LONG column. You can insert any LONG RAW value into a LONG RAW column. You cannot retrieve a value longer than 32,760 bytes from a LONG or LONG RAW column into a LONG or LONG RAW variable.

You can insert any CHAR or VARCHAR2 value into a LONG column. You cannot retrieve a value longer than 32,767 bytes from a LONG column into a CHAR or VARCHAR2 variable.

You can insert any RAW value into a LONG RAW column. You cannot retrieve a value longer than 32,767 bytes from a LONG RAW column into a RAW variable.

ROWID and UROWID Variables

1.When you retrieve a rowid into a ROWID variable, use the ROWIDTOCHAR function to convert the binary value to a character value.
2.To convert the value of a ROWID variable to a rowid, use the CHARTOROWID function.If the value does not represent a valid rowid, PL/SQL raises the predefined exception SYS_INVALID_ROWID.

3.To retrieve a rowid into a UROWID variable, or to convert the value of a UROWID variable to a rowid, use an assignment statement; conversion is implicit.

BOOLEAN Data Type

The PL/SQL data type BOOLEAN stores logical values, which are the Boolean values TRUE and FALSE and the value NULL. NULL represents an unknown value.

The syntax for declaring an BOOLEAN variable is:

variable_name BOOLEAN

The only value that you can assign to a BOOLEAN variable is a BOOLEAN expression

Because SQL has no data type equivalent to BOOLEAN, you cannot:

• Assign a BOOLEAN value to a database table column

• Select or fetch the value of a database table column into a BOOLEAN variable

• Use a BOOLEAN value in a SQL statement, SQL function, or PL/SQL function invoked from a SQL statement

You cannot pass a BOOLEAN value to the DBMS_OUTPUT.PUT or DBMS_OUTPUT.PUTLINE subprogram. To print a BOOLEAN value, use an IF or CASE statement to translate it to a character value

Example 3-2 Printing BOOLEAN Values

SQL> 
SQL> set serveroutput on
SQL> 
SQL> CREATE PROCEDURE print_boolean (b BOOLEAN) AUTHID DEFINER
  2  AS
  3  BEGIN
  4    DBMS_OUTPUT.put_line (
  5      CASE
  6        WHEN b IS NULL THEN 'Unknown'
  7        WHEN b THEN 'Yes'
  8        WHEN NOT b THEN 'No'
  9      END
 10    );
 11  END;
 12  /

Procedure created.

SQL> BEGIN
  2    print_boolean(TRUE);
  3    print_boolean(FALSE);
  4    print_boolean(NULL);
  5  END;
  6  /
Yes
No
Unknown

PL/SQL procedure successfully completed.

SQL> 

PLS_INTEGER and BINARY_INTEGER Data Types

The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:

• PLS_INTEGER values require less storage.

• PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

For efficiency, use PLS_INTEGER values for all calculations in its range.

Preventing PLS_INTEGER Overflow

A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data type.For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.

Example 3-3 PLS_INTEGER Calculation Raises Overflow Exception 

SQL> 
SQL> DECLARE
  2    p1 PLS_INTEGER := 2147483647;
  3    p2 PLS_INTEGER := 1;
  4    n NUMBER;
  5  BEGIN
  6    n := p1 + p2;
  7  END;
  8  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 6


SQL> 

Example 3-4 Preventing Example 3-3 Overflow

Predefined PLS_INTEGER Subtypes

 

PLS_INTEGER and its subtypes can be implicitly converted to these data types:

• CHAR

• VARCHAR2

• NUMBER

• LONG

All of the preceding data types except LONG, and all PLS_INTEGER subtypes, can be implicitly converted to PLS_INTEGER.

A PLS_INTEGER value can be implicitly converted to a PLS_INTEGER subtype only if the value does not violate a constraint of the subtype. 

Example 3-5 Violating Constraint of SIMPLE_INTEGER Subtype

SQL> 
SQL> DECLARE
  2    a SIMPLE_INTEGER := 1;
  3    b PLS_INTEGER := NULL;
  4  BEGIN
  5    a := b;
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 5


SQL> 

SIMPLE_INTEGER Subtype of PLS_INTEGER

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type that has the same range as PLS_INTEGER and has a NOT NULL constraint. It differs significantly from PLS_INTEGER in its overflow semantics.

If you know that a variable will never have the value NULL or need overflow checking, declare it as SIMPLE_INTEGER rather than PLS_INTEGER. Without the overhead of checking for nullness and overflow, SIMPLE_INTEGER performs significantly better than PLS_INTEGER.

SIMPLE_INTEGER Overflow Semantics

If and only if all operands in an expression have the data type SIMPLE_INTEGER, PL/SQL uses two's complement arithmetic and ignores overflows. Because overflows are ignored, values can wrap from positive to negative or from negative to positive.
 

230 + 230 = 0x40000000 + 0x40000000 = 0x80000000 = -231

-231 + -231 = 0x80000000 + 0x80000000 = 0x00000000 = 0

For example, this block runs without errors: 

SQL> 
SQL> 
SQL> DECLARE
  2    n SIMPLE_INTEGER := 2147483645;
  3  BEGIN
  4    FOR j IN 1..4 LOOP
  5      n := n + 1;
  6      DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
  7    END LOOP;
  8    FOR j IN 1..4 LOOP
  9     n := n - 1;
 10     DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
 11    END LOOP;
 12  END;
 13  /
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645

PL/SQL procedure successfully completed.

SQL> 

 

Expressions with Both SIMPLE_INTEGER and Other Operands

If an expression has both SIMPLE_INTEGER and other operands, PL/SQL implicitly converts the SIMPLE_INTEGER values to PLS_INTEGER NOT NULL.

The PL/SQL compiler issues a warning when SIMPLE_INTEGER and other values are mixed in a way that might negatively impact performance by inhibiting some optimizations.

Integer Literals in SIMPLE_INTEGER Range

Integer literals in the SIMPLE_INTEGER range have the data type SIMPLE_INTEGER. However, to ensure backward compatibility, when all operands in an arithmetic expression are integer literals, PL/SQL treats the integer literals as if they were cast to PLS_INTEGER.

User-Defined PL/SQL Subtypes

PL/SQL lets you define your own subtypes. The base type can be any scalar or user-defined PL/SQL data type specifier such as CHAR, DATE, or RECORD (including a previously defined user-defined subtype).

 

Subtypes can:

• Provide compatibility with ANSI/ISO data types

• Show the intended use of data items of that type

• Detect out-of-range values

Unconstrained Subtypes

An unconstrained subtype has the same set of values as its base type, so it is only another name for the base type. Therefore, unconstrained subtypes of the same base type are interchangeable with each other and with the base type. No data type conversion occurs.

To define an unconstrained subtype, use this syntax:

SUBTYPE subtype_name IS base_type

An example of an unconstrained subtype, which PL/SQL predefines for compatibility with ANSI, is:

SUBTYPE "DOUBLE PRECISION" IS FLOAT

Example 3-6 User-Defined Unconstrained Subtypes Show Intended Use

SQL> 
SQL> DECLARE
  2    SUBTYPE Balance IS NUMBER;
  3  
  4    checking_account        Balance(6,2);
  5    savings_account         Balance(8,2);
  6    certificate_of_deposit  Balance(8,2);
  7    max_insured  CONSTANT   Balance(8,2) := 250000.00;
  8  
  9    SUBTYPE Counter IS NATURAL;
 10  
 11    accounts     Counter := 1;
 12    deposits     Counter := 0;
 13    withdrawals  Counter := 0;
 14    overdrafts   Counter := 0;
 15  
 16    PROCEDURE deposit (
 17      account  IN OUT Balance,
 18      amount   IN     Balance
 19    ) IS
 20    BEGIN
 21      account  := account + amount;
 22      deposits := deposits + 1;
 23    END;
 24    
 25  BEGIN
 26    NULL;
 27  END;
 28  /

PL/SQL procedure successfully completed.

SQL> 

Constrained Subtypes

A constrained subtype has only a subset of the values of its base type.

If the base type lets you specify size, precision and scale, or a range of values, then you can specify them for its subtypes. The subtype definition syntax is:

SUBTYPE subtype_name IS base_type
  { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]

Otherwise, the only constraint that you can put on its subtypes is NOT NULL

SUBTYPE subtype_name IS base_type [ NOT NULL ]

Example 3-7 User-Defined Constrained Subtype Detects Out-of-Range Values

SQL> 
SQL> DECLARE
  2    SUBTYPE Balance IS NUMBER(8,2);
  3   
  4    checking_account  Balance;
  5    savings_account   Balance;
  6   
  7  BEGIN
  8    checking_account := 2000.00;
  9    savings_account  := 1000000.00;
 10  END;
 11  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 9


SQL> 

A constrained subtype can be implicitly converted to another constrained subtype with the same base type only if the source value does not violate a constraint of the target subtype.

Example 3-8 Implicit Conversion Between Constrained Subtypes with Same Base Type

 

SQL> 
SQL> DECLARE
  2    SUBTYPE Digit        IS PLS_INTEGER RANGE 0..9;
  3    SUBTYPE Double_digit IS PLS_INTEGER RANGE 10..99;
  4    SUBTYPE Under_100    IS PLS_INTEGER RANGE 0..99;
  5   
  6    d   Digit        :=  4;
  7    dd  Double_digit := 35;
  8    u   Under_100;
  9  BEGIN
 10    u := d;   -- Succeeds; Under_100 range includes Digit range
 11    u := dd;  -- Succeeds; Under_100 range includes Double_digit range
 12    dd := d;  -- Raises error; Double_digit range does not include Digit range
 13  END;
 14  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 12


SQL> 

Subtypes with Base Types in Same Data Type Family

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

In Example 3-9, 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.

 Example 3-9 Implicit Conversion Between Subtypes with Base Types in Same Family

 

SQL> 
SQL> 
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 := 'Hurry!';
  8    sentence3  Text := 'See Tom run.';
  9   
 10  BEGIN
 11    sentence1 := verb;  -- 3-character value, 15-character limit
 12    verb := sentence2;  -- 5-character value, 6-character limit
 13    verb := sentence3;  -- 12-character value, 6-character limit
 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>