Discussion :

[HurtMarley]

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 :

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
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 :

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

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

[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/

[HurtMarley]

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 :

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
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 :

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

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

[mnitu]

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

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
 
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;

[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

[mnitu]

...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.

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.

[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.

[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

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.

[HurtMarley]

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