Character Datatypes

NLS parameters (V$NLS_PARAMETERS)

• NLS_LENGTH_SEMANTICS
  1. BYTE (default)
  2. CHAR
• NLS_CHARACTERSET
  Character encoding of CHAR, VARCHAR2 and CLOB datatypes
  eg: AL32UTF8 (UTF8 not recommended)
  1. Latin characters -> 1 byte
  2. Chinese characters(in BMP) -> 3 bytes
• NLS_NCHAR_CHARACTERSET
  Character encoding of NCHAR, NVARCHAR2 and NCLOB datatypes
  eg: AL16UTF16 (UTF8 not recommended)
  1. Latin characters -> 2 bytes
  2. Chinese characters(in BMP) -> 2 bytes

Char

• Store character data encoded by NLS_CHARACTERSET

• Char(2000 byte)
  We can store up to 666 Chinese characters(666 * 3 = 1998 bytes) and 2 Latin characters(2 * 1 = 2 bytes).

• Char(n char)
  We can store n chars as long as the length of encoded chars <= 2000 bytes

  1. 0 < n <= 666
     We can store n(bytes = 3*n <= 1998) Chinese chars.
  2. 666 < n <= 2000
     You can't store n(bytes = 3*n > 2000) Chinese chars.

VarChar2

• Store character data encoded by NLS_CHARACTERSET

• MAX_STRING_SIZE

  1. STANDARD 4000 bytes
  2. EXTENDED 32767 bytes

• VarChar2(4000 byte)
  We can store up to 1333 Chinese characters(1333 * 3 = 3999 bytes) and 1 Latin characters(1 * 1 = 1 bytes).

• VarChar2(n char)
  We can store n chars as long as the length of encoded chars <= 4000 bytes

  1. 0 < n <= 1333
     We can store n(bytes = 3*n <= 3999) Chinese chars.
  2. 1333 < n <= 4000
     You can't store n(bytes = 3*n > 2000) Chinese chars.

NChar

• Store unicode character data encoded by NLS_NCHAR_CHARACTERSET

For a Unicode Standard-enabled database(NLS_CHARACTERSET is set to AL32UTF8), we can also use datatype char to store unicode character data. NChar is NOT the preferred option to store unicode character data.

NVarChar2

• Store unicode character data encoded by NLS_NCHAR_CHARACTERSET
• MAX_STRING_SIZE
  1. STANDARD 4000 bytes
  2. EXTENDED 32767 bytes

For a Unicode Standard-enabled database(NLS_CHARACTERSET is set to AL32UTF8), we can also use datatype VarChar2 to store unicode character data. NVarChar is NOT the preferred option to store unicode character data.

Internal Storage

We can use dump function to inspect how data is stored.

Sample output Typ=96 Len=5: 230,177,137,49,32
Typ=96 represent data type is Char
Len=5 represent data is stored by 5 bytes
'230,177,137,49,32' represent the actual data

DROP TABLE zz_char;

CREATE TABLE zz_char (
   char_b_1       CHAR(5 BYTE),
   varchar2_b_1   VARCHAR2(4000 BYTE),
   nchar_1        NCHAR(5),
   nvarchar2_1    NVARCHAR2(2000)
);

INSERT INTO zz_char (
   char_b_1,
   varchar2_b_1,
   nchar_1,
   nvarchar2_1
) VALUES (
   '汉1',
   '汉2',
   '字3',
   '字4'
);

SELECT char_b_1, dump(char_b_1),
       varchar2_b_1, dump(varchar2_b_1),
       nchar_1, dump(nchar_1),
       nvarchar2_1, dump(nvarchar2_1)
FROM zz_char;

• 汉1 -> AL32UTF8 (230,177,137,49,32)
  230,177,137 -> 11100110 10110001 10001001 -> 11100110 10110001 10001001 -> 0110 1100 0100 1001 -> 0x 6C 49 -> 汉
  49 -> character 1
  32 -> blank character(blank-padded)

• 字3 -> AL16UTF16(91,87,0,51,0,32,0,32,0,32)
  91,87 -> 0x 5B 57 -> 字
  0,51 -> character 3
  0,32,0,32,0,32 -> 3 blank characters(blank-padded)

Character Datatypes Summary

Numeric Datatypes

Number Datatype

• Number[(p[,s])]
• p - Precision(total number of digits) (1~38)
• s - Scale(number of digits to the right of decimal point) (-84~127)
• 1~21 bytes (see dump in Internal Numeric Format section)
• Fixed-point Number(p,s)
• Floating-point Number

Internal Storage

SELECT '9.9999999999999999999999999999999999999e125',
       dump(9.9999999999999999999999999999999999999E125),
       '1e-130',
       dump(1E-130),
       '-1e-130',
       dump(-1E-130),
       '-9.9999999999999999999999999999999999999e125',
       dump(-9.9999999999999999999999999999999999999E125),
       0,
       dump(0),
       utl_raw.cast_to_number('FF65'),
       dump(utl_raw.cast_to_number('FF65') ),
       utl_raw.cast_to_number('00'),
       dump(utl_raw.cast_to_number('00') )
FROM dual;

• Positive Number 12345.6789
  Typ=2 Len=6: 195,2,24,46,68,90

  1. First byte 195 -> 1100 0011
     Sign -> positive, the first bit is 1
     Exponent -> 3, the following 7 bits (100 0011) - 64 (100 0000) = 3
  2. The other data (2,24,46,68,90) subtract 1
     01, 23, 45, 67, 89
  3. Get the result
     0.0123456789*100^3 = 12345.6789

• Negative Number -12345.6789
  Typ=2 Len=7: 60,100,78,56,34,12,102

  1. First byte 60 -> 0011 1100
     Sign -> negative, the first bit is 0
     Exponent -> 3, the following 7 bits (011 1100) transfer to ones’ complement (100 0011) - 64 (100 0000) = 3

  2. 100 subtract each of (100,78,56,34,12) then plus 1
     The last byte is 102 for every negative number (exclude it)
     01, 23, 45, 67, 89

     • Why 102? -2 > -2.0001 and 62,99,102 > 62,99,101,100,102

       
       
       Why 102?.png

  3. Get the result
     -0.0123456789*100^3 = -12345.6789

• Zero, Infinite, -Infinite

Float Datatype

• Float[(p)]
• p - binary precision (1-126)
• binary precision * 0.30103 = decimal precision (digits = ceil(bits / log(2,10))

Binary_Float Datatype

• Single-precision floating-point
• 5 bytes (4 data bytes + 1 length byte)

Internal Storage

IEEE754 Format wiki

wiki 4 bytes float - bits

wiki 4 byte float - result formula

1. Sign (-1) ^ b³¹ = (-1) ^ 0= (+1)
2. Exponent b₃₀...b₂₃ (0111 1100) = 124
3. (1.b₂₂...b₀)₂ = 1 + b₂₂ ^(-1) + b₂₁^(-2) + ... + b₀^(-23) = 1 + 1^(-2) = 1.25
4. Result = (+1) * 2 ^(124-127) * 1.25 = 0.15625

Oracle Format

select dump(0.15625f,16) from dual;
Typ=100 Len=4: be,20,0,0
Binary format 1011 1110 0010 0000 0000 0000 0000 0000

The first bit(sign bit) is different from IEEE754 format, all other bits are identical.

Binary_Double Datatype

• Double-precision floating-point
• 9 bytes (8 data bytes + 1 length byte)

Internal Storage

IEEE754 Format wiki

wiki 8 bytes double - bits

wiki 8 bytes double - result formula

Numeric Datatypes Summary

Date Datatypes

Alter date/timestamp/timestamp with tz format.
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ssxff';
alter session set nls_timestamp_tz_format = 'yyyy-mm-dd hh24:mi:ssxff tzr';

Datetime Datatypes

Date

Timestamp

• Timestamp[(fractional_seconds_precision)]
• fractional_seconds_precision (0-9, default 6)

Timestamp with time zone

• TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
• fractional_seconds_precision (0-9, default 6)

Timestamp with local time zone

• TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
• fractional_seconds_precision (0-9, default 6)

Internal Storage and Summary

DROP TABLE zz_date;

CREATE TABLE zz_date (
   date_1        DATE,
   timestamp_1   TIMESTAMP(9),
   timestamp_2   TIMESTAMP(9) WITH TIME ZONE,
   timestamp_3   TIMESTAMP(9) WITH LOCAL TIME ZONE
);

INSERT INTO zz_date (
   date_1,
   timestamp_1,
   timestamp_2,
   timestamp_3
) VALUES (
   ( '2019-03-04 17:30:40' ),
   ( '2019-03-04 17:30:40.000000256' ),
   ( '2019-03-04 17:30:40.000000512 +08:00' ),
   ( '2019-03-04 17:30:40.000001024' )
);

SELECT date_1,
       dump(date_1),
       timestamp_1,
       dump(timestamp_1),
       timestamp_2,
       dump(timestamp_2),
       timestamp_3,
       dump(timestamp_3)
FROM zz_date;

Interval Datatypes

Interval year to month

• INTERVAL YEAR [(year_precision)] TO MONTH
• year_precision (0-9, default 2)

Interval day to second

• INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
• day_precision (0-9, default 2)
• fractional_seconds_precision (0-9, default 6)

Internal Storage and Summary

DROP TABLE zz_interval;

CREATE TABLE zz_interval (
   timestamp_1       TIMESTAMP(9),
   interval_year_1   INTERVAL YEAR(9) TO MONTH,
   interval_day_1    INTERVAL DAY(9) TO SECOND(9)
);

INSERT INTO zz_interval ( timestamp_1, interval_year_1, interval_day_1 ) VALUES (
   ( '2019-03-05 17:30:40.000000256' ),
   INTERVAL '+100-2' YEAR ( 9 ) TO MONTH,
   INTERVAL '10 1:2:3.000000001' DAY TO SECOND ( 9 )
);

INSERT INTO zz_interval ( timestamp_1, interval_year_1, interval_day_1 ) VALUES (
   ( '2019-03-05 17:30:40.000000256' ),
   to_yminterval('100-2'),
   to_dsinterval('10 1:2:3.000000001')
);

SELECT timestamp_1,
       timestamp_1 + interval_year_1,
       timestamp_1 + interval_day_1,
       dump(interval_year_1),
       dump(interval_day_1)
FROM zz_interval;

LOB Datatypes

• Create file /tmp/abc.txt in database server file system
  sh-4.2# echo 123456 > /tmp/abc.txt
• Create oracle directory object
  CREATE DIRECTORY TMP_DIR AS '/tmp';
• Create lob table

CREATE TABLE zz_lob (
   lob_id    NUMBER,
   clob_1    CLOB,
   nclob_1   NCLOB,
   blob_1    BLOB,
   bfile_1   BFILE
);

CLOB

• Store large object data of characters encoded by NLS_CHARACTERSET
• Maximum size is (4 gigabytes - 1) * (database block size)

INSERT INTO zz_lob ( clob_1 ) VALUES (to_clob('123'));

NCLOB

• Store large object data of unicode character encoded by NLS_NCHAR_CHARACTERSET
• Maximum size is (4 gigabytes - 1) * (database block size)

INSERT INTO zz_lob ( nclob_1 ) VALUES (to_nclob('123'));

BLOB

• Store binary large object(image, video, audio..)
• Maximum size is (4 gigabytes - 1) * (database block size)

Store BLOB

DECLARE
   src_lob    BFILE := bfilename('TMP_DIR', 'abc.txt');
   dest_lob   BLOB;
BEGIN
   INSERT INTO zz_lob ( lob_id, blob_1 ) VALUES ( 2, empty_blob() ) RETURNING blob_1 INTO dest_lob;
   dbms_lob.open(src_lob, dbms_lob.lob_readonly);
   dbms_lob.loadfromfile(
      dest_lob   => dest_lob,
      src_lob    => src_lob,
      amount     => dbms_lob.getlength(src_lob)
   );
   dbms_lob.close(src_lob);
   COMMIT;
END;

BFILE

• Store a BFILE locator which points to a binary file in file system outside the database
• The BFILE locator (directory name and file name)
• Maximum size of BFILE is 4 gigabytes

Store BFILE

• bfilename

INSERT INTO zz_lob ( lob_id, bfile_1 ) VALUES (
   1,
   bfilename('TMP_DIR', 'abc.txt')
);

• Read BFILE by dbms_lob api

DECLARE
   fil   BFILE;
   pos   NUMBER := 1;
   bat   NUMBER := 5;
   buf   RAW(5);
BEGIN
   SELECT bfile_1 INTO fil FROM zz_lob WHERE lob_id = 1;
--   fil:=BFILENAME('TMP_DIR','abc.txt');

   dbms_lob.open(fil, dbms_lob.lob_readonly);

   FOR i IN 1..ceil(dbms_lob.getlength(fil) / bat) LOOP
      pos   := 1 + bat * ( i - 1 );
      dbms_output.put_line('i ' ||  i || ' start ' || pos || ' end ' || (pos + bat) );
      dbms_lob.read(fil, bat, pos, buf);
      dbms_output.put_line(utl_raw.cast_to_varchar2(buf) );
   END LOOP;
   dbms_lob.close(fil);
END;

• BFILE output

--i 1 start 1 end 6
12345
--i 2 start 6 end 11
6

Other Datatypes

Raw

• Raw(n)

• Store binary data/byte string

• Variable length

• MAX_STRING_SIZE

  1. STANDARD 2000 bytes
  2. EXTENDED 32767 bytes

DROP TABLE zz_raw;

CREATE TABLE zz_raw (
   raw_1   RAW(5),
   raw_2   RAW(5)
);

DELETE FROM zz_raw;

INSERT INTO zz_raw VALUES (
   utl_raw.cast_to_raw('a'),
   utl_raw.cast_from_number(1)
);

SELECT raw_1,
       dump(raw_1, 16),
       utl_raw.cast_to_varchar2(raw_1),
       raw_2,
       dump(raw_2, 16),
       utl_raw.cast_to_number(raw_2)
FROM zz_raw;

RowId URowId

RowId

Base64 string representing unique physical address of each row in heap-organized table.

• RowId Type
  RowId type: 0 is restricted, 1 is extended
  dbms_rowid.rowid_type(ROWID)
• Data Object Number
  The unique indentifier of the object.
  dbms_rowid.rowid_object(ROWID)
• File Number
  The relative number of the datafile in the tablespace.
  dbms_rowid.rowid_relative_fno(ROWID)
• Data Block Number
  The relative block number in the datafile.
  dbms_rowid.rowid_block_number(ROWID)
• Row Number
  The relative rownum within the block.
  dbms_rowid.rowid_row_number(ROWID)

URowId

Base 64 string representing the logical address of a row of an index-organized table.

• UROWID [(size)]
• size (4000bytes)

Reference

Oracle Datatypes

Fixed-point and floating-point representations of numbers
Introduction to Fixed Point Number Representation

How numbers are saved in oracle
Oracle dump number

Single-precision_floating-point
Double-precision_floating-point_format

ORAFAQ Data_types