Oracle Internal Core : les types de données gérés par Oracle - The types of data managed by Oracle
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;
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)