Hello,
Could you please help me with this issue?
I am trying to load the contents of a file on the disk to a BLOB column.
The table looks like this:
create table customer_profile_sf
(customer_id number,
first_name varchar2(40),
last_name varchar2(80),
profile_info blob)
lob(profile_info) store as securefile(tablespace sf_tbs1);
I am trying to understand the following:
I have this procedure that reads the file in a blob variable and then tries to inserts it in the table.
If my procedure looks like below, then I get the error:
create or replace procedure write_sf_1 is
v_blob blob;
v_dir varchar2(20) := 'DIR_TST';
v_file_name varchar2(50) := 'surname.name.docx';
v_bfile bfile;
v_amount integer;
begin
insert into customer_profile_sf values(2, 'tst_name2', 'tst_surname2', empty_blob()) returning profile_info into v_blob;
v_amount := 4000;
v_bfile := bfilename(v_dir, v_file_name);
dbms_lob.open(v_bfile, dbms_lob.lob_readonly);
v_amount := dbms_lob.getlength(v_bfile);
DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, v_amount);
dbms_lob.close(v_bfile);
commit;
end;
but if i change the procedure, and I remove the insert in bold and I replace it with the 2 instructions in bold from below, I get this error at the instruction:
DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, v_amount);
22275. 00000 - "invalid LOB locator specified"
create or replace procedure write_sf_1 is
v_blob blob;
v_dir varchar2(20) := 'DIR_TST';
v_file_name varchar2(50) := 'prenume.nume.doc';
v_bfile bfile;
v_amount integer;
begin
v_blob := empty_blob();
v_amount := 4000;
v_bfile := bfilename(v_dir, v_file_name);
dbms_lob.open(v_bfile, dbms_lob.lob_readonly);
v_amount := dbms_lob.getlength(v_bfile);
DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, v_amount);
insert into customer_profile_sf values(2, 'tst_name2', 'tst_surname2', v_blob);
dbms_lob.close(v_bfile);
commit;
end;
I found this in the oracle documentation : https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions049.htm
"Restriction on LOB Locators You cannot use the locator returned from this function as a parameter to the DBMS_LOB package or the OCI."
but isn't it the same in the first place too? just a different way of assigning the variable v_blob with empty_blob()?