Discussion :

[jujunior]

Bonjour,

Dans le cadre dans stage, je travaille actuellement une BD Oracle. C'est la première fois et je ne suis vraiment pas familier avec les requêtes SQL.

Mon problème est le suivant : je voudrais créer un requête SQL qui à partir de la table ALL_CATALOG, qui contient entre autre le nom de toutes les tables et views de la BD, me retourne toutes les views commençant par "V$" et non vide. Je voudrais ensuite appeler celle-ci avec JDBC.

J'ai essayé, de manière très naïve je l'avoue, avec une requête de cette forme :

Code :

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

1
2
3
select TABLE_NAME from ALL_CATALOG
where TABLE_NAME like 'V$%' and
(select count(*) from TABLE_NAME where rownum = 1)<>0;

Malheureusement, le second select ne marche pas (cela aurait été trop facile...). J'ai lu sur divers forum que puisque le nom de la table où j'exécute ce second Select est variable, qu'il faut utiliser du SQL dynamique ou bien des curseurs, mais, encore une fois, étant un débutant en SQL, je n'ai pas réussi à créer cette requête.

Quelqu'un aurait il la moindre idée ou la moindre piste?

Merci d'avance,

Cordialement,

Julien

[Garuda]

Tu dois effectivement faire du SQL dynamique.
D'autre part , tu ne peux pas non plus faire cette requete en SQL pur (sauf erreur)
TU dois passer par un bloc PL/SQL anonyme :

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
 
/* Formatted on 09/09/2010 12:15:34 (QP5 v5.115.810.9015) */
DECLARE
    r NUMBER;
BEGIN
-- boucle sur toutes les tables et vues du catalogue
FOR C1 IN (SELECT TABLE_NAME FROM ALL_CATALOG
WHERE TABLE_NAME like 'V$%') LOOP
-- requte nombre de lignes
execute immediate 'SELECT count(*) FROM '||C1.table_name INTO r;
-- affichage
IF r>0 THEN
    dbms_output.put_line(C1.table_name);
END IF;
END LOOP;
END;

NB cette requete fait une sortie à l'écran : ne pas oublier d'activer DBMS_OUTPUT

ATTENTION :
Cette requete peut etre tres longue (depend du nombre de tables/vues et de la taille de cells-ci) :
il faut eviter d'interroger les tables SYSTEMES

Code :

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

1
2
 
select table_name from all_catalog where owner not in ('SYS','SYSTEM','SYSMAN')

ou

Code :

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

1
2
 
select table_name from all_catalog where owner =user

pour limiter aux objet de l'utilisateur connecté

[jujunior]

Merci !

J'ai un peu modifié le code parce que finalement j'utilise une autre table que ALL_CATALOG. Cela donne donc ceci :

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
 
DECLARE
    r NUMBER;
BEGIN
  FOR C1 IN (SELECT TABLE_NAME FROM MY_TABLE) LOOP
    BEGIN
      execute immediate 'SELECT count(*) FROM '||C1.table_name||' WHERE rownum=1' INTO r;
    EXCEPTION WHEN OTHERS THEN
      r := 0;
    END;
    IF r>0 THEN
      dbms_output.put_line(C1.table_name);
    END IF;
  END LOOP;
END;

J'ai aussi ajouté une exception pour le cas où une table de MY_TABLE n'existe pas.

Merci encore pour l'aide.

Julien

[Garuda]

De rien !
Mais peux_tu m'expliquer à quoi sert

Code :

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

1
2
 
WHERE rownum=1'

[jujunior]

Cela permet d’accélérer le count(*) . Cela s’arrête dès qu'une valeur est trouvée du coup ça t’évite de parcourir toute la table. Bien pratique quand tu en as certaines avec plus de 30000 lignes.

[Garuda]

Ah , d'accord !
Je vais tester ca, car, comme St Thomas, je ne crois que ce que je vois !

[Garuda]

En fait

Code :

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

1
2
 
Select count(*) from my_table where rownum=1

peremet de savoir si une table a au moins une ligne, mais pas le nombre de ligne => ruse de sioux !
Equivalent de "Exists" !

[jujunior]

J'avoue avoir trouvé cette solution sur un autre forum, et l'avoir moi aussi testé pour vérifier son efficacité

J'ai maintenant un autre problème, bien que toujours en relation avec l'original.
Prenons par exemple la vue V$ACTIVE_SESSION_HISTORY qui appartient à ma liste de tables contenues dans MY_TABLE. Lorsque je fais une simple requête SQL :

Code :

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

SELECT count(*) FROM V$ACTIVE_SESSION_HISTORY

j'obtiens comme résultat 7755. Mais, lorsque je lance cette procédure PL/SQL, cette vue est déclarée comme inexistante (erreur 942), mais, bizarrement, d'autres non, comme la vue V$ADVISOR_PROGRESS par exemple.

Je ne comprends pas pourquoi avec un requête SQL, la vue V$ACTIVE_SESSION_HISTORY existe, mais avec une procédure PL/SQL non...

Sais-tu d'où cela pourrait-il venir ?

[Garuda]

Pour être vu en PL/SQL par un user, les tables système (et les vues correspondantes) doit avoir été "grantée" directement et non pas au travers du role.

[jujunior]

Merci pour l'info !
Sais-tu s'il est possible de "granter" chacune de mes tables dans la boucle de ma procedure, ou si je dois déclarer un GRANT global en tout début ?

Je tiens à préciser que je souhaite que cette procédure ne puisse être appelée que par un utilisateur system.

Merci d'avance.