Error in Job Schelduling
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Error in Job Schelduling

Hybrid View

  1. #1
    Join Date
    Jan 2004
    Location
    Paris, France
    Posts
    52

    Unhappy 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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    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"

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Paris, France
    Posts
    52
    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

  5. #5
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    maybe you forgot to write
    declare
    in the begining of pl/sql block
    Andrej

  6. #6
    Join Date
    Jan 2004
    Location
    Paris, France
    Posts
    52
    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

  7. #7
    Join Date
    Jan 2004
    Location
    Paris, France
    Posts
    52
    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.

  8. #8
    Join Date
    Jul 2002
    Location
    Slovenia
    Posts
    42
    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 09:25 AM.
    Andrej

  9. #9
    Join Date
    Jan 2004
    Location
    Paris, France
    Posts
    52
    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
  •  



Click Here to Expand Forum to Full Width