Skip to Main Content
  • Questions
  • LOB Error ORA-22275 - "invalid LOB locator specified"

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, iulia.

Asked: February 05, 2016 - 9:45 am UTC

Last updated: February 05, 2016 - 10:18 am UTC

Version: 11g

Viewed 50K+ times! This question is

You Asked

Hello,
I don't understand why you cannot use a blob variable v_blob := empty_blob() as a parameter to the DBMS_LOB package or the OCI but
if you assign the value using an insert first, you can use v_blob as a parameter to DBMS_LOB package or the OCI
insert into customer_profile_sf values(2, 'tst_name2', 'tst_surname2', empty_blob()) returning profile_info into v_blob;

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()?

and Chris said...

It's an implementation restriction of DBMS_LOB:

For the successful completion of DBMS_LOB subprograms, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external file system


http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lob.htm#ARPLS66616

If you just assign empty_blob as in your second example it's not associated with an existing LOB. There's no pointer to a physical location, i.e. it doesn't have a locator.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

iulia, February 05, 2016 - 10:31 am UTC

Thank you:) it's clear now.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here