sqlplus下加载CLOB 字段的乱码问题

tom 在 expert one on one 中提到了 LOB 类型加载到数据库的方法

tom大师的结果当然不能怀疑。于是我在生产上用sqlplus 做了这个实际操作。之前都用dev来更新。

select dbms_lob.getlength(introabr) from Dbwebins.Tb_Rationtype where c_rationtype=100009;--46204declarea_clob clob;bfile_name bfile :=bfilename('BUS_DMP','1.txt');begin  update Dbwebins.Tb_Rationtype set introabr=empty_clob() where c_rationtype=100009  returning introabr into a_clob;  dbms_lob.fileopen(bfile_name);  dbms_lob.loadfromfile(a_clob,bfile_name,dbms_lob.getlength(bfile_name));  dbms_lob.fileclose(bfile_name);  commit;end;select dbms_lob.getlength(introabr) from Dbwebins.Tb_Rationtype where c_rationtype=100009;--84942

--这里发现的确是加载了数据进去。但是数据查询出来为乱码。
--难道是tom的方法有误？查询了mos 发现 文档ID 562717.1，说明了这个问题，继续看解决办法ID 437432.1
--问题的出现是存储过程dbms_lob.loadfromfile 存在bug,加载file的时候 因为bfile为二进制类型,对于二进制类型
--加载的转换该存储过程不能正确分辨。
--问题的解决：使用 loadclobfromfile/loadblobfromfile 过程来替代loadfromfile。

SQL>  CREATE TABLE test_clob (id NUMBER, col_clob CLOB);Table created.SQL> INSERT INTO test_clob VALUES(1, EMPTY_CLOB());1 row created.SQL> commit;Commit complete.

 

SQL> CREATE OR REPLACE PROCEDURE file_to_clob IS     2   b_fil bfile := BFILENAME('FILE_DUMP', '&filename');   3   -- Ensure f.txt exists    v_clob CLOB;    dest_offset NUMBER := 1;   source_offset NUMBER := 1;     7   src_csid NUMBER := NLS_CHARSET_ID('ZHS16GBK');   lang_ctx INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;     9   warn INTEGER;  BEGIN    11   -- Retrieve the lob locator    SELECT col_clob INTO v_clob FROM test_clob WHERE id = 1 FOR UPDATE;     13   -- 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);   16   -- 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);  18   -- Check for the warning    IF warn = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN      DBMS_OUTPUT.PUT_LINE('Some Characters couldn''t be converted');    -- Close the LOBs    END IF;      22   -- Close the LOBs    23   DBMS_LOB.CLOSE(v_clob);    24   DBMS_LOB.CLOSE(b_fil);  END;  26   /Enter value for filename: 1.txtold   2:  b_fil bfile := BFILENAME('FILE_DUMP', '&filename');new   2:  b_fil bfile := BFILENAME('FILE_DUMP', '1.txt');Procedure created.

SQL> set heading offSQL> set line 1000SQL> set long 50000SQL> select * from test_clob;         1 --------------------------------------------------------------------------------           --           -- File name:   latchprof.sql ( Latch Holder Profiler )           -- Purpose:     Perform high-frequency sampling on V$LATCHHOLDER           --              and present a profile of latches held by sessions           --           -- Author:      Tanel Poder           -- Copyright:   (c) http://www.tanelpoder.com           --           -- Usage:       @latchprof <what> <sid> <latch name> <#samples>           --              @latchprof name 350 % 100000                - monitor all           latches SID 350 is holding           --              @latchprof sid,name % library 1000000       - monitor which           SIDs hold latches with "library" in their name           --              @latchprof sid,name,laddr % 40D993A0 100000 - monitor which           SIDs hold child latch with address 0x40D993A0           -- Other:           --              The sampling relies on NESTED LOOP join method and having           --              V$LATCHHOLDER as the inner (probed) table. Note that on 9i           --              you may need to run this script as SYS as it looks like           otherwise           --              the global USE_NL hint is not propagated down to X$ base           tables           --           --              The join in exec plan step 8 MUST be a NESTED LOOPS join, this           is how           --              the high speed sampling of changing dataset from V$LATCHHOLDE

--结果正常。

--保存下mos 的解决办法

 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('FILE_DUMP', '&filename');  -- Ensure f.txt exists    v_clob CLOB;    dest_offset NUMBER := 1;   source_offset NUMBER := 1;    src_csid NUMBER := NLS_CHARSET_ID('ZHS16GBK');   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;  /