Discussion :

[P3dr075]

Salut,

Je suis débutant pl/sql et j'ai un probleme à résoudre: Sur une instance j'ai n schémas comportants les memes tables ayant toutes la meme structure.
Le but de ma procédure est de récupérer la valeur d'un champs donné sur l'ensemble de ces schémas.

j'ai commencé à ecrire la procédure suivante qui prend en paramètre le nom de la table et un champs:

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
create or replace PROCEDURE GET_FIELD_VALUE( TAB_NAME IN VARCHAR2, TAB_FIELD IN VARCHAR2) IS
 
username dba_users.username%TYPE;
table_name VARCHAR2(50):='MA_TABLE';
field_value VARCHAR(50):='MON_CHAMPS';
sSqlStmt VARCHAR2(500):='';
 
  /*recupere les bases courantes à partir de dba_users*/
  CURSOR C1 IS
  SELECT DISTINCT(USERNAME)
  FROM dba_users
  ORDER BY USERNAME;
 
BEGIN
  OPEN C1;
  LOOP
    FETCH C1 INTO username;
    EXIT WHEN C1%NOTFOUND;
    sSqlStmt:= 'SELECT'||' '|| field_value||' '|| 'FROM'||' '|| username||'.'||table_name||';'; 
    DBMS_OUTPUT.PUT_LINE (sSqlStmt);
    --DBMS_OUTPUT.PUT_LINE (username);
  END LOOP;
    CLOSE C1;
END;

je bloque en faite pour faire executer la requete construite dynamiquement et obtenir le résultat à partir du nom du schéma, de la table et du champs de cette table. Dois ouvrir un second curseur auquel je passe le résultat du premier?

merci pour vos conseils,

[McM]

Oui, 1 curseur pour lire les différents user ayant cette table (et pas tous les utilisateurs de la base comme tu l'as fait)
Ensuite pour chaque utilisateur, lire les données.

Petit conseil, évite les OPEN FETCH quand tu le peux. Préfère le FOR LOOP qui ferme automatiquement le curseur, pas de test d'existence, etc..
Evite aussi les variables avec des noms qui peuvent être des nom de colonne (exemple table_name)

Enfin il faut gérer les différents types de colonnes (VARCHAR, NUMBER, DATE).. et voir comment récupérer les données.

Dans l'exemple ci dessous, je passe par un cursor, mais on pourrait faire un EXECUTE IMMEDIATE BULK COLLECT INTO pour renseigner directement un tableau de valeur.

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
CREATE OR REPLACE PROCEDURE GET_FIELD_VALUE( TAB_NAME IN VARCHAR2, TAB_FIELD IN VARCHAR2) IS
	TYPE Tcur IS REF CURSOR;
	cur Tcur;
	sSqlStmt VARCHAR2(500):='';
 v_donnee VARCHAR2(20);
BEGIN	
--	FOR r1 IN (SELECT owner FROM ALL_TABLES WHERE table_name = TAB_NAME ORDER BY OWNER)
-- Mieux pour être sur que la colonne existe dans cette table pour cet utilisateur
	FOR r1 IN (SELECT owner FROM ALL_TAB_COLUMNS WHERE table_name = TAB_NAME AND column_name = TAB_FIELD ORDER BY owner)
  LOOP
    sSqlStmt:= 'SELECT '|| field_value ||' FROM '|| r1.owner ||'.'|| TAB_NAME; 
    DBMS_OUTPUT.PUT_LINE (sSqlStmt);
		-- On lit les données
    OPEN cur FOR sSqlStmt;
    LOOP
      FETCH cur INTO v_donnee;
      EXIT WHEN cur%NOTFOUND OR cur%NOTFOUND IS NULL;
    	DBMS_OUTPUT.PUT_LINE(v_donnee);
    END LOOP;
    CLOSE cur;
  END LOOP;
END;