[PL/SQL][Oracle 8i] Variable de nom dynamique

**GoLDoZ** · 26/06/2006, 11h26

Bonjour.

Voici mon souci.

J'ai un tableau contenant les noms des colonnes d'une table, ordonnés comme il faut. J'ai également un curseur sur la table.
A partir de ce curseur, je dois concaténer les valeurs des colonnes de la table (ligne par ligne).

Voici le squelette de la procédure:

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
PROCEDURE maprocedure IS

 CURSOR cur_matable IS
  SELECT * FROM matable;

 rec_matable matable%ROWTYPE;

 CURSOR cur_montableau IS
  SELECT nom_colonne
   FROM matableref
  WHERE nom_table = matable
   ORDER BY ordre_colonne;

 TYPE T_REC_tableau IS RECORD (
  nom_colonne ....
  long_colonne ...
  typ_colonne...

 TYPE TYP_TABLEAU IS TABLE OF T_REC_tableau INDEX BY BINARY_INTEGER ;
 tab_montableau TYP_TABLEAU;

Begin

-- remplissage de tab_montableau à partir de cur_montableau: OK

OPEN cur_matable ;
  LOOP
   FETCH cur_matable INTO rec_matable ;
   EXIT WHEN cur_matable %NOTFOUND;

   ls_ligne := '';
   -- on crée la ligne en concaténant les valeurs des colonnes dans le bon ordre
   FOR i IN 1..longueur_montableau
   LOOP
    ls_ligne := ls_ligne || rec_matable.tab_montableau(i).nom_colonne;
   END LOOP;

 END LOOP;
END;

mon pb est ici: rec_matable.tab_montableau(i).nom_colonne

je voudrais que tab_montableau(i).nom_colonne donne le nom de la colonne, qui ensuite, me donne la valeur de cette colonne à travers le curseur rec_matable.

en gros, le principe des pointeurs de pointeurs, ou $$nomvariable=$mavariable=mavaleur en php

comment contourner cela?

merci

**GoLDoZ** · 26/06/2006, 11h41

Au cas où vous auriez plus simple, je vous explique le but de la procédure.

Mon but est d'écrire dans un fichier les lignes d'une table ligne par ligne, avec les colonnes concaténées (et formatées) dans un ordre précis

Cet ordre, je vais le récupérer dans une table de réfrence (qui contient les noms des tables, des colonnes et un ordre associé), et je renseigne tab_montableau.

Ensuite, je parcoure la table et concatène les colonnes selon l'ordre de tab_montableau.

Vu qu'il y a une trentaine de tables à extraire, je souhaite quelquechose de dynamique.
Si je n'ai pas le choix, je mettrai tout en dur dans chaque procédure et zapperai la table de référence mais je préfère éviter.

**GoLDoZ** · 26/06/2006, 12h06

J'avais trouvé une première solution mais le problème est le même.
Le nom de la table est dynamique:

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
PROCEDURE CONCAT(nom_table IN VARCHAR2) IS
 ma_requete VARCHAR2(500);
 
 TYPE TYP_REF_CUR IS REF CURSOR ;
 mon_curseur TYP_REF_CUR ;
 
 mon_record nom_table%ROWTYPE;
  
Begin
 ma_requete := 'SELECT ....... FROM ma_table';
 
 Open mon_curseur For ma_requete;
 LOOP
  FETCH mon_curseur INTO mon_record;

.....

Si 'ma_requete' était remplacé implicitement par sa valeur et que la déclaration du record fonctionnait, tout irait bien...

**GoLDoZ** · 26/06/2006, 14h32

beaucoup de visites, pas beaucoup de réponses...
serait-ce insurmontable?

**plaineR** · 26/06/2006, 14h32

Tu es en quelle version d'oracle ? C'est peut-être pour cela que tu n'as pas de réponses :
http://www.developpez.net/forums/showthread.php?t=30569

**GoLDoZ** · 26/06/2006, 14h46

j'ai rajouté 8i

**plaineR** · 26/06/2006, 15h22

Si j'ai compris ce que tu souhaites faire tu peux faire un truc du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
id_cur := dbms_sql.open_cursor;
dbms_sql.parse (id_cur, v_requete, dbms_sql.native);
for i = 1 to nb_colonnes_table loop
   dbms_sql.define_column (id_cur, i, v_val_col, n);
end loop;
v_nblig := dbms_sql.execute (id_cur);
v_nblig := dbms_sql.fetch_rows (id_cur);
for i = 1 to nb_colonnes_table loop
    dbms_sql.column_value (id_cur_mov, 1, v_val_col);
    v_lig := v_lig || v_val_col;
end loop;

- v_val_col doit être déclaré en varchar2(n)
- n est la longueur maxi de tes colonnes (si tu ne souhaites pas prendre de risque, mets 4000)
- nb_colonnes_table est le nombre de colonnes dans ta table
- dans ta variable v_requete, tu dois indiquer les colonnes dans l'ordre souhaité.

**GoLDoZ** · 26/06/2006, 16h19

ah, je n'ai pas l'habitude d'utiliser le package dbms_sql...

je voulais utiliser 'EXECUTE IMMEDIATE' mais je ne voyais pas comment gérer l'ordre des colonnes à la récupération

pour être sûr, que fait la fonction dbms_sql.define_column?

puis-je déclarer v_val_col en varchar2 même si toutes mes colonnes ne sont pas de ce type?

id_cur_mov = id_cur?

edit: j'ai trouvé une description ici: http://download-west.oracle.com/docs...ql.htm#1006465

**plaineR** · 26/06/2006, 16h39

En fait tu retrouves en détaillé tout ce que fait execute immediate.

L'avantage, c'est que tu peux gérer en dynamique le nombre de colonnes.

DBMS_SQL.DEFINE_COLUMN est la phase de définition des colonnes. Tu indiques dans quelle colonne (type et longueur) tu vas renvoyer la valeur de ta colonne.

**GoLDoZ** · 27/06/2006, 12h06

merci plaineR, je creuse le sujet

ceci dit, j'ai un doute...
dans ton exemple, v_val_col est-il un tableau?

je me pose la question quand je regarde cette page http://download-west.oracle.com/docs...ql.htm#1006465 et que je lis ceci

Code :

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

1
2
3
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);

puis

Code :

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

1
2
3
DBMS_SQL.COLUMN_VALUE(source_cursor, 1, id_var); 
DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name_var); 
DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate_var);

**plaineR** · 27/06/2006, 12h30

Envoyé par plaineR

- v_val_col doit être déclaré en varchar2(n)
- n est la longueur maxi de tes colonnes (si tu ne souhaites pas prendre de risque, mets 4000)

**orafrance** · 27/06/2006, 13h40

Envoyé par GoLDoZ

pour être sûr, que fait la fonction dbms_sql.define_column?

utilise la fonction rechercher, ça a été traité très souvent

**GoLDoZ** · 27/06/2006, 13h59

@fred: je suis un adepte de la recherche

mais désolé, la recherche de "define_column" ne donne aucun résultat, je n'ai que le lien que j'ai mis plus haut pour m'aider...

@plaineR: j'avais bien vu

mais je ne trouve pas ça très logique que ce soit une chaîne et non un tableau

en effet, tu écris:

Code :

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

1
2
3
for i = 1 to nb_colonnes_table loop
   dbms_sql.define_column (id_cur, i, v_val_col, n);
end loop;

que contient la variable v_val_col? pour moi c'est une variable qui va contenir les valeurs des différentes colonnes, et donc un tableau

j'aurais plutôt vu ça:

Code :

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

1
2
3
for i = 1 to nb_colonnes_table loop
   dbms_sql.define_column (id_cur, i, v_val_col(i), n);
end loop;

par analogie à ça:

Code :

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

1
2
3
DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id_var); 
DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name_var, 30); 
DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate_var);

d'où ma question

**GoLDoZ** · 27/06/2006, 15h16

bon, j'ai finalement réussi à obtenir ce que je veux, sauf que je n'avais pas les bonnes valeurs

une coQuille s'est glissée dans le script de plaineR

Code :

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

1
2
3
4
for i = 1 to nb_colonnes_table loop
    dbms_sql.column_value (id_cur_mov, 1, v_val_col);
    v_lig := v_lig || v_val_col;
end loop;

il faut mettre i au lieu de 1

reste plus qu'à formater tout ça pour l'envoyer dans un fichier et la migration sera prête

merci

**GoLDoZ** · 27/06/2006, 15h25

je rajoute quelquechose d'utile pour ceux qui voudront faire la même chose

pour boucler sur les toutes les lignes de la table, il faut mettre un LOOP autour du fetch et du column_value, et traiter l'erreur du "fetch out of sequence" pour sortir gentiment et continuer le script

voici la partie de mon code concernant tout le sujet

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
ls_requete := 'SELECT ' || ls_colonnes || ' FROM ' || p_table;
 
id_cur := dbms_sql.open_cursor;
dbms_sql.parse (id_cur, ls_requete, dbms_sql.native);
 
FOR i IN 1..ln_nb_colonnes LOOP
  dbms_sql.define_column (id_cur, i, ls_val_col, ln_long_max);
END LOOP;
 
ln_nb_lignes := dbms_sql.EXECUTE (id_cur);
 
BEGIN
  LOOP
    ls_ligne := '';
 
    IF dbms_sql.fetch_rows (id_cur) > 0 THEN 
      FOR i IN 1..ln_nb_colonnes LOOP
        dbms_sql.column_value (id_cur, i, ls_val_col);
        ls_ligne := ls_ligne || Format_Colonne(ls_val_col);
      END LOOP;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(id_cur) THEN 
      DBMS_SQL.CLOSE_CURSOR(id_cur); 
    END IF;
END;