[PL/SQL] Curseurs Imbriqués visibilité de variables

Version imprimable

Bonjour,
Je dois effectuer un traitement sur des colones varchar2 de tables ayant un nom générique du style XXXXX_%
Pour se faire j'ai un premier curseur qui va me chercher le nom des ces tables et de leurs colones dans une table systeme
et un deuxième curseur qui va me chercher les infos dans les tables.
J'ai des erreurs à la compil que vous pouvez voir ci dessous, je n'arrive pas à linker mais deux curseurs.
Si vous avez une solution je suis prenneur.
Une solution que j'ai trouvé est de remplacer mon sous curseur par un execute immediate mais je ne sais
pas ensuite comment boucler sur le résultat de cet execute immédiate.

Merci
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 declare cursor tableName is select distinct TABLE_NAME, COLUMN_NAME, DATA_LENGTH from DBA_TAB_COLUMNS where TABLE_NAME like 'XXXXX_%' and DATA_TYPE = 'VARCHAR2' order by TABLE_NAME; cursor tableData (tabName in varchar2, colName in varchar2, dataLength in number) is select colName from tabName where length(colName) = tableName.dataLength; begin for tabs in tableName loop for datas in tableData(tabs.TABLE_NAME,tabs.COLUMN_NAME,tabs.DATA_LENGTH) loop dbms_output.put_line(datas.colName); end loop; end loop; end;
Code:

1 2 3 4 5 6 7 8 ORA-06550: line 10, column 29: PLS-00201: identifier 'TABNAME' must be declared ORA-06550: line 10, column 9: PL/SQL: SQL Statement ignored ORA-06550: line 19, column 26: PLS-00364: loop index variable 'DATAS' use is invalid ORA-06550: line 19, column 5: PL/SQL: Statement ignored

29/10/2007, 17h50
Invité

Tu ne peux pas utiliser une variable pour définir ta table dans un select -> Solution : SQL dynamique
http://sheikyerbouti.developpez.com/execute_immediate/

Je précise que je suis sur 8i.
D'apres la solution que tu m'a donné il faut que j'utilise un "bulk collect" car mon select renvoie un resultat multiligne
hors le compilateur n'a pas l'air de reconnaitre l'instruction "bulk" qui est pourtant un instruction 8i.
Merci de votre aide.

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
declare	   	   
	   -- curseur sur table systeme
	   cursor tableName is
	   		  select distinct TABLE_NAME, COLUMN_NAME, DATA_LENGTH from DBA_TAB_COLUMNS
			  where TABLE_NAME like 'XXXXXXXXX_%'
			  and DATA_TYPE = 'VARCHAR2'
			  order by TABLE_NAME;			  
 
	   -- structure de res du execute immediate
	   type T_Data is table of varchar2(256);
	   rowO T_Data;
	   querry varchar2(256);
 
begin
	--boucle sur les noms et colonnes des tables
	for tabs in tableName
	loop
		--bouble sur les tables physique
		begin
			 querry:= 'select ' ||tabs.COLUMN_NAME || ' from ' || tabs.TABLE_NAME ||
						  ' where length(' ||tabs.COLUMN_NAME|| ') = ' ||tabs.DATA_LENGTH ;
			 dbms_output.put_line(querry);
 
			 execute immediate querry bulk collect into rowO;
		end;
 
		...
 
	end loop;
end;

Code:

1
2
3
4
5
6
ORA-06550: line 36, column 30:
PLS-00103: Encountered the symbol "BULK" when expecting one of the following:
 
   . ( * @ % & = - + ; < / > at in mod not rem return returning
   <an exponent (**)> <> or != or ~= >= <= <> and or like
   between into using is null is not || is dangling

Voilà un exemple, j’espère que ça aide

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
 
Declare
  Cursor tableName IS
      Select OWNER, TABLE_NAME, COLUMN_NAME, DATA_LENGTH 
        From DBA_TAB_COLUMNS
       Where TABLE_NAME LIKE 'EMP%'
	       And DATA_TYPE = 'VARCHAR2'
       Order By TABLE_NAME;
  --	   
  Type t_CurTyp Is Ref Cursor;
  crsData       t_CurTyp;
  columnName    Varchar2(30);
  l_sql         Varchar2(32676);
  --  
Begin
  For tabs In tableName
  Loop
    l_sql := 'Select '|| tabs.column_name||
              ' From '||tabs.Owner||'.'||tabs.table_name||
             ' Where length('||tabs.column_name||') = :l';
		Open crsData For l_sql Using tabs.data_length;
		Loop
       Fetch crsData Into columnName;
       Exit When crsData%NOTFOUND;
			 dbms_output.put_line(tabs.Owner||'.'||tabs.table_name||'.'||tabs.column_name);
		End loop; 
    Close crsData;    
  End loop;	
End;

30/10/2007, 14h32
HurtMarley

Merci, ça aide beaucoup et surtout ça marche. Par contre je ne comprend pas l'utilité de la variable de substitution sur les data_length dans le cas présent.

Je reste quand meme curieux de savoir pourquoi le "bulk collect" ne passait pas parceque du coup la seule solution que j'ai vu (à part la tienne qui marche) était de compter le nombre de ligne retournées et d'accéder ligne a ligne dirrectement (tres cochon)

Merci à tous
31/10/2007, 09h21
mnitu

Citation:

Envoyé par HurtMarley

...Par contre je ne comprend pas l'utilité de la variable de substitution sur les data_length dans le cas présent.

C'était supposé d'être un exemple, je l’ai ajouté tout à la fin.:oops:

Citation:

Envoyé par HurtMarley

Je reste quand meme curieux de savoir pourquoi le "bulk collect" ne passait pas ...

Le « bulk collect » c’est une optimisation; elle ne change pas la logique du traitement. A partir de cet exemple il est facile de l’ajouter sur la première boucle. Ensuite le passer en dynamique ça va tout seul. Par contre, il faut s’interroger sur l’utilité d’un traitement en « bulk collect » sur la boucle intérieure, parce que l’exemple telle qu’il est, affiche le nom de la colonne pour chaque valeur qui est égale à la taille de la colonne.
31/10/2007, 10h20
HurtMarley

Ok pour la var de substitution ;).

Pour le bulk collect ce que je ne comprenais pas dans mon second exemple c'est pourquoi le compilateur me jetait sur le mot clé "bulk" sachant qu' apriori la syntaxe était bonne.
31/10/2007, 11h30
mnitu

C'est peut être la version d'Oracle. La possibilité d'utiliser "bulk collect" pour un sql dynamique existe seulement à partir de la version Oracle 9.0.1

Citation:

Enhancements to Bulk Operations
You can now perform bulk SQL operations, such as bulk fetches, using native dynamic SQL (the EXECUTE IMMEDIATE statement). You can perform bulk insert or update operations that continue despite errors on some rows, then examine the problems after the operation is complete.
31/10/2007, 13h15
HurtMarley

D'accord, j'avais lu que ça marchait en 8i aussi :aie:.
Merci.