Discussion :

[crob37]

Bonjour

Je dois faire une très petite requête SQL sur les vues ALL_TAB_COLS et DBA_TABLES dont voici la traduction en Français.

Dans ma base de données j'ai un schéma SAPSR3.
Dans ce schéma il y a 85000 tables environ.

Avec ça je sais qu'il y a 64000 tables qui ont une certaine colonne 'MANDT' :

Code :

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

1
2
3
 
 
select count(*) from all_tab_cols where owner = 'SAPSR3' and column_name = 'MANDT' ;

Je souhaite maintenant savoir combien de tables et le nom des tables ayant pour valeur 'MANDT=120'.

Ca doit être simple mais je suis rouillé en SQL !!
Merci.

[dell68]

Alors pour ta requête, je n'ai pas pu trouver, c'est un peu délicat pour la colonne MANDT ayant
une valeur 120, Mais je t'envoi un bloc PL/SQL, je l'ai testé sur un schema et çà marche :

sous sys

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
 
set  serveroutput on 
 
declare 
   x  number := 0;  
   cursor c_cur is 
   select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT'  ;
   lignes  c_cur%rowtype ;         
begin 
   open c_cur;   
loop 
      fetch  c_cur into  lignes; 
      exit  when c_cur%notfound ; 
      execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT=120'  
      into x ;
      if  x > 0  then   dbms_output.put_line(lignes.table_name) ;
      end if; 
end loop;
     close c_cur ;
end;
/

j'éspère que ça marche sur ta base ?

[crob37]

Salut Dell68

Je te remercie de ta réponse. Je viens de tester le code ; ça me renvoie une erreur :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
VTBPRODT                                                                                                                                                                                                       
VSCAN_PROF_PARGL                                                                                                                                                                                               
VSCAN_PROF_MIME                                                                                                                                                                                                
VSCAN_PROFT                                                                                                                                                                                                    
VSCAN_PROF                                                                                                                                                                                                     
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 12

Les objets commençant par la lettre "V..." sont bien des tables mais il semble qu'il rencontre un problème de conversion de type NUMERIC / ALPHA ?
J'ai des tables particulières commençant par le caractère "/" (exemple : /BEV3/CHVSALEI nomenclature interne de SAP pour je ne sais quelle raison !!) mais je ne sais pas si cela vient de ça, je n'en trouve pas dans le log de sortie en tout cas.

La quantité de tables remontées est de 1% de la totalité des tables, avant que n'apparaisse cette erreur.

[mnitu]

Quel est le type de la colonne MANDT ?

[crob37]

le TYPE de la colonne MANDT est VARCHAR2

j'ai essayé de remplacer dans le bloc

Code :

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

  execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT=120'

par

Code :

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

1
2
 
execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT="120" '

mais ORA-01756: quoted string not properly terminated

[mnitu]

Alors c'est normale. Vous devez doubler les cotes non pas remplacer par des guillemets

Code :

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

1
2
 
where  MANDT=''120'''

[mnitu]

Voila une autre solution sans PL (suppose existence de la table emp)

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
 
Select owner,
       table_name,
       to_number(
       extractvalue(
          xmltype(
             dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name||' where ename like ''A%'''))
        ,'/ROWSET/ROW/C')) count
  From All_tables t
 Where Exists (Select Null 
                From all_tab_cols c 
               Where c.owner      = t.owner
                 And c.table_name = t.table_name 
                 And column_name = 'ENAME')   
Order By owner, table_name

[crob37]

@mnitu : heu ... est-ce le bon code sql ?

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
40
41
42
 
SAPSR3                         AVORRAT                               0
SAPSR3                         CACS00_B_VAR_ACT                      0
SAPSR3                         CACS00_B_VAR_BDL                      0
SAPSR3                         CACS00_B_VAR_CAS                      0
SAPSR3                         CACS00_B_VAR_INV                      0
SAPSR3                         CACS00_B_VAR_LIN                      0
SAPSR3                         CACS00_B_VAR_OBJ                      0
SAPSR3                         CACS00_B_VAR_PAR                      0
SAPSR3                         CACS00_B_VAR_REL                      0
SAPSR3                         CACS_B_VAR                            0
SAPSR3                         COVAD                                 0
SAPSR3                         COVAL                                 0
SAPSR3                         HRP5581                               0
SAPSR3                         ISSRPREFLOW                           0
SAPSR3                         ISSR_RPI_MFT                          0
SAPSR3                         ISSR_RPI_MFT_BCK                      0
SAPSR3                         J_3RSEXTRACTD                         0
SAPSR3                         J_3RSEXTRACTH                         0
SAPSR3                         LTAP                                  0
SAPSR3                         OBJTYPEGROUPS                         0
SAPSR3                         PA0001                                0
SAPSR3                         PA0412                                0
SAPSR3                         PB0001                                0
SAPSR3                         QASH                                  0
SAPSR3                         QAST                                  0
SAPSR3                         SVRS_VCNT_HEAD                        0
SAPSR3                         T5A1L                              3328
SAPSR3                         T5A1M                              3292
SAPSR3                         T5A1N                              1904
SAPSR3                         T5VT0                                 0
SAPSR3                         T5W9S                                 0
SAPSR3                         T7EHS00_EXAMT                         0
SAPSR3                         T7IE6                                 0
SAPSR3                         T7IE9                                 0
SAPSR3                         TISSR_CHA_MDATA                       0
SAPSR3                         TISSR_LT_HEADER                       0
SAPSR3                         VARID                               719
SAPSR3                         VARID_CI                              0
SAPSR3                         VERSOBJ_ALIAS                         0
SAPSR3                         WVOBJECTTYPES                         0
SYS@DEV _SQL >

[dell68]

J'ai éssayé le code PL/SQL, en ajoutant double quotes, sur un exemple schema HR,
avec une colonne HR.EMPLOYEES.JOB_ID de type VARCHAR2, et ça fonctionne !

[crob37]

Désolé je sais que ton programme a l'air de marcher mais je ne comprends pas :

J'ai collé ça dans un fichier t.sql pour le lancer directement car en ligne de commande ça me fait toujours la même erreur.

Fichier t.sql :

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
 
set  serveroutput on
declare
   x  number := 0;
   cursor c_cur is
   select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT'  ;
   lignes  c_cur%rowtype ;
begin
   open c_cur;
loop
      fetch  c_cur into  lignes;
      exit  when c_cur%notfound ;
      execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT='''120''
      into x ;
      if  x > 0  then   dbms_output.put_line(lignes.table_name) ;
      end if;
end loop;
     close c_cur ;
end;
/

Code :

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

1
2
3
4
5
6
7
 
SYS@DEV _SQL >@t
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 12

si je ne mets plus que 2 cotes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SYS@DEV _SQL >@t
      execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT='120''
                                                                                             *
ERROR at line 12:
ORA-06550: line 12, column 94:
PLS-00103: Encountered the symbol "120" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
The symbol "* was inserted before "120" to continue.

[mnitu]

Ce n'est pas la peine de faire n'importe quoi il suffit de respecter ce qu'on vous dit sans y ajouter la touche perso.

Ma proposition était

Code :

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

1
2
 
where  MANDT=''120'''

que vous avez transformé en

Code :

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

1
2
 
where  MANDT='''120''

[crob37]

Non mais là ça fait strictement pareil ...

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
 
SYS@DEV _SQL >@t
SYS@DEV _SQL >set  serveroutput on
SYS@DEV _SQL >declare
  2     x  number := 0;
  3     cursor c_cur is
  4     select table_name from all_tab_cols
  5              where owner='SAPSR3' and  column_name='MANDT'  ;
  6     lignes  c_cur%rowtype ;
  7  begin
  8     open c_cur;
  9  loop
 10        fetch  c_cur into  lignes;
 11        exit  when c_cur%notfound ;
 12        execute immediate  'select count(*) from SAPSR3.'||lignes.table_name|| ' where  MANDT=''120'''
 13        into x ;
 14        if  x > 0  then   dbms_output.put_line(lignes.table_name) ;
 15        end if;
 16  end loop;
 17       close c_cur ;
 18  end;
 19  /
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 12
 
 
Elapsed: 00:00:00.26

[mnitu]

Modifiez votre code pour afficher le nom de la table avant de l'envoyer dans execute immediate.

[dell68]

En définitif, éssay le code suivant sans le mettre dans un fichier, comme il n'est pas long :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
declare
   x  number := 0;
begin
   for i in (select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT' ) 
loop
      execute immediate  'select count(*) from   SAPSR3.'||i.table_name|| '  where   MANDT=''120'''
      into x ;
      if  x > 0  then   dbms_output.put_line(i.table_name) ;
      end if;
end loop;
end;
/

[crob37]

Merci de l'obstination ; comme ça fait toujours pareil je me demande si il n'y a pas un affichage parasite :

Pour faire strict, je me connecte en SQLPLUS as sysdba et je ne fais aucune commande de formatage, style "set head off" ou "set line 999" ou ...
on est d'accord ?

je te donne le résultat de la requête SQL en amont dans ton code :

Code :

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

1
2
3
 
select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT'

REPONSE :

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
 
                        <----   ligne vide !!! >>
PDVER_BW_V       <----  table normale
/SAPCEM/VS1     <----  table spéciale
BIW_0PM_MAINTP_T
U_27645
U_30132
EHSWAV_APPBP3
V_VIWB21HI
JKSDVZSADR
V_OUTLA
M_MJJ1A
/ISDFPS/V_LMFLAT
 
64244 rows selected.

Pour rester lisible dans le même message, l'erreur est

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
 
SYS@DEV _SQL >declare
   x  number := 0;
begin
   for i in (select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT' )
loop
      execute immediate  'select count(*) from   SAPSR3.'||i.table_name|| '  where   MANDT=''120'''
      into x ;
      if  x > 0  then   dbms_output.put_line(i.table_name) ;
      end if;
end loop;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13
declare
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 7

[dell68]

1) - Quand tu isoles le code de la requête du bloc PL/SQL, ton affichage des tables commence par
la première ligne vide, peut-être que l'erreur ora-00903 du moteur SQL vient de cette
première ligne qui indique le nom de la table invalide (qui est chaîne vide)


2) - Tu as les nom spéciaux sur qques tables, peut-être que c'est ces tables qui renvoi cette erreur


Réssaye le code suivant, tu as auras normalement que les tables normales qui s'afficheront et
non les tables spéciaux, et aussi si l'applicatif le permet tu peux renommer ces tables

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
 
 
declare
   x  number := 0;
begin
   for i in (select table_name from all_tab_cols
            where owner='SAPSR3' and  column_name='MANDT' and 
             table_name is not null and table_name not like '/%' )
loop
      execute immediate  'select count(*) from   SAPSR3.'||i.table_name|| '  where   MANDT=''120'''
      into x ;
      if  x > 0  then   dbms_output.put_line(i.table_name) ;
      end if;
end loop;
end;
/

Et pour finir, quelle l'utilité de cette requête ???

[mnitu]

Filtrez les noms spéciaux des tables si cela convient sinon utilisez les guillemets autour du nom de la table.