1 2 Previous Next 22 Replies Latest reply: Jun 16, 2009 3:32 AM by Roger25 RSS

    jobs question

    Roger25
      Hello,

      let's say i have a table:
      create table A (
      id integer,
      name varchar2(10)
      b_id integer 
      constraint fk_Bid foreign key(b_id) references B(id)
      );
      
      create table B(
      id integer,
      start_date date,
      end_date date)
      B table can contain :

      1 15.09.2008 23.02.2009
      2.16.06.2009 18.08.2009

      now i wanna update the b_id from the table a using a job, so when sysdate becomes a date from start_date from the table B, i wanna update b_id from the table A with the corresponding id from B table
      for example, in A table now i can have

      1 'John' 1

      tomorrow, being 16.06.2009, i wanna update that b_id with value 2. how can i do this using jobs?

      Regards,
        • 1. Re: jobs question
          Walter Fernández
          Hi,

          I think a better approach is to put all the login in a stand alone procedure (or a procedure inside a package), test it and then put it in a job.
          CREATE OR REPLACE PROCEDURE proc_copy IS
          BEGIN
              UPDATE a
              SET    a.b_id = (SELECT b.id
                               FROM   b
                               WHERE  b.start_date = SYSDATE);
              COMMIT;
          EXCEPTION
              WHEN NO_DATA_FOUND THEN
                  ROLLBACK;
              WHEN OTHERS THEN
                  ROLLBACK;
          END;
          /
          For examples using jobs you can see [DBMS_JOB|http://www.psoug.org/reference/dbms_job.html]

          Regards,

          Edited by: Walter Fernández on Jun 15, 2009 2:40 PM - Adding URL...
          • 2. Re: jobs question
            Roger25
            and in DBMS_SCHEDULER.create_job what should i write to parameters start_date and repeat_interval?

            Thanks.

            Edited by: Roger22 on 15.06.2009 21:07
            • 3. Re: jobs question
              Roger25
              i have a problem running the job
              so i wrote in PL/SQL Developer
              declare
              interval integer;
              begin
              select trunc(min(data_start)-sysdate) into interval from semestru where sysdate<data_Start;
              
              dbms_scheduler.create_job(job_name => 'upd_semestru_eleviclasa', job_type => 'STORED_PROCEDURE', job_action => 'roger.proc_upd_idsemestru_eleviclasa', start_date => sysdate, repeat_interval => sysdate+interval, end_date => null, enabled => true, comments => 'Job pentru actualizarea automata a semestrului');
              
              end;
              but i got the error

              ORA-27465: invalid value 16-JUN-09 for attribute REPEAT_INTERVAL
              ORA-06512: at "SYS.DBMS_ISCHED", line 99
              ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
              ORA-06512: at line 6

              i wanna repeat this job each time the sysdate = (select min (data_start) from semestru where sysdate<data_start)
              how can i deal with this?

              Regards,
              • 4. Re: jobs question
                Warren Tolentino
                Roger22 wrote:
                ORA-27465: invalid value 16-JUN-09 for attribute REPEAT_INTERVAL
                ORA-06512: at "SYS.DBMS_ISCHED", line 99
                ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
                ORA-06512: at line 6

                i wanna repeat this job each time the sysdate = (select min (data_start) from semestru where sysdate<data_start)
                how can i deal with this?
                the parameter repeat_interval is a VARCHAR2 of datatype.

                here is an example for passing a parameter value for repeat_interval:
                repeat_interval => 'freq=daily;byhour=22;byminute=30;bysecond=0'
                • 5. Re: jobs question
                  Roger25
                  Ok, now i got
                  declare
                  interval integer;
                  begin
                  select trunc(min(data_start)-sysdate) into interval from semestru where sysdate<data_Start;
                  dbms_scheduler.create_job(job_name => 'upd_semestru_eleviclasa', job_type => 'STORED_PROCEDURE', job_action => 'roger.proc_upd_idsemestru_eleviclasa', start_date => sysdate, repeat_interval => 'sysdate+interval', end_date => null, enabled => true, comments => 'Job pentru actualizarea automata a semestrului');
                  end;
                   
                  ORA-27465: invalid value sysdate+interval for attribute REPEAT_INTERVAL
                  ORA-06512: at "SYS.DBMS_ISCHED", line 99
                  ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
                  ORA-06512: at line 6
                  what's wrong?

                  Thanks
                  • 6. Re: jobs question
                    Warren Tolentino
                    i guess the parameter you are passing is still incorrect. what is the return result for this query:
                    select trunc(min(data_start)-sysdate) interval from semestru where sysdate < data_Start;
                    • 7. Re: jobs question
                      Roger25
                      Ok, i solved now with dbms_job package (anyway, the problem was at string concatenation)
                      I executed
                      declare
                      v_job number;
                      interval1 integer;
                      begin
                      select trunc(min(data_start)-sysdate) into interval1 from semestru where sysdate<data_Start;
                      dbms_job.submit(v_job,'roger.proc_upd_idsemestru_eleviclasa;',sysdate, 'sysdate+'||to_char(interval1));
                      end;
                      now how can i see the status of this job in PL/SQL (if it's running, etc..)

                      Regards
                      • 8. Re: jobs question
                        Warren Tolentino
                        use the query below:
                        select job, 
                               next_date, 
                               next_sec, 
                               failures, 
                               broken, 
                               substr(what,1,40) job_description
                          from user_jobs;
                        • 9. Re: jobs question
                          Roger25
                          Thanks. Or dba_jobs :)
                          but i have a problem regarding to this job
                          declare
                          v_job number;
                          interval1 integer;
                          begin
                          select trunc(min(data_start)-sysdate) into interval1 from semestru where sysdate<data_Start;
                          dbms_job.submit(v_job,'roger.proc_upd_idsemestru_eleviclasa;',sysdate, 'sysdate+'||to_char(interval1));
                          end;
                          i wrote this in an anonymous block, so when i execute it, the value to_char(interval1) is passed at the last parameter of dbms_job.submit. and this will be permanently the same in the job (i.e. if now i run this job, the last parameter will be: sysdate+1, so this is the repeating interval).
                          after the job executes successfully, the next_time is still sysdate+1. i wanna after the job is executed, to dynamically obtain that interval1 from the query
                          select trunc(min.........
                          is it possible? so i wanna set dynamically the next time the job will execute, depending on that select

                          Thanks
                          • 10. Re: jobs question
                            Peter Gjelstrup
                            Roger22 wrote:
                            Ok, i solved now with dbms_job package (anyway, the problem was at string concatenation)
                            Come on Roger, don't take to deprecated functionality just to get it working. Stick with DBMS_SCHEDULER.

                            Regards
                            Peter
                            • 11. Re: jobs question
                              Roger25
                              Ok, but in dbms_scheduler will happen the same thing (if you have read what i posted :) )
                              so i need that interval1 variable to obtain dynamically, not just the value from the first run of that anonymous block
                              • 12. Re: jobs question
                                Peter Gjelstrup
                                Roger,

                                I'm afraid I'm not a scheduler expert, but I think it should be something like this.
                                declare
                                     interval integer;
                                begin
                                     select trunc(min(data_start)-sysdate) 
                                       into interval 
                                       from semestru 
                                      where sysdate<data_Start;
                                
                                     dbms_scheduler.create_job(job_name => 'upd_semestru_eleviclasa'
                                                    , job_type => 'STORED_PROCEDURE'
                                                    , job_action => 'roger.proc_upd_idsemestru_eleviclasa'
                                                    , start_date => sysdate
                                                    , repeat_interval => 'FREQ=DAILY; INTERVAL=' || interval || ';'
                                                    , end_date => null
                                                    , enabled => true
                                                    , comments => 'Job pentru actualizarea automata a semestrului');
                                end;
                                If not, read about [Calendar expressions|http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/scheduse.htm#ADMIN10040]

                                Regards
                                Peter
                                • 13. Re: jobs question
                                  Roger25
                                  shouldn't be

                                  , repeat_interval => 'FREQ=DAILY; INTERVAL=' sysdate+|| interval || ';'

                                  ?

                                  Thank you
                                  • 14. Re: jobs question
                                    Peter Gjelstrup
                                    No, it should not.

                                    Docs clearly say that INTERVAL is a number
                                    interval_clause = "INTERVAL" "=" intervalnum
                                       intervalnum = 1 through 99
                                    But why not try it yourself, and perhaps even read the link I gave you.

                                    Regards
                                    Peter
                                    1 2 Previous Next