Les types opaques sous Oracle 9i.

Date de publication : 05/01/2004

Par Laurent Dardenne (home page)
 

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 oeuvre le polymorphisme et la généricité.

Attention ces nouveaux type ne sont disponibles qu'à partir de la version 9i.

1. Public concerné

2. Introduction

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

• 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.

3. Type générique et type transitoire

3-1. SYS.ANYTYPE

3-2. SYS.ANYDATA

3-3. SYS.ANYDATASET

3-4. Utilisation

4. Définitions

4-1. Type persistant ( Persistent types )

4-2. Type transitoire ( Transient type )

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

4-3. Objet transitoire ( Transient Object )

4-4. Donnée auto-descriptive ( Self-descriptive data )

4-5. Ensemble de donnée auto-descriptive ( Self-descriptive dataset )

4-6. Surtype ( Supertype )

4-7. Soustype ( Subtype )

4-8. Substitution

4-9. Objet persistant ( persistent object )

4-9-1. Objet autonome ( Standalone object )

CREATE TYPE person_t AS OBJECT
 (name varchar2(30),
  age number(3));

CREATE TABLE person_tab OF person_t;

4-9-2. Objet inclu ( Embedded object )

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

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

• 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.

  // 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 );

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

Commit;

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

Nom de type
--------------------------------------------------------------------------------
SYS.NUMBER
SCOTT.EMPLOYEE

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;
/

Select GetData( DATA ) Données from Mytab;

DONNÉES
----------------------
5
** inconnu **

6. Exemple commenté

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

 CASE v_typecode
 WHEN Dbms_Types.Typecode_NUMBER THEN
  IF v_type IS NOT NULL
  -- Cette condition devrait ne 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;
/

SQL> EXEC P;

1: NUMBER = 5
2: user-defined type = SCOTT.EMPLOYEE ( 5555, john )

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

7-1. Le polymorphisme

8. Exemple de généricité

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;

8-1. Avec des tables objets

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;
/

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;
/

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;

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;

8-2. Avec des tables relationnelles

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)
)
/

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;

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;

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;

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;

    -- Elé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éfini le mode d'accès attribut par attribut 
   l_anydata.PieceWise;

    -- l_count renvoi 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;
/

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 suivante 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;
/

9. Le type AnyDataSet

9-1. Utilisation avec un type natif

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;

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

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

9-2. Utilisation avec un type objet

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;
/

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

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

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

9.3. Utilisation avec un Anydata

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;
/

Create Type TvryTest AS VARRAY(4) of ANYDATA
/

9.3. Utilisation avec d'autres types de donnés

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

10. Annexe

10-1. Comment retrouver la taille stockée dans un Anydata.

 Select id,vsize(data) from mytab;

10-2. Liens