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

Laurent Dardenne

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

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

- Auteur : Laurent Dardenne

- Parution : 29 juin 2004

- Public visé :

- Licence : 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 ».

Sommaire

- I. Avant-propos

- II. Prérequis

- II-A. Le code source des exemples

- III. Procédure externe

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

- III-A-1. Configuration du listener

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

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

- III-A-2. Identifier la DLL

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

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

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

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

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

- III-A-5-a. Clauses de publication

- 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

- III-A-6. Passage de paramètres

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

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

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

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

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

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

- III-A-6-b-iii. Paramètre de valeur NULLE

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

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

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

- IV. Introduction à OCI

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

- IV-A-1. Clause With Context

- IV-A-2. Gestion des erreurs

- IV-A-2-a. OCIErrorGet

- IV-A-2-b. OCIExtProcRaiseExcp

- IV-A-2-c. OCIExtProcAllocCallMemory

- IV-A-2-d. OCIExtProcRaiseExcpWithMsg

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

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

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

- IV-A-3-a. OCINumber

- IV-A-3-b. OCIDate

- IV-A-3-c. OCIDateTime

- IV-A-3-d. OCIInterval

- IV-A-3-e. OCIString

- IV-A-3-f. OCIRAW

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

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

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

- IV-A-4. Limites de l'exercice

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

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

- IV-A-7. Annexe

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 égale à 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 droit 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 source 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.

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

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

III - Procédure externe

On appelle routine externe, toute procédure ou fonction écrites 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 certaines tâches et/ou situations, 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

Lorsqu’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

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

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.

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

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.

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

La vue du dictionnaire user_libraries permet de retrouver les informations :

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 :

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 :

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

Déclaration du wrapper :

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

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

// 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 - 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-i - 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-ii - 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-iii - 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-iv - 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 doubles quotes le nom devient sensible à la casse.

III-A-5-a-v - NAME

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

III-A-5-a-vi - 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-vii - 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 impacter le processus extproc primaire.
Elle permet également un appel via un DBLINK d'une procédure distante. Veuillez consulter la documentation Oracle pour plus de précision.
Cette clause est disponible à partir de la version Oracle 9i.

III-A-5-a-viii - 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 telles 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 :

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-i - 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-ii - C vers Delphi

Vous trouverez des informations sur :

Conversion et instructions 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émentaires 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ée.
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ètres que l'on peut passer à une procédure externe C est de 128.
Chaque variable de type DOUBLE ou FLOAT étant compté comme deux 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-i - 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.

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-ii - Valeur de retour d'une fonction

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

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

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 :

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-iv - 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-v - 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, consultez la documentation OCI/NLS.

III-A-6-b-vi - 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.

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.

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.

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.

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.

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

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.

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

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

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.

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

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 :

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

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 ;

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

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 à déboguer 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 documentaires

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 utilisables 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 ?)