CAST d'une table problèmatique

Version imprimable

Bonjour à tous,

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

J'explique :

Je suis sur Oracle 11g.

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

Code:

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

Et j'effectue la requête suivante :
Code:

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:

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

01/03/2013, 14h58
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.

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:

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.

01/03/2013, 17h31
skuatamad

Citation:

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.

Il vous manque un multiset

Code:

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

06/03/2013, 11h32
geekomono

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

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.
06/03/2013, 11h55
Rei Ichido

Est-ce que l'erreur ne provenait pas du binding (côté Java) de la variable de type "MONTYPEDETABLE" ?
06/03/2013, 12h24
geekomono

L'erreur remontée était : MULTISET est un mot clef. Je n'ai plus la trace exacte.
06/03/2013, 13h23
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.
06/03/2013, 15h34
geekomono

Citation:

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.

Citation:

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" ?
06/03/2013, 16h04
Rei Ichido

Citation:

Envoyé par geekomono

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.

Citation:

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 !

Citation:

Envoyé par geekomono

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

Voici un example en Pl/SQL

Code:

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.

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

Code:

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.

07/03/2013, 10h05
mnitu

Si cette requête passe je ne vois pas trop pourquoi la requête d'origine ne passera pas non plus. :koi:
11/03/2013, 14h14
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.