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.

ORA-29292 and XMLType

ORA-29292: file rename operation failed

I was attempting to relocate a xml file to another folder (a folder containing troublesome files) after an unsuccessful attempt of validating it, but the operation failed with the aforementioned error.
Later on i found out that the file had remained open and, as you might expect, you cannot move an open file.

I was trying to load the file into an XMLType column by means of a bfile.

insert into xml_imports(xmlfile)
values(bfilename('IMPORT_DIR','PRODUCTS.XML'));

If the operation succeeds, the file is closed and i can eventually move it to another folder, for archiving purposes for instance, but if the xml parser fails for any reason, the file is left open.
This must be a quirk of the XMLType method dealing with bfiles.

The bad news is that i don't know the handle to the file, so i can't explicitly close it, the only way I know to do this is by disconnecting the session, which is clearly unacceptable.

The only alternative I could see, was to write my own version of "bfilename", getting the content into a clob and then inserting it into the xmltype column.

For some reason Oracle published a function similar to what I need in chapter 3 of the XML DB Developer's Guide of Oracle 10G Release 1.

But this fuction still suffers the same problem as the built-in xmltype/bfilename method, so I had to change it slightly.
For my purposes it was more convenient to pass the bfile locator directly, so I changed also the parameter declaration section of the function and trapped unexpected errors, closing the file if necessary, and the function now looks as follows:

CREATE OR REPLACE
FUNCTION getFileContent(
        file in out bfile,
            charset in varchar2 default 'AL32UTF8')
return CLOB
is
   fileContent     CLOB := NULL;
   dest_offset     number :=  1;
   src_offset      number := 1;
   lang_context    number := 0;
   conv_warning    number := 0;
begin
    DBMS_LOB.createTemporary(fileContent, true, DBMS_LOB.SESSION);
    DBMS_LOB.fileopen(file, DBMS_LOB.file_readonly);
    DBMS_LOB.loadClobfromFile
    (
       fileContent,
       file,
       DBMS_LOB.getLength(file),
       dest_offset,
       src_offset,
       nls_charset_id(charset),
       lang_context,
       conv_warning
    );
    DBMS_LOB.fileclose(file);
    return fileContent;
exception
when others then
    DBMS_LOB.fileclose(file);
    raise;
end;

Please note that as stated in the Oracle DB Developer's Guide, you'll need to dispose of the temporary clob object returned by the function, by calling procedure dbms_lob.freetemporary, as follows:

      ...
      l_tmp_clob := getFileContent(l_proddatafile);
      insert into xml_imports (xmlfile)
                  values(xmltype(l_tmp_clob));
      dbms_lob.freetemporary(l_tmp_clob);
      ...