Hi,
Oracle :Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Hi,
Im loading csv files to external tables ,Before loading checking the file exists in unix server for all files like below.
Some time input files coming with caps(.CSV) and some time small(.csv).Is there any way to handle the code without hard coding.
here l_file_ext is given .CSV in variable,but some time .csv coming.so it will fail.
set serveroutput on
DECLARE
book_file_loc BFILE := NULL;
book_file_exists BOOLEAN := FALSE;
pi_dir_name varchar2(100);
l_file_name varchar2(100);
l_file_ext varchar2(10):='.CSV';
BEGIN
l_file_name := l_file_name||l_file_ext;
l_file_loc := BFILENAME( pi_dir_name, l_file_name );
l_file_exists := DBMS_LOB.FILEEXISTS( l_file_loc ) = 1;
book_file_loc := BFILENAME( pi_dir_name, l_file_name );
book_file_exists := DBMS_LOB.FILEEXISTS( book_file_loc ) = 1;
IF book_file_exists
THEN
DBMS_OUTPUT.PUT_LINE
('chapter01.txt exists in BOOK_TEXT directory');
ELSE
DBMS_OUTPUT.PUT_LINE
('chapter01.txt DOES not exist in BOOK_TEXT directory');
END IF;
END;
/