Discussion :

[Loïck]

Bonjour à tous,
y a-t'il un moyen simple de rechercher une chaîne de caractère contenue dans une table que l'on ne connait pas (et bien sûr, ni le nom de la colonne)?
La seule chose que je connaisse est le nom du schéma ....
En fait, ca ressemblerait à rechercher comme dans windows à partir d'un repertoire le fichier contenant la chaîne de caractère "xxx".
Merci d'avance pour votre aide.

[kalyparker]

Bonjour,

y a-t'il un moyen simple de rechercher une chaîne de caractère contenue dans une table que l'on ne connait pas (et bien sûr, ni le nom de la colonne)?

Simple ? Non, du moins pas à ma connaissance...

Par contre une procedure pl devrait faire l'affaire... Mais ce genre de chose serait une catastrophe en terme de perf

[laurentschneider]

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
43
44
45
 
SQL> create or replace function search_in_table(
  2    owner varchar2,
  3    table_name varchar2,
  4    search_string varchar2)
  5  return varchar2 is
  6    stmt varchar2(4000);
  7    col varchar2(4000);
  8  begin
  9    stmt := 'select ';
 10    for c in (
 11      select column_name
 12      from all_tab_columns
 13      where all_tab_columns.table_name=search_in_table.table_name
 14            and all_tab_columns.owner=search_in_table.owner
 15            and data_type like '%CHAR%' )
 16    loop
 17      stmt := stmt || 'max(decode("' || c.column_name || '",''' ||
 18        search_string || ''',''' || c.column_name || ' ''))||';
 19    end loop;
 20    stmt:= stmt||'nvl2(count(*),null,null) from "'||
 21      owner||'"."'||table_name||'"';
 22    execute immediate stmt into col;
 23    return col;
 24  end;
 25  /
 
Function created.
 
SQL> 
SQL> show err
No errors.
SQL> 
SQL> 
SQL> select table_name,cols from (
  2  select rownum,
  3    table_name,
  4    search_in_table(owner,table_name,'KING') cols
  5    from all_tables where owner='SCOTT')
  6  where cols is not null;
TABLE_NAME COLS
---------- -----------------------------------------------------------
EMP        ENAME
T          C
T2         C1 C2

[Loïck]

Merci, cette fonction marche parfaitement.
Est-il possible de la modifier (et comment) pour permettre de rechercher non plus une chaine exacte mais une partie de son contenu, en faisant intervenir un LIKE ?
Merci d'avance.

[laurentschneider]

avec un instr()

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
 
CREATE OR REPLACE FUNCTION search_in_table(
    owner varchar2,
    table_name varchar2,
    search_string varchar2)
  RETURN varchar2 IS
    stmt varchar2(4000);
    col varchar2(4000);
  begin
    stmt := 'select ';
    FOR c IN (
      SELECT column_name
      FROM all_tab_columns
      WHERE all_tab_columns.table_name=search_in_table.table_name
            AND all_tab_columns.owner=search_in_table.owner
            AND data_type LIKE '%CHAR%' )
    loop
      stmt := stmt || 'max(decode(instr("' || c.column_name || '",''' ||
        search_string || '''),0,null,null,null,'''|| c.column_name || ' ''))||';
    end loop;
    stmt:= stmt||'nvl2(count(*),null,null) from "'||
      owner||'"."'||table_name||'"';
    execute immediate stmt INTO col;
    RETURN col;
  end;
/

[peopleunderthestairs]

J'ai créé une table de test:

Code :

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

1
2
3
4
5
6
7
SQL> select * from sonar.funk; 
 
        ID GROOVE 
---------- ------------------------------------------------------------ 
         2 getfunky 
         2 getdown 
         2 get¿groove

Je lance ensuite votre fonction après l'avoir compilée sans souci avec une recherche sur une chaîne de caractère complète :

Code :

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

1
2
3
4
5
6
7
SELECT table_name,cols 
FROM ( 
SELECT rownum, 
table_name, 
search_in_table('SONAR','FUNK','getfunky') cols 
FROM all_tables WHERE owner='SONAR') 
WHERE cols IS NOT NULL;

Et la surprise il me renvoie une cinquantaine de tables mais toujours avec le bon nom de colonne même si cette colonne n'existe pas dans les autres tables indiquées autre que la table funk:

TABLE_NAME                     COLS 
------------------------------ -------------------- 
SCHEMA_MIGRATIONS              GROOVE 
PROJECTS                       GROOVE 
SNAPSHOTS                      GROOVE 
METRICS                        GROOVE 
PROJECT_MEASURES_OLD           GROOVE 
RULES                          GROOVE 
RULES_PARAMETERS               GROOVE 
PROJECT_LINKS                  GROOVE 
USERS                          GROOVE 
RULES_PROFILES                 GROOVE 
ACTIVE_RULES                   GROOVE 
ACTIVE_RULE_PARAMETERS         GROOVE 
SNAPSHOT_SOURCES               GROOVE 
EVENTS                         GROOVE 
ALERTS                         GROOVE 
PROPERTIES                     GROOVE 
MEASURE_DATA                   GROOVE 
GROUPS                         GROOVE 
GROUPS_USERS                   GROOVE 
GROUP_ROLES                    GROOVE 
USER_ROLES                     GROOVE 
DEPENDENCIES                   GROOVE 
QUALITY_MODELS                 GROOVE 
CHARACTERISTICS                GROOVE 
CHARACTERISTIC_EDGES           GROOVE 
CHARACTERISTIC_PROPERTIES      GROOVE 
ACTIVE_DASHBOARDS              GROOVE 
DASHBOARDS                     GROOVE 
WIDGETS                        GROOVE 
WIDGET_PROPERTIES              GROOVE 
ACTIVE_RULE_CHANGES            GROOVE 
ACTIVE_RULE_PARAM_CHANGES      GROOVE 
MANUAL_MEASURES                GROOVE 
LOADED_TEMPLATES               GROOVE 
ACTION_PLANS                   GROOVE 
DOCUMENT                       GROOVE 
SNAPSHOT_DATA                  GROOVE 
DUPLICATIONS_INDEX             GROOVE 
ISSUES                         GROOVE 
ISSUE_CHANGES                  GROOVE 
PROJECT_MEASURES               GROOVE 
FUNK                           GROOVE 
ACTIVE_RULE_NOTES              GROOVE 
RULE_NOTES                     GROOVE 
AUTHORS                        GROOVE 
RESOURCE_INDEX                 GROOVE 
SEMAPHORES                     GROOVE 
MEASURE_FILTERS                GROOVE 
MEASURE_FILTER_FAVOURITES      GROOVE 
GRAPHS                         GROOVE 
NOTIFICATIONS                  GROOVE 

51 rows selected. 

Je refais la même chose avec votre seconde fonction, celle avec le like (c'est celle qui m'intéresse) et la même chose, même résultat (50 tables dont 49 non concernée) ; en fait je voudrais rechercher l'ensemble des colonnes ayant été impactées par l'insert du caractère spécial ¿ dans la base :

Pouvez vous svp m'aider ? J'utilise ORACLE version 11G. En vous remerciant d'avance.

[wahnfried]

Bonsoir à tous,

Pour avoir traité le cas une fois, j'ajouterais également le test sur une colonne de type 'CLOB'...