昨天遇到个BUG,原因是数据库里的clob字段存储的内容有问题,DBLOADER导入的数据有问题,我只是想要跑通程序,故直接在本机的测试库中修改了这个有问题的clob字段,写了一个存储过程,如下
create or replace
procedure UPDATECLOBFORMFILE (TABLENAME varchar2,CLOB_COL_NAME varchar2,P_RID rowid,DIRNAME varchar2,FILENAME varchar2,ISPRINT BOOLEAN)
--tableName : the table's name which you will be update
--clob_col_name : the column'name which type is clob and you will be update
--p_rid : the record 's rowid use for filter
--dirName : the dirctory name you create in oracle which mapping the dirctory in your os
--fileName : the file's name which you want to save
--isPrint : is print the file's context or not
is
P_CLOB clob;
P_UPDATESQL varchar2(200);
P_BFILE bfile;
P_DEST_OFFSET integer:=1;
P_SRC_OFFSET integer:=1;
P_CHARSET varchar2(32);
P_BFILE_CSID number;
P_LANG_CONTEXT integer :=DBMS_LOB.DEFAULT_LANG_CTX;
P_WARNING integer;
P_BUFFER raw(32000);
P_BUFFER_SIZE integer:=32000;
P_OFFSET integer:=1;
begin
--get the db charset id use for load file by suitable charset ,otherwise the context read from file will be garbled
select value into P_CHARSET from V$NLS_PARAMETERS where PARAMETER='NLS_CHARACTERSET';
select NLS_CHARSET_ID(P_CHARSET) into P_BFILE_CSID from DUAL;
--create the dynamic sql str
P_UPDATESQL :='update '||TABLENAME||' set '||CLOB_COL_NAME||'=empty_clob() where rowid=:1 return '||CLOB_COL_NAME||' into :2';
--execute the dynamic sql
execute immediate P_UPDATESQL using P_RID returning into P_CLOB ;
P_BFILE := BFILENAME(DIRNAME,FILENAME);
if (DBMS_LOB.FILEEXISTS(P_BFILE)!=0)
then
DBMS_LOB.FILEOPEN(P_BFILE,DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADCLOBFROMFILE(P_CLOB,P_BFILE,DBMS_LOB.GETLENGTH(P_BFILE),P_DEST_OFFSET,P_SRC_OFFSET,P_BFILE_CSID,P_LANG_CONTEXT,P_WARNING);
if ISPRINT then
--setup the print buffer size
DBMS_OUTPUT.enable (BUFFER_SIZE=>null);
WHILE P_OFFSET<DBMS_LOB.GETLENGTH(P_CLOB) LOOP
DBMS_LOB.read(P_BFILE,P_BUFFER_SIZE,P_OFFSET,P_BUFFER);
P_OFFSET:=P_OFFSET+P_BUFFER_SIZE;
DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(P_BUFFER));
end LOOP;
end if;
DBMS_LOB.FILECLOSE(P_BFILE);--close the file
commit;
else--if the specific file is not exist
dbms_output.put_line('file not found');
rollback;
end if;
--close refcursor;
exception when others then
DBMS_OUTPUT.PUT_LINE('other exception occur,pls check the trace log!');
raise;
end;
可以直接读取文本文件然后将文件存储到数据库的clob字段
调用方式
DECLARE
TABLENAME VARCHAR2(200);
CLOB_COL_NAME VARCHAR2(200);
P_RID ROWID;
DIRNAME VARCHAR2(200);
FILENAME VARCHAR2(200);
ISDEBUG BOOLEAN;
BEGIN
TABLENAME := 'tablename';--表名称
CLOB_COL_NAME := 'columnname';--clob字段名称
P_RID := 'AAAXKyAAGAAAG72AAK';--需要更新clob的记录的rowid
DIRNAME := 'BFILE_DIR';--oracle目录名称
FILENAME := 'aaa.txt';--文件名称
ISDEBUG:=TRUE;
UPDATECLOBFORMFILE(
TABLENAME => TABLENAME,
CLOB_COL_NAME => CLOB_COL_NAME,
P_RID => P_RID,
DIRNAME => DIRNAME,
FILENAME => FILENAME,
ISPRINT => ISDEBUG
);
END;
create directory bfile_dir as 'E:/ora_dir'
然后把要导入成clob的文件放在该目录中
另外如果要查看输出的话,参数isprint传true,同时在会话中打开ouput
set serveroutput on