Friday, November 18, 2005

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);
...

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