Oracle: Reading a Text File into a CLOB

Using Oracle11g, this procedure reads a text file, in this case an xml file, into a CLOB.

DECLARE l_clob CLOB; l_bfile BFILE := BFILENAME('SCRATCH','test.xml'); BEGIN INSERT INTO xml_test ( id, xml ) VALUES ( 2, EMPTY_CLOB() ) RETURNING xml INTO l_clob; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); DBMS_LOB.LOADFROMFILE ( dest_lob => l_clob, src_lob => l_bfile, amount => DBMS_LOB.GETLENGTH(l_bfile) ); DBMS_LOB.CLOSE(l_bfile); END; /

If you need to worry about different character sets etc, then use DBMS_LOB.LOADCLOBFROMFILE, which has several more parameters.

File Read and Inserted. See also: Querying XML from a CLOB