Loading LOB Columns

This section shows on how to insert data into BLOB, CLOB, BFILE, XMLTYPE (Non-Schema Based) columns.

--Create the Directory Object used to access the File
--that contains the Binary or character data (Eg: pdf, xml)
CREATE OR REPLACE DIRECTORY
testdir AS
'C:\';


--Create the test table to where we are going to import the data
CREATE TABLE testdata
(
numcol NUMBER,
datecol DATE,
tscol TIMESTAMP(6),
varcol VARCHAR2(4000 BYTE),
chrcol CHAR(2000 BYTE),
clobcol CLOB,
blobcol BLOB,
bfilecol BFILE,
xmlcol SYS.XMLTYPE
);



--PLSQL Script that imports the File contents into Database Columns
DECLARE
bfil_ctnt BFILE;
dest_blob BLOB;
dest_clob CLOB;
BEGIN
--Storing the refernce of the XML file into the BFILE variable
bfil_ctnt := BFILENAME ('TESTDIR', 'SalesQuoteSample.xml');
--Opening the File contents for access in the plsql
DBMS_LOB.OPEN (bfil_ctnt, DBMS_LOB.lob_readonly);
-- Inserting a row in testdata tale and creating an empty instance of BLOB,CLOB
--and loading the lob locator in the dest_blob and dest_clob variables
INSERT INTO testdata
(blobcol, clobcol,
xmlcol,
bfilecol
)
VALUES (EMPTY_BLOB (), EMPTY_CLOB (),
XMLTYPE (BFILENAME ('TESTDIR', 'SalesQuoteSample.xml'),
NLS_CHARSET_ID ('AL32UTF8')
),
bfil_ctnt
)
RETURNING blobcol, clobcol
INTO dest_blob, dest_clob;
--Loading the LOB segment with the locator stored in dest_clob, dest_blob
DBMS_LOB.OPEN (dest_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.OPEN (dest_clob, DBMS_LOB.lob_readwrite);
--Loading the contents of the file from Bfil_ctnt to the lob columns
DBMS_LOB.loadfromfile (dest_lob => dest_blob,
src_lob => bfil_ctnt,
amount => DBMS_LOB.getlength (bfil_ctnt)
);
DBMS_LOB.loadfromfile (dest_lob => dest_clob,
src_lob => bfil_ctnt,
amount => DBMS_LOB.getlength (bfil_ctnt)
);
--Closing the LOB opened before exiting from the procedure
DBMS_LOB.CLOSE (dest_blob);
DBMS_LOB.CLOSE (dest_clob);
DBMS_LOB.CLOSE (bfil_ctnt);
COMMIT;
END;
/