Forum Stats

  • 3,854,674 Users
  • 2,264,399 Discussions
  • 7,905,751 Comments

Discussions

Erratic DMBS_Scheduler Behavior

mahy
mahy Member Posts: 21
edited Jan 24, 2010 4:33PM in General Database Discussions
Hello folks,

this problem is really bugging me and I've already pulled half of my hair out! I've specified a DBMS_Scheduler job, which executes a procedure every 5 seconds. This procedure looks into a certain database table, looks for non-null values in a certain column, and copies each non-null value into another table, before nullifying it again. The thing is, this job works, but only for the FIRST TIME after being enabled! Then it simply ignores all non-null values, in other words, it doesn't see them, it reports them all as null! This incorrect behavior continues until I (1.) re-enable the job, (2.) recompile the procedure (3.) make any change in the database, e.g. truncating an unrelated table. Then it suddenly works ONCE, and afterwards slips into this incorrect behavior again, until I make yet another change. Then the whole thing iterates. What is this supposed to mean? The database is simply doing whatever it likes. Any help will be much appreciated. TIA

Edited by: mahy on Jan 13, 2010 6:41 PM
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Oracle provides a Read Consistent view of the data.
    Session won't necessarily "see" new data that changes after the session starts.
  • ajallen
    ajallen Member Posts: 1,796
    Five second cycle time seems a bit small. However, I suggest you look at the procedure and see if it is committing after it finishes all its work and before it exits.
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    As per my knowledge your job is executing fine - but problem might exists with your procedure. Let's go in divide and rule fashion. Stop the Job. Just execute the procedure and check and log into temp table. Next after 5 mins you manually execute and check whether you expected values are validated in table or some thing else is happening, that makes your clear picture either job execution or procedure problem.

    - Pavan Kumar N
    Oracle 9i/10g - OCP
    http://oracleinternals.blogspot.com/
  • mahy
    mahy Member Posts: 21
    Hello again,

    thanks to all replies. I've already done detailed debugging of the procedure itself (with all jobs switched off). The same problem appears again. The relevant "select" - with or without a cursor - boldly reports "null" when the fields are most certainly NOT null (as verified by another standalone SQL query outside any procedure). And just like within a job, any "truncate table dummy_table" means normal behavior of the procedure for one iteration...
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    The relevant "select" - with or without a cursor - boldly reports "null" when the fields are most certainly NOT null (as verified by another standalone SQL query outside any procedure)
    Different sessions can return different results while running EXACT same SQL, depending upon when their transaction started
    & what DML has been COMMITted.

    SESSION1 - TRUNCATE TABLE FOOBAR;
    SESSION1 - SELECT COUNT(*) FROM FOOBAR; -- 0 ROWS
    SESSION2 - INSERT INTO FOOBAR ....;
    SESSION2 - COMMIT;
    SESSION1 - SELECT COUNT(*) FROM FOOBAR; -- 0 ROWS which is consistent with when SESSION1 started
  • Lakmal Rajapakse
    Lakmal Rajapakse Member Posts: 827 Silver Badge
    This sounds strange please provide code...
  • mahy
    mahy Member Posts: 21
    Don't bother, I added "commit" at the end of every relevant procedure and now it works like a Swiss clock :)
This discussion has been closed.