Thursday, July 03, 2008

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You may see this error when invoking procedure DBMS_LOB.LOADCLOBFROMFILE as follows:
create or replace
FUNCTION LoadTempClobFromFile (
p_Dir IN VARCHAR2,
p_FileName IN VARCHAR2,
p_csid IN INTEGER)
RETURN CLOB IS

l_srcFile BFILE := BFILENAME(p_Dir, p_FileName);
l_tmpClob CLOB;
l_warning INTEGER;
l_dest_offset INTEGER := 1;
l_src_offset INTEGER := 1;
l_lang INTEGER := 0;

BEGIN

DBMS_LOB.CREATETEMPORARY(l_tmpClob, TRUE, DBMS_LOB.SESSION);
BEGIN
-- DBMS_LOB.OPEN(l_srcFile);
DBMS_LOB.LOADCLOBFROMFILE(dest_lob => l_tmpClob,
src_bfile => l_srcFile,
amount => DBMS_LOB.LOBMAXSIZE,
dest_offset => l_dest_offset,
src_offset => l_src_offset,
bfile_csid => p_csid,
lang_context => l_lang,
warning => l_warning);
-- DBMS_LOB.CLOSE(l_srcFile);
EXCEPTION
WHEN OTHERS THEN
DBMS_LOB.FREETEMPORARY(l_tmpClob);
RAISE;
END;

RETURN l_tmpClob;

END;
/

DECLARE
mydoc CLOB;
BEGIN
mydoc := LoadTempClobFromFile('IMPORT_DIR', 'some_utf8_file.txt', 873);
-- do something with the CLOB
--
-- eventually release the resource
DBMS_LOB.FREETEMPORARY(mydoc);
END;

ORA-22289: cannot perform LOADFROMFILE operation on an unopened file or LOB
Note that indeed i didn't explicitly open the BFILE prior to reading the file (the relevant line is commented out), on the other hand the official documentation for PL/SQL packages and Types version 10GR1 doesn't state that opening the file is mandatory, on the contrary it says:
"It is not mandatory that you wrap the LOB operation inside the Open/Close APIs".
However the Application Developer's Guide for Large Objects (10GR2) does.

This is not the only problem with the instructions.
The dest_lob parameter should be CLOB not BLOB and parameter src_csid doesn't exist, the right name is bfile_csid.
In the documentation for 10GR2 the parameter name has been amended, but the wrong BLOB type remains. Finally, in 11GR1 all typos have been fixed, but i could not verify if one can omit to wrap the call between DBMS_LOB.OPEN and DBMS_LOB.CLOSE, probably not.

See message translations for ORA-22289 and search additional resources.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles