Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: updating the records based on dependency using PL/SQL.

    Hi,

    how to create the Scheduler using PL/SQL.

    At a particular time e.g 00.00 AM every day i want to update a table in DB.
    updating the rows by dependency.
    how to acheive using PL/SQL.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Read The Fine Manual about DBMS_JOB
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    can you please provide the links for DBMS_JOB documents.

    i believe DBMS_JOB utility in Oracle 11g but not in Oracle 10g.
    how can i implement this in 10g.
    Last edited by laknar; 02-19-09 at 01:43.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you are unwilling or incapable of using GOOGLE, then you can do without it.

    http://www.dbforums.com/oracle/10316...s-posters.html
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Why re-writing your post after my response above?

    >i believe DBMS_JOB utility in Oracle 11g but not in Oracle 10g.
    Believe what you will. DBMS_JOB has been available since at least V7

    If you are unwilling or incapable to RTFM from previously provided link,
    then so be it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2008
    Posts
    94
    i believe DBMS_JOB utility in Oracle 11g but not in Oracle 10g.
    how can i implement this in 10g.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oracle 10g introduced a more advanced scheduling package over DBMS_JOB - DBMS_SCHEDULER. Read all about them in Oracle 10g documentation.

  8. #8
    Join Date
    Jul 2008
    Posts
    94
    Requirement is i have 10 jobs in a table
    every day 10pm i have to run all the 10 jobs.

    first five jobs there is no dependency i have to update as RUN.
    if any of the jobs failed i have to update as FAILED

    if all the five jobs completed successfully then start next 3 jobs.

    if the 3 jobs completed successfully.then run the last 2 jobs.

    if any of the jobs failed in first five.how can i rerun only the failed job.
    if i update failed job into WAIT then it should start from beginning.

    Jobname Status

    a.sql STATUS
    b.sql STATUS
    c.sql STATUS
    d.sql STATUS
    e.sql STATUS
    f.sql STATUS
    g.sql STATUS
    h.sql STATUS
    i.sql STATUS
    j.sql STATUS

    How can i do this using DBMS_JOB Utility.Please advice me.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can i do this using DBMS_JOB Utility.Please advice me.
    so are you admitting DBMS_JOB exists in V10 Oracle?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2008
    Posts
    94
    DBMS_SCHEDULER utility is there in 10g.
    im new to oracle

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >DBMS_SCHEDULER utility is there in 10g.
    Yes & DBMS_JOB existed since at least V7.
    So what is your point?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jul 2008
    Posts
    94
    Requirement is i have 10 jobs in a table
    every day 10pm i have to run all the 10 jobs.

    first five jobs there is no dependency i have to update as RUN.
    if any of the jobs failed i have to update as FAILED

    if all the five jobs completed successfully then start next 3 jobs.

    if the 3 jobs completed successfully.then run the last 2 jobs.

    if any of the jobs failed in first five.how can i rerun only the failed job.
    if i update failed job into WAIT then it should start from beginning.

    Jobname Status

    a.sql STATUS
    b.sql STATUS
    c.sql STATUS
    d.sql STATUS
    e.sql STATUS
    f.sql STATUS
    g.sql STATUS
    h.sql STATUS
    i.sql STATUS
    j.sql STATUS

    the thing is not creating a scheduler.
    we need to perform the above operation by executing the stored procedure at any time.

  13. #13
    Join Date
    Jul 2008
    Posts
    94
    can anyone please advice me for this post.

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's nothing much to tell; obviously, you know what you want (you've explained it in words) - now you have to code it.
    • run first five procedures (if you are about to schedule them using one of Oracle's scheduling packages, I'd suggest you to rewrite SQL scripts into stored procedures)
    • schedule another (independent) procedure which will check whether the first five have completed; if not, re-schedule it to check that again later. Then, if they have completed, check whether it was successfully. If not, re-run failing procedure(s), re-schedule this "checking" procedure until everything is OK. Then schedule the next 3 procedures and repeat this step in order to run the final two of them.
      It will require some coding, most probably IF-THEN-ELSEs, but - that's up to you.

    Furthermore, saying that
    the thing is not creating a scheduler
    is - in my opinion - wrong. Scheduler IS designed to do such things. Otherwise, how do you plan to run these procedures at 00:00 o'clock? Stay awake every night? Work night shifts for the rest of your life? I don't think so.

    Though, you might try to avoid DBMS_JOB or DBMS_SCHEDULER by scheduling these SQL scripts on the operating system level, using your OS's scheduling tools. This would probably be a worse idea as OS scheduler can't check whether procedures have ended successfully, so you'd have to do that in the database, return some kind of a code (status) back to OS level and re-schedule the whole process once again. I'd rather do that in the database, not operating system. But you may do whatever you want, of course.

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by laknar
    can you please provide the links for DBMS_JOB documents.

    i believe DBMS_JOB utility in Oracle 11g but not in Oracle 10g.
    how can i implement this in 10g.
    DBMS_JOB in Oracle V9
    DBMS_JOB, 1 of 2
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •