IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

Les types opaques sous Oracle 9i

Cet article est une adaptation de la documentation officielle d'Oracle 9i r2 et concerne les nouveaux types de données qui permettent de mettre en œuvre le polymorphisme et la généricité. Attention ces nouveaux types ne sont disponibles qu'à partir de la version 9i. ♪

Article lu   fois.

L'auteur

Profil ProSite personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Avant-propos

Testé sous Oracle 9i r4 sous XP pro.

version 1.0

Je tiens à remercier Pascal Jankowski pour ses corrections orthographiques et les membres de l'équipe Oracle pour leurs relectures.

II. Introduction

La version Oracle 9i propose, au travers de trois nouveaux types de données, de connaître dynamiquement le type de donnée d'une instance. Dans les précédentes versions, cette connaissance s'arrêtait à la compilation.

Il est parfois nécessaire de manipuler des données dont le type change lors de l'exécution ou est inconnu lors de la compilation.
Par exemple le langage Java propose l'opérateur typeof et le langage C proposant le mot-clé union :

Une union en langage C est une variable qui peut contenir (à des moments différents) des objets de types et de tailles différents. Elles permettent donc de manipuler différents types de données dans un même espace mémoire.

Cf. Langage C, Kernighan et Ritchie, ed Masson.

Oracle9i inclut la possibilité de créer des variables et des colonnes pouvant contenir des données de n'importe quel type, et permet aussi de retrouver le type de données sous-jacent.

En utilisant cette possibilité, une colonne de table peut contenir une valeur numérique pour une ligne, une valeur chaîne dans une seconde ou encore un objet dans une troisième.

Vous pouvez employer le type natif SYS.ANYDATA pour représenter des valeurs de n'importe quel type scalaire ou objet. Il représente des valeurs dont le type peut changer à l'exécution.

Le type ANYDATA est un type objet avec des méthodes lui permettant de manipuler les valeurs des différents types, mais aussi de retrouver leur type d'origine. Il peut être vu comme un conteneur générique ressemblant ainsi au type variant disponible sous certains L4G qui associe les informations du type de l'instance et son contenu.

De la même manière, vous pouvez employer le type natif SYS.ANYDATASET pour représenter dans une collection des valeurs de n'importe quel type.

Vous trouverez le texte d'origine dans ces trois documents :

  • Application Developer's Guide - Fundamentals, pages 3-43 ;
  • Application Developer's Guide - Object-Relational Features, pages 6-37 ;
  • Supplied PL/SQL Packages and Types Reference ;
  • Oracle Call Interface- Programmer's Guide.

Vous trouverez ici les sources des scripts utilisés.

III. Type générique et type transitoire

Oracle a trois types spéciaux de données SQL qui vous permettent d'encapsuler dynamiquement et d'accéder à la description du type, aux données de l'instance de n'importe quel autre type SQL, y compris le type objet et le type collection. Vous pouvez également employer ces trois types spéciaux pour créer des types anonymes, c'est-à-dire sans nom, y compris des collections de types anonymes.

Ces trois types SQL sont implémentés en tant que types opaques. En d'autres termes, la structure interne de ces types n'est pas connue de la base de données : leurs données peuvent être interrogées seulement en mettant en application des fonctions dédiées, typiquement des routines L3G.

Ces types de données génériques sont ANYDATA pour un article simple, ANYDATASET pour une TABLE ou une collection VARRAY, et ANYTYPE, qui décrit le type de données stockées dans des variables ou des colonnes de type ANYDATA ou ANYDATASET.

Oracle fournit des API OCI et PL/SQL pour mettre en application de telles fonctions.

III-A. SYS.ANYTYPE

C'est un type de description de type de donnée. Un SYS.ANYTYPE peut contenir une description de type de n'importe quel type SQL, nommé ou anonyme, y compris des types d'objets et des types de collections.

Un ANYTYPE peut contenir une description de type d'un type persistant, mais un ANYTYPE lui-même est transitoire, en d'autres termes, la valeur d'un ANYTYPE elle-même n'est pas automatiquement stockée dans la base de données. Pour créer un type persistant, employez l'instruction SQL CREATE TYPE.

III-B. SYS.ANYDATA

C'est un type d'instance de donnée autodescriptif. Un SYS.ANYDATA contient une instance d'un type donné, avec ses données, plus une description du type. Dans ce sens, un SYS.ANYDATA est autodescriptif. Un ANYDATA peut être persistant, c'est-à-dire stocké dans la base de données, sauf si le type référencé contient un attribut de type LOB.

III-C. SYS.ANYDATASET

Un ensemble de types de données autodescriptifs. Un type SYS.ANYDATASET contient une description d'un type donné et un seul, plus un ensemble d'instances de données de ce type. Un ANYDATASET peut être persistant, c'est-à-dire stocké dans la base de données.
L'accès aux données ne peut se faire que de manière séquentielle.

III-D. Utilisation

Chacun de ces trois types peut être employé avec n'importe quel type natif de la base de données aussi bien avec des types objet que des types collection, nommés et anonymes (sans nom). Ces types fournissent une manière générique de travailler dynamiquement avec des descriptions de type, des instances seules, et des ensembles d'instances d'autres types.

En utilisant les API, vous pouvez créer une description ANYTYPE transitoire de n'importe quel type. De même, vous pouvez créer ou convertir/'caster' une valeur de données de n'importe quel type SQL en ANYDATA et inversement pouvez convertir un ANYDATA en type SQL. Et pareillement encore avec des ensembles de valeurs et ANYDATASET.

Les types génériques simplifient le travail avec des procédures stockées. Vous pouvez employer les types génériques pour encapsuler des descriptions et des données des types standard et passez l'information encapsulée dans des paramètres des types génériques. Dans le corps de la procédure, vous pouvez détailler comment manipuler les données encapsulées et la description de type de tous les types que vous voudrez.

Vous pouvez également stocker des données encapsulées d'une variété de types dérivés dans une colonne de table de type ANYDATA ou ANYDATASET.
Par exemple, vous pouvez employer ANYDATA avec Advanced Queuing pour modéliser des files d'attente de types de données hétérogènes. Vous pouvez interroger les données de types dérivés comme n'importe quelles autres données.

IV. Définitions

Avant d'aller plus avant dans cet article, je vous propose quelques définitions issues de la documentation Oracle.

IV-A. Type persistant (Persistent types)

Ceux-ci sont créés en utilisant l'instruction SQL CREATE TYPE. Ils sont persistants, c'est dire stockés dans la base de données.

IV-B. Type transitoire (Transient type)

Description anonyme de type (il ne possède pas de noms, à l'inverse du type NUMBER par exemple), ils ne sont pas stockés dans la base de données. Ils sont créés à la volée par des programmes et sont utiles pour échanger dynamiquement l'information de type, si besoin, entre divers composants d'une application. C'est un type 'éphémère'.

Note
Pour un type transitoire, la méthode GetTypeName() renvoie NULL :

 
Sélectionnez
DBMS_OUTPUT.PUT_LINE('La variable l_data est de type '||Nvl(l_data.GetTypeName(),'Type transitoire anonyme'));

IV-C. Objet transitoire (Transient Object)

Un objet transitoire est une instance d'un type d'objet. Il peut avoir un identificateur d'objet. Sa vie ne peut pas excéder celle de l'application. L'application peut également supprimer un objet transitoire à tout moment. Les objets transitoires ne peuvent pas être convertis en objets persistants. Leur rôle est fixé lorsqu'ils sont instanciés.

IV-D. Donnée autodescriptive (Self-descriptive data)

Données encapsulant l'information de type avec son contenu réel. Une valeur de donnée de la plupart des types SQL peut être convertie en AnyData qui peut alors être converti de nouveau à l'ancienne valeur de données.
Le type SYS.ANYDATA modélise de telles données dans le SQL ou le PL/SQL.

IV-E. Ensemble de données autodescriptives (Self-descriptive dataset)

L'encapsulation d'un ensemble d'instances de données de même type et de leur description de type.
Le type SYS.ANYDATASET modélise de telles données dans le SQL ou le PL/SQL.

IV-F. Surtype (Supertype)

La documentation Oracle utilise le terme 'supertype' ou 'surtype' pour désigner la classe ancêtre.

IV-G. Soustype (Subtype)

La documentation Oracle utilise le terme 'sous-type' pour désigner les classes dérivées.

IV-H. Substitution

La documentation Oracle utilise le terme 'substitution' pour désigner la capacité qu'une instance d'objet ancêtre à de contenir un objet d'une classe dérivée.

IV-I. Objet persistant (persistent object)

Les instances de type Oracle sont classées par catégorie dans des objets persistants et des objets transitoires basés sur leur durée de vie.
Des instances d'objets persistants peuvent être encore divisées en objets autonomes et objets inclus selon s'ils sont référençables par un identificateur d'objet (OID).

Un objet persistant est un objet qui est stocké dans une base de données Oracle. La vie d'un objet persistant peut excéder celle de l'application qui y accède. Une fois qu'il est créé, il reste dans la base de données jusqu'à ce qu'il soit explicitement supprimé.

Il existe deux types d'objets persistants, les exemples SQL suivants démontrent la différence entre ces deux types d'objets persistants.

IV-I-1. Objet autonome (Standalone object)

Des instances autonomes sont stockées dans les lignes d'une table, et chacune a un identificateur unique. L'objet autonome peut également être désigné sous le nom d'objet référençable. Il est également possible d'effectuer un select sur un objet référençable, dans ce cas vous cherchez l'objet par valeur au lieu de chercher sa référence (REF).

 
Sélectionnez
CREATE TYPE person_t AS OBJECT
 (name varchar2(30),
  age number(3));
CREATE TABLE person_tab OF person_t;

Les objets qui sont stockés dans la table d'objet Person_tab sont des instances autonomes. Ils ont des identifiants d'objet et sont référençables.

IV-I-2. Objet inclus (Embedded object)

Des instances incluses ne sont pas stockées comme une ligne dans une table. Elles sont incluses dans d'autres structures. Exemple, des objets inclus sont des objets qui sont des attributs d'un autre objet ou des instances qui existent dans une colonne d'objet d'une table de base de données. Les instances incluses n'ont pas d'identificateur d'objet.

Des objets inclus peuvent également être désignés sous le nom d'objet non référençables ou valeur d'instance. Vous pouvez parfois voir qu'ils sont référencés comme valeurs, qui ne doivent pas être confondues avec des valeurs scalaires de données. Le contexte devrait aider à faire la distinction.

 
Sélectionnez
CREATE TABLE department
 (deptno number,
  deptname varchar2(30),
  manager person_t);

Les objets qui sont stockés dans la colonne Manager de la table Department sont des objets inclus. Ils n'ont pas d'identifiant d'objet et ils ne sont pas référençables.

V. Type de données dynamiques sous Oracle 9i

Attention les scripts présentés utilisent le schéma SCOTT. Nous utiliserons les conventions suivantes :

  • p_Nom pour un nom de paramètre de fonction ou de procédure ;
  • l_Nom pour un nom de variable locale à une fonction ou une procédure ;
  • g_Nom pour un nom de variable globale d'un package.

Par exemple les instructions suivantes déclarent un type objet et une table contenant une colonne de type SYS.ANYDATA (cf. GETDATA.SQL) :

 
Sélectionnez
// Force la suppression du type Objet Employee
DROP TYPE Employee FORCE;
DROP TABLE mytab;
CREATE OR REPLACE TYPE Employee AS OBJECT ( empno NUMBER,ename VARCHAR2(10) );
/
CREATE TABLE mytab ( id NUMBER, data SYS.ANYDATA );

Les instructions suivantes permettent d'insérer dans la colonne DATA une donnée de type number pour la ligne ayant l'ID 1 et une donnée de type objet pour la ligne ayant l'ID 2 :

 
Sélectionnez
INSERT INTO mytab VALUES (1, SYS.ANYDATA.ConvertNumber(5));
INSERT INTO mytab VALUES (2, SYS.ANYDATA.ConvertObject(Employee(5555, 'john')));
Commit;

Pour manipuler et contrôler l'information de type, vous devez employer le type natif SYS.ANYTYPE en association avec le package DBMS_TYPES.

Il n'existe pas de méthode pour afficher dans une requête le contenu d'une colonne de type ANYDATA. On ne peut qu'appliquer ces propres méthodes, par exemple :

 
Sélectionnez
SELECT T.Data.GetTypeName() "Nom de type" from Mytab t;

renvoi comme résultat :

 
Sélectionnez
Nom de type
--------------------------------------------------------------------------------
SYS.NUMBER
SCOTT.EMPLOYEE

On s'aperçoit que le nom de type pour un objet contient le nom du schéma propriétaire associé au nom de l'objet.

Une fois les insertions réalisées, il nous reste à interpréter le type d'origine de chaque ligne pour traiter convenablement chaque valeur. L'exemple ci-dessous définit et exécute une procédure PL/SQL qui utilise les méthodes de SYS.ANYDATA pour accéder aux informations des données stockées dans la colonne de table de type SYS.ANYDATA :

 
Sélectionnez
create or replace function GetData( p_Data in sys.anyData ) return Varchar2
as
    l_Num number;
    l_Date date;
    l_Varchar2 varchar2(4000);
begin
    case p_Data.GetTypeName
        when 'SYS.NUMBER' then
            if ( p_Data.GetNumber( l_Num ) = dbms_types.success )
            then
                l_Varchar2 := l_Num;
            end if;
        when 'SYS.DATE' then
            if ( p_Data.GetDate( l_Date ) = dbms_types.success )
            then
                l_Varchar2 := l_Date;
            end if;
        when 'SYS.VARCHAR2' then
            if ( p_Data.GetVarchar2( l_Varchar2 ) = dbms_types.success )
            then
                null;
            end if;
        else
            l_Varchar2 := '** inconnu **';
    end case;
    return l_Varchar2;
end;
/

En exécutant cette procédure, nous obtenons le résultat suivant :

 
Sélectionnez
Select GetData( DATA ) Données from Mytab;
DONNÉES
----------------------
5
** inconnu **

Note
Il n'existe pas de nom de type tel que SYS.OBJECT, par contre il existe la constante numérique Dbms_Types.Typecode_OBJECT.

VI. Exemple commenté

Cet exemple ci-dessous est extrait de la documentation Oracle, il définit et exécute une procédure PL/SQL qui utilise les méthodes de SYS.ANYDATA pour accéder aux informations des données stockées dans une colonne de table de type SYS.ANYDATA (cf. DemoAnyData-1.sql).

 
Sélectionnez
CREATE OR REPLACE procedure P IS
 
CURSOR cur IS 
 SELECT id, data 
 FROM mytab;
 -- Déclaration de variables basées table
v_id        mytab.id%TYPE;
v_data        mytab.data%TYPE;
 -- Déclaration de variables pour la gestion du type d'une variable
v_type        SYS.ANYTYPE;
v_typecode    PLS_INTEGER;
v_typename    VARCHAR2(60);
 -- Déclaration de variables pour la gestion d'une variable EMPLOYEE
v_dummy    PLS_INTEGER;
v_n        NUMBER;
v_employee    Employee;
 -- Déclaration des exceptions gérées
non_null_anytype_for_NUMBER    Exception;
unknown_typename            Exception;
BEGIN
 OPEN cur;
 LOOP
   FETCH cur INTO v_id, v_data;
   EXIT WHEN cur%NOTFOUND;
    
   v_typecode := v_data.GetType (v_type); -- v_type est un paramètre OUT

La variable v_typecode indique quel type est représenté par v_data.
La méthode GetType renvoie une valeur de type SYS.AnyType qui permet via des méthodes appropriées de retrouver la précision et la taille d'un nombre, la longueur d'une chaîne, et ainsi de suite.

Maintenant nous comparons la variable v_typecode aux constantes contenues dans le package DBMS_TYPES afin de voir quel genre de données nous avons, et décider comment les représenter.

 
Sélectionnez
CASE v_typecode
 WHEN Dbms_Types.Typecode_NUMBER THEN
  IF v_type IS NOT NULL
  -- Cette condition ne devrait jamais se produire, mais nous vérifions juste au cas où.
   THEN RAISE non_null_anytype_for_NUMBER; 
  END IF;
    -- Pour chaque type natif, il y a une méthode Get.
   v_dummy := v_data.GetNumber ( v_n ); -- v_n est un paramètre OUT
   Dbms_Output.Put_Line (
   To_Char(v_id) || ': NUMBER = ' || To_Char(v_n) );
 WHEN Dbms_Types.Typecode_OBJECT THEN
  v_typename := v_data.GetTypeName();
  -- Un nom de type objet est qualifié avec le nom du schéma.
  IF v_typename NOT IN ( 'SCOTT.EMPLOYEE' ) 
  -- Si on rencontre n'importe quel type d'objet autre qu'EMPLOYEE, on lève une exception.
   THEN RAISE unknown_typename; 
  END IF;
  -- Récupère la valeur de l'instance contenue dans la variable v_Data
  v_dummy := v_data.GetObject ( v_employee ); -- v_employee est un paramètre OUT
  Dbms_Output.Put_Line (
  To_Char(v_id) || ': type utilisateur = ' || v_typename ||
' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
 END CASE;
END LOOP;
CLOSE cur;
EXCEPTION
 WHEN non_null_anytype_for_NUMBER THEN
  RAISE_Application_Error ( -20000,
  ' ATTENTION : l''instance AnyType retournée par GetType ' ||
  ' devrait être NUL pour presque tous les types utilisateur.' );
 WHEN unknown_typename THEN
  RAISE_Application_Error ( -20000, 'Type utilisateur inconnu ' ||
  v_typename || ' - programme écrit pour traiter uniquement SCOTT.EMPLOYEE' ); 
END;
/

En exécutant cette procédure, nous obtenons le résultat suivant :

 
Sélectionnez
SQL> EXEC P;
1: NUMBER = 5
2: user-defined type = SCOTT.EMPLOYEE ( 5555, john )

Note
Vous pouvez accéder aux mêmes possibilités du langage via OCI, en utilisant les interfaces OCIType, OCIAnyData et OCIAnyDataSet.

VII. Comment ANYDATA diffère des types définis par l'utilisateur

ANYDATA est un type Oracle qui peut contenir des instances de n'importe quel type de données Oracle ou défini par l'utilisateur. ANYDATA est un type autodescriptif et propose une simulation de la réflexion que vous pouvez employer pour déterminer le type réel d'une instance. On peut donc demander à une instance de type AnyData le type exact de l'instance contenue.

VII-A. Le polymorphisme

L'héritage, par la possibilité de substitution, et le type ANYDATA proposent le polymorphisme permettant de stocker n'importe quel ensemble d'instances possibles dans un 'conteneur', les deux modèles offrent cette possibilité de manière très différente. Dans le modèle d'héritage, l'ensemble polymorphe d'instances possibles doit faire partie d'une hiérarchie de type simple. Une variable peut potentiellement contenir des instances seulement de son type défini ou de ses sous-types.

Vous pouvez accéder aux attributs du supertype et appeler des méthodes définies dans le supertype (et potentiellement surchargées par le sous-type). Vous pouvez également examiner le type spécifique d'une instance en utilisant les opérateurs IS OF et TREAT.

Les variables ANYDATA, cependant, peuvent stocker des instances hétérogènes. Vous ne pouvez pas accéder à des attributs ou appeler des méthodes d'une instance réelle stockée dans une variable de type ANYDATA (à moins que vous l'extrayiez de l'instance dans une variable de son type). Vous employez les méthodes d'ANYDATA pour retrouver et extraire le type de l'instance. ANYDATA est un mécanisme très utile pour passer des paramètres à une fonction ou procédure qui ne s'inquiète pas de leur type spécifique.

L'héritage offre une meilleure modélisation, un typage fort, la spécialisation… Employez ANYDATA quand vous voulez simplement pouvoir manipuler tout type possible d'instances qui n'ont rien en commun.

VIII. Exemple de généricité

Je remercie M. Zlatko Sirotic qui m'a permis d'utiliser l'exemple suivant issu de son article publié sur Quest-pipelines.com.

L'exercice que l'on se propose de réaliser est une insertion d'un type enregistrement générique en utilisant les types ANYDATA et ANYTYPE.

Sous Oracle 9i, nous pouvons employer les nouveaux types de données ANYDATA, ANYTYPE et ANYDATASET. Nous pouvons également insérer ou mettre à jour une table SQL en indiquant une variable PL/SQL de type record plutôt que d'indiquer chaque attribut séparément (voir l'exemple DemoO39.sql).
Ces exemples montrent comment employer ensemble ces deux possibilités.

Comment construire une procédure générique permettant d'insérer un enregistrement dans n'importe quelle table, équivalente à :

 
Sélectionnez
PROCEDURE gen_insert ( p_table  VARCHAR2,  p_record "Type_enregistrement_générique") IS
   l_requete VARCHAR2 (32000);
BEGIN
   l_requete:= 'INSERT INTO ' || p_table || ' VALUES (:record)';
   EXECUTE IMMEDIATE l_requete USING IN p_record;
END;

Ainsi, si p_table = 'EMP' alors la variable p_record est du type EMP%ROWTYPE, et si p_table = 'DEPT' alors elle est du type DEPT%ROWTYPE, etc.

VIII-A. Avec des tables objet

Si nous avons des tables d'objets uniquement (ni objet-relationnel ni relationnel), la solution est alors relativement simple (cf. GenericAnyData-2-1.sql) :

 
Sélectionnez
CREATE TYPE dept_obj_type AS OBJECT (
   deptno NUMBER (2),
   dname  VARCHAR2 (20)
)
/
CREATE TABLE dept_obj_table OF dept_obj_type
/
CREATE TYPE emp_obj_type AS OBJECT (
   empno  NUMBER (4),
   ename  VARCHAR2 (20),
   deptno NUMBER (2)
)
/
CREATE TABLE emp_obj_table OF emp_obj_type
/
-- Procédure d'insertion d'une variable "type objet générique" 
CREATE OR REPLACE PROCEDURE gen_insert1 (
   p_table   VARCHAR2,
   p_anydata ANYDATA)
IS
   l_statement VARCHAR2 (32000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('Nom du type de la variable p_anydata: '||
                Nvl(p_anydata.GetTypeName(),'Type transitoire anonyme'));
   l_statement :=
   '  DECLARE' ||
   '     l_anydata ANYDATA := :p_anydata;' ||
   '     l_object ' || p_anydata.GetTypeName || ';' ||
   '     l_result_code PLS_INTEGER;' ||
   '  BEGIN ' ||
   '     l_result_code := l_anydata.GetObject (l_object);' ||
   '     INSERT INTO ' || p_table || ' VALUES (l_object);' ||
   '  END;';
  EXECUTE IMMEDIATE l_statement USING IN p_anydata;
END;
/

Utilisons un bloc anonyme pour tester cette procédure :

 
Sélectionnez
DECLARE
   l_dept dept_obj_type;
   l_emp  emp_obj_type;
BEGIN
   -- création des objets
   l_dept := dept_obj_type (10, 'dept 10');
   l_emp  := emp_obj_type (2000, 'emp 2000', 10);
   -- insertion des objets
   gen_insert1 ('dept_obj_table', ANYDATA.ConvertObject (l_dept));
   gen_insert1 ('emp_obj_table', ANYDATA.ConvertObject (l_emp));
END;
/

Nous utilisons ici une des deux possibilités pour construire une instance de type Anydata en utilisant ses propres méthodes, ici ConvertObject. Dans ce cas l'opération se fait en une seule instruction. Les méthodes Convert*() servent de fonction de CAST explicite pour tout type natif Oracle vers un type AnyData.
Consultez le document «  Supplied PL/SQL Packages and Types Reference » pour le détail des méthodes des types ANYDATA, ANYDATASET et ANYDATYPE.

Les requêtes générées sont

 
Sélectionnez
DECLARE 
 l_anydata    ANYDATA := :p_anydata;     
 l_object        SCOTT.DEPT_OBJ_TYPE;
 l_result_code    PLS_INTEGER;  
BEGIN
 l_result_code := l_anydata.GetObject(l_object); 
 INSERT INTO dept_obj_table VALUES (l_object); 
END;
 
Sélectionnez
DECLARE 
 l_anydata    ANYDATA := :p_anydata;
 l_object        SCOTT.EMP_OBJ_TYPE;
 l_result_code    PLS_INTEGER;
BEGIN
 l_result_code := l_anydata.GetObject(l_object);
 INSERT INTO emp_obj_table VALUES (l_object); 
END;

VIII-B. Avec des tables relationnelles

Si nous utilisons uniquement des tables relationnelles, la solution n'est pas si simple (cf. GenericAnyData-2-2.sql) :

 
Sélectionnez
CREATE TABLE dept_rel_table (
   deptno NUMBER (2),
   dname  VARCHAR2 (20)
)
/
CREATE TABLE emp_rel_table (
   empno  NUMBER (4),
   ename  VARCHAR2 (20),
   deptno NUMBER (2)
)
/

La seconde possibilité, pour construire une instance de type Anydata, est une approche élément par élément.
L'instance AnyData doit être construite ou consultée séquentiellement à partir de son premier attribut (ou élément de collection).
L'appel de la méthode BeginCreate() commence automatiquement la construction en mode élément par élément et la méthode EndCreate() doit être appelée pour terminer la construction. Entre ces deux appels, les différents attributs d'un type d'objet ou les éléments d'une collection peuvent être renseignés en utilisant les méthodes Set*(). Dans ce cas il n'y a aucun besoin d'appeler la méthode PieceWise() juste après BeginCreate().

Pour accéder aux éléments des collections et des attributs des objets élément par élément, la méthode PieceWise() devrait être appelée avant les appels à Get*().
Ce mode d'accès ne permet pas d'accéder aux attributs imbriqués de type objet ou collection.

Nous utiliserons un package pour créer les enregistrements de type ANYTYPE et ANYDATA des tables Dept et Emp.

Nous déclarons donc pour chaque table utilisée dans cet exemple, un enregistrement de table et deux fonctions dédiées. La première fonction NomTable_anytype permet de déclarer un type transitoire et la seconde NomTable_anydata permet d'assigner les valeurs d'attribut d'une instance de ce type.

 
Sélectionnez
CREATE OR REPLACE PACKAGE create_anydata IS
g_dept_rel_table     dept_rel_table%ROWTYPE;
FUNCTION dept_anytype RETURN ANYTYPE;
FUNCTION dept_anydata (
   p_anytype ANYTYPE,
   p_deptno  NUMBER,
   p_dname   VARCHAR2)
   RETURN ANYDATA;
g_emp_rel_table emp_rel_table%ROWTYPE;
FUNCTION emp_anytype RETURN ANYTYPE;
FUNCTION emp_anydata (
   p_anytype ANYTYPE,
   p_empno   NUMBER,
   p_ename   VARCHAR2,
   p_deptno  NUMBER)
   RETURN ANYDATA;
END create_anydata;

Voici la définition de la Fonction dept_anytype :rmations des données stockées dans la colonne de table

 
Sélectionnez
FUNCTION dept_anytype RETURN ANYTYPE IS
   l_dept_anytype ANYTYPE;
BEGIN
   -- Création d'un nouveau type transitoire l_dept_anytype
   -- Bien que l'on veuille créer un enregistrement on utilise 
   -- la constante DBMS_TYPES.TYPECODE_OBJECT
   ANYTYPE.BeginCreate (
      typecode => DBMS_TYPES.TYPECODE_OBJECT,
      atype    => l_dept_anytype);
   -- Ajoute les attributs 'deptno' et 'dname' au type transitoire l_dept_anytype
   l_dept_anytype.AddAttr (
      aname    => 'deptno',
      typecode => DBMS_TYPES.TYPECODE_NUMBER,
      prec     => 2,
      scale    => 0,
      len => NULL, csid => NULL, csfrm => NULL);
   l_dept_anytype.AddAttr (
      aname    => 'dname',
      typecode => DBMS_TYPES.TYPECODE_VARCHAR2,
      len      => 20,
      prec => NULL, scale => NULL, csid => NULL, csfrm => NULL);
   -- Termine la construction du type transitoire l_dept_anytype 
   l_dept_anytype.EndCreate;
   RETURN l_dept_anytype;
END;

La méthode BeginCreate débute la construction du type l_dept_anytype, notez qu'on appel cette méthode en préfixant avec ANYTPE et pas avec l_dept_anytype qui «  n'existe » pas encore. Une fois ce nouveau type créé nous appelons sa méthode AddAttr qui déclare chacun de ses attributs puis nous terminons sa construction en appelant la méthode EndCreate et ici en préfixant l'appel avec le nom du type l_dept_anytype.
Le nouveau type étant créé, on peut dorénavant créer une instance de ce type et renseigner (assigner) la valeur de ses attributs.

 
Sélectionnez
FUNCTION dept_anydata (
   p_anytype ANYTYPE,
   p_deptno  NUMBER,
   p_dname   VARCHAR2)
   RETURN ANYDATA
IS
   l_anytype ANYTYPE := p_anytype;
   l_anydata ANYDATA;
BEGIN
    -- Débute la création de la variable l_anydata de type ANYDATA
    -- Le type "spécifique" de l_anydata est l_anytype
   ANYDATA.BeginCreate (
     dtype => l_anytype,
     adata => l_anydata);
    -- Renseigne les valeurs de la variable
   l_anydata.SetNumber   (num => p_deptno);
   l_anydata.SetVarchar2 (c   => p_dname);
    -- Termine la création de la variable l_anydata
   l_anydata.EndCreate;
   RETURN l_anydata;
END;

La méthode BeginCreate débute la construction de l'instance l_anydata, notez qu'on appel cette méthode en préfixant avec ANYDATA et pas avec l_anydata qui «  n'existe » pas encore. Le paramètre p_anytype sera renseigné directement par l'appel de create_anydata.dept_anytype qui renverra le type transitoire l_dept_anytype.
Puis nous appelons, pour chaque attribut, sa méthode Setxxx et nous terminons la construction du type par l'appel de l_anydata.EndCreate.
L'instance étant créé on peut dorénavant la manipuler.
Une fois cette instance créée nous appelons la méthode Setxxx correspondant au type de l'attribut que nous renseignons, puis nous terminons sa construction en appelant la méthode EndCreate et ici en préfixant l'appel avec le nom de l'instance l_anydata.

Pour manipuler les types précédemment créés, on utilisera la procédure suivante :

 
Sélectionnez
CREATE OR REPLACE PROCEDURE gen_insert2 (
   p_table   VARCHAR2,
   p_anydata ANYDATA)
IS
   l_anydata       ANYDATA;
   l_anytype       ANYTYPE;
   l_typeid        PLS_INTEGER;
   l_attr_typeid   PLS_INTEGER;
    -- Éléments d'information sur un attribut
   l_prec          PLS_INTEGER;
   l_scale         PLS_INTEGER;
   l_len           PLS_INTEGER;
   l_csid          PLS_INTEGER;
   l_csfrm         PLS_INTEGER;
   l_schema_name   VARCHAR2(30);
   l_type_name     VARCHAR2(30);
   
   l_version       VARCHAR2(30);
   l_count         PLS_INTEGER;
   
   l_attr_elt_type ANYTYPE;
   l_aname         VARCHAR2(30);
   l_statement     VARCHAR2 (32000);
   l_result_code   PLS_INTEGER;
   l_number        NUMBER;
   l_string        VARCHAR2 (32000);
   l_date          DATE;
BEGIN
   l_anydata := p_anydata;
    -- Obtient le typecode de l_anydata dans  l_anytype
   l_typeid  := l_anydata.GetType (typ => l_anytype);
    -- Obtient les informations de type de l_anytype. C'est-à-dire la taille, la précision…
   l_typeid  := l_anytype.GetInfo (
                   l_prec, l_scale, l_len, l_csid, l_csfrm,
                   l_schema_name, l_type_name, l_version, l_count);
    -- Définit le mode d'accès attribut par attribut 
   l_anydata.PieceWise;
    -- l_count renvoie le nombre d'attributs
   FOR pos IN 1..l_count LOOP
       -- Obtient l'information de type d'un attribut du type
      l_attr_typeid := l_anytype.GetAttrElemInfo (
                          pos,
                          l_prec, l_scale, l_len, l_csid, l_csfrm,
                          l_attr_elt_type, l_aname);
       -- Construit un bloc PL/SQL anonyme afin de renseigner une des variables globales du package
      l_statement :=
      '  BEGIN ' ||
      '     create_anydata.g_' || p_table || '.' || l_aname || ' := :l_column;' ||
      '  END;';
       -- Obtient la valeur actuelle du type correspondant
       -- et exécute le bloc pour une assignation dynamique de l'enregistrement
      CASE l_attr_typeid
         WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
            l_result_code := l_anydata.GetNumber (l_number);
            EXECUTE IMMEDIATE l_statement USING IN l_number;
        
         WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN
            l_result_code := l_anydata.GetVarchar2 (l_string);
            EXECUTE IMMEDIATE l_statement USING IN l_string;
         
         WHEN DBMS_TYPES.TYPECODE_DATE THEN
            l_result_code := l_anydata.GetDate (l_date);
            EXECUTE IMMEDIATE l_statement USING IN l_date;
         ELSE
            NULL;
      END CASE;
   END LOOP;
    -- Construit un bloc PL/SQL anonyme afin d'insérer l'enregistrement précédemment crée
    -- Ce type d'insertion est possible uniquement sous Oracle 9.2
   l_statement :=
   '  BEGIN' ||
   '     INSERT INTO ' || p_table ||
   '          VALUES create_anydata.g_' || p_table || ';' ||
   '  END;';
   EXECUTE IMMEDIATE l_statement;
END;
/

Utilisons ce bloc PL/SQL anonyme pour tester ce package :

 
Sélectionnez
DECLARE
   l_dept_anydata    ANYDATA;
   l_emp_anydata    ANYDATA;
   l_anytype         ANYTYPE;
   ltype            pls_integer;
   ret            pls_integer;
BEGIN
   - - création des enregistrements
   l_dept_anydata :=
      create_anydata.dept_anydata (
         p_anytype => create_anydata.dept_anytype,
         p_deptno  => 10,
         p_dname   => 'dept 10');
   l_anytype:=create_anydata.dept_anytype;
   DBMS_OUTPUT.PUT_LINE('Nom du type de la variable l_dept_anydata: '||
            Nvl(l_dept_anydata.GetTypeName(),'Type transitoire anonyme'));
    -- Ajout de 2 lignes suivantes afin d'afficher le code  du type
   ret:=l_dept_anydata.GetType(ltype);
   DBMS_OUTPUT.PUT_LINE('GetType '||Nvl(ltype,'TypeCode est NULL'); 
   l_emp_anydata :=
      create_anydata.emp_anydata (
         p_anytype => create_anydata.emp_anytype,
         p_empno   => 2000,
         p_ename   => 'emp 2000',
         p_deptno  => 10);
   -- insertion des enregistrements
   gen_insert2 ( p_table   => 'dept_rel_table',
         p_anydata => l_dept_anydata);
   gen_insert2 ( p_table   => 'emp_rel_table',
         p_anydata => l_emp_anydata);
END;
/

IX. Le type AnyDataSet

Un type d'ANYDATASET contient une description d'un type donné plus un ensemble d'instances de données de ce type. Un ANYDATASET peut être stocké dans la base de données ou il peut être employé comme paramètres pour communiquer des ensembles de données autodescriptives qui appartiennent à un certain type et à un seul.

IX-A. Utilisation avec un type natif

La construction d'un type ANYDATASET est pratiquement identique à celui d'un ANYDATA (cf. AnyDataSet-base.sql).

 
Sélectionnez
Declare
 I              pls_integer;
 l_ADS          AnyDataSet;
 l_Anytype      AnyType;
 Nombre         Number; 
Begin
 AnyDataSet.BeginCreate(DBMS_TYPES.TYPECODE_NUMBER,l_Anytype,l_Ads);
 
  -- Assignation des données
 For I IN 1..3 Loop
   -- Ajoute une instance puis sa valeur
  l_Ads.AddInstance;
  l_Ads.SetNumber(I);
 End Loop;
 l_Ads.EndCreate;
 
 DBMS_OUTPUT.PUT_LINE('Nom du type de la variable l_Ads : '||Nvl(l_Ads.GetTypeName(),'Type transitoire anonyme'));
 
  -- Retrouve les données
 For I IN 1..l_Ads.GetCount Loop
  If l_Ads.GetInstance=DBMS_TYPES.SUCCESS 
   Then
    Nombre:=0;
    If l_Ads.GetNumber(Nombre)=DBMS_TYPES.SUCCESS 
     Then Dbms_Output.Put_Line('itération '||To_Char(I)||' Nombre='||To_Char(Nombre));
    End If;
  End If;  
 End Loop;
End;

À noter que la modification, du bloc gérant l'assignation de données, de la manière suivante

 
Sélectionnez
For I IN 1..3 Loop
  l_Ads.AddInstance;
  l_Ads.SetNumber(I);
 End Loop;
 l_Ads.SetVarchar2('Dernier');
 l_Ads.EndCreate;

provoquera l'erreur ORA-22626, car l'ajout d'une donnée d'un type différent de celui déclaré lors de l'appel à BeginCreate n'est pas autorisé.

 
Sélectionnez
ERREUR à la ligne 1 :
ORA-22626: Non-concordance de types lors de la structuration de OCIAnyData ou lors de son accès
ORA-06512: à "SYS.ANYDATASET", ligne 0
ORA-06512: à ligne 18

IX-B. Utilisation avec un type objet

Ici la construction d'un type ANYDATASET diffère du précédent, car on doit retrouver l'information de type de l'objet que l'on souhaite manipuler (cf. AnyDataSet-objet.sql).

 
Sélectionnez
Declare
 l_Department     dept_obj_type;
 l_Employe     emp_obj_type;
 I         pls_integer;
 l_ADS        AnyDataSet;
 l_Anytype     AnyType;
  
Begin
 l_Department:=dept_obj_type(10,'Ventes');
 l_Employe:=emp_obj_type(1,'Dupont',10);
 
 -- Le nom de l'objet doit être en majuscule
 l_Anytype := AnyType.GetPersistent('SCOTT','DEPT_OBJ_TYPE') ;    
 AnyDataSet.BeginCreate(DBMS_TYPES.TYPECODE_OBJECT,l_Anytype,l_Ads);
  -- Assignation des données
 l_Ads.AddInstance;
 l_Ads.SetObject(l_Department);
 -- l_Ads.AddInstance; -- si 2 appels de addinstance alors une erreur survient
 
 l_Ads.AddInstance;
 l_Ads.PieceWise() ;
 l_Ads.SetNumber(20);
 l_Ads.SetVarchar2('SAV');
 l_Ads.EndCreate;
 
 DBMS_OUTPUT.PUT_LINE('Nom du type de la variable l_Ads : '||Nvl(l_Ads.GetTypeName(),'Type transitoire anonyme'));
 
  -- Retrouve les données
 For I IN 1..l_Ads.GetCount Loop
  If l_Ads.GetInstance=DBMS_TYPES.SUCCESS 
   Then
    l_Department:=Null;
    If l_Ads.GetObject(l_Department)=DBMS_TYPES.SUCCESS 
     Then Dbms_Output.Put_Line('itération '||To_Char(I)||' DEPT_OBJ_TYPE ='||l_Department.Deptno||' '||l_Department.DName);
    End If;
  End If;  
 End Loop;
End;
/

L'appel suivant :

 
Sélectionnez
AnyDataSet.BeginCreate(DBMS_TYPES.TYPECODE_OBJECT,'SCOTT.DEPT_OBJ_TYPE',l_Ads);

Provoquera l'erreur ORA-22369 :

 
Sélectionnez
ERREUR à la ligne 1 :
ORA-22369: paramètre non valide dans la méthode ORA-22369: paramètre non valide
dans la méthode AnyDataSet BeginCreate
ORA-06512: à "SYS.ANYDATASET", ligne 0
ORA-06512: à ligne 13

On doit donc récupérer l'information de type par l'appel de la méthode AnyType.Getpersistent.

À noter que vous perdrez la connexion avec la base si vous appelez la méthode AddInstance deux fois de suite sans, entre-temps, appeler la méthode Setxx.

 
Sélectionnez
ERREUR à la ligne 1 :
ORA-03114: pas connecté à ORACLE

IX-C. Utilisation avec un Anydata

Ici nous ne pouvons utiliser la constante DBMS_TYPES.TYPECODE_OBJECT, car dans ce cas nous attendons un type objet défini par l'utilisateur. On utilisera par contre la constante DBMS_TYPES.TYPECODE_OPAQUE en association avec un type Anydata.
Ainsi cet ensemble pourra contenir différentes instances de différents types (cf. AnyDataSet-opaque.sql).

 
Sélectionnez
Declare
 l_Department     dept_obj_type;
 l_Employe     emp_obj_type;
 Nombre:=159;
 I         pls_integer;
 Anyd        AnyData;
 l_ADS        AnyDataSet;
 l_Anytype     AnyType;
 Nombre     Number; 
Begin
 l_Department:=dept_obj_type(10,'Ventes');
 l_Employe:=emp_obj_type(1,'Dupont',10);
 
 -- Le nom de l'objet doit être en majuscule
 l_Anytype := AnyType.GetPersistent('SYSTEM','ANYDATA') ;
 AnyDataSet.BeginCreate(DBMS_TYPES.TYPECODE_OPAQUE,l_Anytype,l_Ads);
  
  -- Assignation des données
 
 l_Ads.AddInstance;
 Anyd:=ANYDATA.ConvertObject(l_department);
 l_Ads.SetObject(Anyd);
 
 l_Ads.AddInstance;
 Anyd:=ANYDATA.ConvertObject(l_Employe);
 l_Ads.SetObject(Anyd);
 
 l_Ads.AddInstance;
 Anyd:=ANYDATA.ConvertNumber(Nombre); -- ou  Anyd:=ANYDATA.ConvertNumber(159);
 l_Ads.SetObject(Anyd);
 
 l_Ads.EndCreate;
 
 DBMS_OUTPUT.PUT_LINE('La variable l_Ads est de type  '||
            Nvl(l_Ads.GetTypeName(),'Type transitoire anonyme')||' 
            et contient '||To_Char(l_Ads.GetCount)||' éléments' );
 
  -- Retrouve les données
 For I IN 1..l_Ads.GetCount Loop
  If l_Ads.GetInstance=DBMS_TYPES.SUCCESS 
   Then
    Anyd:=Null;
    If l_Ads.GetObject(Anyd)=DBMS_TYPES.SUCCESS 
     Then Dbms_Output.Put_Line('L''instance '||To_Char(I)||' est de type '||
            Nvl(Anyd.GetTypeName(),'Type transitoire anonyme'));
    End If;
  End If;  
 End Loop;
End;
/

Une collection offre plus de souplesse et un accès indicé aux données.

 
Sélectionnez
Create Type TvryTest AS VARRAY(4) of ANYDATA
/

IX-D. Utilisation avec d'autres types de données

Vous trouverez des exemples de source manipulant d'autre type de données dans la distribution Oracle (Cf. ..\Demo-Oracle\*.sql) :

  • %Oracle_Home%\rdbms\demo\anytype.sql ;
  • %Oracle_Home%\rdbms\demo\anydata.sql ;
  • %Oracle_Home%\rdbms\demo\anydset.sql.

X. Annexe

X-A. Comment retrouver la taille stockée dans un Anydata

Vous pouvez retrouver la taille en octets de l'instance contenue dans un AnyData en utilisant la fonction VSize :

 
Sélectionnez
Select id,vsize(data) from mytab;

X-B. Liens

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2010 Laurent Dardenne. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.