Discussion :

[sergio_is_back]

Bonjour à tous, pour une fois c'est moi qui vais ouvrir une discussion, je vais vous expliquer le contexte :

J'ai un client que utilise un ERP sous Oracle 11g dans lequel beaucoup de ses fonctionnalités métier sont regroupés dans des packages (ensemble de fonctions Oracle) et je dois m'interfacer avec.
Comme j'ai déjà plusieurs fois travaillé avec des procédures stockées sous Oracle je ne pensai avoir de problèmes, mais comme d'habitude c'est quand on pense que ça marcher comme sur des roulettes que ça dérape, bref.

Certaines fonctions attendent plusieurs paramètres en entrées et renvoi des paramètres en sortie, en particulier des CLOB (Character Large OBject dans la terminologie Oracle)
Bon je me suis je vais tester ça avant et faire une petite fonction de mon crû avant de me lancer dans le bain et là je me rend compte que ça merde direct !

Explication :

J'ai codé une fonction Oracle (Juste à titre d'exemple) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
create or replace FUNCTION TEST1
(
  P_IN1 IN VARCHAR2,
  P_OUT1 OUT VARCHAR2
) RETURN NUMBER AS
 
BEGIN
  IF P_IN1 IS NULL THEN
    P_OUT1 := 'NULL !';
    RETURN 0;
  ELSE
    IF P_IN1 = 'VERIF' THEN
        P_OUT1 := P_IN1;
        RETURN 1;
    ELSE
      IF P_IN1 = 'FFAB' THEN
        P_OUT1 := P_IN1;
        RETURN 2;
      END IF;
    END IF;
  END IF;
 
  P_OUT1 := 'ERREUR';
  RETURN 0;
END TEST1;

Cette fonction ne fait pas grand chose mais bon c'est suffisant pour tester. Si je passe 'VERIF' dans P_IN1 elle me renvoi 1 et copie P_IN1 dans P_OUT1, si je passe 'FFAB' dans PIN1 elle me renvoi 2 et copie P_IN1 dans P_OUT1
Dans le cas où P_IN1 est NULL elle me renvoi 0 et 'NULL !' dans P_OUT1 et dans les autres cas elle me renvoi 'ERREUR' dans P_OUT1 et 0 en valeur de retour
Tout ça marche très bien dans SQLPlus et SQLDevelopper

Comme j'ai un Delphi 10.3 version Pro, j'ai pas le driver Firedac pour Oracle...
Qu'à cela ne tienne me dis-je, j'utiliserai la ZeoLib que j'ai utilisé sur d'autres projets avec succès...

Après avec trouvé un peu de documentation sur l'appel de fonctions Oracle avec TDbGo je me dis que ça doit fonctionner à peu près pareil.
Donc j'instancie un TZStoredProc et je lui créé la liste de paramètres ha-doc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
function TDiamDatabase.Test: Integer;
var
    OracleFunc  :   TZStoredProc;
    Param       :   TParam;
    Buffer      :   array of Char;
begin
    Result:=-1;
 
    // Instancier TZStoredProc et lui affecter la connexion en cours
    OracleFunc:=TZStoredProc.Create(nil);
    OracleFunc.Connection:=Self.GetConnection;
    OracleFunc.StoredProcName:='TEST1';
 
    // Définir les paramètres
    OracleFunc.Params.Clear;
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftWideString;
    Param.ParamType:=ptInput;
    Param.Name:='P_IN1';
    Param.Value:='FFAB';
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftWideString;
    Param.ParamType:=ptOutput;
    Param.Name:='P_OUT1';
 
    // Le résultat de la fonction Oracle
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftFloat;
    Param.ParamType:=ptResult;
    Param.Name:='ReturnValue';
 
    try
        // Appel
        OracleFunc.Open;
        ShowMessage(Format('P_OUT1 = %s, RESULT = %d',[OracleFunc.ParamByName('P_OUT1').AsString,OracleFunc.ParamByName('ReturnValue').AsInteger]));
        Result:=OracleFunc.ParamByName('ReturnValue').AsInteger;
    except on E:Exception do
        MessageDlg(E.Message,mtError,[mbOk],0);
    end;
    OracleFunc.Free;
 
end;

Tout ça marche très bien... Je récupère les valeurs prévues

Jusqu'a ce que rajoute le fameux CLOB !!!

Petit exemple :

Prenons la même fonction (renommée en TEST2) et on rajoute un deuxième paramètre OUT

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create or replace FUNCTION TEST2
(
  P_IN1 IN VARCHAR2,
  P_OUT1 OUT VARCHAR2,
  P_OUT2 OUT CLOB
) RETURN NUMBER AS
  
  TMP VARCHAR2(4000);
  
BEGIN
  P_OUT2 := EMPTY_CLOB();

  -- Juste pour avoir des données dans le CLOB
  TMP:='{"rfidList":[{"rfid":"DLU2000000000","artiCode":"DIO0001","artiDesignation":"Libelle1"},{"rfid":"DLU2000000100","artiCode":"DIO0002","artiDesignation":"Libelle2",},...';

  IF P_IN1 IS NULL THEN
    P_OUT1 := 'NULL !';
    RETURN 0;
  ELSE
    IF P_IN1 = 'VERIF' THEN
        P_OUT1 := P_IN1;
        P_OUT2 := TO_CLOB(TMP);
        RETURN 1;
    ELSE
      IF P_IN1 = 'FFAB' THEN
        P_OUT1 := P_IN1;
        P_OUT2 := TO_CLOB(TMP);
        RETURN 2;
      END IF;
    END IF;
  END IF;
  
  P_OUT1 := 'ERREUR';
  RETURN 0;
END TEST2;

Pareil que le premier appel, je rajoute mon paramètre P_OUT2 en prenant soin de le définir correctement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
function TDiamDatabase.Test: Integer;
var
    OracleFunc  :   TZStoredProc;
    Param       :   TParam;
    Buffer      :   array of Char;
begin
    Result:=-1;
 
    // Instancier TZStoredProc et lui affecter la connexion en cours
    OracleFunc:=TZStoredProc.Create(nil);
    OracleFunc.Connection:=Self.GetConnection;
    OracleFunc.StoredProcName:='TEST2';
 
    // Définir les paramètres
    OracleFunc.Params.Clear;
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftWideString;
    Param.ParamType:=ptInput;
    Param.Name:='P_IN1';
    Param.Value:='FFAB';
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftWideString;
    Param.ParamType:=ptOutput;
    Param.Name:='P_OUT1';
    Param.Value:=Unassigned;
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftOraClob;
    Param.ParamType:=ptOutput;
    Param.Name:='P_OUT2';
    Param.Value:=Unassigned;
 
    // Le résultat de la fonction Oracle
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftFloat;
    Param.ParamType:=ptResult;
    Param.Name:='ReturnValue';
 
    try
        // Appel
        OracleFunc.Open;
        ShowMessage(Format('P_OUT1 = %s, RESULT = %d',[OracleFunc.ParamByName('P_OUT1').AsString,OracleFunc.ParamByName('ReturnValue').AsInteger]));
        Result:=OracleFunc.ParamByName('ReturnValue').AsInteger;
    except on E:Exception do
        MessageDlg(E.Message,mtError,[mbOk],0);
    end;
    OracleFunc.Free;
 
end;

Et là paf ! pastèque !
A l'exécution du Open j'ai l'erreur :

OCI_ERROR: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

Bon je doute que c'est paramètre le CLOB qui provoque cette erreur
L'allocation du buffer ne doit pas se faire correctement mais je vois pas où
J'ai parcouru le forum de la ZeosLib sans trouver ni d'exemple ni d'explication particulière sur les CLOB Oracle (pas très utilisés ?)
Et le source de la ZeosLib n'est pas plus explicite (pas beaucoup de commentaires)
Je précise que lorsque l'appel renvoi un CLOB vide (Si je passe XXXX dans P_IN1 par exemple) ça ne pose pas problème particulier... Mais ça vous deviez déjà l'avoir deviné !!

La question est : Quelqu'un a t'il déjà travaillé sous Oracle avec des CLOB ?

Bon ça fait que 2 jours que je cherche...

[sergio_is_back]

Bon après de multiples essais, j'ai trouvé la solution :

En fait coté Delphi il suffit de définir le type de données du paramètres comme un WideString

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
    Param:=OracleFunc.Params.AddParameter;
    Param.DataType:=ftWideString;
    Param.ParamType:=ptOutput;
    Param.Name:='P_OUT2';
    Param.Value:=Unassigned;

Et là miracle ça marche...
J'ai l'impression que la ZeosLib n'alloue pas de zone mémoire pour le paramètre Oracle quand on lui défini un ftOraClob ou un ftOraBlob et du coup ça part en vrille
Reste à tester si j'ai plus de 4000 caractères, car normalement les VARCHAR2 sous Oracle sont limité à 4000 caractères par construction...

[sergio_is_back]

Bon je l'avais évoqué hier, en forçant le type de données du paramètres sur ftWideString je ne récupère que les 4000 premiers caractères du CLOB, la faute à la constante entourée en rouge là dessous :



Cette constante est initialisée à 4000 dans l'unité ZDbcOracleUtils.pas ce qui correspond à la longueur maximale du VARCHAR2 sous Oracle

On remarque aussi plusieurs choses :

• Les initialisation pour les types AsciiStream et stUnicodeStream sont aussi bloqués à 4000 caractères...
• Si l'on exécute le code le type ftOraClob est mappé en stUnknow pour l'appel à la librairie Oracle
• La proprieté Size des paramètres n'est pas prise en compte (voir dessous)

La valeur de la propriété Size n'est pas utilisée pour allouer les espaces mémoire dévolus aux divers paramètres
Pour les chaines c'est la valeur de la constante Max_OCI_String_Size qui est utilisée

En consultant la document sur l'api du client Oracle (oci.dll) il est dit que la taille d'un objet (un paramètre, une colonne de table de type BLOB, etc...) peut aller jusqu’à 4GB
La taille de tous les objets cumulés alloués (ensemble des paramètres de la requête et des valeurs retournés pour 1 ligne de résultat) ne doit pas dépasser 4GB en version 32 bits (j'ai pas encore regardé la version 64 bits mais je pense que cette limite a du être levée)

J'ai donc d'abord fait un test en ajoutant une directive de compilation pour altérer la valeur de Max_OCI_String_Size et vérifier si je pouvait récupérer plus de 4000 caractères en forçant mon paramètre de ftOraClob à ftWideString :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
const
  MAX_SQLVAR_LIMIT = 1024;
{$ifdef MAX_OCI_STRING_OVERRIDE }
  Max_OCI_String_Size = 32768;
{$else}
  Max_OCI_String_Size = 4000;
{$endif}

Et ça fonctionne, si je créer un CLOB de 10000 caractères, je récupère mes 10000 caractères coté Delphi
Mais c'est pas satisfaisant car si je devais récupérer plusieurs CLOB et plusieurs chaines de caractères dans une table par exemple cela équivaudrait à allouer des blocs mémoire beaucoup plus grand que d'habitude (certes on peut penser s'en absoudre sur des systèmes avec 16GB de mémoire, mais les PC du client n'ont peu être pas tous 16GB et encore moins 16GB libre...)

Je vais donc être obligé de gratter un peu le code de la ZeosLib pour la forcer à faire ce que je veux sans pour autant modifier les traitements liés au type chaine traditionnel d'Oracle...

[sergio_is_back]

Bon après quelques modifications de code, j'ai pu obtenir un résultat propre...
Pour ceux que ça intéresse :

Le Datatype ftOraClod était mappé comme stUnknow pour l'appel à la dll client Oracle
Je l'ai donc remappé en stUnicodeString

J'ai aussi ajouté un champ supplémentaire pSize au record TZOracleParam qui représente les caractéristiques du paramètre,
Lorsque l'on est sur un paramètre avec un Datatype ftOraClob ou ftOraBlob, la valeur Size si elle est supérieure à zéro est prise en lieu et place de la valeur de la constante Max_OCI_String_Size et stockée dans le record TZOracleParam

Restait plus alors qu'a allouer la taille en mémoire pour les données, ceci en modifiant la procedure TZOracleCallableStatement.PrepareInParameters



Et tout fonctionne correctement,
Je ne sais pas si c'est la façon la plus élégante de faire, en tout cas c'est efficace, pas plus de 15 lignes de codes modifiés
Je vais proposer mon correctif à l'équipe de ZeosLib et leur demander ce qu'ils en pensent....

Je vais enfin pouvoir me concentrer sur la partie métier, ce qui est l'essentiel...