CREATE OR REPLACE PROCEDURE insertXmlFile( dir VARCHAR2, file VARCHAR2, name VARCHAR2 := NULL) IS theBFile BFILE; theCLob CLOB; theDocName VARCHAR2(200) := NVL(name,file); 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()) RETURNING xmldoc INTO theCLob; -- (3) Get a BFile handle to the external file theBFile := BFileName(dir,file); -- (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; |