Oracle 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