This discussion is archived
4 Replies Latest reply: Nov 14, 2012 1:26 AM by myOra_help RSS

add new line in existing scheduled jobs in oracle

Sameer_DBA Newbie
Currently Being Moderated
Hi,

I tried to add below line in existing scheduled jobs using DBMS_JOB.WHAT (JOB IN <job number>,
what IN execute immediate ''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"''.
end;
/

But no luck it given syntax error, so can you give me the right solution if possible.

Note: ( my job has been scheduled in dba_jobs).


Regards
  • 1. Re: add new line in existing scheduled jobs in oracle
    myOra_help Journeyer
    Currently Being Moderated
    You are getting error because you are passing invalid statement to what. What expect only plsql procedure to run.
    You should pass whole plsql block or a procedure as a job not a single statement like execute immidiate to add.
    You should add your new code with existing and pass to what procedure at once.
  • 2. Re: add new line in existing scheduled jobs in oracle
    Sameer_DBA Newbie
    Currently Being Moderated
    I used to add the new line using below commnad:
    BEGIN
    DBMS_JOB.WHAT (job IN <job number, what IN execute immediate ''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"''),
    end;
    /

    Regards.
  • 3. Re: add new line in existing scheduled jobs in oracle
    Purvesh K Guru
    Currently Being Moderated
    Sameer_DBA wrote:
    I used to add the new line using below commnad:
    BEGIN
    DBMS_JOB.WHAT (job IN <job number, what IN execute immediate ''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"''),
    end;
    /

    Regards.
    Not sure, but I guess, you should include Execute Immediate part into a Single Quote.

    This way:
    BEGIN
        DBMS_JOB.WHAT (job IN <job number>, what IN 'execute immediate  ''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"''');
    end;
  • 4. Re: add new line in existing scheduled jobs in oracle
    myOra_help Journeyer
    Currently Being Moderated
    try this (not tested)

    but it will replace your previous WHAT content.
    BEGIN
    DBMS_JOB.WHAT (job IN <job number, what IN 'begin execute immediate ''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"'' end;'),
    end;
    if you wnat to add this statement to previous one (existing code) then you should first get WHAT content and add your new code to it and put whole in WHAT procedure like below. (not tested)
    BEGIN
    DBMS_JOB.WHAT (job IN <job number, what IN 'begin ***** Your previous code  *****; execute immediate''ALTER SESSION SET NLS_DATE_FORMAT = "YYYY MM DD"'' end;'),
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points