How To Load CLOB Data from a File into a CLOB column using PL/SQL (Doc ID 437432.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.3 and later
Information in this document applies to any platform.

***Checked for relevance on 06-Jul-2017***


Goal

The following code demonstrates how to load CLOB data (greater than 32 KBytes) from a file into a CLOB column. 

The package DBMS_LOB is used to access a file stored on the operating system and load that data into a CLOB column. 

The DBMS_LOB.LOADCLOBFROMFILE() procedure loads the contents of the BFILE into the CLOB column using DBMS_LOB.LOBMAXSIZE which loads until the end of the BFILE is reached. 

This method allows the data to be loaded up to the maximum size that the CLOB column can hold. Passing DBMS_LOB.LOBMAXSIZE causes the procedure to read the entire BFILE. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms