Thanks for the question, Patrick.
Asked: September 06, 2016 - 6:56 pm UTC
Last updated: July 09, 2021 - 3:35 am UTC
Version: 11.2.0.4
Viewed 10K+ times! This question is
You Asked
I am having a problem using FCOPY to make a few copies of a file that I've created with UTL_FILE.PUT_LINE.
The goal is to generate a txt file and dump into a directory .../output_1/file_1.txt and then copy & rename this file into another directory: ../output_2/rename_file_1.txt.
If I simply copy the file and don't rename, there is no issue.
However, when I attempt to rename the file, the file is still generated and placed in the proper directory, but several record lines and the trailer record have been truncated.
Here is a snip of my code:
BEGIN
Pin_file_dir := 'OUTPUT_DIR';
Lv_file_Nm:='RECON_FIXED_'||to_char(sysdate,'yyyymmddhh24miss')||'.txt';
Lv_file := UTL_FILE.FOPEN(LOCATION =>Pin_file_dir , -- Specific to One predefined directory only
filename => Lv_file_Nm , -- Predefined file name hardcoded as requested
open_mode => 'w', ---- Open File In Write Mode
max_linesize => 32767);
ld_systemtime := to_char(systimestamp, 'YYYYMMDD HH24MISS');
UTL_FILE.PUT_LINE(Lv_file, 'FUND_EXTRACT; '||ld_systemtime);
FOR i IN C_accdata
LOOP
Lv_FinalData := i.FUNDDATA/*||Lv_attribdata ||'|'*/; --i.FUNDDATA is the result of a cursor containing the data
UTL_FILE.PUT_LINE(Lv_file,Lv_FinalData); -- write whole line in file
ln_count := C_accdata%ROWCOUNT;
END LOOP;
UTL_FILE.PUT_LINE(Lv_file, 'EOF RECORD Count '||ln_count||','); --v1.1 added comma at end of trailer
-- code to copy the file to the NDM Directory for System1(fixed file format)
--
UTL_FILE.FCOPY (
'OUTPUT_DIR' , --THIS IS A ORACLE DIRECTORY
Lv_file_Nm , --FILE NAME
'SYSTEM1' , --THIS IS A ORACLE DIRECTORY
'SYS_1_FILE.txt'); --DESTINATION FILE
-- code to copy the file to the NDM Directory for System2(fixed file format)
UTL_FILE.FCOPY (
'OUTPUT_DIR' , -- THIS IS A ORACLE DIRECTORY
Lv_file_Nm , --FILE NAME
'SYSTEM2' , -- THIS IS A ORACLE DIRECTORY
'SYS_2_FILE.txt' ); -- DESTINATION FILE
-- code to move the file to the NDM Directory for System3
UTL_FILE.FCOPY (
'OUTPUT_DIR' , --THIS IS ORACLE DIRECTORY
Lv_file_Nm , --FILE NAME
'SYSTEM3', -- THIS IS A ORACLE DIRECTORY
'SYS_3_FILE.txt' ); -- DESTINATION FILE
/** code to move the file from Output to System4
and then to the Feed Archive via shell script **/
UTL_FILE.FRENAME (
'OUTPUT_DIR' , --THIS IS ORACLE DIRECTORY
Lv_file_Nm, --FILE NAME
'System4', -- THIS IS A ORACLE DIRECTORY
'RECON_FIXED_20160906.txt', --Lv_file_Nm,
FALSE /*overwrite*/);
Would appreciate any assistance you can provide.
and Connor said...
I dont see any calls to UTL_FILE.FFLUSH or UTL_FILE.FCLOSE in there.
Try those before you copy the file to make sure the buffer is fully flushed out to the file before the copy is done.
If the problem is still there, ping us back via review.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment