Discussion :

[Yvesdu06]

Bonjour à toutes et tous,

Je vous soumets aujourd'hui un problème auquel je ne trouve pas de solution malgré de nombreuses tentatives et la lecture de beaucoup de posts sur le sujet, car je ne trouve pas d'exemple précis du cas auquel je suis confronté.

Dans un contexte d'analyse de l'environnement d'audit de Business Objects 4.2 (socle data est Oracle 11g), j'ai une sous-requête qui ne ramène aucun résultat à cause d'un problème de jointure sur une colonne de type CLOB (que je caste pourtant en VARCHAR2(100)) avec une colonne qui est nativement de type VARCHAR2(100).

Dans mon jeu de test, j'ai isolé une valeur précise qui, quand je la passe en "dur" (libellé de type texte) permet à ma sous-requête de ramener les valeurs attendues en rendant la jointure fonctionnelle.

Mais quand je la passe sous la forme d'une colonne de la table source (donc de type CLOB "castée" en VARCHAR2), la sous-requête ne ramène aucun résultat, et je soupçonne que le résultat du CAST ne correspond pas exactement à la valeur équivalente de type VARCHAR2 issue d'une autre table.

En alternative du CAST, j'ai aussi utilisé la fonction DBMS_LOB.SUBSTR, mais le résultat est le même, c'est à dire aucun résultat retourné, et j'imagine que le problème est identique, différence de contenu des colonnes de la jointure qui la rende non effective.

Voici ci-dessous la sous-requête concernée avec des commentaires pour clarifier mon propos :

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
53
54
-- test sous-requête avec mise en évidence du problème lié au CAST (avec commentaires)
SELECT DISTINCT
  EYE_DOCUMENT.DOCUMENT_ID AS ID,
  CMS_DOCUMENT.CUID,
  A.EVENT_ID,
  MAX(A.START_TIME) AS DERNIERE_ACTUALISATION
FROM
  PBI4360_13135.EYE_DOCUMENT 
  INNER JOIN PBI4360_13135.EYE_SNAPSHOT ON (EYE_SNAPSHOT.SNAPSHOT_ID = EYE_DOCUMENT.DOCUMENT_SNAPSHOT_ID  AND  EYE_SNAPSHOT.SNAPSHOT_NAME = 'WEBI' AND EYE_SNAPSHOT.SNAPSHOT_RESULT = 1)
  INNER JOIN PBI4360_13135.CMS_DOCUMENT ON (EYE_DOCUMENT.DOCUMENT_ID = CMS_DOCUMENT.ID),
  PBI4AUDIT.ADS_EVENT A 
WHERE
  (
    CMS_DOCUMENT.CUID = 
       (
        SELECT DISTINCT 
           -- pour test : ce CUID explicite correspond à l'évènement d'audit dont l'ID est 17710233064828830405 -> ainsi la requête ramène la ligne attendue
           'M18kMqsAAKt6ACMDZgAAR0v.An71mMoAAAA'
           -- fin test
           -- avec cette fonction et pour ce même CUID (pour mémoire DBMS_LOB.SUBSTR produit toujours un VARCHAR2) -> ainsi la requête ne ramène rien :-(
           --DBMS_LOB.SUBSTR(C.EVENT_DETAIL_VALUE, 100, 1)
           -- en "castant" ce même CUID au format exact de CMS_DOCUMENT.CUID qui est nativement un VARCHAR2(100) -> ainsi aussi la requête ne ramène rien :-(
           --CAST(C.EVENT_DETAIL_VALUE AS VARCHAR2(100))  
        FROM
           PBI4AUDIT.ADS_EVENT B,
           PBI4AUDIT.ADS_EVENT_DETAIL C
        WHERE
           -- pour traiter l'évènement de la requête principale
           B.EVENT_ID = A.EVENT_ID
           -- pour aller chercher le détail de type 12 (CUID du document parent) dans la table détail des évènements
           AND C.EVENT_ID = B.EVENT_ID
           AND C.EVENT_DETAIL_TYPE_ID = 12
       )
   -- pour test : je force l'ID de l'évènement    
   AND A.EVENT_ID = 17710233064828830405
   -- fin test
   -- pour limiter le scope sur les 13 derniers mois glissants
   AND A.START_TIME >= ADD_MONTHS(SYSDATE, -13)
   -- seulement les actualisations, planifications et publications
   AND A.EVENT_TYPE_ID IN (1003,1011,1012)
   -- seulement ces types d'objets
   AND A.OBJECT_TYPE_ID IN ('AYfjfcAV7cNPh33akDfm2RE','AcfGhH5SbBNIsBrpeExxhDQ','AafS60mXMNlMv86vS0DuroU','AVx_3364fDBFni_bGCHtSyQ','AVSAUQfHHtpFsd9M2RM.qJ0') 
   -- l'objet de l'évènement d'audit doit être dans le répertoire générique de l'établissement    
   AND A.OBJECT_FOLDER_PATH LIKE '%/ETABLISSEMENTS/313 MP/%'
   -- pour utiliser les données du dernier snapshot Eyes360
   AND EYE_SNAPSHOT.SNAPSHOT_ID = (SELECT MAX(S.SNAPSHOT_ID) FROM PBI4360_13135.EYE_SNAPSHOT S WHERE S.SNAPSHOT_CMS = '@BI42PRD' AND S.SNAPSHOT_NAME = 'WEBI' AND S.SNAPSHOT_RESULT = 1 GROUP BY S.SNAPSHOT_CMS)
   -- par sécurité, je ne sélectionne aussi que les documents se trouvant dans le répertoire générique de l'établissement  
   AND EYE_DOCUMENT.DOCUMENT_PATH  LIKE  '%/ETABLISSEMENTS/313 MP/%'
  )
GROUP BY 
   EYE_DOCUMENT.DOCUMENT_ID,
   CMS_DOCUMENT.CUID,
   A.EVENT_ID
ORDER BY 4 DESC

Avez-vous une idée de ce qu'il se passe sur cette jointure et si oui, comment contourner ce problème ?

N'hésitez pas si vous avez des questions par rapport à cet exemple car ce n'est pas forcément évident de se plonger à brûle pourpoint dans le contexte d'une telle sous-requête qui fait partie d'une (très) grosse requête principale.

Je vous remercie beaucoup par avance pour votre avis et votre aide, tous vos conseils seront les bienvenus.

Bel après-midi à vous toutes et tous,

Yves

[Waldar]

Avant de rentrer dans le problème du CLOB, est-ce que cette requête donne les mêmes résultats ?

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
    select ed.DOCUMENT_ID     as ID
         , cd.CUID
         , ev.EVENT_ID
         , max(ev.START_TIME) as DERNIERE_ACTUALISATION
      from PBI4360_13135.EYE_DOCUMENT ed
inner join PBI4360_13135.CMS_DOCUMENT cd on cd.ID = ed.DOCUMENT_ID
cross join PBI4AUDIT.ADS_EVENT        ev
     where ev.EVENT_ID              = 17710233064828830405
       and ev.START_TIME           >= add_months(sysdate, -13)
       and ev.EVENT_TYPE_ID        in (1003,1011,1012)
       and ev.OBJECT_TYPE_ID       in ('AYfjfcAV7cNPh33akDfm2RE','AcfGhH5SbBNIsBrpeExxhDQ','AafS60mXMNlMv86vS0DuroU','AVx_3364fDBFni_bGCHtSyQ','AVSAUQfHHtpFsd9M2RM.qJ0')
       and ev.OBJECT_FOLDER_PATH like '%/ETABLISSEMENTS/313 MP/%'
       and ed.DOCUMENT_PATH      like '%/ETABLISSEMENTS/313 MP/%'
       and ed.DOCUMENT_SNAPSHOT_ID  = (select max(SNAPSHOT_ID)
                                         from PBI4360_13135.EYE_SNAPSHOT
                                        where SNAPSHOT_CMS    = '@BI42PRD'
                                          and SNAPSHOT_NAME   = 'WEBI'
                                          and SNAPSHOT_RESULT = 1)
       and cd.CUID                  = (select 'M18kMqsAAKt6ACMDZgAAR0v.An71mMoAAAA'
                                         from PBI4AUDIT.ADS_EVENT_DETAIL ee
                                        where ee.EVENT_ID             = ev.EVENT_ID
                                          and ee.EVENT_DETAIL_TYPE_ID = 12)
  group by ed.DOCUMENT_ID
         , cd.CUID
         , ev.EVENT_ID
  order by max(ev.START_TIME) desc;

Il y a moins de jointures déjà.

[Yvesdu06]

Avant de rentrer dans le problème du CLOB, est-ce que cette requête donne les mêmes résultats ?

Il y a moins de jointures déjà.

Bonjour Waldar,

Merci beaucoup pour votre réponse.

Testée à l'instant dans SQL Developer, je vous confirme que votre requête renvoie exactement la même ligne.

Bien à vous,

Yves

[Waldar]

Ok bien, maintenant que renvoie cette requête ?

Code :

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

1
2
3
4
5
6
7
8
9
select EVENT_DETAIL_VALUE
     , dbms_lob.substr(EVENT_DETAIL_VALUE, 100, 1)
     , cast(EVENT_DETAIL_VALUE as varchar2(100))
     , dump(EVENT_DETAIL_VALUE, 1016) -- si ça ne marche pas : dump(EVENT_DETAIL_VALUE, 1016, 1, 100)
     , dump(dbms_lob.substr(EVENT_DETAIL_VALUE, 100, 1), 1016)
     , dump(EVENT_DETAIL_VALUE as varchar2(100), 1016)
  from PBI4AUDIT.ADS_EVENT_DETAIL
 where EVENT_ID             = 17710233064828830405
   and EVENT_DETAIL_TYPE_ID = 12;

[Yvesdu06]

Ok bien, maintenant que renvoie cette requête ?

Code :

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

1
2
3
4
5
6
7
8
9
select EVENT_DETAIL_VALUE
     , dbms_lob.substr(EVENT_DETAIL_VALUE, 100, 1)
     , cast(EVENT_DETAIL_VALUE as varchar2(100))
     , dump(EVENT_DETAIL_VALUE, 1016) -- si ça ne marche pas : dump(EVENT_DETAIL_VALUE, 1016, 1, 100)
     , dump(dbms_lob.substr(EVENT_DETAIL_VALUE, 100, 1), 1016)
     , dump(EVENT_DETAIL_VALUE as varchar2(100), 1016)
  from PBI4AUDIT.ADS_EVENT_DETAIL
 where EVENT_ID             = 17710233064828830405
   and EVENT_DETAIL_TYPE_ID = 12;

Bonjour Waldar,

A nouveau merci beaucoup pour votre réponse et cette requête de test.

Concrètement, il y avait une erreur de syntaxe sur le dump du dbms_lob.substr mais en m'orientant sur un dump des colonnes pour en voir la valeur interne réelle vous avez mis le doigt sur le problème : le transtypage avec CAST ajoute un caractère 00 de padding à la fin et qui évidemment n'est pas affichable, d'où l'échec de jointure.

J'ai finalement opté pour le CAST et voici la requête de test que j'ai faite et qui confirme l'ajout de ce caractère de fin "parasite" :

Code :

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

1
2
3
4
5
6
7
8
9
select EVENT_DETAIL_VALUE as VALEUR_INITIALE_DETAIL_EVENT
     , cast(EVENT_DETAIL_VALUE as varchar2(100)) as CAST_BRUT
     , length(trim(cast(EVENT_DETAIL_VALUE as varchar2(100)))) as LONGUEUR_CAST_BRUT
     , dump(cast(EVENT_DETAIL_VALUE as varchar2(100)), 1016) as DUMP_CAST_BRUT
     , substr(cast(EVENT_DETAIL_VALUE as varchar2(100)),1,length(trim(cast(EVENT_DETAIL_VALUE as varchar2(100))))-1) as CAST_TRONQUE     
     , length(substr(cast(EVENT_DETAIL_VALUE as varchar2(100)),1,length(trim(cast(EVENT_DETAIL_VALUE as varchar2(100))))-1)) as LONGUEUR_CAST_TRONQUE
  from PBI4AUDIT.ADS_EVENT_DETAIL
 where EVENT_ID  = 17710233064828830405
   and EVENT_DETAIL_TYPE_ID = 12

Résultat de la valeur "brut" issue du CAST montrant la longueur à 36 et le caractère 00 en fin de chaîne (le nombre de caractères utiles est de 35) :



Il m'a donc suffit de remplacer dans ma sous-requête initiale la valeur "brut" résultant du CAST par un substr avec length-1 de cette valeur et évidemment la jointure est désormais valide et renvoie la ligne résultat escomptée.

Code :

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

1
2
3
4
-- donc 
cast(EVENT_DETAIL_VALUE as varchar2(100)) 
-- est remplacé par :
substr(cast(EVENT_DETAIL_VALUE as varchar2(100)),1,length(trim(cast(EVENT_DETAIL_VALUE as varchar2(100))))-1)

Mon problème est donc résolu et je vous remercie mille fois pour votre aide efficace et super rapide.

Bien à vous,

Yves

[Waldar]

Félicitations à vous, vous avez su deviner les étapes suivantes et résoudre le problème.