Discussion :

[geekomono]

Bonjour à tous,

En cette fin de semaine je ne pensais pas tomber sur un problème de SQL.

J'explique :

Je suis sur Oracle 11g.

J'ai un type de table MONTYPEDETABLE défini ainsi :

Code :

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

create or replace TYPE  "MONTYPEDETABLE" AS TABLE OF VARCHAR2(4000);

Et j'effectue la requête suivante :

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
 
SELECT * FROM (
  SELECT
    d.code_dest,
    l.id_livraison,
    l.code_livr,
    l.nom_lieu_livr,
    l.pays_livr
  FROM
    LIVRAISON l, 
    DESTINATAIRES d
  WHERE
    l.id_destinataire = d.id_destinataire
) 
QRSLT  WHERE ( 
  ( 
    code_dest MEMBER OF (
      CAST ( 
        ( 
          SELECT d2.code_dest from DESTINATAIRES d2 WHERE d2.code_dest='XXXXXX' 
        ) 
        AS MONTYPEDETABLE
      )
    )
  ) 
) 
ORDER BY "CODE_LIVR";

Une exception est remontée :

Code :

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

1
2
3
4
5
6
 
ORA-00932: types de données incohérents ; attendu : - ; obtenu : CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Erreur à la ligne 19, colonne 17

L'erreur provient du CAST( ... AS MONTYPEDETABLE).

Le type de DESTINATAIRES.CODE_DEST est VARCHAR2(20).

Si vous avez des pistes de correction je prend.

Merci

[skuatamad]

Quel est l'intérêt de passer par un type et member of dans ce cas ?
Un simple IN suffit, et même un simple filtre suffit.

[geekomono]

Bonne question,

Y a pas trop d'intérêt si ce n'est que c'est un framework qui me génère la requête SQL.

Elle a cette tête :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT * FROM (SELECT
  d.code_dest,
  l.id_livraison,
  l.code_livr,
  l.nom_lieu_livr,
  l.pays_livr
FROM
  LIVRAISON l, DESTINATAIRES d
WHERE
  l.id_destinataire = d.id_destinataire) QRSLT  WHERE ( ( code_dest MEMBER OF (CAST (:CodesDestinataires AS MONTYPEDETABLE)) ) ) ORDER BY "CODE_LIVR"

J'ai simplement remplacé la variable CodeDestinataires (qui un tableau de String en Java) par un SELECT.
La partie verte, je ne peut pas la toucher.

J'ai tenté le IN (:CodesDestinataires) simple mais ça ne fonctionne pas.

[skuatamad]

Y a pas trop d'intérêt si ce n'est que c'est un framework qui me génère la requête SQL.

Donc il y a un intérêt.
As tu essayé sans CAST ? Sinon regarde binding in-lists in 10g au cas où ça te débloque.

[mnitu]

Il vous manque un multiset

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
SELECT * FROM (
  SELECT
    d.department_name,
    e.first_name
  FROM
    hr.employees e, 
    hr.departments d
  WHERE
    e.department_id = d.department_id
) 
QRSLT  WHERE ( 
  ( 
    department_name MEMBER OF (
      CAST ( 
        multiset (           SELECT d2.department_name FROM hr.departments d2 WHERE d2.department_id = 10 
        ) 
        AS MONTYPEDETABLE
      )
    )
  ) 
)

[geekomono]

Merci pour le multiset. Je connaissais pas. La requête passe mais JDBC me renvoie une erreur.

Après recherche j'ai trouvé peu de chose et ne voyant pas de solution simple, j'ai donc tout refait la partie en Java/ADF pour au final avoir un IN ('XXXX', ...).

C'est plus compréhensible, plus maintenable (côté Java) et ça fonctionne.

Merci pour votre aide.

[Rei Ichido]

Est-ce que l'erreur ne provenait pas du binding (côté Java) de la variable de type "MONTYPEDETABLE" ?

[geekomono]

L'erreur remontée était : MULTISET est un mot clef. Je n'ai plus la trace exacte.

[mnitu]

Multiset c'était pour votre requête SQL ad-hoc. Dans le code générés nul besoin de multiset.
Votre solution est peut-être plus maintenable pour vous mais le code pose maintenant des problèmes de hard-parsing inutiles et prolifération des versions de la requête dans le shared_pool.

[geekomono]

Dans le code générés nul besoin de multiset.

C'est à dire ? Car si je ne mets pas le multiset, je reviens à la case départ.

problèmes de hard-parsing inutiles et prolifération des versions de la requête dans le shared_pool

Alors j'ai pas de notions sur le hard parsing avec Oracle mais pour la prolifération des requêtes, je ne vois ce que ça va créer en plus par rapport à ma requête initiale ?

Il vaut mieux avoir un "MEMBER OF" qu'un "IN" ?

[Rei Ichido]

C'est à dire ? Car si je ne mets pas le multiset, je reviens à la case départ.

Mais MULTISET sert à "transforme un résultat de SELECT en TABLE OF" ; le problème est que votre requête de post initial n'est pas votre besoin - vous devriez justement avoir déjà un TABLE OF.
Retirez le Multiset, et envoyez votre code Java - qui potentiellement ne passe pas le bon type.

Alors j'ai pas de notions sur le hard parsing avec Oracle mais pour la prolifération des requêtes, je ne vois ce que ça va créer en plus par rapport à ma requête initiale ?

Il vaut mieux avoir un "MEMBER OF" qu'un "IN" ?

Dans ce cas précis, si la requête est souvent utilisée avec des valeurs différentes, oui. Chaque requête générée avec le IN devra être "hard parsée", c'est à dire finalement compilée en un plan d'exécution et stockée. Alors que le MEMBER OF verra toujours la même requête, et donc ira piocher le plan déjà créé : beaucoup de boulot en moins !

[mnitu]

C'est à dire ? Car si je ne mets pas le multiset, je reviens à la case départ.
...

Voici un example en Pl/SQL

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
 
declare
  r sys_refcursor;
  l_nt MONTYPEDETABLE := MONTYPEDETABLE('Administration', 'Marketing');
  l_dname             varchar2(100);
  l_ename             varchar2(100);
Begin
  Open r for SELECT * FROM (
    SELECT
      d.department_name,
      e.first_name
    FROM
      hr.employees e, 
      hr.departments d
    WHERE
      e.department_id = d.department_id
  ) 
  QRSLT  WHERE ( 
    ( 
      department_name MEMBER OF Cast(l_nt as MONTYPEDETABLE)
    ) 
  );
  --
    Loop
      Fetch r Into l_dname, l_ename;
      Exit When r%NotFound;
      dbms_output.put_line(l_dname);
    End Loop;
    --
    Close r;
End;

Maintenant vous devez trouver comment passer et binder un tableau String en Java comme type MONTYPEDETABLE.

[geekomono]

Après quelques tests, je suis parvenu à ceci :

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
 
SELECT * 
FROM (
    SELECT LivraisonEO.ID_LIVRAISON, 
    LivraisonEO.CODE_LIVR, 
    LivraisonEO.NOM_LIEU_LIVR, 
    LivraisonEO.PAYS_LIVR, 
    LivraisonEO.ID_DESTINATAIRE,
    DestinatairesEO.CODE_DEST
    FROM LIVRAISON LivraisonEO, DESTINATAIRES DestinatairesEO
    WHERE LivraisonEO.ID_DESTINATAIRE = DestinatairesEO.ID_DESTINATAIRE
    AND DestinatairesEO.CODE_DEST MEMBER OF CAST (
        :CodesDestinataires AS MONTYPEDETABLE
         )
    ) 
    QRSLT  WHERE (
        (1=1) 
    ) 
ORDER BY CODE_LIVR

Ca change beaucoup de ma requête initiale et ça fonctionne.

[mnitu]

Si cette requête passe je ne vois pas trop pourquoi la requête d'origine ne passera pas non plus.

[geekomono]

Après re-test des deux solutions, je confirme la première requête ne passe pas, la seconde si. Je ne comprends pas trop la raison non plus. En tout cas, merci pour l'aide.