取mysql clob 乱码_sqlplus下加载CLOB 字段的乱码问题

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

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

select dbms_lob.getlength(introabr) from Dbwebins.Tb_Rationtype where c_rationtype=100009;

--46204

declare

a_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.txt

old 2: b_fil bfile := BFILENAME('FILE_DUMP', '&filename');

new 2: b_fil bfile := BFILENAME('FILE_DUMP', '1.txt');

Procedure created.

SQL> set heading off

SQL> set line 1000

SQL> set long 50000

SQL> 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 -- @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;

/