1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Create and Schedule Job - Problem

Discussion in 'SQL PL/SQL' started by miki86, Sep 18, 2012.

  1. miki86

    miki86 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    hi everyone,

    I'm trying to write an Job which I would like to schedule. Function of this Job is to delete all entries older than 1 Month in the table "tabelle1". As I understand, you can put in job_action the procedure statement.

    BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => 'delete_all_entries',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DELETE FROM tabelle1 WHERE last_date < (SYSDATE - INTERVAL '1' MONTH) AND (status = 'processed'); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=10;BYMINUTE=00',
    end_date => NULL,
    enabled => TRUE,
    comments => 'Job defined entirely by the CREATE JOB procedure.');

    END;

    I get the following error message:

    --------------------------------------------------------------------------------------------------------------
    ORA-06550: Zeile 5, Spalte 118:
    PLS-00103: Encountered the symbol "1" when expecting one of the following:

    ) , * & = - + < / > at in is mod remainder not rem
    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
    like4 likec between || multiset member submultiset
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
    -------------------------------------------------------------------------------------------------------------

    What am I doing wrong?.
    Thx in advance!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not properly escaping your embedded single quotes:

    Code (SQL):
    BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => 'delete_all_entries',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DELETE FROM tabelle1 WHERE last_date < (SYSDATE - INTERVAL ''1'' MONTH) AND (status = ''processed''); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=10;BYMINUTE=00',
    end_date => NULL,
    enabled => TRUE,
    comments => 'Job defined entirely by the CREATE JOB procedure.');

    END;

     
    You absolutely MUST 'double up' on the single quotes to get them to be recognized within the body of a declared string.
     
  3. miki86

    miki86 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank youu my friend!! :)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are welcome.
     
  5. miki86

    miki86 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David,
    the script runs and in the execution log, there stands "succeeded". The function of this job is to delete the entries in "tabelle1" which are older than 1 month. When I test this script, the entries are not deleted?.
    Maybe an idea?.

    Thanks in advance!,

    Milan
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to commit your work and you haven't. You should write a PL/SQL procedure to do this then call the procedure from your scheduled job. As it is you have no commit and apparently there is no implied commit from such tasks.