PL-SQL et procédures externe, mise en œuvre avec DELPHI

Cet article décrit la mise en ouvre des procédures externes utilisées avec le langage PLS/SQL d'Oracle server (NT). Il s'agit d'une introduction, si vous souhaitez approfondir le sujet ou si vous utilisez le C ou JAVA consulter la documentation Oracle.

Article lu   fois.

L'auteur

Site personnel

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

I. Avant-propos

Testé avec Delphi 5 sous XP pro.

version 1.0
Dernière mise à jour, le 29/06/2004

II. Prérequis

Connaissances de base sur les langages SQL, PL/SQL et Delphi, accessoirement quelques notions sur le langage C.

Logiciels :
Oracle Serveur version supérieure à 8.0.x
Delphi version supérieure ou égal à 3.
La possibilité d'ouvrir une session sur une instance Oracle.
L'accès à l'outil SQL*Plus ou un outil similaire.
Un accès à la documentation Oracle.

Les fichiers sources : unités OCI_*.Pas, scripts *.SQL…
La procédure Delphi Log utilise la variable TEMP et référence l'unité de disque C:. Modifiez-la selon votre environnement.

Vous pouvez télécharger la documentation Oracle sur http://otn.oracle.com/documentation/oracle9i.html

Droits Oracle
Ouvrir une session, créer des procédures / fonctions, des librairies
Droit EXECUTE sur les objets library concernés.
(Droit de créer un utilisateur et ce pour le script DroitsAppelantCreateur.sql )

Droits NT
Une session donnant droits aux outils Oracle et Delphi.

Plate-forme utilisée lors de la rédaction :
Windows XP Pro, Oracle 9.2.0.4.0, Delphi v5 C/S patché.

II-A. Le code sources des exemples

Vous trouverez ici les exemples en Delphi et PL/SQL utilisés dans cet article. J'ai essayé d'aborder progressivement le sujet au travers de ces 6 exemples.

Chaque répertoire contient un fichier projet d'une DLL Delphi.
Chaque DLL est associée à un fichier TestN.sql qui contient les ordres de création des wrappers, et à un ou plusieurs fichiers RunN.sql qui contiennent le code de démonstration/exécution des DLL.

Chaque fichier script référençant des ordres de création de Library doit être modifié pour refléter votre arborescence.

 
Sélectionnez
Trace.pas:	Fonction de trace/log.

OraTypes.pas:    Déclaration des types OCI de base.

OCI_Number.pas:  Gestion du type OCINUMBER

OCI_Date.pas:    Gestion du type OCIDATE

OCI_Statement.pas: Gestion des callback SQL de Delphi vers la base. Toutes les fonctions ne sont pas présentes.
OCI_ExtProc.pas: Gestion des callback d'erreur de Delphi vers la base.

J'ai écrit le code Delphi de manière la plus simple afin de se concentrer sur le sujet OCI. Il est effectivement possible de créer des objets autour de ces types et fonctions OCI. Par contre l'approche composant ne me semble pas judicieux.

Le répertoire ExtProc contient les DLL créés sous Delphi.

III. Procédure externe

On appelle routine externe, toute procédure ou fonction écrite dans un langage autre que le PL/SQL et pouvant être appelée à partir du PL/SQL.
Le langage PL/SQL s'avère parfois inadéquat pour certaine tâche et/ou situation, dans ces cas là on utilisera un L3G tel que le C, JAVA ou Delphi pour résoudre le problème, ou tout autre langage supportant les conventions d'appel du langage C.

Seuls les langages C et Java sont supportés nativement par Oracle.
Sous Windows il s'agit d'un fichier .DLL, sous Unix / Linux, il s'agit d'un fichier .so
Ces fichiers peuvent contenir une ou plusieurs procédures externes.

III-A. Chargement d'une procédure externe

Lorsque a lieu un appel à une routine externe, le listener Net8 généra un processus spécial nommé extproc. C'est ce processus qui chargera dynamiquement les DLL contenant les sous-programmes C ou Delphi et l'exécutera, puis il retournera le résultat au processus serveur puis au client.
La durée de vie du processus extproc est égale à celui de la session et il existe un processus extproc par session. Il occupe une place mémoire en dehors de la SGA.

À noter que l'architecture est différente lorsque l'on utilise java.

Oracle et les procédures externes

Image non disponible

III-A-1. Configuration du listener

Les fichiers listener.ora et tnsnames.ora sont situés dans le répertoire %ORACLE_HOME%\network\admin.
Si vous avez installé la version Oracle 9i (disponible sur le site WEB d'Oracle ) avec les options par défaut, la configuration est déjà réalisée.
Vous pouvez utiliser l'outil Net8 Assistant (8.1.7), Net Manager ( 9i R2) ou modifier manuellement les fichiers.

III-A-1-a. Configuration du fichier LISTENER.ORA du serveur

Ajoutez un nom d'identifiant système (SID) et un nom de programme pour EXTPROC

 
Sélectionnez
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =

      (ADDRESS_LIST =        ajouter cette ligne
        (ADDRESS =           ajouter cette ligne
          (PROTOCOL = IPC)   ajouter cette ligne
          (KEY = EXTPROC0))  ajouter cette ligne
        )                    ajouter cette ligne
)                            ajouter cette ligne

      (ADDRESS_LIST =
…

SID_LIST_LISTENER =
  (SID_LIST =

    (SID_DESC =                           ajouter cette ligne
      (SID_NAME = PLSExtProc)             ajouter cette ligne
      (ENVS=EXTPROC_DLLS=ANY)             ajouter cette ligne
      (ORACLE_HOME = C:\oracle\ora92)     ajouter cette ligne, * modifier avec votre Home Directory
      (PROGRAM = extproc)                 ajouter cette ligne
    )                                     ajouter cette ligne

    (SID_DESC =
…

III-A-1-b. Configuration du fichier TNSNAMES.ORA du serveur

Ajoutez une entrée de nom de service pour EXTPROC.

 
Sélectionnez
…
EXTPROC_CONNECTION_DATA =       ajouter cette ligne
  (DESCRIPTION =                ajouter cette ligne
    (ADDRESS_LIST =             ajouter cette ligne
      (ADDRESS =                ajouter cette ligne
        (PROTOCOL = IPC)        ajouter cette ligne
       (KEY = EXTPROC0))        ajouter cette ligne
    )                           ajouter cette ligne
    (CONNECT_DATA =             ajouter cette ligne
      (SID = PLSExtProc)        ajouter cette ligne
      (PRESENTATION = RO)       ajouter cette ligne
    )                           ajouter cette ligne
  )                             ajouter cette ligne
…

Par défaut sous Oracle9i r2, extproc chargera uniquement les librairies présentes dans les répertoires %ORACLE_HOME%/bin ou %ORACLE_HOME%/lib.

Afin d'autoriser le chargement à partir d'autre chemin, on utilisera
soit la déclaration d'une variable d'environnement EXTPROC_DLLS contenant une suite de chemin d'accès séparé par le caractère deux point ':'.

 
Sélectionnez
Set EXTPROC_DLLS= c:\mydlls\mydll.dll:c:\bill\mydll\bill_lib.dll

soit la déclaration du paramètre ENVS dans la partie SID_DESC du fichier Listener.

 
Sélectionnez
(ENVS=EXTPROC_DLLS=ANY) ou (ENVS=EXTPROC_DLLS=ONLY :DLL1 :DLL2).

Le processus extproc créé hérite des privilèges système du Listener.
Par conséquent modifier la configuration du Listener afin de l'exécuter avec des privilèges système moindres.

Pour des informations détaillées sur l'aspect sécurité voir « Oracle9i Net Services Administrator's Guide », « Modifying Configuration of External Procedures for Higher Security ».

III-A-2. Identifier la DLL

Pour identifier la DLL on utilise un objet du dictionnaire de type Library qui permet de préciser l'emplacement de la bibliothèque partagée, cet emplacement étant à l'extérieur de la base de données.

 
Sélectionnez
CREATE LIBRARY nom_bibliothéque IS | AS 'chemin_système_exploitation'

La vue du dictionnaire user_libraries permet de retrouver les informations :

 
Sélectionnez
Select * from user_libraries where library_name='DELPHILIBRARY';

Note, erreur possible :
ORA-06520: PL/SQL : Erreur pendant le chargement de la bibliothèque externe
ORA-06522: Unable to load DLL
La DLL n'existe pas dans le répertoire déclaré dans l'objet Library ou le chemin précisé est faux.

III-A-3. Publication de la procédure externe

La routine externe doit être publiée dans PL/SQL au moyen d'un point d'entrée appelable par PL/SQL, qu'Oracle nomme WRAPPER ( qui renvoi à une notion d'encapsulation ).
Son rôle est multiple, il met en correspondance les paramètres PL/SQL et les paramètres C/Java/Delphi, permet aux procédures appelantes de déterminer les dépendances et enfin indique à PL/SQL le nom de la procédure externe.

Le wrapper peut être une procédure, une fonction ou encore une partie d'un package ou d'un corps de package.

Le code de la DLL :

 
Sélectionnez
library ora_dll_1;

uses  Windows,  SysUtils,  Classes, Trace;
{$R *.RES}

Procedure IntTest(I:Integer); cdecl;
begin
  Log(inttostr(I));    // La procédure Log permet d'écrire la donnée reçue dans un fichier.
end;

Exports   IntTest;

Begin
end.

Le fichier …\Exemples\Exemple1\ora_dll_1.dpr contient 2 procédures supplémentaires.

Déclaration de la librairie, modifiez le chemin d'accès :

 
Sélectionnez
CREATE OR REPLACE LIBRARY LibraryDelphi AS
   'C:\Delphi\Aprg\Ora-DLL\ExtProc\ora_dll_1.dll';

Déclaration du wrapper :

 
Sélectionnez
CREATE OR REPLACE PROCEDURE IntDelphi (p_Int IN PLS_INTEGER)
  AS LANGUAGE C
  LIBRARY LibraryDelphi
  NAME "IntTest"
  PARAMETERS (p_Int INT);

Sous Sql*plus, exécuter le fichier …\Exemples\Exemple1\Test1.sql. La première exécution de ce script provoquera quelques erreurs car il débute par une suite de drop d'objets inexistants.

Vous devez bien entendu compiler la DLL et la placer dans le répertoire référencé par l'objet LIBRARY.

Note, sous Delphi, l'erreur fatale 'Ne peut créer le fichier de sortie 'C:\..\ora_dll_1' provient du fait que la DLL compilée est déjà chargée en mémoire, on doit déconnecter les sessions Oracle l'utilisant.

III-A-4. Exécuter la procédure externe

Maintenant que la procédure externe est publiée, on peut l'utiliser.
On n'appelle pas directement la procédure externe, mais on utilise un sous-programme PL/SQL qui appellera cette procédure. Ce type d'appel peut se trouver dans :

  • un bloc anonyme
  • Un en-tête ou dans un corps de package
  • Un en-tête de type ou dans un corps de type d'un objet Oracle
  • un triggers de base
  • une instruction SQL ( uniquement via l'appel d'une procédure d'un package ). Dans ce cas la procédure ou fonction devra utiliser la clause PRAGMA RESTRICT_REFERENCES.

III-A-4-a. Les possibles emplacements d'un Wrapper

Script ..\Exemples\Exemple1\Wrapper.sql, modifiez le chemin d'accès

 
Sélectionnez
CREATE OR REPLACE LIBRARY LibraryDelphi AS
  'C:\Delphi\Aprg\Ora-DLL\ExtProc\ora_dll_1.dll';

CREATE OR REPLACE LIBRARY WrapperLib AS
  'C:\Delphi\Aprg\Ora-DLL\ExtProc\ora_dll_1.dll';  

-- Déclaration de procédure 
CREATE OR REPLACE PROCEDURE StringDelphi(
  p_String1 IN VARCHAR2,
  p_String2 IN VARCHAR2)
  AS LANGUAGE C
  LIBRARY LibraryDelphi
  NAME "StringTest"
  PARAMETERS (p_String1 STRING,
              p_String2 STRING);


-- En-tête de Package
CREATE OR REPLACE PACKAGE WrapperPackage AS
  PROCEDURE WrapperBody (p_Int IN PLS_INTEGER);

  PROCEDURE WrapperHeader(p_String1 IN VARCHAR2,
                          p_String2 IN VARCHAR2) IS
  LANGUAGE C
  LIBRARY WrapperLib
  NAME "StringTest"
  PARAMETERS (p_String1 STRING,
              p_String2 STRING);
END WrapperPackage;

-- Corps de package
CREATE OR REPLACE PACKAGE BODY WrapperPackage AS

  PROCEDURE WrapperBody (p_Int IN PLS_INTEGER) is
  LANGUAGE C
  LIBRARY WrapperLib
  NAME "IntTest"
  PARAMETERS (p_Int INT);

END WrapperPackage;

-- En-tête d'un type Object
CREATE OR REPLACE TYPE WrapperType AS OBJECT (
  v_Attribute VARCHAR2(10),

  MEMBER PROCEDURE WrapperBody(p_Int IN PLS_INTEGER),

  MEMBER PROCEDURE WrapperHeader(p1 IN BINARY_INTEGER,
                                 p2 OUT BINARY_INTEGER) IS
    LANGUAGE C
    LIBRARY WrapperLib
    NAME "StringTest"
    PARAMETERS (SELF,
        p1 INT,
        p2 INT)
);

-- Corps d'un type Object

CREATE OR REPLACE TYPE BODY WrapperType AS
  MEMBER PROCEDURE WrapperBody(p_Int IN PLS_INTEGER) is
  LANGUAGE C
  LIBRARY WrapperLib
  NAME "IntTest"
  PARAMETERS (SELF,p_Int INT)
END;

III-A-4-b. Exécution de la procédure externe

Sous Sql*plus, exécutez le fichier …\Exemples\Exemple1\Run1.sql

 
Sélectionnez
// Appel de la routine externe 'StringText' via la procédure PL/SQL StringDelphi
Declare
  Local  PLS_INTEGER;
  Str2   Varchar2(50);
BEGIN
 Str2:='Une seconde chaine';

 StringDelphi('Une chaîne PLSQL', Str2);

 Local:=591;

 IntDelphi(Local);

END;

Le résultat des informations reçues sous Delphi se trouve dans le fichier %TEMP%\Oradll_log.txt'.

Note, erreur possible sous SQL*Plus
ERREUR à la ligne 1 :
ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: à "SYSTEM.STRINGDELPHI", ligne 0
ORA-06512: à ligne 2
Vérifier le fichier LISTENER.ORA. L'ajout de la chaîne (ENVS=EXTPROC_DLLS=ANY) peut corriger le problème dans ce cas arrêter et redémarrer le listener.

Jusqu'ici nous savons déclarer une librairie, une procédure externe et nous pouvons via une DLL réalisé sous Delphi récupérer des informations en provenance du PL/SQL.

Mais il reste d'autres points à aborder :
La récupération sous PL/SQL d'informations modifiées sous Delphi,
la gestion des valeurs nulles,
la correspondance des types de donnée entre Delphi et PL/SQL,
la gestion et la propagation d'erreurs de Delphi vers un script PL/SQL,

III-A-5. Déclaration d'une procédure externe

Sous Delphi, on utilisera la convention d'appel du C en spécifiant cdecl dans les déclarations des procédures et fonctions utilisées dans un wrapper PL/SQL.
Avec la convention cdecl, c'est à l'appelant de supprimer les paramètres de la pile au retour de l'appel.

Recommandation d'Oracle :
Bien que la DLL soit mise dans le cache ( celui d'Oracle pas celui du système ), il n'y a aucune garantie que votre DLL y restera; ne stockez donc pas de variables globales dans votre DLL.

Les DLL contenant des fonctions avec la convention d'appel StdCall, par exemple la fonction GetTempPathA de la DLL kernel32, ne peuvent être utilisées directement dans un Wrapper ( erreur ORA-28576 ) :

Pour régler ce problème, on utilisera donc une DLL une fonction intermédiaire qui se chargera d'appeler cette fonction.

Lorsqu'il est inclus dans un package ou un type, le Wrapper PL/SQL peut être surchargé.

Note : erreur possible sous PL/SQL

ERREUR à la ligne 1 :
ORA-06520: PL/SQL : Erreur pendant le chargement de la bibliothèque externe
ORA-06522: Unable to load DLL
ORA-06512: à "SYSTEM.NUMBERTEST", ligne 0
ORA-06512: à ligne 6
Une procédure ou fonction n'existe pas dans la DLL, vérifier la syntaxe et l'ordre export.

ERREUR à la ligne 1 :
ORA-28576: connexion RPC avec l'agent de procédure externe interrompue
ORA-06512: à "SYSTEM.NUMBERTEST", ligne 0
ORA-06512: à ligne 6
La procédure ou fonction DLL appelée en PL/SQL n'est pas déclarée avec la convention d'appel cdecl.

ERROR:
ORA-03114: pas connecté à ORACLE
ou
ERREUR à la ligne 1 :
ORA-03113: fin de fichier sur canal de communication
Une erreur interne dans la DLL s'est produite.

III-A-5-a. Clauses de publication

III-A-5-a-1. AUTHID

Spécifie les droits appliqués lors de l'appel de la procédure :
Definer : Droits du créateur. C'est avec l'ensemble des privilèges du propriétaire du sous-programme qu'il sera fait référence aux objets de la base de données.

Current_user : Droits de l'appelant. C'est avec l'ensemble des privilèges de l'appelant et non du propriétaire que les références aux objets seront résolues.
Par défaut on applique les droits du créateur, comme un sous-programme PL/SQL.

III-A-5-a-2. LANGUAGE (8i )

Spécifie le L3G (C ou JAVA) utilisé avec lequel la procédure a été écrite.
Par défaut C.
Sous 8.0 utiliser AS EXTERNAL.

III-A-5-a-3. CALLING STANDARD

Spécifie le standard du type d'appel Windows NT (C ou Pascal) avec lequel la procédure externe a été compilée.
Par défaut C.

III-A-5-a-4. LIBRARY

Spécifie l'alias local d'une librairie, vous ne pouvez pas utiliser un Database Link pour spécifier une librairie distante.
Si vous insérez le nom entre double cote le nom devient sensible à la casse.

III-A-5-a-5. NAME

Spécifie le nom de la procédure externe à appeler.
Si vous insérez le nom entre double cote le nom devient sensible à la casse, par défaut il est stocké en MAJUSCULE.

III-A-5-a-6. WITH CONTEXT

Spécifie qu'un pointeur de context sera passé à la procédure externe.
Il pointe sur une structure de donnée opaque qui contient des informations sur la session Oracle, handles OCI, qui permettront des rappels vers la base ou des appels de fonctions OCI. Voir le chapitre Introduction à OCI.

III-A-5-a-7. AGENT IN

Cette clause permet de définir un processus externe particulier pour une procédure externe. Cela permet en cas de crash de ne pas impacté le processus extproc primaire.
Elle permet également un appel via un DBLINK d'une procédure distante. Veuillez consulter le documentation Oracle pour plus de précision.
Cette clause est disponible à partir de la version Oracle 9i.

III-A-5-a-8. PARAMETERS

Spécifie le nom, la position et le type de donnée passé à la procédure externe. Il peut aussi spécifier des propriétés supplémentaires pour chaque paramètre tel que la longueur, la taille maximum, la nullité, le type de passage de paramètre (par valeur ou par adresse), la valeur de retour pour une fonction.
Oracle recommande de toujours déclarer cette clause. En cas d'omission on utilise les conventions par défaut.

Exemple :

 
Sélectionnez
CREATE OR REPLACE PROCEDURE NumberTest( p_I IN NUMBER)
 AS LANGUAGE C
 LIBRARY DelphiLibrary
 NAME "NumberTest"
 WITH CONTEXT
 PARAMETERS (CONTEXT,
             p_I OCINUMBER);

III-A-6. Passage de paramètres

III-A-6-a. Correspondance des types de données

Un des problèmes lors de la communication entre 2 langages concerne la conversion entre les types de données. Les procédures externes exigent que soit spécifiée la manière dont les types de données PL/SQL utilisés pour les paramètres du wrapper seront mis en correspondance avec les types de données C utilisés pour les paramètres de la procédure externe.

III-A-6-a-1. PL/SQL vers C

Voir le document :
« Oracle9i Application Developer's Guide Fundamentals.pdf », Chapitre 10 Calling external procedures page 10-17

III-A-6-a-2. C vers Delphi

Vous trouverez des informations sur :

Convertion et instuctions de Turbo Pascal vers le langage C

http://www.delphi-jedi.org/

PL/SQL vers Delphi

L'unité Delphi Oratypes.pas contient les déclarations de base.

PL/SQL Type Supported External Types Default External Type Delphi type
BINARY_INTEGER [UNSIGNED] CHAR INT Integer
BOOLEAN [UNSIGNED] SHORT    
PLS_INTEGER [UNSIGNED] INT    
  [UNSIGNED] LONG    
  SB1, SB2, SB4   SB1, SB2, SB4
  UB1, UB2, UB4   UB1, UB2, UB4
  SIZE_T   SIZE_T
NATURAL [UNSIGNED] CHAR UNSIGNED INT Integer
NATURALN [UNSIGNED] SHORT    
POSITIVE [UNSIGNED] INT    
POSITIVEN [UNSIGNED] LONG    
SIGNTYPE SB1, SB2, SB4   SB1, SB2, SB4
  UB1, UB2, UB4   UB1, UB2, UB4
  SIZE_T   SIZE_T
FLOAT FLOAT FLOAT Real
REAL     Real
DOUBLE PRECISION DOUBLE DOUBLE Double
CHAR STRING STRING PChar
CHARACTER OCISTRING   pOCIString
LONG     Pchar
NCHAR     PChar
NVARCHAR2     PChar
ROWID     pOCIROWID
VARCHAR     PChar
VARCHAR2     PChar
LONG RAW RAW RAW PChar
RAW OCIRAW   pOCIRAW
BFILE OCILOBLOCATOR OCILOBLOCATOR pOCILOBLOCATOR
BLOB      
CLOB      
NCLOB      
NUMBER OCINUMBER OCINUMBER pOCINUMBER
DATE OCIDATE OCIDATE OCIDATE
TIMESTAMP OCIDateTime OCIDateTime pOCIDateTime
TIMESTAMP WITH TIME      
ZONE      
TIMESTAMP WITH LOCAL      
TIME ZONE      
INTERVAL DAY TO SECOND OCIInterval OCIInterval pOCIInterval
INTERVAL YEAR TO MONTH      
composite object types: dvoid dvoid Pointer
ADTs      

Vous trouverez des informations complémentaire en parcourant les scripts PL/SQL suivants :

%ORACLE_HOME%\rdbms\admin\pipidl.sql
%ORACLE_HOME%\rdbms\admin\stdspec.sql

Le passage de paramètre vers une procédure externe n'est pas facilité dans plusieurs circonstances :

L'ensemble disponible des types de données de PL/SQL ne correspond pas linéairement à l'ensemble des types de données du C ou de Delphi.

À la différence du C ou de Delphi, PL/SQL inclut le concept SGBDR de la nullité. Par conséquent, les paramètres de PL/SQL peuvent être NULS, tandis que les paramètres du C ou de Delphi ne le peuvent pas.

Le NULL en C et NIL en Delphi représente une valeur connue 0 (zéro) et sont utilisés pour les initialisations/vérifications de pointeur.
Un pointeur est soit valide, non-initialisé ou pointe sur une zone mémoire désallouée ou zone mémoire non autorisé.
Pour une SGBDR la nullité représente le concept « d'inconnu » ou « non applicable » (cf. l'ouvrage « Sql avancé de Joe Celko », chapitre 6).

La procédure externe pourrait avoir besoin :

  • de la longueur courante ou de la longueur de maximum d'un paramètre CHAR, LONG RAW, RAW ou VARCHAR2.
  • des informations de page de code (characterset) pour les paramètres de type CHAR, VARCHAR2, et CLOB.

le moteur PL/SQL pourrait avoir besoin de la longueur courante, de la longueur maximum, ou du statut de nulle des valeurs retournées par la procédure externe.

Le nombre maximum de paramètre que l'on peut passer à une procédure externe C est de 128.
Chaque variable de type DOUBLE ou FLOAT étant compté comme 2 paramètres.

La clause PARAMETERS nous permet de résoudre ces problèmes.

III-A-6-b. Propriétés de la clause PARAMETERS

III-A-6-b-1. Utilisation de la clause BY REFERENCE et BY VALUE

Par défaut tous les paramètres IN scalaires sont passés par valeur et les paramètres OUT et IN OUT par référence.
Il est néanmoins possible de passer un paramètre IN par référence en utilisant BY REFERENCE, ce qui peut se révéler plus rapide si la valeur est longue.

 
Sélectionnez
CREATE or REPLACE PROCEDURE Nom(p_param1 IN BINARY_INTEGER)
  AS LANGUAGE C
  LIBRARY unnom
  NAME "maprocédure"
  PARAMETERS ( p_param1 BY REFERENCE SB4);

On pourrait également utiliser BY VALUE pour indiquer que le paramètre est passé par valeur mais uniquement pour les paramètres IN. Les paramètres OUT et IN OUT ne peuvent être passés par valeur puisse qu'un résultat doit être retourné.

Pour les paramètres IN, BY VALUE peut être utilisé pour commenter la routine.

III-A-6-b-2. Valeur de retour d'une fonction

Pour récupérer une valeur de retour d'une fonction, on utilisera le mot clé RETURN.

 
Sélectionnez
CREATE or REPLACE FUNCTION Nom(p_param1 IN BINARY_INTEGER)
  RETURN BINARY_INTEGER
  AS LANGUAGE C
  LIBRARY unnom
  NAME "maprocédure"
  PARAMETERS ( p_param1 INT,
          RETURN INT );
III-A-6-b-3. Paramètre de valeur NULLE

Pour récupérer l'information sur la nullité d'un paramètre, on utilisera le mot clé INDICATOR. Son rôle est d'indiquer qu'une variable PL/SQL est nulle. Notez qu'une variable C ou Delphi ne peut être nulle au sens SGBDR.

D'après les déclarations contenues dans le fichier oro.h :

 
Sélectionnez
OCI_IND_NOTNULL	: OCIInd = 0;    // not NULL

OCI_IND_NULL    : OCIInd = -1;   // NULL
CREATE or REPLACE FUNCTION Nom(p_param1 IN BINARY_INTEGER)
  RETURN BINARY_INTEGER
  AS LANGUAGE C
  LIBRARY unnom
  NAME "maprocédure"
  PARAMETERS ( p_param1 INT,
           p_param1 INDICATOR,      -- On utilise le même nom de paramètre
           RETURN INT
           RETURN INDICATOR);       -- Ici il s'agit de la valeur de retour de la fonction

Une fonction pouvant retourner une valeur NULLE on ajoutera RETURN INDICATOR dans la clause PARAMETERS d'une fonction.

Option STRUCT et TDO.
L'utilisation du paramètre INDICATOR associé aux propriétés d'un objet n'est pas supportée. Du fait que les arguments d'un objet nécessitent plus d'un indicateur vous devez l'indiquer en employant l'option STRUCT.

Vous devez utiliser l'option TDO (Descripteur de Type d'Objet) pour les objets et les collections composites.

III-A-6-b-4. 3.1.6.2.4. Longueur de chaîne

LENGTH est utilisé pour stocker la longueur courante et MAXLEN, la longueur maximale d'un paramètre CHAR ou VARCHAR2 ou d'un paramètre RAW ou LONG RAW. Ils sont nécessaires pour garantir qu'une routine connaîtra la longueur des paramètres en entrée de manière à ne pas écraser l'espace destiné aux paramètres en sortie.

Pour les paramètres IN, LENGTH est considéré en lecture seule et est passé par valeur ( à moins d'utiliser BY REFERENCE ). MAXLEN ne s'applique pas aux paramètres IN.

Pour les paramètres OUT ou IN OUT, les propriétés sont passées par référence. MAXLEN bien que passé par référence est considéré en lecture seule.
La routine doit définir LENGTH avec une valeur correcte pour les données retournées ( qui ne devront pas dépasser MAXLEN)

Note, si un paramètre NULL est retourné, on définira LENGTH avec la valeur zéro de même que la chaîne retournée ('\0').

III-A-6-b-5. Jeu de caractères international

CHARSETID et CHARSETFORM sont utilisés pour spécifier l'identifiant et le format du jeu de caractères qui sont utilisés dans les environnements NLS pour les paramètres CHAR, CLOB et VARCHAR2. Les attributs OCI équivalents sont OCI_ATTR_CHARSET_ID et OCI_ATTR_CHARSET_FORM.

Ces deux propriétés sont toujours considérées en lecture seule. Pour plus d'information consulter la documentation OCI/NLS.

III-A-6-b-6. SELF ( Objet )

Le paramètre SELF est implicite dans l'appel d'une procédure en PL/SQL.
Pour une routine externe il doit être explicitement déclaré dans la clause parameters.
SELF référence l'objet lui-même.

Attention au point suivant qui peut porter à confusion, les conventions de passage de paramètres sous PL/SQL différent des conventions de passage des clauses parameters d'un wrapper PL/SQL vers une procédure externe.

Convention de passage de paramètre

  PL/SQL vers PL/SQL Wrapper PL/SQL vers une procédure externe
IN Par référence, on utilise un pointeur sur l'adresse de la variable. Par défaut tous les paramètres IN scalaires sont passés par valeur. Pour le passer par référence, on utilisera la clause BY REFERENCE.
OUT et IN OUT Par valeur, on utilise une copie du contenu de la variable et ce jusqu'à ce que NOCOPY soit spécifié. L'utilisation du hint NOCOPY modifiant le passage du paramètre par référence et non plus par valeur. Le compilateur peut ne pas en tenir compte de ce hint. Les paramètres OUT et IN OUT sont passés par référence.

…\Exemples\Exemple2\Test2.sql
…\Exemples\Exemple2\Run2.sql

Gestion des propriétés de la clause PARAMETERS :
…\Exemples\Exemple3\Test3.sql
…\Exemples\Exemple3\Run3.sql

III-A-6-c. Conventions d'appel pour les clauses PARAMETERS

  Allowed Default     Default PL/SQL
  External Types External Type Allowed Allowed Passing
Property (C) (C) PL/SQL Types PL/SQL Modes Method
INDICATOR SHORT SHORT all scalars IN BY VALUE
        IN OUT BY REFERENCE
        OUT BY REFERENCE
        RETURN BY REFERENCE
LENGTH [UNSIGNED] INT CHAR IN BY VALUE
  SHORT   LONG RAW IN OUT BY REFERENCE
  [UNSIGNED] INT   RAW OUT BY REFERENCE
  [UNSIGNED]   VARCHAR2 RETURN BY REFERENCE
  LONG        
MAXLEN [UNSIGNED] INT CHAR IN OUT BY REFERENCE
  SHORT   LONG RAW OUT BY REFERENCE
  [UNSIGNED] INT   RAW RETURN BY REFERENCE
  [UNSIGNED]   VARCHAR2    
  LONG        
CHARSETID [UNSIGNED] [UNSIGNED] INT CHAR IN BY VALUE
CHARSETFORM SHORT   CLOB IN OUT BY REFERENCE
  [UNSIGNED] INT   VARCHAR2 OUT BY REFERENCE
  [UNSIGNED]     RETURN BY REFERENCE
  LONG        

Notez que le paramètre MAXLEN ne peut s'appliquer à un paramètre IN.

III-A-6-d. Dépendances des routines externes

La dépendance entre un wrapper et une bibliothèque (LIBRARY) est stockée dans le dictionnaire de données.
Si on supprime un objet library les wrappers associés deviendront invalides.

 
Sélectionnez
Select object_name,object_type,status
  From user_objects
   Where object_name in ('TESTSINULL',' LIBRARYDELPHI3');

Si on supprime la DLL la librairie et les wrappers associés resteront valide mais leur exécution provoquera l'erreur suivante :

ERREUR à la ligne 1 :
ORA-06520: PL/SQL : Erreur pendant le chargement de la bibliothèque externe
ORA-06522: Unable to load DLL

Lors d'une sauvegarde du serveur Oracle n'oubliez pas d'inclure les fichiers DLL afin de restaurer correctement son environnement.

IV. Introduction à OCI

Oracle Call Interface (OCI) est une API qui permet aux applications supportant les conventions d'appel du langage C ou C++ d'interagir avec un ou plusieurs serveurs de base de données Oracle. Elle permet à vos programmes d'exécuter l'ensemble des opérations possible sur les serveurs de base de données Oracle y compris l'exécution d'instructions SQL et la manipulation d'objets.

Jusqu'ici nous n'avons pas abordé les types de données Number, Date, etc.
Oracle propose l'accès à ce type de donnée par l'intermédiaire de structure opaque devant être manipulée par des fonctions OCI.

Pour avoir le détail d'appel des fonctions citées dans ce chapitre consulter
soit les header dans le répertoire %Oracle_Home%\oci\include
soit la documentation Oracle « Oracle Call Interface Programmer's Guide », %OraDoc%\appdev.920\a96584.pdf.

À noter :
Certaines fonctions déclarées dans le fichier ociextp.h sont exportées dans la DLL Oracle OCI.DLL avec leur nom court.

IV-A. Rappel vers la base de données

Une routine externe peut effectuer des rappels vers la base de données pour propager des erreurs ou encore émettre des commandes SQL. Dans le cas de routines externes C/Delphi ces rappels se font au moyen de l'interface OCI d'Oracle.
Toutes les instructions exécutées à partir d'une routine externe utilisent la connexion existante et la transaction établie dans la session PL/SQL appelante.

IV-A-1. Clause With Context

Cette clause permet de récupérer le contexte d'exécution de la session PL/SQL.

Une connexion OCI est représentée par plusieurs handles incluant le handle d'environnement, le handle de contexte de service et le handle d'erreur.

On les récupère en appelant la fonction OCIExtProcGetEnv qui retournera ces handles sur la base de la structure context passée à la routine externe.

 
Sélectionnez
CREATE OR REPLACE PROCEDURE NumberTest(  p_I IN NUMBER)
  AS LANGUAGE C
  LIBRARY DelphiLibrary
  NAME "NumberTest"
  WITH CONTEXT
  PARAMETERS ( CONTEXT,          -- Pointeur sur le contexte de la session PL/SQL
           p_I OCINUMBER);

IV-A-2. Gestion des erreurs

IV-A-2-a. OCIErrorGet

Un bon nombre de fonctions retourne un code d'erreur, de type sword.
On utilisera ce code avec la fonction OCIErrorGet afin de récupérer des informations complémentaires sur l'erreur rencontrée.

On doit auparavant, et à partir du paramètre Context, récupérer le handle d'erreur de la session.

À noter
Pour le code d'erreur OCI_ERROR il peut être nécessaire d'appeler cette fonction tant qu'elle ne renvoie pas le code de retour OCI_NO_DATA.

 
Sélectionnez
message d'erreur=''

    Nouveau message d'erreur=''

    Tant que  OCIErrorGet <> OCI_NO_DATA

    call  OCIErrorGet(Nouveau message d'erreur)

message d'erreur= message d'erreur+Nouveau message d'erreur

IV-A-2-b. OCIExtProcRaiseExcp

Cette fonction lève une exception Oracle prédéfinie, semblable à l'instruction PL/SQL RAISE. Errnum contient le numéro de l'erreur que vous souhaitez propager.

 
Sélectionnez
OCIExtProcRaiseExcp(with_context, errnum);

IV-A-2-c. OCIExtProcAllocCallMemory

Cette fonction alloue de la mémoire dans une procédure externe. Cette allocation à une durée de vie égale à celle de l'appel, la mémoire ainsi allouée est libérée automatiquement par PL/SQL à l'issue de la procédure externe.

Elle permet l'allocation d'une taille mémoire suffisante pour recevoir le texte du message d'erreur utilisée avec la fonction OCIExtProcRaiseExcpWithMsg..

 
Sélectionnez
OCIExtProcAllocCallMemory(with_context :pOCIExtProcContext; amount :size_t);

N'utiliser en aucun cas de fonction de libération mémoire propre au C ou à Delphi pour la libérer.

IV-A-2-d. OCIExtProcRaiseExcpWithMsg

Cette fonction lève une exception définie par l'utilisateur, semblable à l'instruction PL/SQL RAISE_APPLICATION_ERROR.

 
Sélectionnez
OCIExtProcRaiseExcpWithMsg(with_context, errnum, errmsg, msglen);

Si errmsg se termine par NULL (null terminated string ) alors msglen sera égal à zéro sinon msglen sera égale à la longueur de errmsg.
Le numéro d'erreur devrait être compris entre 20000 et 20999.

IV-A-2-e. Erreurs gérées dans Delphi.

La propagation d'une exception Delphi hors de la routine externe provoquera une perte de connexion avec le processus EXTPROC.

À noter
Lors d'essai, l'utilisation des instructions Delphi Abort et Assume s'est avéré impossible. Leur utilisation provoquant une erreur à l'exécution et la perte de connexion avec le processus EXTPROC.

Voir les exemples :
…\Exemples\Exemple4\Test4.sql
…\Exemples\Exemple4\Run4.sql

puis
…\Exemples\Exemple4\Run42.sql

et enfin
…\Exemples\Exemple4\Run43.sql

IV-A-2-f. Résultat dans SQLPlus.

 
Sélectionnez
SQL> @Run4.sql

Local avant appel -1

Local après appel -1

Local avant appel 123456789012345

Declare

*

ERREUR à la ligne 1 :

ORA-20000: OCI-22053: Erreur de dépassement

ORA-06512: à "SYSTEM.NUMBERTEST", ligne 0

ORA-06512: à ligne 11

Ici l'erreur utilisateur n° 20000 est bien propagée et la description de l'erreur OCI affichée.

 
Sélectionnez
SQL>  @Run42.sql
Local avant appel -1
Local après appel -1
Local avant appel 123456789012345
Declare
*
ERREUR à la ligne 1 :
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeurOCI-22053: Erreur
de dépassement
ORA-06512: à "SYSTEM.NUMBERTEST2", ligne 0
ORA-06512: à ligne 11

Ici l'erreur Oracle n° 6502 est bien propagée et la description de l'erreur OCI est ajoutée au message d'erreur initiale.

 
Sélectionnez
SQL>  @Run43.sql
Local avant appel -1
Local après appel -1
Local avant appel 123456789012345
Declare
*
ERREUR à la ligne 1 :
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à "SYSTEM.NUMBERTEST3", ligne 0
ORA-06512: à ligne 11

Ici l'erreur Oracle n° 6502 est bien propagée sans ajout de la description de l'erreur OCI.

IV-A-3. Type de données OCI

Vous pouvez modifier les données des types suivant, en utilisant les fonctions OCI appropriées.

IV-A-3-a. OCINumber

Le type NUMBER d'Oracle NUMBER, NUMERIC, INT, SHORTINT, REAL, DOUBLE PRECISION, FLOAT et DECIMAL sont représentés en C par le type OCINUMBER qui est une structure opaque. On doit donc manipuler ce type par les fonctions prévues à cet effet.

Rappel

 
Sélectionnez
NUMBER
Use the NUMBER datatype to store real numbers in a fixed-point or floating-point format.
Numbers using this datatype are guaranteed to be portable among different Oracle platforms,
and offer up to 38 decimal digits of precision.
You can store positive and negative numbers of magnitude 1 x 10-130 through 9.99 x10125,
as well as zero, in a NUMBER column.

NUMBER (p, s)
Description :
 Variable-length numeric data. Maximum precision p and/or scale s is 38.
Column Length and Default :
 Variable for each row. The maximum space required for a given column is 21 bytes per row.

IV-A-3-b. OCIDate

Le type DATE d'Oracle est représenté en C par le type OCIDate qui est une structure. Ces éléments représentent l'année, le mois, le jour, l'heure, les minutes et les secondes.

Voir les exemples : …\Exemples\Exemple5\Test5.sql
…\Exemples\Exemple5\Run5.sql

Lors d'une modification d'une date dans une procédure externe on doit impérativement vérifier sa validité en utilisant la fonction OCIDateCheck, sinon on risque de retrouver des incohérences, exemple :

 
Sélectionnez
SQL>@C:\Delphi\Aprg\Ora-DLL\Exemples\Exemple5\Run5.sql
Avant appel
Date1 27/10/2004 21:45 , Date2 01/01/0300 16:45
Après appel
Date1 27/10/2004 21:45 , Date2 00/00/0000 25:61

Procédure PL/SQL terminée avec succès.

Note
Sous Delphi on ne peut pas gérer les dates avant Jésus Christ avec le type TDateTime, ni utiliser la fonction EncodeDate.

IV-A-3-c. OCIDateTime

Le type de donnée OCIDateTime est une structure opaque employée pour représenter les types de donnée temps et intervalle de temps d'Oracle (TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH TIME ZONE) ainsi que le type ANSI DATE.
Ce type de donnée est utilisé à partir de la version Oracle 9i.

IV-A-3-d. OCIInterval

Le type de donnée OCIInterval est une structure opaque et est employé pour représenter les types de données d'intervalle de temps d'Oracle (INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND).
Ce type de donnée est utilisé à partir de la version Oracle 9i.

IV-A-3-e. OCIString

Le type OCIString permet de gérer les chaînes de caractères de longueur fixe ou variable. La longueur de la chaîne de ce type n'inclut pas le zéro terminal de fin de chaîne.

IV-A-3-f. OCIRAW

Le type OCIRAW est une structure opaque et est employé pour représenter le type de donnée de longueur variable RAW.

IV-A-3-g. Exécution d'instruction SQL dans une routine externe

Il est possible d'exécuter des instructions SQL dans une routine externe,
On doit :

  • Récupérer les handles de la session PL/SQL courante,
  • Allouer un Handle d'instruction SQL,
  • Préparer l'instruction SQL,
  • Créer une association entre les variables locales et les variables de l'instruction SQL ( Bind ),
  • Exécuter l'instruction,
  • Et enfin désallouer le Handle d'instruction SQL précédemment alloué.

…\Exemples\Exemple6\Test6.sql
…\Exemples\Exemple6\Run6.sql

On peut désormais tester la clause AUTHID :
…\Exemples\Exemple6\ DroitsAppelantCreateur.sql

IV-A-3-h. Restrictions concernant les Callbacks

Les commandes et fonctions suivante ne sont pas supportées :

les commandes de control de transaction tel que COMMIT
les commandes de définition de donnée tel que CREATE

Les fonctions OCI orientées objet indiqué dans la documentation Oracle.
Consulter 'Oracle9i Application Developer's Guide Fundamentals' : Restrictions on Callbacks,page 10-48.

Ne sont pas supportés avec la fonction OCI OCIHandleAlloc, les types de handle suivants :

OCI_HTYPE_SERVER
OCI_HTYPE_SESSION
OCI_HTYPE_SVCCTX
OCI_HTYPE_TRANS

IV-A-3-i. Déboguer une procédure externe

Pour les routines C vous pouvez utiliser gdb et le package suivant qui contient quelques explications sur la marche à suivre :
%Oracle_HOME%\plsql\demo\dbgextp.sql

Sous Delphi je n'ai pas réussi à debugger une DLL chargée par Extproc, problème de droits sur le process ? exécution du listener avec le compte différent ?

IV-A-4. Limites de l'exercice

Documentation détaillée de la configuration du listener
Conversion date Oracle vers date Delphi
Debug d'une procédure externe plus explicite.
Performance et coût d'appel.

IV-A-5. Bibliographie et références documentaire

Oracle 8i programmation avancée PL/SQL
Oracle Press, Editeur Campus Press
ISBN 2-7440-1113-4

Téléchargement libre des sources C et JAVA : WWW.Campuspress.net

Documentation Oracle v 9i , Release 2 :
Oracle Call Interface Programmer's Guide
%OraDoc%\appdev.920\a96584.pdf

Oracle9i Application Developer's Guide - Fundamentals
%OraDoc%\appdev.920\a96590.pdf

Oracle9i SQL Reference
%OraDoc%\server.920\a96540.pdf

Oracle9i Database Error Messages
%OraDoc%\server.920\a96525.pdf

Net Service Administrator Guide
%OraDoc%\network.920\a96580.pdf

IV-A-6. Sources de composant OCI sous Delphi

NCOCI8 Library Version 1.0.2 . Dmitry Arefiev Copyright (c) 1999-2001.
http://www.da-soft.com

VK NLO (Vlad Karpov Native Link to ORACLE) Version 1.0.0 . Vlad Karpov Copyright (c) 2001-2002.
Native Link to ORACLE

Ces composants ne proposent pas tous les types de données OCI utilisable dans une procédure externe.

IV-A-7. Annexe

Le document, note:69028.1 du 31-MAR-2003, disponible sur Metalink propose une explication détaillée sur la gestion interne du type DATE (How does Oracle store the DATE datatype internally ?)

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

Ce document est issu de www.developpez.com et reste la propriété exclusive de son auteur. La licence d'utilisation Open Content (opencontent.org) vous permet d'utiliser librement les documents publiés sur mon espace personnel à condition de mentionner clairement le nom de son auteur avec un lien sur mon espace personnel de ce site et de garder son caractère « Open Content ».