Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

xml content in CLOB in Oracle 8i

489456Feb 9 2006 — edited Feb 14 2006
We 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.

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 14 2006
Added on Feb 9 2006
1 comment
5,502 views