-
Error in Job Schelduling
-
Re: Error in Job Schelduling
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.
Are these scripts gonna work ?
Sure. DBMS_JOBS are OS Independent, as long as no OS dependent command in "What".
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
-
I have change the script (I have removed the IN)
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
-
maybe you forgot to write
declare
in the begining of pl/sql block
Andrej
-
Thanks it is compiling OK now with the following statement.
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
-
Thanks it is compiling OK now with the following statement.
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
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.
-
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;
Last edited by andrejm; 02-10-2004 at 10:25 AM.
Andrej
-
Hi
I understand that you recommand that I have only one job doing the all thing.
Is my statements correct for the procedure ?
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/
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/
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|