Purge de données par JOB

Version imprimable

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:

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:

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:

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:

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:

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.

07/05/2010, 11h26
McM

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.
12/05/2010, 12h48
mortimer.pw

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:

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.
12/05/2010, 16h14
McM
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:

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:

NULL;

pour voir si la planification et l'exécution marchent
Puis tu dé-commentes les lignes petit à petit.
12/05/2010, 16h28
McM

Citation:

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:

cRequete:= 'DELETE FROM '||cSchema||'.'||cTable||' WHERE '||cChamp||' < :ladate' USING dFirstJour;
14/05/2010, 07h39
mortimer.pw
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:

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.
14/05/2010, 10h12
McM

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

Citation:

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.
14/05/2010, 10h57
mortimer.pw

Super, merci McM.

Avec une procédure ca fonctionne.

J'avais pourtant lu la doc (peut-être sans mes lunettes :D).

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.
14/05/2010, 11h17
McM

Citation:

Envoyé par mortimer.pw

Super, merci McM.

Avec une procédure ca fonctionne.

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

Citation:

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 :mrgreen:
14/05/2010, 11h26
mortimer.pw

Ok j'y vais voir.

Merci encore McM.