Is this an answer for this prob
A reader, March 05, 2017 - 6:20 am UTC
Whatever the Eason is. Say he just want to record in db the timestamp the row was put in the file.
Anyway since there is no relationship as you said, the question remains legitimate.
March 07, 2017 - 1:55 am UTC
"the question remains legitimate."
As does the answer
"There is no relationship between utl_file and locks inside the database. "
ORA-29285: file write error
Prabhu Hooli, March 05, 2017 - 6:35 am UTC
Hi Connor,
application user may create new record on the table while running this job and FOR UPDATE NOWAIT clause used to lock the rows which were picked at that time for further processing.
after the file write there are other updates on the subsequent tables with in the procedure based on the cursor rows.
I read about the ORA-29285: file write error may also cause in UTL_FILE.FREMOVE when the writing file is still locked.
please correct if i am wrong in understanding the concept. what would be the actual cause of this file write error. Thanks
March 07, 2017 - 2:01 am UTC
You do not need to lock the rows to get a consistent view of them at the time you query them to write your file. Similarly, locking the existing rows does not stop anyone from adding new rows.
If you need to detect new/changed rows, you would need a mechanism to establish a point of serialisation for your consistent read. You could do this with something like:
lock table T in exclusive mode;
open C for select * from t;
commit;
fetch ...
So your lock is extremely brief and you know that at the moment you opened your cursor there were no inflight transactions.
But none of these locks are related to file level locks.
Prabhu Hooli, March 10, 2017 - 1:37 pm UTC
Hi Connor,
My issue is not with FOR UPDATE NOWAIT i just wanted understand whether it could be a reason for the error. real issue is with UTL_FILE.fclose which is failing close the file at directory and throwing the file write error.
Sorry about that i mistakenly mentioned as UTL_FILE.fremove actually it UTL_FILE.fclose function.
interestingly, same code is running fine on next day/run without any error. directory/File has enough permission as file is getting created.
March 11, 2017 - 1:54 am UTC
We cant tell - because we've never seen your code, test case or otherwise.