Error in Job Schelduling

Printable View

02-10-2004, 05:32 AM
Latyr

Error in Job Schelduling

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
02-10-2004, 06:29 AM
SANJAY_G

Re: Error in Job Schelduling

Quote:

Originally posted by Latyr
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.

Very good decision.

Quote:

Are these scripts gonna work ?

Sure. DBMS_JOBS are OS Independent, as long as no OS dependent command in "What".
02-10-2004, 07:21 AM
DaPi

Your errror is from:
v_job in number;

You don't want the "in", just:
v_job number;

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
02-10-2004, 09:24 AM
Latyr

I have change the script (I have removed the IN)

Quote:

v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'begin delete from LOT_TRAVAIL where R_GED=''1''; end;',
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

and I have this message :
SP2-0734: commande inconnue au dÃ©but de "v_job numb..." - le reste de la ligne est ignorÃ©.
(job=>v_job,
*
ERREUR Ã* la ligne 3 :
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 3, colonne 7 :
PLS-00201: l'identificateur 'V_JOB' doit Ãªtre dÃ©clarÃ©
ORA-06550: Ligne 2, colonne 1 :
PL/SQL: Statement ignored
02-10-2004, 09:37 AM
andrejm

maybe you forgot to write
declare
in the begining of pl/sql block
02-10-2004, 10:12 AM
Latyr

Thanks it is compiling OK now with the following statement.

Quote:

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'begin delete from LOT_TRAVAIL where R_GED=''1''; end;',
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

Now how should I do to create a procedure for each of my tables ?
Cant I do
CREATE OR REPLACE PROCEDURE Purge_Lot
AS
BEGIN
02-10-2004, 10:19 AM
Latyr

Thanks it is compiling OK now with the following statement.

Quote:

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'begin delete from LOT_TRAVAIL where R_GED=''1''; end;',
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

Now how should I do to create a procedure for each of my tables ?
Cant I do for instance
CREATE OR REPLACE PROCEDURE Purge_Table1
AS
BEGIN
DELETE FROM Table1 WHERE r_ged='1';
COMMIT ;
END
/

and then do this

Quote:

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'execute Purge_Table1';
what=>'execute Purge_Table3';
what=>'execute Purge_Table2';
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

Is it a good way to proceed ? I mean to have one job and many tasks schelduled or is it rather better and safer (in case of one job failed) to have one job per task ?

Thanks in advance for your help.

BTW : Any online ressource where I can have a short PL/SQL tutorial ? I am not a full DBA, it's just 10% of my job and it's mainly just backup/restore with RMAN and some monitoring. I do really occasionaly few developpement like this one but I wish to be more confident and independant.
02-10-2004, 10:21 AM
andrejm

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'
begin Purge_Table1; end; commit;/* if you want to or need to*/
begin Purge_Table3; end;
begin purge_Table2; end;';
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;
02-10-2004, 10:34 AM
Latyr

Hi
I understand that you recommand that I have only one job doing the all thing.

Is my statements correct for the procedure ?

Quote:

CREATE OR REPLACE PROCEDURE Purge_Table1
AS
BEGIN
DELETE FROM Table1 WHERE r_ged='1';
COMMIT ;
END
/

Do I need to have a commit in the procedure declaration ?

In a performance manner and a security point of vue what is the difference between 1/ and 2/
1/

Quote:

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'
begin Purge_Table1; end;
begin Purge_Table3; end;
begin purge_Table2; end;';
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

2/

Quote:

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'
begin Purge_Table1; end;
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'
begin Purge_Table3; end;
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

declare
v_job number;
begin
dbms_job.submit
(job=>v_job,
what=>'
begin purge_Table2; end;';
next_date=>sysdate,
interval=>'trunc(sysdate,''hh24'')+6/24'
);
commit;
end;

Thanks for your help and explanations.

Latyr

All times are GMT -4. The time now is 12:26 AM.