How To Load CLOB Data from a File into a CLOB column using PL/SQL

主题:	How To Load CLOB Data from a File into a CLOB column using PL/SQL
	文档ID:	437432.1	类型:	HOWTO
	Modified Date:	02-OCT-2007	状态:	MODERATED

In this Document
Goal
Solution

---

This document is being delivered to you viaOracleSupport'sRapid Visibility(RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

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

Goal

The following code demonstates 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 CLOBcolumn usingDBMS_LOB.LOBMAXSIZE which loads until the end of the BFILE is reached.

Thismethodallows the data to be loaded up to the maximum size that the CLOB column can hold. Passing DBMS_LOB.LOBMAXSIZEcauses theprocedure to read the entire BFILE. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.

Solution

CREATE OR REPLACE DIRECTORY workdir as '/tmp/laura'; --Change the directory

DROP TABLE test_clob;

CREATE TABLE test_clob (id NUMBER, col_clob CLOB);

INSERT INTO test_clob VALUES(1, EMPTY_CLOB());

CREATE OR REPLACE PROCEDURE file_to_clob IS
b_fil bfile := BFILENAME('WORKDIR', 'f.txt'); -- Ensure f.txt

exists
v_clob CLOB;
dest_offset NUMBER := 1;
source_offset NUMBER := 1;
src_csidNUMBER:=NLS_CHARSET_ID('UTF8');
lang_ctx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
warn INTEGER;
BEGIN
-- Retrieve the lob locator
SELECT col_clob

INTO v_clob FROM test_clob WHERE id = 1 FOR UPDATE;

-- Open the target CLOB and the source BFILE
DBMS_LOB.OPEN(v_clob, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.OPEN(b_fil, DBMS_LOB.FILE_READONLY);

-- Load the contents of the bfile into the CLOB column
DBMS_LOB.LOADCLOBFROMFILE(v_clob, b_fil,

DBMS_LOB.LOBMAXSIZE,
dest_offset,
source_offset,
src_csid,
lang_ctx, warn);

-- Check for the warning
IF warn = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
DBMS_OUTPUT.PUT_LINE('Some

Characters couldn''t be converted');
END IF;

-- Close the LOBs
DBMS_LOB.CLOSE(v_clob);
DBMS_LOB.CLOSE(b_fil);

END;
/

To test:

SQL>EXEC file_to_clob;

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_LOB.GETLENGTH(col_clob) FROM test_clob;

DBMS_LOB.GETLENGTH(COL_CLOB)
----------------------------
121094