PL-SQL et procédures externes, mises en œuvre avec DELPHI

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

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 =
…

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

…
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-points ':'.

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.

(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 ».

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

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;

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

// 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ée 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-a-i. AUTHID▲

III-A-5-a-ii. LANGUAGE (8i )▲

III-A-5-a-iii. CALLING STANDARD▲

III-A-5-a-iv. LIBRARY▲

III-A-5-a-v. NAME▲

III-A-5-a-vi. WITH CONTEXT▲

III-A-5-a-vii. AGENT IN▲

III-A-5-a-viii. PARAMETERS▲

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

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-i. PL/SQL vers C▲

III-A-6-a-ii. C vers Delphi▲

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

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

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

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-iii. Paramètre de valeur NULLE▲

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

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

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

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

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

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.

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 valides, 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.

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.

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

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.

OCIExtProcRaiseExcp(with_context, errnum);

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

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.

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

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.

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ée 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

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.

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.

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.

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

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.

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 :

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.

Le type de donnée OCIDateTime est une structure opaque employée pour représenter les types de données 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.

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.

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.

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

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

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

les commandes de contrôle de transaction tel que COMMIT ;
les commandes de définition de donnée telle que CREATE.

Les fonctions OCI orientées objet indiquées 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.

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 à déboguer une DLL chargée par Extproc, problème de droits sur le process ? Exécution du listener avec le compte différent ?