Oracle | Data Types

Oracle Data Types and Subtypes Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Predefined Scalar Data Types

Data types that do not contain internal components

Data Dictionary Source DBA_TYPES

Defined Data Type and Subtype Definitions

Type	Primary Subtype	Secondary Subtype	Tertiary Subtype	Quaternary Subtype
type BLOB is BLOB_BASE;
type BOOLEAN is (FALSE, TRUE);
type VARCHAR2 is new CHAR_BASE;	subtype DBMS_ID is VARCHAR2(ORA_MAX_NAME_LEN); subtype DBMS_QUOTED_ID is VARCHAR2(ORA_MAX_NAME_LEN+2); subtype DBMS_ID_30 is VARCHAR2(30); subtype DBMS_QUOTED_ID_30 is VARCHAR2(32); subtype STRING is VARCHAR2; subtype LONG is VARCHAR2(32760); subtype ROWID is VARCHAR2(256); subtype "NVARCHAR2" is VARCHAR2 CHARACTER SET NCHAR_CS;
	subtype VARCHAR is VARCHAR2;	subtype "CHARACTER VARYING" is VARCHAR; subtype "CHAR VARYING" is VARCHAR;
	subtype RAW is VARCHAR2;	subtype "LONG RAW" is RAW(32760);
	subtype CHAR is VARCHAR2;	subtype CHARACTER is CHAR; subtype "NATIONAL CHARACTER" is CHAR CHARACTER SET NCHAR_CS; subtype "NATIONAL CHAR" is CHAR CHARACTER SET NCHAR_CS; subtype "NCHAR" is CHAR CHARACTER SET NCHAR_CS;
type CLOB is CLOB_BASE;	subtype "CHARACTER LARGE OBJECT" is CLOB; subtype "CHAR LARGE OBJECT" is CLOB; subtype "NATIONAL CHARACTER LARGE OBJECT" is CLOB CHARACTER SET NCHAR_CS; subtype "NCHAR LARGE OBJECT" is CLOB CHARACTER SET NCHAR_CS; subtype "NCLOB" is CLOB CHARACTER SET NCHAR_CS;
type DATE is DATE_BASE;
type TIMESTAMP is new DATE_BASE;	subtype TIMESTAMP_UNCONSTRAINED is TIMESTAMP(9);
type "TIMESTAMP WITH TIME ZONE" is new DATE_BASE;	subtype TIMESTAMP_TZ_UNCONSTRAINED is TIMESTAMP(9) WITH TIME ZONE;
type "TIMESTAMP WITH LOCAL TIME ZONE" is new DATE_BASE;	subtype TIMESTAMP_LTZ_UNCONSTRAINED is TIMESTAMP(9) WITH LOCAL TIME ZONE;
type "INTERVAL YEAR TO MONTH" is new DATE_BASE;	subtype YMINTERVAL_UNCONSTRAINED is INTERVAL YEAR(9) TO MONTH;
type "INTERVAL DAY TO SECOND" is new DATE_BASE;	subtype DSINTERVAL_UNCONSTRAINED is INTERVAL DAY(9) TO SECOND (9);
	subtype TIME_UNCONSTRAINED is TIME(9); subtype TIME_TZ_UNCONSTRAINED is TIME(9) WITH TIME ZONE;
type JSON is BLOB_BASE;
type NUMBER is NUMBER_BASE;	subtype FLOAT is NUMBER; -- NUMBER(126)	subtype REAL is FLOAT; -- FLOAT(63) subtype "DOUBLE PRECISION" is FLOAT;
	subtype INTEGER is NUMBER(38,0);	subtype INT is INTEGER;
		subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;	subtype NATURAL is BINARY_INTEGER range 0..2147483647;	subtype NATURALN is NATURAL not null;
			subtype POSITIVE is BINARY_INTEGER range 1..2147483647;	subtype POSITIVEN is POSITIVE not null;
			subtype SIGNTYPE is BINARY_INTEGER range '-1'..1; -- for SIGN functions
			subtype PLS_INTEGER is BINARY_INTEGER;
			subtype SIMPLE_INTEGER is BINARY_INTEGER NOT NULL;
			subtype SIMPLE_FLOAT is BINARY_FLOAT NOT NULL;

	subtype SMALLINT is NUMBER(38,0);
	subtype DECIMAL is NUMBER(38,0);	subtype NUMERIC is DECIMAL; subtype DEC is DECIMAL;
	type BINARY_FLOAT is NUMBER;
	type BINARY_DOUBLE is NUMBER;		subtype SIMPLE_DOUBLE is BINARY_DOUBLE NOT NULL;

String Data Types

Fixed length and variable length string data types


      
        
          Data Type Definition
          PL/SQL
          SQL
        
        
          Fixed Length
        
        
          CHAR(<chars>)
          32,767 bytes
          2,000 chars
        
        
          NCHAR(<bytes>)
          32,767 bytes
          1,000 bytes
        
        
          Variable Length
        
        
          LONG
          32,760 bytes
          2GB
        
        
          VARCHAR2(<chars>)
          32,767 bytes
          4,000 chars
        
        
          NVARCHAR2(<bytes>)
          32,767 bytes
          2,000 bytes

Note: The database character set controls (and specifies) the character set of CHAR and VARCHAR2 columns. The national character set controls the character set of NCHAR and NVARCHAR2 columns.

CHAR & NCHAR Subtypes


          
            
              Data Type Definition
              PL/SQL
              SQL
            
            
              CHARACTER(<chars>)
              -
              2,000 chars
            
            
              NATIONAL CHAR VARYING(<chars>)
              32,767 bytes
              2,000 bytes
            
            
              NATIONAL CHARACTER VARYING(<chars>)
              32,767 bytes
              2,000 bytes
            
            
              NCHAR VARYING(<chars>)
              32,767 bytes
              2,000 bytes

VARCHAR2 & NVARCHAR2 Subtypes


          
            
              Data Type Definition
              PL/SQL
              SQL
            
            
              CHAR VARYING(<chars>)
              32,767 bytes
              4,000 bytes
            
            
              CHARACTER(<chars>)
              32,767 bytes
              -
            
            
              CHARACTER VARYING(<chars>)
              32,767 bytes
              4,000 bytes
            
            
              STRING(<chars>)
              32,767 bytes
              N/A
            
            
              VARCHAR
              32,767 bytes
              4,000 bytes

Create Table CREATE TABLE test ( charcol CHAR(2000), charvaryingcol CHAR VARYING(4000), charactercol CHARACTER(2000), charactervaryingcol CHARACTER VARYING(4000), nationalcharvarying NATIONAL CHAR VARYING(2000), nationalcharactervaryingcol NATIONAL CHARACTER VARYING(2000), ncharcol NCHAR(1000), ncharvaryingcol NCHAR VARYING(2000), nvarchar2col NVARCHAR2(2000), varcharcol VARCHAR(4000), varchar2col VARCHAR2(4000)); SQL> desc test Name Type --------------------------- -------------- CHARCOL CHAR(2000) CHARVARYINGCOL VARCHAR2(4000) CHARACTERCOL CHAR(2000) CHARACTERVARYINGCOL VARCHAR2(4000) NATIONALCHARVARYING NVARCHAR2(2000) NATIONALCHARACTERVARYINGCOL NVARCHAR2(2000) NCHARCOL NCHAR(1000) NCHARVARYINGCOL NVARCHAR2(2000) NVARCHAR2COL NVARCHAR2(2000) VARCHARCOL VARCHAR2(4000) VARCHAR2COL VARCHAR2(4000)

Define Variables ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; CREATE OR REPLACE PROCEDURE test IS charcol CHAR(32767); charvaryingvar CHAR VARYING(32767); charactervar CHARACTER(32767); charactervaryingvar CHARACTER VARYING(32767); nationalcharvaryvar NATIONAL CHAR VARYING(32767); nationalcharactervaryingvar NATIONAL CHARACTER VARYING(32767); ncharvar NCHAR(32767); ncharvaryingvar NCHAR VARYING(32767); nvarchar2var NVARCHAR2(32767); stringvar STRING(32767); varcharvar VARCHAR(32767); varchar2var VARCHAR2(32767); BEGIN NULL; END test; /

Numeric Data Types

Numeric Data Types

precision 1 to 38
scale -84 to 127


          
            
              Data Type Definition
              Variable Size
              Column Size
            
            
              Integer
            
            
              PLS_INTEGER or BINARY_INTEGER

              (Signed integer 32 bits)
              -2,147,483,647 through 2,147,483,647
              N/A
            
            
              Floating Point
            
            
              NUMBER
              38 chars
              38 chars
            
            
              Fixed Point
            
            
              NUMBER(<precision>,<scale>)
              38 chars
              38 chars

Binary Integer SubTypes


          
            
              Data Type Definition
              Variable Size
              Column Size
            
            
              Non-negative Integers
            
            
              NATURAL

              (BINARY_INTEGER range 0..2147483647)
              32 bit
              N/A
            
            
              POSITIVE

              (BINARY INTEGER range 1..2147483647)
              32 bit
              N/A
            
            
              Not Nullable Non-negative Integers
            
            
              NATURALN (NATURAL NOT NULL)
              32 bit
              N/A
            
            
              POSITIVEN (POSITIVE NOT NULL)
              32 bit
              N/A
            
            
              Not Nullable Integers
            
            
              SIMPLE_INTEGER
              32 bit
              N/A
            
            
              SIMPLE_DOUBLE
              32 bit
              N/A
            
            
              SIMPLE_FLOAT
              32 bit
              N/A
            
            
              Restricted
            
            
              SIGNTYPE
              -1, 0, +1
              N/A

NUMBER SubTypes


          
            
              Data Type Definition
              Variable Size
              Column Size
            
            
              Integers with up to 38 decimal digits
            
            
              INTEGER (NUMBER(38,0)
              38 integer digits
              38
            
            
              INT (INTEGER)
              38 integer digits
              38
            
            
              SMALLINT (NUMBER(38,0)
              38 integer digits
              38
            
            
              Fixed point numbers up to 38 decimal digits
            
            
              DECIMAL (NUMBER(38,0)
              38 decimal digits
              38
            
            
              DEC(DECIMAL)
              38 decimal digits
              38
            
            
              NUMERIC(DECIMAL)
              38 decimal digits
              38
            
            
              Floating point numbers up to 126 binary digits
            
            
              DOUBLE PRECISION (FLOAT)
              126 binary digits
              126
            
            
              FLOAT (NUMBER(126))
              126 binary digits
              126
            
            
              REAL (FLOAT(63))
              63 binary digits
              63 bin. digits
            
            
              Floating point numbers using native machine arithmetic
            
            
              BINARY_DOUBLE (single prec)
              64 bit
              64 bit
            
            
              BINARY_FLOAT (double prec)
              32 bit
              32 bit

Create Table CREATE TABLE test ( deccol DEC(38), decimalcol DECIMAL(38), doubleprecisioncol DOUBLE PRECISION, floatcol FLOAT(126), intcol INT, integercol INTEGER, numbercol NUMBER(38), numberfcol NUMBER, numericcol NUMERIC(38), numericfcol NUMERIC, realcol REAL, smallintcol SMALLINT); SQL> desc test Name Type ------------------- -------------- DECCOL NUMBER(38) DECIMALCOL NUMBER(38) DOUBLEPRECISIONCOL FLOAT(126) FLOATCOL FLOAT(126) INTCOL NUMBER(38) INTEGERCOL NUMBER(38) NUMBERCOL NUMBER(38) NUMBERFCOL NUMBER NUMERICCOL NUMBER(38) NUMERICFCOL NUMBER(38) REALCOL FLOAT(63) SMALLINTCOL NUMBER(38)

Define Variables ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; CREATE OR REPLACE PROCEDURE test IS decvar DEC(38); decimalvar DECIMAL(38); doubleprecisionvar DOUBLE PRECISION; floatvar FLOAT(126); intvar INT; integervar INTEGER; naturalvar NATURAL; naturalnvar NATURALN := 0; numbervar NUMBER(38); numberfvar NUMBER; numericvar NUMERIC(38); numericfvar NUMERIC; plsvar PLS_INTEGER; positivevar POSITIVE; positivenvar POSITIVEN := 1; realvar REAL; signtypevar SIGNTYPE; smallintvar SMALLINT; simplevar SIMPLE_INTEGER := 1; simpledouble SIMPLE_DOUBLE := 1; simplefloat SIMPLE_FLOAT := 1; BEGIN NULL; END test; /

Additional Numeric Subtypes

Subtype	Description
BINARY_DOUBLE_INFINITY (*)	Double-precision positive infinity
BINARY_DOUBLE_MAX_NORMAL	Maximum normal BINARY_DOUBLE value
BINARY_DOUBLE_MAX_SUBNORMAL	Maximum subnormal BINARY_DOUBLE value
BINARY_DOUBLE_MIN_NORMAL	Minimum normal BINARY_DOUBLE value
BINARY_DOUBLE_MIN_SUBNORMAL	Minimum subnormal BINARY_DOUBLE value
BINARY_DOUBLE_NAN (*)	BINARY_DOUBLE value for which the condition IS NAN (not a number) is true
BINARY_FLOAT_INFINITY (*)	Single-precision positive infinity
BINARY_FLOAT_MAX_NORMAL	Maximum normal BINARY_FLOAT value
BINARY_FLOAT_MAX_SUBNORMAL	Maximum subnormal BINARY_FLOAT value
BINARY_FLOAT_MIN_NORMAL	Minimum normal BINARY_FLOAT value
BINARY_FLOAT_MIN_SUBNORMAL	Minimum subnormal BINARY_FLOAT value
BINARY_FLOAT_NAN (*)	BINARY_FLOAT value for which the condition IS NAN (not a number) is true

Date Data Types

Date Type

Oracle's stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:


      
        
          Byte
          Description
        
        
          1
          Century value but before storing it add 100 to it
        
        
          2
          Year and 100 is added to it before storing
        
        
          3
          Month
        
        
          4
          Day of the month
        
        
          5
          Hours but add 1 before storing it
        
        
          6
          Minutes but add 1 before storing it
        
        
          7
          Seconds but add 1 before storing it
        
       
      
        
          Data Type Definition
          Variable Size
          Column Size
        
        
          Date
        
        
          DATE (1/1/4712 BC-12/31/9999)
          DD-MON-YYYY

          HH{A|P}M :MI:SS
          DD-MON-YYYY

          HH{A|P}M :MI:SS
        
        
          Interval (see link at page bottom)
        
        
          INTERVAL DAY TO SECOND
          DD MI SS
          DD MI SS
        
        
          INTERVAL YEAR TO MONTH
          YYYY MM
          YYYY MM
        
        
          Timestamp (see link at page bottom)
        
        
          TIMESTAMP(<precision>)
          DD-MON-YYYY

          HH.MI.SS.

          SSSSSSSSS {A|P}M
          DD-MON-YYYY

          HH.MI.SS.

          SSSSSSSSS {A|P}M
        
        
          TIMESTAMP WITH TIME ZONE
          as above

          with timezone
          as above

          with timezone
        
        
          TIMESTAMP WITH LOCAL TIME ZONE
          as above with

          local timezone
          as above with

          local timezone

Create Table CREATE TABLE test ( date_col DATE, int_d2s_col INTERVAL DAY TO SECOND, int_y2m_col INTERVAL YEAR TO MONTH, ts_col TIMESTAMP, tswtz_col TIMESTAMP WITH TIME ZONE, tswltz_col TIMESTAMP WITH LOCAL TIME ZONE); SQL> desc test Name Type ------------------- -------------- DATE_COL DATE INT_D2S_COL INTERVAL DAY(2) TO SECOND(6) INT_Y2M_COL INTERVAL YEAR(2) TO MONTH TS_COL TIMESTAMP(6) TSWTZ_COL TIMESTAMP(6) WITH TIME ZONE TSWLTZ_COL TIMESTAMP(6) WITH LOCAL TIME ZONE

Define Variables ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; CREATE OR REPLACE PROCEDURE test IS datevar DATE; int_d2s_var INTERVAL DAY TO SECOND; int_y2m_var INTERVAL YEAR TO MONTH; ts_var TIMESTAMP; tswtz_var TIMESTAMP WITH TIME ZONE; tswLtz_var TIMESTAMP WITH LOCAL TIME ZONE; BEGIN NULL; END test; /

Partial text from Metalink Note: 69028.1 Since Oracle 7 the DATE datatype is stored in a proprietary format. DATE values are always stored in 7 bytes, excluding the length byte, within a datafile. These bytes store the century, year, month, day, hour, minute, and second details respectively. The following is the definition of Oracle's internal DATE storage structure:

BYTE Meaning ---- ------- 1 Century -- stored in excess-100 notation 2 Year -- " " 3 Month -- stored in 0 base notation 4 Day -- " " 5 Hour -- stored in excess-1 notation 6 Minute -- " " 7 Second -- " "

Note that the century and year components are stored in 'excess 100 format', which means that 100 must be deducted from the byte's value. If a negative number results, then we've got a BC date at which point we take the absolute number. Also, to avoid ever having a zero byte, 1 is added to the hour, minute and second bytes. Therefore, 1 must be detected to get the correct value.

For example, take the following date again: 17-DEC-1980 00:00:00

we would expect this date to be stored internally as follows: 119, 180, 12, 17, 01, 01, 01

Let's confirm that hypothesis by dumping the data block in question from an Oracle 8 database and examining its contents. Let's use the "SCOTT.EMP" table for our example:

1) First let's select a row with DATE information to examine:

SQL> desc emp

Result: We see that the HIREDATE column is the fifth column.

SQL> SELECT to_char(hiredate, 'DD-MON-YYYY HH24:MI:SS') FROM emp WHERE rownum = 1;

Result: 17-DEC-1980 00:00:00

2) Next we need to dump the data block in question:

SQL> SELECT rowid FROM emp WHERE rownum = 1;

Result: AAAAtaAABAAAEG1AAA

SQL> SELECT dbms_rowid.ROWID_TO_ABSOLUTE_FNO('AAAAtaAABAAAEG1AAA', 'SCOTT','EMP') FROM dual;

Result: 1

SQL> SELECT dbms_rowid.ROWID_BLOCK_NUMBER('AAAAtaAABAAAEG1AAA') FROM dual;

Result: 16821

SQL> alter system dump datafile 1 block 16821;

Finally, we need to open the dump file which is located in our user trace directory and locate the first row dump in the file:

Locate the 5th column in the first row using a 0 based indexing scheme.

Result:
... col 4: [ 7] 77 b4 0c 11 01 01 01 <= Hexadecimal representation
...

Convert the hexidecimal dump to a decimal representation

Result: 119 180 12 17 01 01 01 <= Decimal representation

As a result, we have proven our assumption for the date "17-DEC-1980 00:00:00".

The above method is quite tedious. Is there no easier means of viewing internal date information? Let's try using the DUMP() function to do the same thing. Issue the following statement:

SQL> SELECT dump(to_date('17-DEC-1980 00:00:00', 'DD-MON-YYYY HH24:MI:SS')) FROM dual;

Result: Typ=13 Len=8: 188,7,12,17,0,0,0,0

What happened? Is the information above incorrect or does the DUMP() function not handle DATE values? No, you have to look at the "Typ=" values to understand why we are seeing these results. The datatype returned is 13 and not 12, the external DATE datatype. This occurs because we rely on the TO_DATE function! External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations. Note that the same result can be seen when DUMPing the value SYSDATE.

Using deductive logic, we can derive the following storage format for type 13 data:

Byte 1 - Base 256 year modifier 2 - Base 256 year 3 - Month 4 - Day 5 - Hours 6 - Minutes 7 - Seconds 8 - Unused

For AD dates, the year and base 256 modifier are stored in base 0 notation and we must add the modifier to the year to obtain the true year. For BC dates, the year and base 256 modifier are stored in excess-255 notation. We must subtract the modifier from the year to obtain the true year.

For our year 1980, we could read this to be, Byte 1 + Byte 2 * 256. In other words, 188 + 7 * 256 = 1980.

Let's try another DUMP but using a date extracted from the original table:

SQL> SELECT dump(hiredate) FROM emp WHERE rownum = 1;

Result: Typ=12 Len=7: 119,180,12,17,1,1,1

Now we have a datatype 12 with a length of 7 and the results are as expected.

In terms of limits, Oracle is capable of handling dates from: 01-JAN-4712 BC 00:00:00 - Julian Day: 1
through
31-DEC-9999 AD 23:59:59 AD - Julian Day: 5373484

The Julian Day number is a count of days elapsed since Greenwich mean noon on 1 January 4712 B.C. in the Julian proleptic calendar. The Julian Date is the Julian Day number followed by the fraction of the day elapsed since the preceding noon.

All calculations made on DATE values are based on fractional days. In other words, the values of SYSDATE+1 is tomorrow's DATE at this time. This is reminiscent of the Julian Day number behavior described above. By using the widely accepted convention of the Julian calendar that 1 day is the basic unit of time measurement, DATE calculation logic is greatly simplified.

Logical Data Types

BOOLEAN set serveroutput on DECLARE x BOOLEAN; BEGIN x := TRUE; IF x THEN dbms_output.put_line('TRUE'); ELSIF NOT x THEN dbms_output.put_line('FALSE'); END IF; END; /

-- see diutil.bool_to_int and diutil.int_to_bool

Binary Data Types

Binary Types


      
        
          Data Type Definition
          Variable Size
          Column Size
        
        
          LONG RAW
          32,760 bytes
          ~2GB
        
        
          MLSLABEL
          2-5 bytes
          2-5 bytes
        
        
          RAW(<maximum_size_in_bytes>)
          32,767 bytes
          2,000 bytes

Rowid Data Types

ROWID & UROWID


      
        
          Data Type Definition
          Variable Size
          Column Size
        
        
          ROWID - physical row identifier
          block.row.file
          block.row.file
        
        
          UROWID - universal (IOT)
          N/A
          4,000 bytes

Note: Rowid consists of four parts ... characters 1-6 = data object id, characters 7-9 = file number, characters 10-15 = block, characters 16-18 - row number

Oracle Supplied Data Types

Polymorphic Types

Data Type Definition	Description
SYS.ANYTYPE	Can contain a type description of any persistent SQL type, named or unnamed, including object types and collection types. Only new transient types can be constructed using the ANYTYPE interfaces.
SYS.ANYDATA	See link at page bottom
SYS.ANYDATASET	Contains a description of a given type plus a set of data instances of that type. An ANYDATASET can be persistently stored in the database or can be used as an interface parameter to communicate self-descriptive sets of data, all of which belong to a certain type.

Media Types

Data Type Definition	Description
ORDAudio	Supports the storage of audio data
ORDDoc	Supports the storage and management of any tpe of media data, including audio, image, and video
ORDImage	Supports the storage of image data
ORDImageSignature	Supports the a compact representation of color, texture, and shape information of image data data
SI_AverageColor	Characterizes an image by its average color
SI_Color	Encapsulates color values
SI_ColorHistogram	Characterizes an image by the relative frequencies of the color exhibited by samples of the raw image
SI_FeatureList	For up to 4 image features represented by SI_AVERAGE_COLOR, SI_COLORHISTOGRAM, SI_POSITIONAL_COLOR and SI_TEXTURE where the feature is associated with a feature weight
SI_PositionalColor	Given an image divided into rectangles, represents the feature that characterizes an image by the n by m most significant colors of the rectangles.
SI_StilImage	Represents digital images with inherent image characteristics such as height, width, and format
SI_Texture	Characterizes an image by the size of repeating items (coarseness), brightness variations (contrast), and predominant direction (directionality)
ORDVideo	Supports the storage of video data

Spatial Types


       
         
           Data Type Definition
           Description
         
         
           SDO_GEOMETRY
           A geometric description of a spatial object stored in a single row
         
         
           SDO_RASTER
           A raster grid or image stored in a single row

XML Types

For more information on XMLType follow the link at page bottom.


      
        
          Data Type Definition
          Description
        
        
          URIType
          An object type for storing XML
        
        
          DBURIType
          A subtype of URIType used to store DBURIRefs that allow for consistent access to data stored inside and outside the database
        
        
          HTTPURIType
          A subtype of URIType used to store URLs to external web pages or to files. Oracle access these files using HTTP
        
        
          XDBURIType
          A subtype of URIType to expose documents in the XML heirarchy as URIs that can be embedded in any URIType column in a table

XML Type CREATE TABLE xml_tab ( rid INTEGER NOT NULL, xml_data XMLTYPE); INSERT INTO xml_tab (rid, xml_data) VALUES (1, NULL); INSERT INTO xml_tab (rid, xml_data) VALUES (2, XMLTYPE('<DATA><ID>4</ID><DESC>MORGAN</DESC></DATA>')); SELECT * FROM xml_tab;

Predefined Composite Data Types

Data types that contain internal components

Single And Two Dimensional Arrays RECORD: A composite variable that can store data values of different types. In PL/SQL records are useful for holding data from table rows. For ease of maintenance they can be declared with %ROWTYPE.

set serveroutput on

      

      DECLARE

        TYPE TimeType IS RECORD (seconds SMALLINT := 0, minutes SMALLINT := 0, hours SMALLINT := 0);

      

        MyTime  TimeType;

      BEGIN

        MyTime.seconds := 24;

        MyTime.minutes := 37;

        MyTime.hours := 5;

      

        dbms_output.put_line(MyTime.hours || ':' || MyTime.minutes || ':' || MyTime.seconds);

      END;

      /

TABLE: A one-dimensional array with no upper bound.

See link at page bottom for Associative Arrays

VARRAY: A two-dimensional array with a fixed number of elements.

See link at page bottom for VArrays

Reference Data Types

Data types that are pointers that identify data

REF REF CURSOR: A pointer to a result set

See link at page bottom for Ref Cursor

REF object_type: A pointer to an object

CREATE TYPE home_t AS OBJECT (

      address    VARCHAR2(35),

      owner      VARCHAR2(25),

      age        INTEGER,

      style      VARCHAR2(15),

      floor_plan BLOB,

      price      REAL);

      /

      

      CREATE TABLE homes OF home_t;

      

      CREATE TYPE person_t AS OBJECT (

      first_name  VARCHAR2(10),

      last_name   VARCHAR2(15),

      dob         DATE,

      home_addr   REF home_t,

      home_phone  VARCHAR2(15),

      ssn         VARCHAR2(11),

      mother      REF person_t,

      father      REF person_t);

      /

      

      CREATE TABLE person OF person_t;

      

      desc person

      

      set describe depth all linenum on indent on

      

      desc person

LOB Data Type

Data types that holds lob locators specifying the location of large objects stored out-of-line.

Large Object Data Types


      
        
          Data Type Definition
          Variable Size
          Column Size
        
        
          BFILE
          (4GB - 1 byte)
          (4GB - 1 byte)
        
        
          BLOB
          8 to 128 TB
          (4GB-1 byte)*(block size)
        
        
          CLOB
          8 to 128 TB
          (4GB-1 byte)*(block size)
        
        
          NCLOB
          8 to 128 TB
          (4GB-1 byte)*(block size)

Undefined Data Type

This data type is only available in PL/SQL and only in an object defined within a PL/SQL package

The syntax for the UNDEFINED data type is an ellipsis (3 consecutive periods) SQL> SELECT owner, package_name, object_name, argument_name 2 FROM dba_arguments 3 WHERE data_type = 'UNDEFINED' 4 ORDER BY 1,2,3; OWNER PACKAGE_NAME OBJECT_NAME ARGUMENT_NAME ------------------ ----------------- ---------------------------- --------------- ------------------------------ GSMADMIN_INTERNAL DBMS_GSM_UTILITY GETCHUNKID KEYS GSMADMIN_INTERNAL DBMS_GSM_UTILITY GETCHUNKUNIQUEID KEYS SYS DBMS_REPORT BUILD_GENERIC_TAG TAG_INPUTS SYS DBMS_REPORT BUILD_REPORT_REFERENCE_VARG ID_PARAM_VAL SYS UTL_LMS FORMAT_MESSAGE ARGS -- note how the data type for the parameter ARGS is defined CREATE OR REPLACE PACKAGE uwclass.xyz AUTHID DEFINER AS FUNCTION format_message(inval IN VARCHAR2 CHARACTER SET ANY_CS, args ...) RETURN VARCHAR2; END xyz; /

Data Type Demos

Floating Point Numbers CREATE TABLE fpn ( numbercol NUMBER, floatcol FLOAT(126), bindoubcol BINARY_DOUBLE, binfloatcol BINARY_FLOAT, realcol REAL); desc fpn INSERT INTO fpn VALUES (1234.56, 1234.56, 1234.56, 1234.56, 1234.56); COMMIT; SELECT * FROM fpn; SELECT VSIZE(numbercol), VSIZE(floatcol), VSIZE(bindoubcol), VSIZE(binfloatcol), VSIZE(realcol) FROM fpn; set linesize 121 col numbc format a20 col flotc format a20 col bindc format a20 col binfc format a20 col realc format a20 SELECT DUMP(numbercol) NUMBC, DUMP(floatcol) FLOTC, DUMP(bindoubcol) BINDC, DUMP(binfloatcol) BINFC, DUMP(realcol) REALC FROM fpn;

Define table demo CREATE TABLE tnorm ( somecol VARCHAR2(20)); CREATE TABLE tbyte ( somecol VARCHAR2(20 BYTE)); CREATE TABLE tchar ( somecol VARCHAR2(20 CHAR)); desc tnorm desc tbyte desc tchar

BINARY_FLOAT_INFINITY conn hr/hr@pdbdev SELECT COUNT(*) FROM employees WHERE salary < BINARY_FLOAT_INFINITY;