Home » SQL & PL/SQL » SQL & PL/SQL » utl_file halts
utl_file halts [message #205268] Fri, 24 November 2006 02:06 Go to next message
avinash.prabhu
Messages: 1
Registered: November 2006
Junior Member
hi,
i have a procedure which selects file names to be copied from mounted file system to another file system in inux.
files may be in multiples
what happens is while copying some files it gives invalid file operation error and procedure exits.
i have not written exit anywhere..
how should i deal with this..
procedure should log whichever file is in error and then process another file
it should exit only after processing all files and not in between
i have written some functions to check existence of files at o/s level and check attribs still code fails.


1.Multiple files will be selected from table
2.files need to be copied to single location one-by-one as abc.txt which will be read by external table
3.procedure halts when it gets some exception while copying files like invalid operation.
4.requirement:
procedure should log the error in table and proceed for next file or if this is the last file then go for further processing



CREATE OR REPLACE PROCEDURE Proc_rgtr1 AS
-----all variables

--cursor to fecth files

CURSOR cmn_fileupld_cur IS
SELECT unique file_name,file_sts,file_size,file_path,err_file_path,upld_dt
FROM CMN_FILEUPLD_T
where upper(file_name) like '%RGTR%'
--and file_size < 10000
and upper(file_sts) ='D'
and upper(file_name) not LIKE '%.Z%' 
and upper(file_name) not like '%.ZIP%'
and upper(file_name) not like '%.log%'
and upper(file_name) not like '%.log.log%'
and upper(file_name) not like 'c:\%'
and upper(file_name) not like 'C:\%' 
and upper(file_name) not like 'd:\%'
and upper(file_name) not like 'D:\%'
AND file_sts = 'D'
order by UPLD_DT ASC;
BEGIN


UPDATE CMN_FILEUPLD_T
SET file_sts='D',
dest_file_path='/tmp',
err_file_path='/tmp'
WHERE upper(file_name) LIKE '%RGT%'
AND file_sts NOT IN ('CM','FL');
COMMIT;
--DBMS_OUTPUT.PUT_LINE( 'AFTER Ist update');

UPDATE CMN_FILEUPLD_T
SET dest_file_nm = 'rgtr1.txt',
err_file_path='/tmp'
WHERE upper(file_name) LIKE '%RGT%'
AND file_sts = 'D';
COMMIT;
DBMS_OUTPUT.PUT_LINE( 'AFTER IInd update'); 



-- opening cursor (file_names_cur) to get the file name expected, It should return only one row.

FOR file_names_rec IN file_names_cur



LOOP


-- setting the temporary table name
v_file_mas_name := 'TEMP_TABLE_' || TRIM(file_names_rec.file_name);
v_file_name := UPPER(TRIM(file_names_rec.file_name));
V_BAD_ORIG_FILE:= trim(v_file_name)||'.bad';


BEGIN

--Truncating new_ext_table_rgtr1,temporary table
v_file_new_ext :='new_ext_table_rgtr1';
V_FILE_LOG_RGTR1:='log_rgtr1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_TABLE_RGTR1' ;
EXECUTE IMMEDIATE 'TRUNCATE TABLE log_rgtr1';
END;






FOR cmn_fileupld_cur_rec IN cmn_fileupld_cur LOOP

BEGIN
-- log file to log all the information regarding the loading


dbms_output.put_line(' <<new_file>>');
v_log_file_name := cmn_fileupld_cur_rec.file_name || '.log';


v_f1 := utl_file.fopen(cmn_fileupld_cur_rec.err_file_path,
v_log_file_name,
'W'); 

EXCEPTION
WHEN utl_file.invalid_operation THEN
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Unable to create error log' 
WHERE CMN_FILEUPLD_T.file_name LIKE '%rgtr1%';
COMMIT;

utl_file.fclose(v_f1);




END;
/******************************Copy block*******************************/

BEGIN

/*Update file status in table as started copying SC */


v_src_path := cmn_fileupld_cur_rec.file_path;
v_cp_file_name := cmn_fileupld_cur_rec.file_name;

v_dest_path := TRIM(v_ext_dir_rgtr1);

UPDATE CMN_FILEUPLD_T
SET file_sts = 'SC',
lst_mdfct_dt = SYSDATE,
proc_start_date=SYSDATE,
error_msg = 'Copy started of ' || v_cp_file_name || ' FROM ' || v_src_path || ' to ' || v_dest_path || ' )' 
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;

COMMIT;

var_file_exists:=file_exists(v_src_path,v_cp_file_name);






BEGIN

if var_file_exists like 'Y' then
dbms_output.put_line('in y');

utl_file.fcopy(v_src_path, v_cp_file_name, v_dest_path, v_dest_file);
else
dbms_output.put_line('in n');
dbms_output.put_line('source path is :' ||v_src_path); 
dbms_output.put_line('copy file name is : ' ||v_cp_file_name);
dbms_output.put_line('copy file name is : ' ||cmn_fileupld_cur_rec.file_size);
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name || 
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;

end if;

EXCEPTION

WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, 'Invalid Mode Parameter');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE, 
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name; 
COMMIT;
goto new_file;

WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, 'Invalid File Location');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;

WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, 'Invalid Filehandle');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;

WHEN utl_file.invalid_operation THEN
-- dbms_output.put_line('in except');
RAISE_APPLICATION_ERROR (-20054, 'Invalid Operation');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path 
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
goto new_file;
WHEN utl_file.read_error THEN
------goto new_file;
-- RAISE_APPLICATION_ERROR (-20055, 'Read Error');
------goto new_file; 
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name || 
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file; 
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, 'Internal Error');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE, 
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name; 
COMMIT;
goto new_file;
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, 'Opened With FOPEN_NCHAR
But Later I/O Inconsistent');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL', 
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, 'File Already Opened'); 
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name || 
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file; 
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,'Line Size Exceeds 32K');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE, 
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path 
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, 'Invalid File Name');
UPDATE CMN_FILEUPLD_T 
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' || 
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, 'File Access Denied By'); 
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name || 
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file; 
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,'FSEEK Param Less Than 0');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE,
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;

goto new_file;




WHEN others THEN 
dbms_output.put_line('other error');
RAISE_APPLICATION_ERROR (-20099, 'Unknown UTL_FILE Error');
UPDATE CMN_FILEUPLD_T
SET file_sts = 'FL',
lst_mdfct_dt = SYSDATE, 
PROC_END_DATE = SYSDATE,
error_msg = 'Error while copying ' || v_cp_file_name ||
' FROM ' || v_src_path || ' to ' ||
v_dest_path 
WHERE CMN_FILEUPLD_T.file_name = cmn_fileupld_cur_rec.file_name;
COMMIT;
goto new_file;
--utl_file.FCOPY(cmn_fileupld_cur_rec.err_file_path,v_log_file_name,cmn_fileupld_cur_rec.file_path,v_log_file_name); 
-- add a label here to go back to the start of the <startofcur>

END;


<<new_file>>
null;

END;
END LOOP;
END LOOP;

----------------------------------***FUNCTIONS I HAVE TRIED
CREATE OR REPLACE function file_exists( p_path in varchar2,
p_fname in varchar2 ) return varchar2
as
l_temp utl_file.file_type;
begin
l_temp := utl_file.fopen( p_path, p_fname, 'r' );
utl_file.fclose( l_temp );
return 'Y';
exception
when utl_file.invalid_path then return 'N';
when utl_file.invalid_filename then return 'N';
when utl_file.invalid_operation then return 'N';
when others then return 'N';
end;
/
----------------------------------***NEXT FUNCTION
CREATE OR REPLACE function file_err( p_path in varchar2,
p_fname in varchar2 ) return varchar2
as
l_temp utl_file.file_type;
begin



/* Open the file for reading. */ 
l_temp := UTL_FILE.FOPEN(p_path ,p_fname,'r'); 
utl_file.fclose( l_temp );
return 'Y';

EXCEPTION 
WHEN UTL_FILE.INVALID_PATH 
then return 'N';
utl_file.fclose( l_temp );
WHEN UTL_FILE.INVALID_MODE 
then return 'N'; 
utl_file.fclose( l_temp );
WHEN UTL_FILE.INVALID_OPERATION 
then return 'N'; 
utl_file.fclose( l_temp );
when others 

then return 'N'; 
utl_file.fclose( l_temp );

END file_err;
/
Embarassed
Re: utl_file halts [message #205974 is a reply to message #205268] Tue, 28 November 2006 04:41 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
I'm definitely not going to read through this mess. Why don't you indent your code?

Also, it would help if you create a smaller test case.
Re: utl_file halts [message #206057 is a reply to message #205268] Tue, 28 November 2006 08:15 Go to previous message
vshari
Messages: 9
Registered: October 2005
Location: India/UK
Junior Member
Hi,
I agree with Frank there whole lot of code and could not be understood
but from what you seem to suggest it seems you have not placed and exceptional handling block in the loop where you are retrieving the files sequentially.

Place the exception handling in the loop where you are using the files in the filesystem.

It would be helpful if you place the shorter form of the code online.

thanks
Previous Topic: transferring records
Next Topic: sequence problem
Goto Forum:
  


Current Time: Mon Dec 05 09:18:00 CST 2016

Total time taken to generate the page: 0.13770 seconds