Hi

In my last post, I asked if anyone could give me a script to scheldule deletion in my database. I wanted to delete rows from a specific table with a delete_flag raised.
I had the script below :
declare
v_job in number;
begin
dbms_job.submit
(job=>v_job,
what=>'begin delete from my_table where del_flag=''1''; end;',
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;
Unfortunately, when I try to execute it I have :
v_job in number;
*
ERREUR Ã* la ligne 2 :
ORA-00604: Une erreur s'est produite au niveau SQL récursif 1
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne
de caractères trop petit
ORA-06512: Ã* ligne 8
ORA-06550: Ligne 2, colonne 9 :
PLS-00103: Symbole "IN" rencontré Ã* la place d'un des symboles suivants :
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar
Symbole "IN" n'a pas été pris en compte.

Knowing that today the needs had evolved and that I want to delete rows with del_flag set to 1 from differents tables, I would like to know how (or get a script if possible) I can do the following.

1/ for each deletion task from a table declare a procedure. I think about some thing like
DECLARE PROCEDURE DEL_TABLE#1
BEGIN
'begin delete from my_table#1 where del_flag=''1''; end;'
END
/

DECLARE PROCEDURE DEL_TABLE#2
BEGIN
'begin delete from my_table#2 where del_flag=''1''; end;'
END
/

.....

2/ Scheldule them in DBMS_JOB to be execute every 6 hours.

3/ Question : Shall I scheldule them one after one in same Job or independantly. I rather prefer the 2nd solution.

I hope that it is clear.

Thanks in advance for help and support.

BTW I am using Oracle8i 8.1.7.3 on Windows 2000 Server and in September we are planning to migrate to Oracle9i on Linux. Are these scripts gonna work ?

Merci