We have a DB job that runs about 1 month without problems and ends then with ORA-25201. The information available from Oracle about this error is very sparse. I can find neither a bug-fix nor hints how to avoid this problem. DB version is 10g2. Any hints?
Really need to see the code behind the job - but that error suggests that it is probably calling dbms_aq.enqueue or dequeue - which requires a parameter of either a dequeue_options_t TYPE or enqueue_options_t TYPE, each of these have a "VISIBILITY" attribute that can either be:
ON_COMMIT or IMMEDIATE
Your error, suggests you are passing something else:
$ oerr ora 25201
25201, 00000, "invalid value, VISIBILITY should be ON_COMMIT or IMMEDIATE"
// *Cause: An invalid value specified for parameter VISIBILITY.
// *Action: Specify either ON_COMMIT or IMMEDIATE.
At a guess you have some IF statements wrapped around a call to the above, they only fire under circumstances hence the intermittent failures, but as I say without seeing what the job is doing it is difficult to say.
You need to improve the error handling to get a proper view of exactly where the code is failing, either get rid of the exception block altogether (doesn't really serve any purpose other than to make your program think it failed at the RAISE rather than where it really failed) or add some instrumentation into your code to track progress and then print this out in the exception block along with the error message.
Your previous comments are correct though, if you are only setting up the type with default settings this error shouldn't occur unless something else is going on at the Oracle level outside your control (e.g. bug as you say).