Purge de données par JOB

**mortimer.pw** · 03/05/2010, 12h39

Bonjour à tous,

Je travaille sur une BD 11g Entreprise Edition sous Windows XP.

J'ai écrit un petit package dont le but est de supprimer des enregistrements dans une table :

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
CREATE OR REPLACE PACKAGE Maint_pack AS
	PROCEDURE Spy(cTexte VARCHAR2);
	FUNCTION PurgeTable(cSchema VARCHAR2,cTable VARCHAR2,cChamp VARCHAR2,nNbJ NUMBER) RETURN NUMBER;
END Maint_pack;
/
 
CREATE OR REPLACE PACKAGE BODY Maint_pack AS
 
	PROCEDURE Spy(cTexte VARCHAR2) IS
		cFileSpy VARCHAR2(100) :='Maint_pack_'||TO_CHAR(SYSDATE,'DD')||'.txt';
		nFileSpy UTL_FILE.FILE_TYPE;
		cLineSpy VARCHAR2(400);
	BEGIN
		nFileSpy:=UTL_FILE.FOPEN('MAINT_DIR',cFileSpy,'A');
		cLineSpy:='DATE : '||TO_CHAR(SYSDATE,'DD/MM/YY HH24:MI:SS')||'  '||cTexte;
		UTL_FILE.PUT_LINE(nFileSpy,cLineSpy);
		UTL_FILE.FCLOSE(nFileSpy);
	END Spy;
 
	FUNCTION PurgeTable(cSchema VARCHAR2,cTable VARCHAR2,cChamp VARCHAR2,nNbJ NUMBER) RETURN NUMBER IS
		nPurgeTable NUMBER :=1;
		dFirstJour DATE;
		dLastJour DATE;
		cRequete VARCHAR2(5000);
	BEGIN
		cRequete:='SELECT MIN('||cChamp||') FROM '||cSchema||'.'||cTable;
		EXECUTE IMMEDIATE cRequete INTO dFirstJour;
		cRequete:='SELECT MAX('||cChamp||') FROM '||cSchema||'.'||cTable;
		EXECUTE IMMEDIATE cRequete INTO dLastJour;
		IF dLastJour - nNbJ > dFirstJour THEN
			dLastJour:=dLastJour-nNbJ;
			dFirstJour:=dFirstJour+15;
			IF dFirstJour < dLastJour THEN
				cRequete:='DELETE FROM '||cSchema||'.'||cTable||' WHERE '||cChamp||' < TO_DATE('||CHR(39)||dFirstJour||CHR(39)||')';
			ELSE
				cRequete:='DELETE FROM '||cSchema||'.'||cTable||' WHERE '||cChamp||' < TO_DATE('||CHR(39)||dLastJour||CHR(39)||')';
			END IF;
			Spy(cRequete);
			EXECUTE IMMEDIATE cRequete;
			COMMIT;
		END IF;
		RETURN nPurgeTable;
	EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			Spy(SQLERRM);
			nPurgeTable:=0;
			RETURN nPurgeTable;
	END PurgeTable;
 
END Maint_pack;
/

J'ai créé un Program :

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
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name=>'PURGE_TABLE',
program_action=>'TEST.Maint_pack.PurgeTable',
program_type=>'STORED_PROCEDURE',
number_of_arguments=>4,
comments=>'Supprime les enregistrements dans une table.');
END;
/
BEGIN
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name=>'PURGE_TABLE',
argument_position=>1,
argument_name=>'SCH',
argument_type=>'VARCHAR2',
default_value=>'TEST');
END;
/
BEGIN
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name=>'PURGE_TABLE',
argument_position=>2,
argument_name=>'TAB',
argument_type=>'VARCHAR2',
default_value=>'DUAL');
END;
/
BEGIN
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name=>'PURGE_TABLE',
argument_position=>3,
argument_name=>'CHP',
argument_type=>'VARCHAR2',
default_value=>'JOUR');
END;
/
BEGIN
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name=>'PURGE_TABLE',
argument_position=>4,
argument_name=>'NBJ',
argument_type=>'NUMBER',
default_value=>90);
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE('PURGE_TABLE');
END;
/

J'ai créé un Schedule :

Code :

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

1
2
3
4
5
6
7
8
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'DAILY_200000',
start_date => systimestamp at time zone 'Europe/Paris',
repeat_interval => 'FREQ=DAILY;BYHOUR=20;BYMINUTE=0;BYSECOND=0',
comments => 'Tous les jours a 20h00.');
END;
/

J'ai créé un job :

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
BEGIN
DBMS_SCHEDULER.CREATE_JOB( 
job_name => 'PURGE_LIGNE_CMD',
program_name => 'PURGE_TABLE',
schedule_name => 'DAILY_200000');
END;
/
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'PURGE_LIGNE_CMD',
argument_position => 1,
argument_value => 'TEST');
END;
/
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'PURGE_LIGNE_CMD',
argument_position => 2,
argument_value => 'LIGNE_CMD');
END;
/
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'PURGE_LIGNE_CMD',
argument_position => 3,
argument_value => 'JOUR');
END;
/
BEGIN
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name => 'PURGE_LIGNE_CMD',
argument_position => 4,
argument_value => 90);
END;
/
BEGIN
DBMS_SCHEDULER.ENABLE('PURGE_LIGNE_CMD');
END;
/

Le Job s'exécute bien mais j'ai, en retour, une erreur ORA-06576: ceci n'est pas un nom de fonction ou de procédure valide .

J'ai testé ma fonction sous SqlPlus :

Code :

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

1
2
SQL> var myVar number;
SQL> call Maint_pack.PurgeTable('TEST','QUANTITE','JOUR',90) into :myVar;

Comment faire la même chose dans l'appel du PROGRAM ?

Est-ce une mauvaise technique ?

Faut-il faire cela autrement ?

Merci pour vos réponses.

**McM** · 07/05/2010, 12h26

Je ne connais pas dbms_scheduler, mais appeler une fonction ça marche ?
Essaye de la mettre en procédure, c'est surement ce qui plante.

**mortimer.pw** · 12/05/2010, 13h48

Bonjour McM,

J'ai essayé en remplacant la Function par une Procedure.
Toujours le même problème.

Je crois que le problème vient de l'utilisation du :

Code :

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

DELETE FROM + Execute immediate

Merci pour ton aide.

En fait je cherche un moyen de faire du delete "programmé".

Si quelqu'un peu m'aider ? merci par avance.

**McM** · 12/05/2010, 17h14

Alors 4 pistes ... tu as du travail

1 : Essaye d'utiliser dbms_job

2 : Ta requête utilise du to_date sans format.. c'est pas très bon, essaye de formater tes dates.

3 : Un seul select suffira pour les min et max

Code :

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

1
2
cRequete:='SELECT MIN('||cChamp||'), max('|| cChamp ||') FROM '||cSchema||'.'||cTable;
		EXECUTE IMMEDIATE cRequete INTO dFirstJour, dLastJour;

4 : Vu que ça plante et que tu ne sais pas vraiment pourquoi, il te reste LA solution de développeur : Tu élimines tout code et tu rajoutes petit à petit jusqu'à trouver le point de blocage :
Etape 1 : Aucun code, juste un

Code :

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

NULL;

pour voir si la planification et l'exécution marchent
Puis tu dé-commentes les lignes petit à petit.

**McM** · 12/05/2010, 17h28

Envoyé par McM

2 : Ta requête utilise du to_date sans format.. c'est pas très bon, essaye de formater tes dates.

Pff.. je suis fatigué...

Ce que j'ai dit est vrai mais il est plus correct (et plus sur, et plus optimisé) de binder

Code :

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

cRequete:= 'DELETE FROM '||cSchema||'.'||cTable||' WHERE '||cChamp||' < :ladate' USING dFirstJour;

**mortimer.pw** · 14/05/2010, 08h39

Bonjour McM,

Tout d'abord merci pour ton support.

1) Je suis en 11g, le DBMS_JOB ne devient-il pas obsoléte ?

2) Ok pour le formatage des dates.

3) Magnifique si je peux avoir la réponse en une seule requête.

4) La planification marche puisqu'elle me retourne un ORA-06576.

Ma technique d'utiliser le scheduler pour exécuter une Fonction qui se trouve dans un Package est-elle bonne ?

Puisque pour l'exècuter sous SqlPlus, il faut faire :

Code :

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

1
2
SQL> var myVar number;
SQL> call Maint_pack.PurgeTable('TEST','QUANTITE','JOUR',90) INTO :myVar;

Comment faire cela dans la partie définition du "Program" de mon Job ? Est-ce possible ?
Faut-il écrire cela autrement dans le package ?

Merci encore pour ton aide.

**McM** · 14/05/2010, 11h12

Je suis en 9, donc je ne peux pas tester DBMS_SCHEDULER.

Bon, on va refaire un test complet : L'erreur ORA-06576 vient d'un appel de fonction incorrect

ORA-06576: not a valid function or procedure name
Cause: Could not find a function (if an INTO clause was present) or a procedure (if the statement did not have an INTO clause) to call.

Action: Change the statement to invoke a function or procedure

Donc premier changement, passer par une procédure, sans retour.

Petit tour sur oracle : Les fonctions ou procédures avec param OUT ne sont pas autorisés : http://download.oracle.com/docs/cd/B...d.htm#i1010013

Soit tu changes ta fonction en procédure, soit tu crées une procédure qui appelle la fonction et tu changes le scheduler pour appeler la procédure.

**mortimer.pw** · 14/05/2010, 11h57

Super, merci McM.

Avec une procédure ca fonctionne.

J'avais pourtant lu la doc (peut-être sans mes lunettes

).

Puis-je abuser en te demandant :
- De répondre à la question "Ma technique d'utiliser le scheduler pour exécuter une Procédure qui se trouve dans un Package est-elle bonne ?"
- En cas de problème (table absente, arrêt ou plantage de la BD pendant le job, ...) faut-il ajouter une gestion des erreurs dans la Procédure ? ou est-ce que le scheduler peut gérer ? alerter ?

Merci beaucoup pour avoir réglé mon problème qui traine depuis quelques semaines aussi rapidement.

Je vais essayer de faire plus attention lorsque je lis la doc (mon formateur) mais pas toujours facile d'être concentré sur le contenu quand la traduction prend beuacoup de ressources.

**McM** · 14/05/2010, 12h17

Envoyé par mortimer.pw

Super, merci McM.

Avec une procédure ca fonctionne.

Le 12 Mai t'avais pourtant dit que ça marchait pas

Envoyé par mortimer.pw

Puis-je abuser en te demandant :
- De répondre à la question "Ma technique d'utiliser le scheduler pour exécuter une Procédure qui se trouve dans un Package est-elle bonne ?"
- En cas de problème (table absente, arrêt ou plantage de la BD pendant le job, ...) faut-il ajouter une gestion des erreurs dans la Procédure ? ou est-ce que le scheduler peut gérer ? alerter ?

Utiliser une procédure, c'est une bonne chose.

En cas de problème (table absente,etc..), mieux vaut que tu le gères dans la procédure (table de trace, envoi d'email, etc..)
En cas d'arrêt de la BDD .. ben.. tout plante, donc t'auras rien

Je ne sais pas si le DBMS_SCHEDULER peut gérer les erreurs, là, je vais te renvoyer sur la doc

**mortimer.pw** · 14/05/2010, 12h26

Ok j'y vais voir.

Merci encore McM.

Purge de données par JOB

PL/SQL Oracle

Discussions similaires

Partager

Partager