DBMS_SQL DESCRIBE_COLUMNS issue

Version imprimable

Citation:

Envoyé par orafrance

A noter que describe_columns est buggé d'où l'utilisation de describe_columns2 :)

je rencontre un problème dans l'utilisation du package DBMS_SQL et de la construction dynamique d'un ROWTYPE.
En bref, j'ai créé un package qui a pour but de comparer des vues entre deux instances oracle et pour cela j'utilise du 'dynamic SQL' basé sur DBMS_SQL.

Pour certaines vues ça fonctionne très bien et pour d'autres, j'obtiens une erreur ORA-06502 qui semble provenir de la construction dynamique des différentes colonnes.
J'ai essayé d'utiliser la fonction DESCRIBE_COLUMNS2 mais j'obtiens alors une erreur ORA-06508. Est-ce que c'est lié à ma version d'Oracle?

J'ai vu que ce sujet avait déjà été abordé mais je ne sais pas comment résoudre ce problème. Merci pour votre aide, je débute dans le développement pl/sql.

Il faut que je précise que ma version d'oracle n'est pas récente :
Code:

1 2 3 4 5 6 >select * from v$version; Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production PL/SQL Release 8.1.7.4.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.4.0 - Production NLSRTL Version 3.4.1.0.0 - Production
cdlt,
jd

23/11/2007, 13h44
orafrance

Bonjour,

1°) sans le code je vois mal comment on pourrait t'aider
2°) as-tu identifié les vues qui posent problème ?
3°) as-tu identifié le morceau de code qui déclenche l'erreur ?
4°) on ne debuggue pas les scripts sur le fourm ;)

précision nulle

J'utilise le package DYN_FETCH qui avait été publié dans oramag
(http://www.oracle.com/technology/ora...03/042003.html) pour générer dynamiquement mes requêtes de comparaison de vues.

La fonction suivante permet de construire la ROWTYPE dynamiquement qui est ensuite utilisé pour traiter le résultat d'un cursor REF CURSOR construit également dynamiquement.

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
 
CREATE OR REPLACE PACKAGE dyn_fetch AUTHID CURRENT_USER IS
   TYPE ref_cur_t IS REF CURSOR;
 
   g_table       VARCHAR2 (100);
   g_file        UTL_FILE.FILE_TYPE;
 
   g_query       VARCHAR2 (32000);
   g_count       NUMBER;
   g_desc_tab    DBMS_SQL.DESC_TAB;
 
   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type   CONSTANT PLS_INTEGER := 2;
   date_type     CONSTANT PLS_INTEGER := 12;
   rowid_type    CONSTANT PLS_INTEGER := 11;
   char_type     CONSTANT PLS_INTEGER := 96;
 
   long_type     CONSTANT PLS_INTEGER := 8;
   raw_type      CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type     CONSTANT PLS_INTEGER := 112;
   blob_type     CONSTANT PLS_INTEGER := 113;
   bfile_type    CONSTANT PLS_INTEGER := 114;
 
   PROCEDURE describe_columns;
   FUNCTION  record_def RETURN VARCHAR2;
   PROCEDURE fetch_ref_cur (p_function_ref_cur VARCHAR2, p_process_def VARCHAR2);
 
END;
/
 
CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
   ...
   FUNCTION record_def RETURN VARCHAR2 IS
      l_record_def     VARCHAR2 (32000);
      l_type             VARCHAR2 (100);
      l_col_type        BINARY_INTEGER;
      l_col_max_len   BINARY_INTEGER;
      l_col_precision  BINARY_INTEGER;
      l_col_scale       BINARY_INTEGER;
 
   BEGIN
      FOR i IN 1..g_count LOOP
         l_col_type      := g_desc_tab(i).col_type;
         l_col_max_len   := g_desc_tab(i).col_max_len;
         l_col_precision := g_desc_tab(i).col_precision;
         l_col_scale     := g_desc_tab(i).col_scale;
 
         IF    l_col_type = varchar2_type THEN
            l_type := 'VARCHAR2(' || l_col_max_len || ')';
         ELSIF l_col_type = number_type THEN
            l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale || ')';
         ELSIF l_col_type = date_type THEN
            l_type := 'DATE';
         ELSIF l_col_type = rowid_type THEN
            l_type := 'ROWID';
         ELSIF l_col_type = char_type THEN
            l_type := 'CHAR(' || l_col_max_len || ')';
         -- ELSIF  l_col_type ...
	    -- ltype := ...
         END IF;
 
         l_record_def := l_record_def || ' col_' || i || ' ' || l_type || ',';
      END LOOP;
 
      l_record_def := RTRIM (l_record_def, ',');            
      RETURN l_record_def;
   END record_def;

Ce que je constate, c'est que la précision retournée par DESCRIBE_COLUMNS pour certaines colonnes du type NUMBER est nulle.

Code:

1
2
3
4
 
> ORA-06502: PL/SQL: numeric or value error
...
PLS-00216: NUMBER precision constraint must be in range (1 .. 38)

Est-ce un bug de la fonction DESCRIBE_COLUMNS ?
Comment puis-je le contourner ?

Merci
jd

23/11/2007, 15h04
orafrance
Code:

1 2 3 4 SELECT * FROM dba_tab_columns WHERE data_type = 'NUMBER' AND data_precision IS NULL
la précision n'est pas obligatoire donc normal :?

remplace g_desc_tab(i).col_precision par NVL(g_desc_tab(i).col_precision,0) :)
23/11/2007, 16h16
herr_jd

Merci pour ta réponse.
Je ne crois pas que je puisse mettre 0 en cas de valeur Null car apparement la precision est définie entre 1 et 38.

Par contre, je sais maintenant pourquoi j'obtiens des valeurs nulles pour certaines colonnes de type NUMBER. A l'aide de la fonction record_def, je récupère la définition des colonnes de VUES. Et DESCRIBE_COLUMNS me retourne une précision et une échelle égale à 0 dès qu'il rencontre une instruction DECODE! :cry:

Est-ce qu'il y a un moyen pour contourner ce problème ?

Merci
jd