hi all,
followed this link to read files in a folder and it works fine
https://blogs.oracle.com/oraclemagazine/preprocess-external-tables
now I want to select each file ( select... loop ) and insert in a table, to achieve it i created oracle directory to point to actual physical folder on file system ( Winows 8.1 ) and trying following pl/sql block but failed, please guide how I can solve this problem:
here MYDIR i created. ( create directory MYDIR as 'c:\temps\'; grant read, write on directory mydir to hr; )
DECLARE
m_dir varchar2(20) := 'MYDIR';
src_lob BFILE; -- := BFILENAME('MYDIR', '95849176-10458-FSG-Report-Writting.pdf');
dest_lob BLOB;
xx number:=0;
ff varchar2(128);
BEGIN
for i in (select file_name from ext_tab where file_name like '%pdf%')
loop
xx:=xx+1;
ff := i.file_name;
dbms_output.put_line (ff);
src_lob := BFILENAME(m_dir, ff);
INSERT INTO uploaded_files (file_name, file_uploaded) VALUES (ff, EMPTY_BLOB())
RETURNING file_uploaded INTO dest_lob;
--
DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
SRC_LOB => src_lob,
AMOUNT => DBMS_LOB.GETLENGTH(src_lob) );
DBMS_LOB.CLOSE(src_lob);
end loop;
COMMIT;
END;
/
when hardcoding file name (as above in code) then it works perfectly but from for loop it is showing error as below:
SQL> /
95849176-10458-FSG-Report-Writting - Copy.pdf
DECLARE
*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 1014
ORA-06512: at line 17
note that, file name in folder it is showing here.
regards