[9i] bug dbms_sql.describe_columns2

Version imprimable

Bonjour,

J'ai deux requêtes différentes, qui sélectionnent la même colonne d'une même table de type NUMBER(6,0).

Pourtant, lorsque je parse les deux requêtes, dbms_sql.describe_columns2 me renvoie bien NUMBER(6,0) pour la première, mais NUMBER(0, -127) pour la seconde !?!?

Je savais que dbms_sql.describe_columns était buggée, mais j'ignorais que dbms_sql.describe_columns2 l'était également...

Si quelqu'un voit une solution...

Voici le script permettant de reproduire le bug :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
SET serveroutput ON;
 
CREATE TABLE test1(col_number_6 NUMBER(6), col_id_test2 NUMBER(6));
 
CREATE TABLE test2(col_id_test2 NUMBER(6));
 
declare
  curseur number;     /* Pointeur sur le Curseur Dynamique  */
  result number;      /* Resultat sur l'ordre de la requete (OK/KO) */
  nbr_col integer;    /* Nbre de colonne de la requete      */
  les_colonnes dbms_sql.desc_tab2;  /* Structure donnant le descriptif des colonnes */
 
 
  procedure decrit_la_colonne(rec IN dbms_sql.desc_rec2) IS
  begin
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('col_type            =    ' || rec.col_type);
    dbms_output.put_line('col_maxlen          =    ' || rec.col_max_len);
    dbms_output.put_line('col_name            =    ' || rec.col_name);
    dbms_output.put_line('col_name_len        =    ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    ' || rec.col_precision);
    dbms_output.put_line('col_scale           =    ' || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
 
    IF (rec.col_null_ok) Then
           dbms_output.put_line('Oui'); 
    else    
           dbms_output.put_line('Non'); 
    end IF;
  end;
 
begin
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select col_number_6 from test1', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
 
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
 
  dbms_sql.close_cursor(curseur);
 
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select col_number_6 from (test1 FULL OUTER JOIN test2 ON test1.col_id_test2 = test2.col_id_test2)', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
 
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
 
  dbms_sql.close_cursor(curseur);
 
end;
/

Et le résultat, correct pour la première requête, totalement faux pour la seconde... :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    6
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    -127
col_null_ok         =    Oui

PL/SQL procedure successfully completed.

salut,

j'ai remplacé le FULL OUTER JOIN par un union all est ca marche.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
declare
  curseur number;     /* Pointeur sur le Curseur Dynamique  */
  result number;      /* Resultat sur l'ordre de la requete (OK/KO) */
  nbr_col integer;    /* Nbre de colonne de la requete      */
  les_colonnes dbms_sql.desc_tab2;  /* Structure donnant le descriptif des colonnes */
  procedure decrit_la_colonne(rec IN dbms_sql.desc_rec2) IS
  begin
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('col_type            =    ' || rec.col_type);
    dbms_output.put_line('col_maxlen          =    ' || rec.col_max_len);
    dbms_output.put_line('col_name            =    ' || rec.col_name);
    dbms_output.put_line('col_name_len        =    ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    ' || rec.col_precision);
    dbms_output.put_line('col_scale           =    ' || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    IF (rec.col_null_ok) Then
           dbms_output.put_line('Oui');
    else
           dbms_output.put_line('Non');
    end IF;
  end;
begin
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select col_number_6 from test1', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select test1.col_number_6 
                                         from test1 , test2 
                                          where  test1.col_id_test2(+) = test2.col_id_test2
                                    union all 
                                    select test1.col_number_6 
                                    from test1 , test2 
                                        where  test1.col_id_test2 = test2.col_id_test2(+) ', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
end;
/

Le résultat

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
 
SQL> /
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    6
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    6
col_scale           =    0
col_null_ok         =    Oui
 
Procédure PL/SQL terminée avec succès.

16/04/2007, 16h28
Nico57

Merci mais le problème est que ce morceau de code fait partie d'une plus grosse requête (j'ai simplement isolé le problème), mais dans le cadre de ma requête originale, le FULL OUTER JOIN est OBLIGATOIRE... Ce sont en fait des requêtes programmées par des utilisateurs avancés, et je me vois mal leur expliquer qu'il leur est interdit de faire des FULL OUTER JOIN, et qu'il leur faut réécrire leurs requêtes de façon beaucoup plus complexe...

De plus, il n'est tout de même pas normal que ça bugge, que ce soit un FULL OUTER JOIN ou un UNION ALL : ça devrait marcher dans les 2 cas... Je me trompe?

Merci,

Nico'
16/04/2007, 16h32
orafrance

FOJ et UNION ALL ça fait pas du tout la même chose alors vérifie quand même que ta requête est correcte... le bug serait plutôt dans celle-ci :?
16/04/2007, 16h36
Nico57

Citation:

Envoyé par orafrance

FOJ et UNION ALL ça fait pas du tout la même chose alors vérifie quand même que ta requête est correcte... le bug serait plutôt dans celle-ci :?

En fait, ma requête d'origine (avec le FULL OUTER JOIN) produit bien le résultat escompté par les utilisateurs.

Le problème est qu'en la parsant avec dbms_sql.describe_columns2, la colonne retournée est d'un type aberrant!!! Même si la requête produit un résultat mauvais fonctionnellement, ce n'est pas une raison valable pour qu'un NUMBER(6, 0) se tranforme en NUMBER(0, -127) !?!?!
16/04/2007, 16h39
salim11

Salut ,

C'est pas moi qu'il a inventé mais tu peux lire cette article (Full Outer Join)
http://www.oreillynet.com/pub/a/netw.../fulljoin.html
16/04/2007, 16h51
orafrance

Citation:

Envoyé par Nico57

ce n'est pas une raison valable pour qu'un NUMBER(6, 0) se tranforme en NUMBER(0, -127) !?!?!

Ca me parait être une valeur qui pourrait correspondre à une colonne NULL :koi:

Salut,
Bizare j'ai mis uniquement

Code:

1
2
 
test2 FULL OUTER JOIN test1 ON

au lieu

Code:

1
2
 
test1 FULL OUTER JOIN test2 ON

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
declare
  curseur number;     /* Pointeur sur le Curseur Dynamique  */
  result number;      /* Resultat sur l'ordre de la requete (OK/KO) */
  nbr_col integer;    /* Nbre de colonne de la requete      */
  les_colonnes dbms_sql.desc_tab2;  /* Structure donnant le descriptif des colonnes */
  procedure decrit_la_colonne(rec IN dbms_sql.desc_rec2) IS
  begin
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('col_type            =    ' || rec.col_type);
    dbms_output.put_line('col_maxlen          =    ' || rec.col_max_len);
    dbms_output.put_line('col_name            =    ' || rec.col_name);
    dbms_output.put_line('col_name_len        =    ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    ' || rec.col_precision);
    dbms_output.put_line('col_scale           =    ' || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    IF (rec.col_null_ok) Then
           dbms_output.put_line('Oui'); 
    else    
           dbms_output.put_line('Non'); 
    end IF;
  end;
begin
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select col_number_6 from test1', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
  curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select col_number_6 from (test2 FULL OUTER JOIN test1 ON test1.col_id_test2 = test2.col_id_test2)', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
end;
/

Résultat :

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
 
SQL> /
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    6
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COL_NUMBER_6
col_name_len        =    12
col_schema_name     =
col_schema_name_len =    0
col_precision       =    6
col_scale           =    0
col_null_ok         =    Oui
 
Procédure PL/SQL terminée avec succès.

16/04/2007, 18h54
Nico57

Citation:

Envoyé par orafrance

Ca me parait être une valeur qui pourrait correspondre à une colonne NULL :koi:

Pourtant, lorsqu'on exécute la requête, elle fonctionne parfaitement, et la valeur de cette colonne n'est pas toujours NULL...

Salut,

J'ai remarqué une chose, quand je commence le full outer join par le père ça marche bien ( dept FULL OUTER JOIN emp ) sauf pour la la clé primaire de dept et quand c'est contraire ( emp FULL OUTER JOIN dept ) il m'affiche plein de collone avec la précision -127
Remarque : Le résultat de la requette avec dept FULL OUTER JOIN emp et le même qu'avec emp FULL OUTER JOIN dept

Exemple avec ( dept FULL OUTER JOIN emp )

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
declare
  curseur number;     /* Pointeur sur le Curseur Dynamique  */
  result number;      /* Resultat sur l'ordre de la requete (OK/KO) */
  nbr_col integer;    /* Nbre de colonne de la requete      */
  les_colonnes dbms_sql.desc_tab2;  /* Structure donnant le descriptif des colonnes */
  procedure decrit_la_colonne(rec IN dbms_sql.desc_rec2) IS
  begin
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('col_type            =    ' || rec.col_type);
    dbms_output.put_line('col_maxlen          =    ' || rec.col_max_len);
    dbms_output.put_line('col_name            =    ' || rec.col_name);
    dbms_output.put_line('col_name_len        =    ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    ' || rec.col_precision);
    dbms_output.put_line('col_scale           =    ' || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    IF (rec.col_null_ok) Then
           dbms_output.put_line('Oui');
    else
           dbms_output.put_line('Non');
    end IF;
  end;
begin
DBMS_OUTPUT.ENABLE( 1000000 ) ;
curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select  empno,emp.deptno,loc,dept.dname,sal,comm  from dept FULL OUTER JOIN emp  ON emp.deptno = dept.deptno', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
end;
/
SQL> /
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    EMPNO
col_name_len        =    5
col_schema_name     =
col_schema_name_len =    0
col_precision       =    4
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    DEPTNO
col_name_len        =    6
col_schema_name     =
col_schema_name_len =    0
col_precision       =    2
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    1
col_maxlen          =    13
col_name            =    LOC
col_name_len        =    3
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    1
col_maxlen          =    17
col_name            =    DNAME
col_name_len        =    5
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    SAL
col_name_len        =    3
col_schema_name     =
col_schema_name_len =    0
col_precision       =    7
col_scale           =    2
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COMM
col_name_len        =    4
col_schema_name     =
col_schema_name_len =    0
col_precision       =    7
col_scale           =    2
col_null_ok         =    Oui
 
Procédure PL/SQL terminée avec succès.

Exemple avec ( emp FULL OUTER JOIN dept )

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
declare
  curseur number;     /* Pointeur sur le Curseur Dynamique  */
  result number;      /* Resultat sur l'ordre de la requete (OK/KO) */
  nbr_col integer;    /* Nbre de colonne de la requete      */
  les_colonnes dbms_sql.desc_tab2;  /* Structure donnant le descriptif des colonnes */
  procedure decrit_la_colonne(rec IN dbms_sql.desc_rec2) IS
  begin
    dbms_output.put_line('-----------------------------------------------------------');
    dbms_output.put_line('col_type            =    ' || rec.col_type);
    dbms_output.put_line('col_maxlen          =    ' || rec.col_max_len);
    dbms_output.put_line('col_name            =    ' || rec.col_name);
    dbms_output.put_line('col_name_len        =    ' || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    ' || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    ' || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    ' || rec.col_precision);
    dbms_output.put_line('col_scale           =    ' || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    IF (rec.col_null_ok) Then
           dbms_output.put_line('Oui');
    else
           dbms_output.put_line('Non');
    end IF;
  end;
begin
DBMS_OUTPUT.ENABLE( 1000000 ) ;
curseur := dbms_sql.open_cursor;
  dbms_sql.parse(curseur, 'select  empno,emp.deptno,loc,dept.dname,sal,comm  from emp FULL OUTER JOIN dept  ON emp.deptno = dept.deptno', dbms_sql.native);
  result := dbms_sql.execute(curseur);
  dbms_sql.describe_columns2(curseur, nbr_col, les_colonnes);
  ----------------------------------------------------------------
  --   Je decrit les colonnes de ma requete
  ----------------------------------------------------------------
  FOR j IN 1..nbr_col Loop
      decrit_la_colonne(les_colonnes(j));
  End Loop;
  dbms_sql.close_cursor(curseur);
end;
/
SQL> /
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    EMPNO
col_name_len        =    5
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    -127
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    DEPTNO
col_name_len        =    6
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    -127
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    1
col_maxlen          =    13
col_name            =    LOC
col_name_len        =    3
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    1
col_maxlen          =    17
col_name            =    DNAME
col_name_len        =    5
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    0
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    SAL
col_name_len        =    3
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    -127
col_null_ok         =    Oui
-----------------------------------------------------------
col_type            =    2
col_maxlen          =    22
col_name            =    COMM
col_name_len        =    4
col_schema_name     =
col_schema_name_len =    0
col_precision       =    0
col_scale           =    -127
col_null_ok         =    Oui
 
Procédure PL/SQL terminée avec succès.

17/04/2007, 08h40
Nico57

Ca me semble vraiment être un bug Oracle, non?

Je me demande si ça fonctionne bien dans la dernière version, 10g je-sais-combien.
17/04/2007, 09h02
Nico57

Je viens de lire sur Metalink la Note Note:255902.1 .

Un scale de -127, dans ma version d'Oracle, correspond à un NUMBER "tout court" : NUMBER au lieu de NUMBER(6, 0).

Dans d'autres versions d'Oracle, le scale pour un NUMBER "tout court" aurait été de 0, soit...

Jusque là, c'est normal (enfin, si on veut...).

Par contre, dans ma requête d'origine, je ne vois pas pourquoi mon champ col_number_6, qui est un NUMBER(6), se retrouve donc transformé en NUMBER "tout court" lors du parsing par dbms_sql.describe_columns2 ...

Nico'