Hello Oracle Support Community,
I have this the folowing code:
DROP DIRECTORY EXP_AMEX_ARCH_DIR_ARG_XCARD;
CREATE OR REPLACE DIRECTORY
EXP_AMEX_ARCH_DIR_ARG_XCARD AS
'/common/unify/incoming/AR/AP/CreditCard/AMEX/';
GRANT EXECUTE, READ, WRITE ON DIRECTORY EXP_AMEX_ARCH_DIR_ARG_XCARD TO APPS WITH GRANT OPTION;
GRANT READ, WRITE ON DIRECTORY EXP_AMEX_ARCH_DIR_ARG_XCARD TO PUBLIC;
CREATE TABLE lob_table
(
filename VARCHAR2(128),
blobdata BLOB
);
create or replace procedure LoadFileIntoBLOB( myfilename IN varchar2,
mydirloc IN varchar2)
IS
out_blob blob;
in_file bfile := bfilename(mydirloc, myfilename);
blob_length integer;
BEGIN
/* Obtain the size of the blob file */
dbms_lob.fileopen(in_file, dbms_lob.file_readonly);
blob_length:=dbms_lob.getlength(in_file);
dbms_lob.fileclose(in_file);
/* Insert a new record into the table containing the
filename you have specified and a LOB LOCATOR.
Return the LOB LOCATOR and assign it to out_blob. */
insert into lob_table values (myfilename, empty_blob())
returning blobdata into out_blob;
/* Load the image into the database as a BLOB */
dbms_lob.open(in_file, dbms_lob.lob_readonly);
dbms_lob.open(out_blob, dbms_lob.lob_readwrite);
dbms_lob.loadfromfile(out_blob, in_file, blob_length);
/* Close handles to blob and file */
dbms_lob.close(out_blob);
dbms_lob.close(in_file);
commit;
/* Confirm insert by querying the database
for LOB length information and output results */
blob_length := 0;
select dbms_lob.getlength(blobdata) into blob_length
from lob_table where filename = myfilename;
dbms_output.put_line('Successfully inserted BLOB ''' || myfilename || ''' of size ' || blob_length || ' bytes.');
exception when others then
dbms_output.put_line('Error occurred while inserting BLOB: '|| sqlerrm);
end;
This is really wierd when In run this procedure with these parameters:
exec LoadFileIntoBLOB('total_files.txt','EXP_AMEX_ARCH_DIR_ARG_XCARD');
This run OK: Successfully inserted BLOB 'total_files.txt' of size 2 bytes.
but when I try to run with these parameters:
exec LoadFileIntoBLOB('approvals_pdf','EXP_AMEX_ARCH_DIR_ARG_XCARD'); or
exec LoadFileIntoBLOB('doc1.docx','EXP_AMEX_ARCH_DIR_ARG_XCARD');
I get this errror:
Error occurred while inserting BLOB: ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
I will go crazy with this, I have check everything, as you can see this is the same directory and the files have the same
permissions:
Another really wierd things is that when I changed the name of this file:
exec LoadFileIntoBLOB('total_filesep.txt','EXP_AMEX_ARCH_DIR_ARG_XCARD'); total_filesep.txt instead of total_filesep.txt I get the same error:
Error occurred while inserting BLOB: ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
This is my product version Oracle Database 12c Enterprise Edition 12.1.0.2.0 64bit Production
I really appreciate any insight into what can be the issue.
Best Regards