4 Replies Latest reply: Nov 14, 2012 3:26 AM by myOra_help RSS

    add new line in existing scheduled jobs in oracle

    Sameer_DBA
      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
          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
            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
              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
                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;