Générer SQL dynam. dans proc. stocké

Version imprimable

Bonjour,

Je dispose d'une table AUDIT reprenant pour toutes les transactions d'insert/update une trace si l'opération est un succès.

La table AUDIT est composée des champs suivants :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 - TABLE : table impactée par la transaction - TYPE TRANSACTION : opération insert, update (et delete à terme) - DATE TRANSACTION : date de l’opération - CLEPRIM1 : nom de la clé primaire 1 - CLEPRIM2 : nom de la clé primaire 2 (null allowed) - CLEPRIM3 : nom de la clé primaire 3 (null allowed) - CLEPRIM4 : nom de la clé primaire 4(null allowed) - CLEPRIM5 : nom de la clé primaire 5(null allowed) - CLEPRIM6 : nom de la clé primaire 6(null allowed) - CLEPRIM7 : nom de la clé primaire 7(null allowed) - VAL1 : valeur de la clé primaire 1 - VAL2 : valeur de la clé primaire 2(null allowed) - VAL3 : valeur de la clé primaire 3(null allowed) - VAL4 : valeur de la clé primaire 4(null allowed) - VAL5 : valeur de la clé primaire 5(null allowed) - VAL6 : valeur de la clé primaire 6(null allowed) - VAL7 : valeur de la clé primaire 7(null allowed)
Mon objectif serait de créer une procédure stockée avec en paramètre IN, le nom d'une table (table X) et le type de transaction (update).

Dans ce cas, je souhaite que la procédure renvoie grâce aux valeurs de la table AUDIT le résultat du query suivant :
Code:

1 2 SELECT * FROM X where CLEPRIM1&CLEPRIM2 in (....)
Étant novice sous Oracle, je voulais vous demander si vous pouviez m'aider à coder la partie de procédure qui construirait le SQL présenté ci-dessus en fonction des valeurs de la table AUDIT

N'hésitez pas à critiquer si vous voyez d'autres solutions permettant de réaliser le même type de travail.

D'avance, je vous remercie pour toutes réponses éventuelles.

15/04/2009, 20h52
fatsora

Bonsoir,

Quand tu peux faire du SQL , fais-le en SQL .....

regarde ici comment activer AUDIT et FINE_GRAINED AUDITING

http://download.oracle.com/docs/cd/B...t.htm#i1010251

Quelle est ta version ORACLE ?
15/04/2009, 21h57
edefius

Salut,

Je te remercie pour ton lien, il m'apportera de l'information supplémentaire sur les techniques d'audit directement intégrées dans Oracle que j'ai déjà étudiée.
Mon employeur risque de refuser l'emploie de cette technique ainsi que l'emploie de logminer et préfèrera sans doute utiliser des "trigger" pour générer la table d'AUDIT citée précédemment.
Je vais proposer les 3 méthodes en appuyant la solution d'oracle et la table aud$

Ma question reste ouverte, je compléterai le poste si je passe à l'implémentation pour éventuellement faire appel à vos compétences.

Edefius
15/04/2009, 22h17
fatsora

Autre chose ,
si tu peux eviter les triggers .... (Rudiard Kipling !!)

Si ta version Oracle >= 9I alors fined_grained_auditing tu utiliseras ...
a condition d'avoir Oracle Entreprise Edition

Il ne necesite pas un redemarrage de la base
Plus souple, plus d'info que aud$
16/04/2009, 00h34
rvfranck
Salut,
Je n'ai pas bien compris ce que tu veux faire, surtout quand tu as mis ce code:
Code:

1 2 SELECT * FROM X where CLEPRIM1&CLEPRIM2 in (....)
Mais quand vous aurez decidé de la methode que vous utiliserez, laissez nous savoir.
16/04/2009, 09h50
edefius

Bonjour,

Peut être que j'aurais du préciser dès le début mais l'idée finale, est de permettre l'alimentation INCREMENTAL d'un Data Warehouse.

Code:

SELECT * FROM X where CLEPRIM1&CLEPRIM2 in (....)

La table X est en réalité une source du Data Warehouse mais on ne veut récupérer que les mises à jour ou les inserts.
Sachant que la table X ne contient aucun "Timestamp" d'insertion ou de modification.

J'espère que c'est plus clair et que ça va vous donner plein d'idées géniales.

D'avance merci
16/04/2009, 10h06
fatsora

Bonjour,

il y a les vues materialisées pour ca !

voir dans les docs google et tahiti.oracle.com
16/04/2009, 11h45
edefius

Bonjour et merci,

Je viens d'étudier rapidement ce concept et il me paraît des plus intéressants!
Je compte le proposer et le décrire à mon responsable mais avant cela je me pose certaines questions pour être sûre que l'on peut couvrir tous les besoins nécessaires à l'alimentation du DWH.

J'ai compris qu'il s'agissait d'une vue stockée physiquement telle une table, pouvant être indexée,... et rafraichie par le DBMs en synchrone on commit, en asynchrone selon la fréquence désirées,...

Ce qui n'est pas encore clair pour moi :

Toujours dans l'optique de développer une solution de chargement incrémental d'un DWH.
Disons que nous sommes en mode synchrone on commit, la vue matérialisée peut elle faire l'objet de delete afin de ne refléter que les dernières transaction "commités" ?
Ou simplement peut elle intégrer les "timestamp" on insert/on update qui n'existe pas dans la table initial?

Autre point, la gestion des delete dans le DWH est logique, est il possible à l'aide de VM de refléter/détecter les delete physiques dans la table initiale?

Je vous remercie encore pour vos aides précieuses.

Edefius
16/04/2009, 11h55
fatsora

Il y a des restrictions au rafraichissement "ON COMMIT" contrairement a "ON DEMANDE"

Sinon les mises a jour c'est Oracle qui va le faire ...

http://www.sqlsnippets.com/en/topic-12894.html
17/04/2009, 17h36
edefius

Salut Fatsora,

Quand tu dis :

Code:

il y a les vues materialisées pour ca !

J'ai un peu étudié la plupart des concepts de refresh associés (synchrone, asynchrone, complete, fast, force,..)

Mais avec tout ça je ne vois pas comment implémenter ce concept pour répondre à mon besoin. C'est à dire utilisée une vue matérialisée qui me permet de détecter les update et insert (oublions la gestion des delete pour le moment) sur une table source d'un DWH.

Peux tu éclairer ma lanterne sur le concept à mettre en oeuvre?

D'avance merci

Mais as tu lus les doc sur tahiti.oracle.com par exemple ?

sinon Si tu vas sur du "ON COMMIT" par exemple c'est oracle qui va gerer les mises a jour ...

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
> create table t1( key number, val varchar2(2));
 
Table created.
 
> insert into t1
  2  select 1,'a' from dual union
  3  select 2,'b' from dual union
  4  select 3,'C' from dual union
  5  select 4,'d' from dual union
  6  select 5,null from dual;
 
5 rows created.
 
> commit;
 
Commit complete.
 
 
 
> alter table t1 add constraint pk_key primary key (key) using index;
 
Table altered.
 
 
 
 
> create materialized view log on t1 ;
 
Materialized view log created.
 
> create materialized view mv
  2    REFRESH FAST ON COMMIT
  3    as select * from t1
  4  ;
  as select * from t1
> /
 
Materialized view created.
 
 
 
 
> insert into t1 values ( 6, 'e' );
 
1 row created.
 
> select rowid, key, val from mv ;
 
ROWID                     KEY VA
------------------ ---------- --
AAARyOAAEAAAP10AAA          1 a
AAARyOAAEAAAP10AAB          2 b
AAARyOAAEAAAP10AAC          3 C
AAARyOAAEAAAP10AAD          4 d
AAARyOAAEAAAP10AAE          5
 
> commit;
 
Commit complete.
 
> select rowid, key, val from mv ;
 
ROWID                     KEY VA
------------------ ---------- --
AAARyOAAEAAAP10AAA          1 a
AAARyOAAEAAAP10AAB          2 b
AAARyOAAEAAAP10AAC          3 C
AAARyOAAEAAAP10AAD          4 d
AAARyOAAEAAAP10AAE          5
AAARyOAAEAAAP14AAA          6 e
 
6 rows selected.
 
 
 
> delete from t1  where key=5;
 
1 row deleted.
 
> commit;
 
Commit complete.
 
> select rowid, key, val from mv ;
 
ROWID                     KEY VA
------------------ ---------- --
AAARyOAAEAAAP10AAA          1 a
AAARyOAAEAAAP10AAB          2 b
AAARyOAAEAAAP10AAC          3 C
AAARyOAAEAAAP10AAD          4 d
AAARyOAAEAAAP14AAA          6 e
 
>

17/04/2009, 19h01
edefius

:salut:
Je suis désolé mais ta réponse ne m'aide pas et ne m'apporte rien de neuf sans vouloir t'offenser.

Pour rappel mon objectif est : alimenter un DWH à partir de flux ETL (extract transform load) réalisés avec les technologies Business Object. Dans ce cadre, je dois réaliser des flux incrémentaux pour ne traiter qu'une partie des données, uniquement celle qui sont nouvelles, modifiées ou supprimées.
L'etl fonctionne à base de query SQL ou de procédure stockée et je cherche le meilleur moyen de générer ces requêtes à partir d'Oracle

Tout de même un grand merci pour ton attention, et aux autres désolé pour la dérive de ce poste
17/04/2009, 20h18
Waldar

Citation:

Pour rappel mon objectif est : alimenter un DWH à partir de flux ETL

Ce n'est pas très honnête de dire qu'il s'agit d'un rappel, c'est la première fois que vous parlez de Data Integrator.

Pour faire de l'incrémental il faut que ce soit gérer à la source. Si vous avez la main dessus vous pouvez construire une VM, et vous pouvez même piloter son rafraîchissement depuis votre ETL.

Si vous n'avez pas la main sur les sources, pas d'incrémental possible s'il n'y a pas de date fiable, et/ou si les suppressions sont physiques.
17/04/2009, 23h05
edefius

Veuillez accepter mes excuses pour l'emploi de ce terme déplacé. Je n'ai pas l'habitude d'utiliser des forums, je ne me suis pas rendu compte de suite qu'en parlant de DWH, il était important de précisez que l'alimentation se faisait par un ETL autre qu'oracle.
18/04/2009, 11h04
fatsora

Citation:

Envoyé par edefius

Veuillez accepter mes excuses pour l'emploi de ce terme déplacé. Je n'ai pas l'habitude d'utiliser des forums, je ne me suis pas rendu compte de suite qu'en parlant de DWH, il était important de précisez que l'alimentation se faisait par un ETL autre qu'oracle.

Je prends ca sur l'inexperience , car effectivement c'est precisé nul part avant que c'est B.O , et pour l'instant il n'ya pas de rubrique B.O

Ceci dit, B.O n'est pas le seul acteur dans ce domaine .... et n'est ni le plus prestigieux ni le leader dans son domaine ...

Mais ceci est une autre histoire.

Tout d'abord, merci à tous pour toutes l'informations fournie!

Cela m'a permit d'imaginer un mécanisme supplémentaire pour "sourcer" un DWH en mode incrémental. J'explique directement par l'exemple :

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
31
32
33
--1--Creation du log pour la vue matérialisée rafraichie en FAST obligatoire
CREATE MATERIALIZED VIEW LOG ON account.STRUCTURE_SOCIETE;
-- oracle va créer une nouvelle table préfixé par MLOG$_ :  account.MLOG$_STRUCTURE_SOCIETE. Cette table reprend la clé primaire et trace toutes les opérations (insert/update/delete) 
 
--2--Création vue matérialisée en mode Fast refresh ON DEMAND
CREATE MATERIALIZED VIEW account.MV_TEST REFRESH FAST
AS SELECT * FROM account.STRUCTURE_SOCIETE
 
--3--Test de la vue matérialisée précédemment créée 
SELECT * FROM account.MV_TEST
------------------
--4--OPERATIONS d'insert/update/delete pour test sur la table maitre
 
UPDATE account.STRUCTURE_SOCIETE
SET nom='A DETRUIRE----'
WHERE societe=5 AND division=1
 
INSERT INTO account.STRUCTURE_SOCIETE(groupe, societe, division, nom)
VALUES (1,5,7,'Test 7')
 
INSERT INTO account.STRUCTURE_SOCIETE(groupe, societe, division, nom)
VALUES (1,5,8,'Test 8')
 
DELETE FROM account.STRUCTURE_SOCIETE
WHERE groupe=1 AND societe=5 AND division=7
------------------
--5--LOG de la VM 
--Détecte les insert/update/delete réalisés sur la table maitre depuis le dernier refreh de la VM
SELECT * FROM account.MLOG$_STRUCTURE_SOCIETE
------------------
--5—REFRESH DE LA VM 
EXECUTE DBMS_REFRESH.MAKE('groupeDWH','account.MV_TEST',NULL,NULL);
--LES INFORMATIONS DE LA TABLE DE LOG account.MLOG$_STRUCTURE_SOCIETE sont utilisées pour le refresh de la VM. Une fois l’opération terminée par Oracle, la table de log est automatiquement vidée.

L'idée est d'employer la table de log comme table source de l'ETL avant le refresh on demand de la vue matérialisées pour mettre à jour en conséquence le DWH.
Pouvez vous critiquer cette solution? Est ce robuste? Est ce du bricolage?

D'avance merci à tous.

20/04/2009, 11h39
Waldar

Si ça vous donne les données que vous attendez, que ça ne plombe pas les performances et qu'il n'y a pas de choses inutilement compliquées - ce qui a l'air d'être le cas - je dirai que c'est une bonne solution.
20/04/2009, 12h11
edefius

Merci.