用存储过程 create or replace procedure FileToClob(p_para IN VARCHAR2, p_FileName IN VARCHAR2) is v_FileLocator BFILE; v_CLOBLocator CLOB;
begin UPDATE table SET clobcolumn= EMPTY_CLOB() where columnvalue = p_para;//定位并初始化clob
SELECT clobcolumn INTO v_CLOBLocator FROM table where columnvalue = p_para FOR UPDATE;
-- Initialize the BFILE locator for reading. v_FileLocator := BFILENAME('MYDIR', p_FileName); DBMS_LOB.FILEOPEN(v_FileLocator, DBMS_LOB.FILE_READONLY);
-- Load the entire file into the character LOB. -- This is necessary so that we have the data in -- character rather than RAW variables. DBMS_LOB.LOADFROMFILE(v_CLOBLocator, v_FileLocator,DBMS_LOB.GETLENGTH(v_FileLocator)); -- Clean up. DBMS_LOB.FILECLOSE(v_FileLocator); commit;
EXCEPTION when no_data_found then DBMS_LOB.FILECLOSE(v_FileLocator); when too_many_rows then DBMS_LOB.FILECLOSE(v_FileLocator); WHEN OTHERS THEN -- Close the cursor and file, and reraise. DBMS_LOB.FILECLOSE(v_FileLocator);
RAISE;end FileToClob; 绝对好用,我已经插入300多条记录了 不过首先你要create or replace directory mydir as 'C:\temp';建立你的目录
create or replace procedure FileToClob(p_para IN VARCHAR2, p_FileName IN VARCHAR2) is
v_FileLocator BFILE;
v_CLOBLocator CLOB;
begin
UPDATE table SET clobcolumn= EMPTY_CLOB() where columnvalue = p_para;//定位并初始化clob
SELECT clobcolumn
INTO v_CLOBLocator
FROM table
where columnvalue = p_para
FOR UPDATE;
-- Initialize the BFILE locator for reading.
v_FileLocator := BFILENAME('MYDIR', p_FileName);
DBMS_LOB.FILEOPEN(v_FileLocator, DBMS_LOB.FILE_READONLY);
-- Load the entire file into the character LOB.
-- This is necessary so that we have the data in
-- character rather than RAW variables.
DBMS_LOB.LOADFROMFILE(v_CLOBLocator, v_FileLocator,DBMS_LOB.GETLENGTH(v_FileLocator));
-- Clean up.
DBMS_LOB.FILECLOSE(v_FileLocator);
commit;
EXCEPTION
when no_data_found then
DBMS_LOB.FILECLOSE(v_FileLocator);
when too_many_rows then
DBMS_LOB.FILECLOSE(v_FileLocator);
WHEN OTHERS THEN
-- Close the cursor and file, and reraise.
DBMS_LOB.FILECLOSE(v_FileLocator);
RAISE;end FileToClob;
绝对好用,我已经插入300多条记录了
不过首先你要create or replace directory mydir as 'C:\temp';建立你的目录
且要定义BUFFER为定义为blob,clob类型
---------------------------------------------胡说八道,to_lob 8i是支持的,但pl/sql是无法调用而已,不相信你自己使用sql plus调用to_lob函数试一下
楼上的真是文如其名,佩服佩服 ! 就算beckhambobo记错了,你也不至于这样吧。