Introduction
Je poursuis mon voyage au coeur d'Oracle pour comprendre un peu mieux comment celle-ci est construite. Cette fois, nous allons nous intéresser aux type de données gérés par Oracle, où trouver leur liste et comment ils sont définis.




Points d'attention
Aucun.



Base de tests
Une base 12c.



Exemples
============================================================================================
Où trouver la liste des datatypes?

============================================================================================
Le site d'Oracle

Dans la doc officielle d'Oracle 12c https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF30020, 23 datatypes sont répertoriés. Ils ne sont cependant pas tous présents, il manque par exemple les types VARCHAR, SMALLINT, DECIMAL.
          BFILE
          BINARY_DOUBLE
          BINARY_FLOAT
          BLOB
          CHAR
          CLOB
          DATE
          FLOAT
          INTERVAL DAY
          INTERVAL YEAR
          LONG
          LONG RAW
          NCHAR
          NCLOB
          NUMBER
          NVARCHAR2
          RAW
          ROWID
          TIMESTAMP
          TIMESTAMP WITH LOCAL TIME ZONE
          TIMESTAMP WITH TIME ZONE
          UROWID
          VARCHAR2


La vue DBA_TYPES
Dans la vue DBA_TYPES on a une autre liste, de 58 datatypes, en filtrant sur OWNER IS NULL et PREDIFINED = 'YES'. Néanmoins cette liste est encore insatisfaisante car les bornes des types sont absentes et il en manque encore, notamment le type LONG; il est certes Deprecated par Oracle mais encore largement utilisé dans les tables du dictionnaire de données. A cela s'ajoutent des types bizarre mais que je n'arrive pas à filtrer, comme CANONICAL, CONTIGUOUS ARRAY... Après une recherche Google, ils semblent être des types de logiciels de développement Oracle.
          SQL> select TYPE_NAME from DBA_TYPES where OWNER is null AND PREDEFINED = 'YES' order by TYPE_NAME;
          TYPE_NAME
          ------------------------------
          BFILE
          BINARY ROWID
          BINARY_DOUBLE
          BINARY_FLOAT
          BLOB
          CANONICAL
          CFILE
          CHAR
          CLOB
          CONTIGUOUS ARRAY
          DATE
          DECIMAL
          DOUBLE PRECISION
          FLOAT
          INTEGER
          INTERVAL DAY TO SECOND
          INTERVAL YEAR TO MONTH
          LOB POINTER
          NAMED COLLECTION
          NAMED OBJECT
          NUMBER
          OCTET
          OID
          PL/SQL BINARY INTEGER
          PL/SQL BOOLEAN
          PL/SQL COLLECTION
          PL/SQL LONG
          PL/SQL LONG RAW
          PL/SQL NATURAL
          PL/SQL NATURALN
          PL/SQL PLS INTEGER
          PL/SQL POSITIVE
          PL/SQL POSITIVEN
          PL/SQL RECORD
          PL/SQL REF CURSOR
          PL/SQL ROWID
          PL/SQL STRING
          POINTER
          RAW
          REAL
          REF
          SIGNED BINARY INTEGER(16)
          SIGNED BINARY INTEGER(32)
          SIGNED BINARY INTEGER(8)
          SMALLINT
          TABLE
          TIME
          TIME WITH TZ
          TIMESTAMP
          TIMESTAMP WITH LOCAL TZ
          TIMESTAMP WITH TZ
          UNSIGNED BINARY INTEGER(16)
          UNSIGNED BINARY INTEGER(32)
          UNSIGNED BINARY INTEGER(8)
          UROWID
          VARCHAR
          VARCHAR2
          VARYING ARRAY
          58 lignes selectionnees.
           

Le fichier stdspec.sql
Finalement la liste ultime se trouve dans le répertoire $ORACLE_HOME/rdbms/admin qui est le Graal pour les archéologues et explorateurs d'Oracle. Le script stdspec.sql est extrêmement intéressant car on a non seulement les types de données, leur longueur, les sous-types mais aussi leurs plages de valeurs et, trop cool, les types internes Oracle comme DATE_BASE, NUMBER_BASE sur lesquels ils s'appuient!!!!!

Les sous-types permettent notamment de voir la hiérarchie entre les types, par exemple que VARCHAR2 est le type de base pour tous les types gérant les caractères.

Pour une 12.1, ce fichier contient 95 datatypes différents!
          Unix$ more /opt/oracle/srv/12.1.0/rdbms/admin/stdspec.sql
          @@?/rdbms/admin/sqlsessstart.sql
          create or replace
          package STANDARD AUTHID CURRENT_USER is              -- careful on this line; SED edit occurs!
           
            /********** Types and subtypes, do not reorder **********/
            type BOOLEAN is (FALSE, TRUE);   -----> seul type dont il n'y ait pas de sous-type genre BOOLEAN_BASE
           

            type DATE is DATE_BASE;
           

            type NUMBER is NUMBER_BASE;

            subtype FLOAT is NUMBER; -- NUMBER(126)     -----> le début des sous-types basés sur le datatype NUMBER
            subtype REAL is FLOAT; -- FLOAT(63)   -----> on a la longueur du type
            subtype "DOUBLE PRECISION" is FLOAT;
            subtype INTEGER is NUMBER(38,0);
            subtype INT is INTEGER;
            subtype SMALLINT is NUMBER(38,0);
            subtype DECIMAL is NUMBER(38,0);
            subtype NUMERIC is DECIMAL;
            subtype DEC is DECIMAL;
            subtype BINARY_INTEGER is INTEGER range '-2147483647'..2147483647;    -----> ce type a une plage de valeur fixe
            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
           

            type VARCHAR2 is NEW CHAR_BASE; -----> pourquoi le mot NEW, absent pour DATE et NUMBER?

            subtype VARCHAR is VARCHAR2;
            subtype STRING is VARCHAR2;
            subtype LONG is VARCHAR2(32760);
            subtype RAW is VARCHAR2;
            subtype "LONG RAW" is RAW(32760);
            subtype ROWID is VARCHAR2(256);
           
            -- Ansi fixed-length char
            -- Define synonyms for CHAR and CHARN.
            subtype CHAR is VARCHAR2;     -----> CHAR est un sous-type de VARCHAR2
            subtype CHARACTER is CHAR;

           
            type MLSLABEL is new CHAR_BASE;
           
            -- Large object data types.
            --  binary, character, binary file.
            type  BLOB is BLOB_BASE;
            type  CLOB is CLOB_BASE;
            type  BFILE is BFILE_BASE;

           
            -- Verbose and NCHAR type names
            subtype "CHARACTER VARYING" is VARCHAR;
            subtype "CHAR VARYING" is VARCHAR;
            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;
            subtype "NATIONAL CHARACTER VARYING" is VARCHAR CHARACTER SET NCHAR_CS;
            subtype "NATIONAL CHAR VARYING" is VARCHAR CHARACTER SET NCHAR_CS;
            subtype "NCHAR VARYING" is VARCHAR CHARACTER SET NCHAR_CS;
            subtype "NVARCHAR2" is VARCHAR2 CHARACTER SET NCHAR_CS;
            subtype "CHARACTER LARGE OBJECT" is CLOB;
            subtype "CHAR LARGE OBJECT" is CLOB;
            subtype "NATIONAL CHARACTER LARGE OBJEC" is CLOB CHARACTER SET NCHAR_CS;
            subtype "NCHAR LARGE OBJECT" is CLOB CHARACTER SET NCHAR_CS;
            subtype "NCLOB" is CLOB CHARACTER SET NCHAR_CS;
            subtype "BINARY LARGE OBJECT" is BLOB;

           
            subtype pls_integer is binary_integer;

           
            type TIME is new DATE_BASE;
            type TIMESTAMP is new DATE_BASE;
            type "TIME WITH TIME ZONE" is new DATE_BASE;
            type "TIMESTAMP WITH TIME ZONE" is new DATE_BASE;
            type "INTERVAL YEAR TO MONTH" is new DATE_BASE;
            type "INTERVAL DAY TO SECOND" is new DATE_BASE;

           
            SUBTYPE TIME_UNCONSTRAINED IS TIME(9);
            SUBTYPE TIME_TZ_UNCONSTRAINED IS TIME(9) WITH TIME ZONE;
            SUBTYPE TIMESTAMP_UNCONSTRAINED IS TIMESTAMP(9);
            SUBTYPE TIMESTAMP_TZ_UNCONSTRAINED IS TIMESTAMP(9) WITH TIME ZONE;
            SUBTYPE YMINTERVAL_UNCONSTRAINED IS INTERVAL YEAR(9) TO MONTH;
            SUBTYPE DSINTERVAL_UNCONSTRAINED IS INTERVAL DAY(9) TO SECOND (9);

           
            TYPE UROWID IS NEW CHAR_BASE;

           
            type "TIMESTAMP WITH LOCAL TIME ZONE" is new DATE_BASE;
            subtype timestamp_ltz_unconstrained is timestamp(9) with local time zone;

           
            subtype BINARY_FLOAT is NUMBER;
            subtype BINARY_DOUBLE is NUMBER;

           
            -- The following data types are generics, used specially within package
            -- STANDARD and some other Oracle packages.  They are protected against
            -- other use; sorry.  True generic types are not yet part of the language.        -----> merci au développeur pour le clin d'oeil :-)  
            type "<ADT_1>" as object (dummy char(1));
            type "<RECORD_1>" is record (dummy char(1));
            type "<TUPLE_1>" as object (dummy char(1));
            type "<VARRAY_1>" is varray (1) of char(1);
            type "<V2_TABLE_1>" is table of char(1) index by binary_integer;
            type "<TABLE_1>" is table of char(1);
            type "<COLLECTION_1>" is table of char(1);
            type "<REF_CURSOR_1>" is ref cursor;
           

            -- This will actually match against a Q_TABLE
            type "<TYPED_TABLE>" is table of  "<ADT_1>";
            subtype "<ADT_WITH_OID>" is "<TYPED_TABLE>";

           
            -- The following generic index table data types are used by the PL/SQL
            -- compiler to materialize an array attribute at the runtime (for more
            -- details about the array attributes, please see Bulk Binds document).
            type " SYS$INT_V2TABLE" is table of integer index by binary_integer;

           
            -- The following record type and the corresponding generic index table
            -- data types are used by the PL/SQL compiler to materialize a table
            -- at the runtime in order to record the exceptions raised during the
            -- execution of FORALL bulk bind statement (for more details, please
            -- see bulk binds extensions document in 8.2).
            type " SYS$BULK_ERROR_RECORD" is
                    record (error_index pls_integer, error_code pls_integer);
            type " SYS$REC_V2TABLE" is table of " SYS$BULK_ERROR_RECORD"
                                         index by binary_integer;

           
            /* Adding a generic weak ref cursor type */
            type sys_refcursor is ref cursor;
           
            /* the following data type is a generic for all opaque types */
            type "<OPAQUE_1>" as opaque FIXED(1) USING LIBRARY dummy_lib
              (static function dummy return number);

           
            type "<ASSOC_ARRAY_1>" is table of char(1) index by varchar2(1);

           
            /********** Add new types or subtypes here **********/          
            -- Simple scalar types
            subtype SIMPLE_INTEGER is BINARY_INTEGER NOT NULL;
            subtype SIMPLE_FLOAT   is BINARY_FLOAT   NOT NULL;
            subtype SIMPLE_DOUBLE  is BINARY_DOUBLE  NOT NULL;
           

            /********** Predefined constants **********/
            BINARY_FLOAT_NAN constant BINARY_FLOAT;
            BINARY_FLOAT_INFINITY constant BINARY_FLOAT;
            BINARY_FLOAT_MAX_NORMAL constant BINARY_FLOAT;
            BINARY_FLOAT_MIN_NORMAL constant BINARY_FLOAT;
            BINARY_FLOAT_MAX_SUBNORMAL constant BINARY_FLOAT;
            BINARY_FLOAT_MIN_SUBNORMAL constant BINARY_FLOAT;
            BINARY_DOUBLE_NAN constant BINARY_DOUBLE;
            BINARY_DOUBLE_INFINITY constant BINARY_DOUBLE;
            BINARY_DOUBLE_MAX_NORMAL constant BINARY_DOUBLE;
            BINARY_DOUBLE_MIN_NORMAL constant BINARY_DOUBLE;
            BINARY_DOUBLE_MAX_SUBNORMAL constant BINARY_DOUBLE;
            BINARY_DOUBLE_MIN_SUBNORMAL constant BINARY_DOUBLE;