Discussion :

[clement42]

Bonjour, je débute un peu en PL/SQL, et j'ai une question d'ordre générale sur les triggers (déclencheurs)
J'ai actuellement une base de données (bd1) contenant une table personne1 et j'aimerais faire un déclencheur, qui, a chaque insertion dans cette table, insert une ligne dans une table située dans une autre base de données (bd2).
est - il possible de faire un trigger qui lie ainsi deux base de données ? Si oui, pourriez vous me donner un exemple simple d'application ?

Merci d'avance

[pifor]

Je ne pense que c'est possible.

[jlucch]

En premier lieu, il faut créer un database link entre tes 2 instances ....

Le database link te permet de voir une table T2 sur une instance instance2 depuis l'instance : Instance1 :

Code :

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

1
2
3
4
5
 
exemple :
 
conn user/user@Instance1
select count(*) from T2@instance2;

Ensuite il te suffira de créer ton trigger sur ta table T1...

[jlucch]

C'est possible, et cela marche ... pas de soucis ..

[jlucch]

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
 
connect user2/XXX@instance2
drop database link INSTANCE1 ;
create database link INSTANCE1 CONNECT TO user1 identified by XXX;
 
conn user1/user1@instance1
create table T1 (n number);
 
conn user2/user2@instance2
create table T2 (n number);
desc user1.T1@instance1;
CREATE oR REPLACE TRIGGER trg_T2 before insert on T2
for each row
BEGIN
insert into user1.T1@instance values (:new.n);
when others then
   RAISE_APPLICATION_ERROR(-20000,'Erreur  :'||substr(sqlerrm,1,150));
 END;
/
 
insert  into user2.T2 values (1);
commit;
select count(*) from  user1.T1@instance1;

[shark84]

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
 
connect user2/XXX@instance2
drop database link INSTANCE1 ;
create database link INSTANCE1 CONNECT TO user1 identified by XXX;
 
conn user1/user1@instance1
create table T1 (n number);
 
conn user2/user2@instance2
create table T2 (n number);
desc user1.T1@instance1;
CREATE oR REPLACE TRIGGER trg_T2 before insert on T2
for each row
BEGIN
insert into user1.T1@instance values (:new.n);
when others then
   RAISE_APPLICATION_ERROR(-20000,'Erreur  :'||substr(sqlerrm,1,150));
 END;
/
 
insert  into user2.T2 values (1);
commit;
select count(*) from  user1.T1@instance1;

Bonjour,

ton script est très intéressent et j'essaye depuis tout à l'heure de travailler avec mais le problème je veux savoir user1 c'est quoi, instance 1 c'est quoi (est ce que c'est les tables ou autre ...)

Merci pour la réponse

[pifor]

Oui, probablement. Je pensais qu'il s'agissait d'un trigger sur une table de la base distante ...

[McM]

Je sais pas ce que ça peut donner sur une grosse appli, mais j'ai vu chez Asktom que les codes des triggers (bien que compilés) étaient reparsés à chaque exécution.
Il est conseillé de créer une procédure et d'appeler cette procédure dans le trigger.

Est-ce qu'un parse teste le dblink ? si oui, on peut avoir des perfs réduites.

[clement42]

bon en fait j'aimerais que la cible de ce lien soit une table d'une base de données mySQL. A nouveau, cela est il possible ? Comment faut il configurer le "database link" ?

Merci

[pifor]

Au risque de me tromper une seconde fois , là je pense que ce n'est pas possible. Les database links ne concernent que les bases Oracle et ne permettent donc pas de connecter des bases Oracle et des bases non-Oracle (à moins peut-être d'utiliser des produits intermédiaires ?).

[clement42]

justement c'est ce que je pensais, et j'essaie d'uiliser un driver odbc (MySQL Connector / ODBC 3.51). Je l'ai installer, et le test fait par l'outils d'administration windows me montre qu'il fonction. Mon problème maintenant est que je ne sais pas quoi mettre comme valeur à ce moment de création du datalink :
Connexion au schéma : c'est le nom de ma base mysql ?
Mot de passe : le mot de passe de mon user root mysql ?
port d'hote distant : 3306 ?
SID ou nom de service : alors là j'ai aucune idée ...

merci de votre aide

[clement42]

je me répond à moi seul pour faire un peu avancer le débat
Donc j'ai arrêter d'essayer de créer une database link via l'interface graphique d'oracle et me suis plongé dans du bon vieux SQL. Voilà mon script :

Je crée d'abord mon datalink en utilisant mon connecteur odbc, et ca fonctionne :

create database link myLink connect to "root2" identified by "root" using 'myodbc35';

Le problème est quand je test le link de la manière suivante j'obtiens l'erreur d'oracle écrite ensuite ...

select * from ma_table_cible@myLink;

ORA-12154: TNS : l'identificateur de connexion indiqué n'a pas pu être résolu

Quelqu'un sait il d'où peut venir le problème ?

Merci

[pifor]

La raison est la suivante:

the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

[clement42]

Bonjour
j'ai enfin réussi à faire communiquer oracle et mysql. Je peux donc à présent faire des requetes "select" à partir d'oracle sur une table de la base mysql. Cependant, je n'arrive pas à faire fonctionner le trigger que jlucch m'a donné précédemment. J'ai essayé de faire un trigger très simple :

Code :

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

1
2
3
4
5
6
7
8
 
CREATE OR REPLACE TRIGGER  "PERSONNE_SOURCE_T1" 
AFTER
insert on "PERSONNE_SOURCE"
for each row
begin
INSERT INTO personne@msql VALUES(:new.id, :new.nom);
end;

Cependant, lorsque j'essaie d'ajouter une ligne dans la table PERSONNE_SOURCE_T1, j'ai l'erreur suivante :

Code :

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

1
2
3
error ORA-02047: impossible de joindre la transaction distribuée en cours d'exécution
ORA-06512: à "SYSTEM.PERSONNE_SOURCE_T1", ligne 2
ORA-04088: erreur lors d'exécution du déclencheur 'SYSTEM.PERSONNE_SOURCE_T1'

Quelqu'un a une idée pour résoudre ce problème ?? merci

[dyvim]

Est ce qu'une autre solution ne pourrait pas être de créer une procédure stockée JAVA(ou C, C++) qui irait écrire dans la base MySQL, et d'appeler cette procédure stockée dans le trigger?

En tout cas, continue de nous dire ou tu en est clement42 car si tu arrives à trouver une solution qui fonctionne je suis curieux de la connaître.

Bon courage.

[jlucch]

tu peux essayer avec cela :

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
 
CREATE OR REPLACE TRIGGER  "PERSONNE_SOURCE_T1" 
AFTER
insert on "PERSONNE_SOURCE"
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
begin
INSERT INTO personne@msql VALUES(:new.id, :new.nom);
exception
 when others then
     dbms_output.put_line('Erreur dans TRIGGER PERSONNE_SOURCE_T1 :'||substr(sqlerrm,1,150));
     RAISE_APPLICATION_ERROR(-20000,'ECHEC dans triggerPERSONNE_SOURCE_T1 :'||substr(sqlerrm,1,150));
end;
/

==> Si cela marche, je t'invite à faire des recherches sur ces fameuses transactions autonomes ...
==> PS : N'oublie pas la gestion d'erreur dans tes triggers ...

[pifor]

J'ai quelques doutes sur l'utilisation d'une transaction indépendante dans un trigger: c'est possible mais il faut tenir compte des règles suivantes:

1. La transaction indépendante démarrée doit être terminée par COMMIT ou ROLLBACK avant le retour à la transaction en cours: sinon une exception est levée.
2. Une transaction indépendante ne peut voir aucune mise à jour de la transaction en cours.

L'erreur ORA-02047 pourrait être interprêtée en disant que MySQL ne supporte pas le COMMT à 2 phases avec une base Oracle, autrement dit, il ne serait pas possible de faire travailler les 2 bases ensemble dans une même transaction ? Il faudrait vérifier si cela est vraiment possible et si oui dans quelles conditions.

[jlucch]

Effectivement, j'ai oublié le commit ..

[clement42]

je copie colle la procédure qu'un collègue m'a donné et qui m'a permis de résoudre le problème :

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
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 QUICK START GUIDE: 
Windows NT - Generic Connectivity using ODBC
 
 
Purpose
-------
 
This document describes the steps to be performed when configuring Generic 
Connectivity on Windows NT. It is not intended to replace the Oracle8i 
Administrators Guide. Please refer to the Oracle8i Administrators Guide for 
more detailed information.
 
 
What Is Generic Connectivity?
-----------------------------
 
Generic connectivity is intended for low-end data integration solutions 
requiring the ad hoc query capability to connect from Oracle8i to non-Oracle 
database systems. Generic connectivity is enabled by Oracle Heterogeneous 
Services, allowing you to connect to non-Oracle systems with improved 
performance and throughput.
 
Generic connectivity is implemented as a Heterogeneous Services ODBC agent. An 
ODBC agent is included as part of your Oracle8i system. Be sure to use the 
agent shipped with your particular Oracle system and installed in the same 
$ORACLE_HOME. 
 
 
Generic Connectivity Architecture
---------------------------------
 
To access the non-Oracle data store using generic connectivity, the agent works
 
with an ODBC driver. Oracle8i provides support for the ODBC driver interface. 
The driver that you use must be on the same machine as the agent. The non-Oracle
 
data stores can reside on the same machine as Oracle8i or a different machine. 
 
 
Installation Steps
------------------
 
1) Configure an ODBC system data source for the target datastore. Use an ODBC 
   client application or ODBC test to verify connectivity to the target 
   datastore.
 
 
2) Make sure the following entries are in the tnsnames.ora and listener.ora.
 
TNSNAMES.ORA
 
hsmsql =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(host=winhost)(port=1521))
    )
    (CONNECT_DATA =
      (SID = msql)      <== needs to match the sid in listener.ora.
    )
  (HS=OK)               <== HS clause goes in the description.
  )
 
 
LISTENER.ORA
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = winhost)(PORT = 1521))
      )
    )
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = msql)          <== needs to match the sid in tnsnames.ora
      (ORACLE_HOME = E:\Ora816)
      (PROGRAM = hsodbc)         <== hsodbc is the executable       
    )
  )
 
 
4) Start the listener. 
   Run "lsnrctl services" to verify that you now have a service handler for the
 
   hsmsql sid.
   ==============================================================
   LSNRCTL> services
   Connecting to (address=(protocol=tcp)(host=winhost)(port=1521))
   Services Summary...
     msql          has 1 service handler(s)     
       DEDICATED SERVER established:0 refused:0
         LOCAL SERVER
   The command completed successfully
   ==============================================================
 
 
5) Create the Initialization file.
 
   You must create and customize an initialization file for your generic 
   connectivity agent. Oracle supplies a sample initialization file named 
   "inithsodbc.ora" which is stored in the $ORACLE_HOME\hs\admin directory.
 
   To create an initialization file, copy the appropriate sample file and rename
 
   the file to initHS_SID.ora. In this example the sid noted in the listener and
 
   tnsnames is msql so our new initialization file is called initmsql.ora.
 
6) Make sure the following noted entries are in the initmsql.ora located in 
   $ORACLE_HOME/hs/admin:
 
INITMSQL.ORA
 
# HS init parameters
#
HS_FDS_CONNECT_INFO = msql            <= odbc data_source_name
HS_FDS_TRACE_LEVEL = 0               <= trace levels 0 - 4  (4 is verbose)
HS_FDS_TRACE_FILE_NAME = hsmsql.trc  <= trace file name
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>
 
 
7) Create a database link to access the target database. Be sure to use the 
   appropriate quotes as noted below.
 
SQL> create database link msql
SQL> connect to "user" identified by "password" <= user/pwd on the target DB
SQL> using  'hsmsql';
 
8) To test, run a simple query of a known table on the target datastore.
 
SQL> select * from employee@msql;
 
     empid firstname       lastname        department job
---------- --------------- --------------- ---------- ---
     10000 Jerry           Rice            Networking CDW
     10001 John            Lund            Sales      WNV
     10002 Julie           Reynolds        Relations  NPI
     10003 Peter           Loren           Relations  NPO
     10004 Eric            Michael         Telemarket PHN
     10005 Ian             Alexander       Sales      WND
     10006 Ed              Smith           Networking CDG
     10007 Mike            Seibt           Coordinate IDW
 
11 rows selected.

j'ai fais ce ceci il ya 15 jours maintenant, et je ne sais plus exactement, mais il y a une histoire de services à ne pas oublier de redémarrer

Voilà, en tout cas, c'est résolvable

[johnkro]

Bonjour tout le monde,

Je pense avoir le même genre de problèmes que clément a eu, mais je ne connais rien au trigger. En fait je dois copier quotidiennement des données d'une base Oracle distante dans ma base locale MySQL. Mon problème est qu'il y a beaucoup de données à copier (4.000 lignes environ par jour), j'arrive à en copier 2.000 de la façon suivante (2.000 sur 4.000 car le temps d'exécution maximal est dépassé, je ne peux pas le modifier, car je n'ai pas les droits, et de plus, j'aimerais optimiser mon script) :

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
46
47
48
49
50
51
52
53
54
55
$c=ocilogon($user,$password,$database);
include ('date_format_julien.php');
$resultat=ociparse($c,"SELECT * FROM ops\$icb2g.HISTORIQUE1 WHERE DDATE='$jour_format_julien'");
ociexecute($resultat,OCI_DEFAULT);
include ('connexion_pems.ini');
$id_faute1=1;$id_faute2=1;$id_faute3=1;$id_faute4=1;
 
while (ocifetch($resultat)){
echo $i;echo '<br>';
	$DDATE=ociresult($resultat,1);
	$SYSCAA=ociresult($resultat,2);
	$GPS=ociresult($resultat,3);
	$SGTQS=ociresult($resultat,4);
	$NUMES=ociresult($resultat,5);
	$NBR=ociresult($resultat,6);
 
	//On interroge la base locale MySQL locale pour ne sélectionner  
	$requete_seuil = "SELECT * FROM seuil WHERE Mess='$NUMES' AND Priorite!=''";
	$resultat_seuil = mysql_query($requete_seuil) or die ("requete incorrecte au niveau de la recuperation de donnees a partir de la table seuil");
	$row_seuil = mysql_fetch_array($resultat_seuil);
	$Seuil=$row_seuil['Seuil'];
	$Priorite=$row_seuil['Priorite'];	
	$Libelle=$row_seuil['Libelle'];
	$FicheAQF=$row_seuil['FicheAQF'];
	$Position=$row_seuil['Position'];
	$Domaine=$row_seuil['Domaine'];
	//Comparaison du seuil entre la table Historique et la table Seuil
	if ($NBR > $Seuil){		
		//Mise a jour des donnees dans les tables volum_faute1,2,3,4
		if ($Priorite=='Faute1' OR $Priorite=='faute1') {				
			$sql = "INSERT INTO volum_faute1 (ID,Messages,NB,SGTQS,Seuil,Domaine,Priorite,Libelle,FicheAQF,Position) 
				VALUES ('$id_faute1','$NUMES','$NBR','$SGTQS','$Seuil','$Domaine','$Priorite','$Libelle','$FicheAQF','$Position')";
			$res = mysql_query($sql) or die ("requete incorrecte au niveau de l'ajout de donnees dans volum_faute1");
			$id_faute1=$id_faute1+1;
		}
		if ($Priorite=='Faute2' OR $Priorite=='faute2') {
			$sql = "INSERT INTO volum_faute2 (ID,Messages,NB,SGTQS,Seuil,Domaine,Priorite,Libelle,FicheAQF,Position) 
				VALUES ('$id_faute2','$NUMES','$NBR','$SGTQS','$Seuil','$Domaine','$Priorite','$Libelle','$FicheAQF','$Position')";
			$res = mysql_query($sql) or die ("requete incorrecte au niveau de l'ajout de donnees dans volum_faute2");
			$id_faute2=$id_faute2+1;
		}
		if ($Priorite=='Faute3' OR $Priorite=='faute3') {
			$sql = "INSERT INTO volum_faute3 (ID,Messages,NB,SGTQS,Seuil,Domaine,Priorite,Libelle,FicheAQF,Position) 
				VALUES ('$id_faute3','$NUMES','$NBR','$SGTQS','$Seuil','$Domaine','$Priorite','$Libelle','$FicheAQF','$Position')";
			$res = mysql_query($sql) or die ("requete incorrecte au niveau de l'ajout de donnees dans volum_faute3");
			$id_faute3=$id_faute3+1;
		}
		if ($Priorite=='Faute4' OR $Priorite=='faute4') {
			$sql = "INSERT INTO volum_faute4 (ID,Messages,NB,SGTQS,Seuil,Domaine,Priorite,Libelle,FicheAQF,Position) 
				VALUES ('$id_faute4','$NUMES','$NBR','$SGTQS','$Seuil','$Domaine','$Priorite','$Libelle','$FicheAQF','$Position')";
			$res = mysql_query($sql) or die ("requete incorrecte au niveau de l'ajout de donnees dans volum_faute4");
			$id_faute4=$id_faute4+1;
		}
	}
}

Ensuite, j'ai essayé de faire une jointure entre mes 2 tables pour faire diminuer ce temps d'exécution. Celle-ci me permettrait de faire un tri données important et donc faire moins de tour de boucle, mais je n'arrive pas à la mettre en oeuvre. Si j'y arrivais, cela pourrait suffir à passer en dessous des 30 secondes, mais cela est-il possible?

Code :

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

1
2
3
4
5
$c=ocilogon($user,$password,$database);
include ('date_format_julien.php');
include ('connexion_pems.ini');
$resultat=ociparse($c,"SELECT * FROM ops\$icb2g.HISTORIQUE1 INNER JOIN seuil ON ops\$icb2g.HISTORIQUE1.NUMES=seuil.Mess WHERE DDATE='$jour_format_julien'");
ociexecute($resultat,OCI_DEFAULT);

La jointure est-elle possible entre mes 2 tables Oracle et MySQL? Si oui, vaut-il mieux faire une jointure ou serait-il bien d'utiliser les triggers? S'il vaut mieux utiliser les triggers, comment pourrais-je le mettre en oeuvre? Il faudrait que dans une première partie je sélectionne et dans une seconde j'inserts?

La jointure serait beaucoup plus facile à mettre en oeuvre pour moi, donc s'il n'y a pas une grosse différence de performance entre ces 2 solutions, une aide sur les jointures pourrait me suffir...

Merci d'avance pour vos aides