xml content in CLOB in Oracle 8i
489456Feb 9 2006 — edited Feb 14 2006We are using Oracle 8i. We have to store the content of an xml file in a CLOB field in the database. Here is the code we are using to insert xml in the CLOB:
CREATE OR REPLACE PROCEDURE test( dir VARCHAR2,
file1 VARCHAR2,
name VARCHAR2 := NULL) IS
theBFile BFILE;
theCLob CLOB;
theDocName VARCHAR2(200) := NVL(name,file1);
BEGIN
-- (1) Insert a new row into xml_documents with an empty CLOB, and
-- (2) Retrieve the empty CLOB into a variable with RETURNING..INTO
INSERT INTO xml_documents(docname,xmldoc) VALUES(theDocName,empty_clob())
RETURN xmldoc INTO theCLob;
-- (3) Get a BFile handle to the external file
theBFile := BFileName(dir,file1);
-- (4) Open the file
dbms_lob.fileOpen(theBFile);
-- (5) Copy the contents of the BFile into the empty CLOB
dbms_lob.loadFromFile(dest_lob => theCLob,
src_lob => theBFile,
amount => dbms_lob.getLength(theBFile));
-- (6) Close the file and commit
dbms_lob.fileClose(theBFile);
COMMIT;
END;
/
The data appears to be inserted but is corrupted. When we query the table, the field value has junk chars like inverted question marks. What could be wrong? Could it be settings on the server or are we missing something?
Please let us know. Thanks in advance.
regards,
Kameshwari.