ERROR at line 1:
ORA-20006: ORA-20100: File l0005230.tmp creation for FND_FILE failed.
You will find more information on the cause of the error in request log.
ORA-06512: at "APPS.OUR_CUSTOM_CODE", line 949
ORA-06512: at line 2
Thats how I started my day. To say I hate ORA-20100 errors would not accurately portray my feelings. However, after reading Tom Kytes blog posting titled "Why do people do this?" I know why. Guess what is on line 949. ;)
Unlike previous occurrences of this error, its a concurrent manager temp file. So I know right away that the directory in question is pointed to by the environment variable APPLPTMP.
We have hit this error before and usually its the utl_file_dir database parameter. I guess some of us DBA's have fat fingers and even tho we double check this parameter, sometimes a typo sneaks through during cloning. I have a script which writes to each of our utl file directories. So I could rule this out as being a problem.
I can't remember where I obtained the code below, but here is what I use. I write to a file, then try and read from it. Exceptions below give me a good indication of what the problem may be.
CREATE OR REPLACE PROCEDURE utl_test IS
file_handle UTL_FILE.FILE_TYPE; -- file handle of OS flat file
col1 NUMBER; -- C1 retrieved from testtab table
retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
file_handle := UTL_FILE.FOPEN('dir1', 'filetest.txt', 'W');
UTL_FILE.PUT_LINE(file_handle, 'This is a test');
file_handle :=utl_file.fopen('dir1', 'filetest.txt', 'R');
WHEN NO_DATA_FOUND THEN
WHEN UTL_FILE.INVALID_PATH THEN
WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(file_handle);Troubleshooting ORA-20100 on Concurrent Processing
WHEN UTL_FILE.WRITE_ERROR THEN
The next common resolution was to check the location to which the APPLPTMP environment variable points. We have multiple environments on our test dev servers and its not uncommon for one environment to own a tmp file, thus blocking another environment from writing to it. In this case, the file l0005230.tmp existedand was owned by the proper user but was zero size.
-rw-r--r-- 1 oravis dba 0 Feb 5 10:06 l0005230.tmp
We have hit OS fragmentation issues in the past. Just to be safe I copied a large file to the tmp directory without a problem. We have had partitions with 20GB free and have been unable to create a 100k file. A system reboot usually fixed that.
I was out of ideas. I have never gotten this far with this particular error before. So I fired up Metalink. Note:261693.1 Troubleshooting ORA-20100 on Concurrent Processing, listed the options I have tried above and a few more. One suggestion is to use FND_FILE which is the package used by the application to write to OS files.
SQL> exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
If everything is setup correctly, this will write to a file in the directory pointed to by APPLPTMP. Don't go to this directory looking for a file named FND_FILE.LOG. You won't find it. The data, "THIS IS A TEST", will be written to a temp file. So look at tmp files created around the time you executed that command.
THIS IS A TEST
So at this point, none of the suggested troubleshooting steps have pinpointed the problem. To summarize, we have verified:
- that the UTL_FILE_DIR init parameter is correct and each location is writeable.
- that the application should be able to write to the OS using FND_FILE. The file does get created but the app can't write data to it.