Erratic DMBS_Scheduler Behavior

mahy

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

sb92075

Oracle provides a Read Consistent view of the data.
Session won't necessarily "see" new data that changes after the session starts.

ajallen

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

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

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

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

This sounds strange please provide code...

mahy

Don't bother, I added "commit" at the end of every relevant procedure and now it works like a Swiss clock