How To Load CLOB Data from a File into a CLOB column using PL/SQL
来源:互联网 发布:解除windows驱动器锁定 编辑:程序博客网 时间:2024/05/22 03:17
主题:
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 via Oracle Support's Rapid 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 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
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_csid NUMBER := 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
- How To Load CLOB Data from a File into a CLOB column using PL/SQL
- How to import a SQL Server .bak file into MySQL?
- How to read data from a file in reverse order?
- How to: Encrypt a Column of Data
- How to copy a datafile from ASM to a file system not using RMAN
- How to use an ActiveX script task to import data into a new Excel file
- how-to-load-resource-from-jar-file-packaged-in-a-war-file
- How to drag a virtual file from your app into Windows Explorer
- 操作clob 大字段的can bind a LONG value only for insert into a LONG column 异常处理
- insert data into Oracle Clob
- How to load Properties file from a static block or static method
- an example to insert data into Oracle Clob
- How to: Load Unmanaged Resources into a Byte Array
- how to add a member into the PDS using JCL
- How to Convert From Clob to Varchar2 in Oracle
- How to copy a datafile from ASM to a file system not using RMAN (Doc ID 428893.1)
- How to transfer the data from CSV file to Oracle database use the PL/SQL developer tool
- How to divide a data set into equal size buckets
- 重写QAbstractItemModel
- 保存图片到【相机胶卷】和【自定义相册】
- eclipse 导入hadoop源码
- FastJson 栽坑汇总
- 启动Tomcat时一闪而过(Tomcat启动失败)
- How To Load CLOB Data from a File into a CLOB column using PL/SQL
- Windows10 64bit 系统下安装 Anaconda3(Python3)的Theano
- 数据库事务的隔离级别
- C++语言学习指导
- TensorFlow人工智能引擎入门教程之二 CNN卷积神经网络的基本定义理解。
- VMware Fedora固定IP直连路由器
- Linux 技巧:让进程在后台可靠运行的几种方法
- java面试题(五)
- 控制台程序使用GDI+