oracle utl_raw.cast_to_varchar2 不同字符集的行为
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7409662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 03:32:14 来源:igfitidea点击:
utl_raw.cast_to_varchar2 behavior with different character sets
提问by Mike Park
I'm using a pdf package for oracle pl/sql called pl_fpdfto create pdfs on the fly (this is what I have to use at the moment). It works on one database, but doesn't work on the other. I believe I've narrowed down the issue to a difference in character set and the behavior of utl_raw.cast_to_varchar2when trying to convert image binary to ascii (base64).
我正在使用一个用于 oracle pl/sql 的 pdf 包pl_fpdf来动态创建 pdf（这是我目前必须使用的）。它适用于一个数据库，但不适用于另一个数据库。我相信我已经将问题缩小到字符集的差异以及utl_raw.cast_to_varchar2尝试将图像二进制转换为 ascii (base64) 时的行为。
The working character set is WE8MSWIN1252, and the other is AL32UTF8(seems to be much more common these days)
工作字符集是WE8MSWIN1252，另一个是AL32UTF8（这些天似乎更常见）
My question is, how do I make utl_raw.cast_to_varchar2behave the same with AL32UTF8as it does with WE8MSWIN1252so that the resulting base64 image data is correct?
我的问题是，如何使utl_raw.cast_to_varchar2行为与AL32UTF8它相同，WE8MSWIN1252以便生成的 base64 图像数据正确？
Here's the code where I think the issue is. If I'm completely wrong here, then please let me know.
这是我认为问题所在的代码。如果我在这里完全错了，请告诉我。
procedure p_putstream(pData in out NOCOPY blob) is
offset integer := 1;
lv_content_length number := dbms_lob.getlength(pdata);
buf_size integer := 2000;
buf raw(2000);
begin
p_out('stream');
-- read the blob and put it in small pieces in a varchar
while offset < lv_content_length loop
dbms_lob.read(pData,buf_size,offset,buf);
p_out(utl_raw.cast_to_varchar2(buf), false);
offset := offset + buf_size;
end loop;
-- put a CRLF at te end of the blob
p_out(chr(10), false);
p_out('endstream');
exception
when others then
error('p_putstream : '||sqlerrm);
end p_putstream;
采纳答案by user272735
What is p_out? A wrapper around dbms_output.put_line?
什么是p_out？一个包装dbms_output.put_line？
Could this be a client character set issue ? According to the utl_raw.cast_to_varchar2 documentation:
这可能是客户端字符集问题吗？根据utl_raw.cast_to_varchar2 文档：
"When casting to a VARCHAR2, the current Globalization Support character set is used for the characters within that VARCHAR2."
“当转换为 VARCHAR2 时，当前的全球化支持字符集用于该 VARCHAR2 中的字符。”
E.g.
例如
$ export NLS_LANG=AMERICAN_AMERICA.UTF8
$ sqlplus
SQL> select utl_raw.cast_to_varchar2('80') from dual;
UTL_RAW.CAST_TO_VARCHAR2('80')
--------------------------------------------------------------------------------
SQL>
But
但
$ unset NLS_LANG
$ sqlplus
SQL> select utl_raw.cast_to_varchar2('80') from dual;
UTL_RAW.CAST_TO_VARCHAR2('80')
--------------------------------------------------------------------------------
?
SQL>
When database character set is
当数据库字符集是
SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
回答by Richard Sandoz
I solved my own issue. As it turns out, the codebase I am using to import DBF files bastardizes the VARCHAR2 datatype as a RAW. I probably should really rewrite it to build the DBF header using RAW operations. That being said, I just hacked it up some more. In particular, I used nchar_cs, a customized version of utl_raw.cast_to_varchar2 and a customized substr (to return nvarchar2)
我解决了我自己的问题。事实证明，我用来导入 DBF 文件的代码库将 VARCHAR2 数据类型混杂为 RAW。我可能真的应该重写它以使用 RAW 操作构建 DBF 标头。话虽这么说，我只是把它砍了一些。特别是，我使用了 nchar_cs，utl_raw.cast_to_varchar2 的定制版本和定制的 substr（返回 nvarchar2）
select ascii(chr(194)) from dual;
select ascii(chr(194 using nchar_cs)) from dual;
select ascii(chr(193)) from dual;
greatly illustrates the root cause (The top case shows as 0 on my XE installation, but 194 on my 11g enterprise setup). I'm really gunshy about posting this code because it is such a hackjob, but it does work now.
极大地说明了根本原因（最上面的情况在我的 XE 安装中显示为 0，但在我的 11g 企业设置中显示为 194）。我真的很害怕发布这段代码，因为它是一个黑客工作，但它现在确实有效。
create or replace package dbase_fox as
-- procedure to a load a table with records
-- from a DBASE file.
--
-- Uses a BFILE to read binary data and dbms_sql
-- to dynamically insert into any table you
-- have insert on.
--
-- p_dir is the name of an ORACLE Directory Object
-- that was created via the CREATE DIRECTORY
-- command
--
-- p_file is the name of a file in that directory
-- will be the name of the DBASE file
--
-- p_tname is the name of the table to load from
--
-- p_cnames is an optional list of comma separated
-- column names. If not supplied, this pkg
-- assumes the column names in the DBASE file
-- are the same as the column names in the
-- table
--
-- p_show boolean that if TRUE will cause us to just
-- PRINT (and not insert) what we find in the
-- DBASE files (not the data, just the info
-- from the dbase headers....)
procedure load_Table( p_dir in varchar2,
p_file in varchar2,
p_tname in varchar2,
p_cnames in varchar2 default NULL,
p_show in BOOLEAN default FALSE,
p_rownum in BOOLEAN default FALSE);
end;
/
create or replace package body dbase_fox
as
-- Might have to change on your platform!!!
-- Controls the byte order of binary integers read in
-- from the dbase file
BIG_ENDIAN constant boolean default TRUE;
type dbf_header is RECORD
(
version varchar2(25), -- dBASE version number
year int, -- 1 byte int year, add to 1900
month int, -- 1 byte month
day int, -- 1 byte day
no_records int, -- number of records in file,
-- 4 byte int
hdr_len int, -- length of header, 2 byte int
rec_len int, -- number of bytes in record,
-- 2 byte int
no_fields int -- number of fields
);
type field_descriptor is RECORD
(
name varchar2(11),
type char(1),
length int, -- 1 byte length
decimals int -- 1 byte scale
);
type field_descriptor_array
is table of
field_descriptor index by binary_integer;
type rowArray
is table of
varchar2(4000) index by binary_integer;
g_cursor binary_integer default dbms_sql.open_cursor;
function mysubstr(d in varchar2, s in number, l in number) return nvarchar2 is
begin
return substr(d,s,l);
end;
-- Function to convert a binary unsigned integer
-- into a PLSQL number
function to_int( p_data in varchar2 ) return number
is
l_number number default 0;
l_bytes number default length(p_data);
begin
if (big_endian)
then
for i in 1 .. l_bytes loop
l_number := l_number +
ascii(mysubstr(p_data,i,1)) *
power(2,8*(i-1));
end loop;
else
for i in 1 .. l_bytes loop
l_number := l_number +
ascii(mysubstr(p_data,l_bytes-i+1,1)) *
power(2,8*(i-1));
end loop;
end if;
return l_number;
end;
procedure dump( p_data in varchar2 ) is
l_number number default 0;
l_bytes number default length(p_data);
byte_number number;
byte_string nvarchar2 (1);
begin
if( l_bytes > 0 ) then
dbms_output.put_line('toti=' || l_bytes);
for i in 1 .. l_bytes loop
byte_string := substr(p_data,l_bytes-i+1,1);
dbms_output.put_line('i=' || i || ' ref=' || (l_bytes-i+1) || ' val=' || ascii(byte_string));
end loop;
end if;
end;
function mycast( d in varchar2 ) return varchar2 is
--replaces utl_raw.cast_to_varchar2
t varchar2(2000) default '';
l number default length(d)/2;
function h(n in number) return number is
begin
if n > 47 and n < 58 then
return n - 48;
else
return n - 55;
end if;
end;
begin
if( l > 0 ) then
for i in 1 .. l loop
--t := t || substr(d,2*i-1,1) || substr(d,2*i,1);
--dbms_output.put_line('i=' || (2*i-1) || ' val=' || 16*(h(ascii(substr(d,2*i-1,1)))));
--dbms_output.put_line('i=' || (2*i) || ' val=' || (h(ascii(substr(d,2*i,1)))));
--dbms_output.put_line('ii=' || i || ' val=' || ((h(ascii(substr(d,2*i,1))))+16*(h(ascii(substr(d,2*i-1,1))))));
t := t || chr((h(ascii(substr(d,2*i,1))))+16*(h(ascii(substr(d,2*i-1,1)))) using nchar_cs);
end loop;
end if;
return t;
end;
-- Alex from Russia add this function
-- to convert a HexDecimal value
-- into a Decimal value
function Hex2Dec( p_data in varchar2 ) return number
is
l_number number default 0;
l_bytes number default length(p_data);
byte_number number;
byte_string nvarchar2 (1);
begin
if( l_bytes > 0 ) then
for i in 1 .. l_bytes loop
byte_string := substr(p_data,l_bytes-i+1,1);
case byte_string
when 'A' then byte_number:=10;
when 'B' then byte_number:=11;
when 'C' then byte_number:=12;
when 'D' then byte_number:=13;
when 'E' then byte_number:=14;
when 'F' then byte_number:=15;
else byte_number:=to_number(byte_string);
end case;
l_number := l_number + byte_number * power(16,(i-1));
end loop;
return l_number;
else
return 0;
end if;
end;
--Mattia from Italy add this function
function mytrim(p_str in varchar2) return varchar2 is
i number;
j number;
v_res varchar2(100);
begin
for i in 1 .. 11 loop
if ascii(mysubstr(p_str,i,1)) = 0 then
j:= i;
exit;
end if;
end loop;
v_res := mysubstr(p_str,1,j-1);
return v_res;
end mytrim;
-- Routine to parse the DBASE header record, can get
-- all of the details of the contents of a dbase file from
-- this header
procedure get_header
(p_bfile in bfile,
p_bfile_offset in out NUMBER,
p_hdr in out dbf_header,
p_flds in out field_descriptor_array )
is
l_data varchar2(100);
l_hdr_size number default 32;
l_field_desc_size number default 32;
l_flds field_descriptor_array;
begin
p_flds := l_flds;
l_data := mycast(
dbms_lob.substr( p_bfile,
l_hdr_size,
p_bfile_offset ) );
--dump(l_data);
p_bfile_offset := p_bfile_offset + l_hdr_size;
p_hdr.version := ascii( mysubstr( l_data, 1, 1 ) );
p_hdr.year := 1900 + ascii( mysubstr( l_data, 2, 1 ) );
p_hdr.month := ascii( mysubstr( l_data, 3, 1 ) );
p_hdr.day := ascii( mysubstr( l_data, 4, 1 ) );
p_hdr.no_records := to_int( mysubstr( l_data, 5, 4 ) );
--dbms_output.put_line('hdr_len:' || ascii(mysubstr(l_data,9,1)) || ',' || ascii(mysubstr(l_data,10,1)));
p_hdr.hdr_len := to_int( mysubstr( l_data, 9, 2 ) );
p_hdr.rec_len := to_int( mysubstr( l_data, 11, 2 ) );
p_hdr.no_fields := trunc( (p_hdr.hdr_len - l_hdr_size)/
l_field_desc_size );
for i in 1 .. p_hdr.no_fields
loop
l_data := mycast(
dbms_lob.substr( p_bfile,
l_field_desc_size,
p_bfile_offset ));
p_bfile_offset := p_bfile_offset + l_field_desc_size;
p_flds(i).name := mytrim(mysubstr(l_data,1,11));
p_flds(i).type := mysubstr( l_data, 12, 1 );
p_flds(i).length := ascii( mysubstr( l_data, 17, 1 ) );
p_flds(i).decimals := ascii(mysubstr(l_data,18,1) );
end loop;
p_bfile_offset := p_bfile_offset +
mod( p_hdr.hdr_len - l_hdr_size,
l_field_desc_size );
end;
function build_insert
( p_tname in varchar2,
p_cnames in varchar2,
p_flds in field_descriptor_array,
p_rownum in BOOLEAN) return varchar2
is
l_insert_statement long;
begin
l_insert_statement := 'insert into ' || p_tname || '(';
if ( p_cnames is NOT NULL )
then
l_insert_statement := l_insert_statement ||
p_cnames || ') values (';
else
for i in 1 .. p_flds.count
loop
if ( i <> 1 )
then
l_insert_statement := l_insert_statement||',';
end if;
l_insert_statement := l_insert_statement ||
'"'|| p_flds(i).name || '"';
end loop;
--add rownum functionality
if ( p_rownum )
then
l_insert_statement := l_insert_statement ||
',"ROWNUM"';
end if;
l_insert_statement := l_insert_statement ||
') values (';
end if;
for i in 1 .. p_flds.count
loop
if ( i <> 1 )
then
l_insert_statement := l_insert_statement || ',';
end if;
if ( p_flds(i).type = 'D' )
then
l_insert_statement := l_insert_statement ||
'to_date(:bv' || i || ',''yyyymmdd'' )';
else
l_insert_statement := l_insert_statement ||
':bv' || i;
end if;
end loop;
--add rownum functionality
if ( p_rownum )
then
l_insert_statement := l_insert_statement ||
',:bv' || (p_flds.count + 1);
end if;
l_insert_statement := l_insert_statement || ')';
return l_insert_statement;
end;
function get_row
( p_bfile in bfile,
p_bfile_offset in out number,
p_hdr in dbf_header,
p_flds in field_descriptor_array,
f_bfile in bfile,
memo_block in number ) return rowArray
is
l_data varchar2(4000);
l_row rowArray;
l_n number default 2;
f_block number;
begin
l_data := mycast(
dbms_lob.substr( p_bfile,
p_hdr.rec_len,
p_bfile_offset ) );
p_bfile_offset := p_bfile_offset + p_hdr.rec_len;
l_row(0) := mysubstr( l_data, 1, 1 );
for i in 1 .. p_hdr.no_fields loop
l_row(i) := rtrim(ltrim(mysubstr( l_data,
l_n,
p_flds(i).length ) ));
if ( p_flds(i).type = 'F' and l_row(i) = '.' )
then
l_row(i) := NULL;
-------------------working with Memo fields
elsif ( p_flds(i).type = 'M' ) then
--Check is file exists
if( dbms_lob.isopen( f_bfile ) != 0) then
--f_block - memo block length
f_block := Hex2Dec(dbms_lob.substr( f_bfile, 4, to_number(l_row(i))*memo_block+5 ));
--to_number(l_row(i))*memo_block+9 - offset in memo file *.fpt, where l_row(i) - number of
--memo block in fpt file
l_row(i) := mycast(dbms_lob.substr( f_bfile, f_block, to_number(l_row(i))*memo_block+9));
else
dbms_output.put_line('Not found .fpt file');
exit;
end if;
-------------------------------------------
end if;
l_n := l_n + p_flds(i).length;
end loop;
return l_row;
end get_row;
procedure show( p_hdr in dbf_header,
p_flds in field_descriptor_array,
p_tname in varchar2,
p_cnames in varchar2,
p_bfile in bfile,
p_rownum in BOOLEAN )
is
l_sep varchar2(1) default ',';
procedure p(p_str in varchar2)
is
l_str long default p_str;
begin
while( l_str is not null )
loop
dbms_output.put_line( substr(l_str,1,250) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
p( 'Sizeof DBASE File: ' || dbms_lob.getlength(p_bfile) );
p( 'DBASE Header Information: ' );
p( chr(9)||'Version = ' || p_hdr.version );
p( chr(9)||'Year = ' || p_hdr.year );
p( chr(9)||'Month = ' || p_hdr.month );
p( chr(9)||'Day = ' || p_hdr.day );
p( chr(9)||'#Recs = ' || p_hdr.no_records);
p( chr(9)||'Hdr Len = ' || p_hdr.hdr_len );
p( chr(9)||'Rec Len = ' || p_hdr.rec_len );
p( chr(9)||'#Fields = ' || p_hdr.no_fields );
if p_hdr.no_fields > 100 then
return;
end if;
p( chr(10)||'Data Fields:' );
for i in 1 .. p_hdr.no_fields
loop
p( 'Field(' || i || ') '
|| 'Name = "' || p_flds(i).name || '", '
|| 'Type = ' || p_flds(i).Type || ', '
|| 'Len = ' || p_flds(i).length || ', '
|| 'Scale= ' || p_flds(i).decimals );
end loop;
p( chr(10) || 'Insert We would use:' );
p( build_insert( p_tname, p_cnames, p_flds, p_rownum ) );
p( chr(10) || 'Table that could be created to hold data:');
p( 'create table ' || p_tname );
p( '(' );
for i in 1 .. p_hdr.no_fields
loop
--if ( i = p_hdr.no_fields ) then l_sep := ')'; end if;
dbms_output.put
( chr(9) || '"' || p_flds(i).name || '" ');
if ( p_flds(i).type = 'D' ) then
p( 'date' || l_sep );
elsif ( p_flds(i).type = 'F' ) then
p( 'float' || l_sep );
elsif ( p_flds(i).type = 'N' ) then
if ( p_flds(i).decimals > 0 )
then
p( 'number('||p_flds(i).length||','||
p_flds(i).decimals || ')' ||
l_sep );
else
p( 'number('||p_flds(i).length||')'||l_sep );
end if;
elsif ( p_flds(i).type = 'M' ) then
p( 'clob' || l_sep);
else
p( 'varchar2(' || p_flds(i).length || ')'||l_sep);
end if;
end loop;
--add rownum functionality
if ( p_rownum )
then
p( chr(9) || '"ROWNUM" number)' );
end if;
p( '/' );
end;
procedure load_Table( p_dir in varchar2,
p_file in varchar2,
p_tname in varchar2,
p_cnames in varchar2 default NULL,
p_show in BOOLEAN default FALSE,
p_rownum in BOOLEAN default FALSE )
is
l_bfile bfile;
f_bfile bfile;
l_offset number default 1;
l_hdr dbf_header;
l_flds field_descriptor_array;
l_row rowArray;
f_file varchar2(25);
memo_block number;
l_cnt int default 0;
begin
f_file := substr(p_file,1,length(p_file)-4) || '.fpt';
l_bfile := bfilename( p_dir, p_file );
dbms_lob.fileopen( l_bfile );
----------------------- Alex from Russia add this
f_bfile := bfilename( p_dir, f_file );
if( dbms_lob.fileexists(f_bfile) != 0 ) then
dbms_output.put_line(f_file || ' - Open memo file');
dbms_lob.fileopen( f_bfile );
end if;
--------------------------------------------------
get_header( l_bfile, l_offset, l_hdr, l_flds );
if ( p_show )
then
show( l_hdr, l_flds, p_tname, p_cnames, l_bfile, p_rownum );
else
dbms_sql.parse( g_cursor,
build_insert( p_tname, p_cnames, l_flds, p_rownum ),
dbms_sql.native );
-- Memo block size in ftp file
if ( dbms_lob.isopen( f_bfile ) > 0 ) then
memo_block := Hex2Dec(dbms_lob.substr(f_bfile, 2, 7));
else
memo_block := 0;
end if;
for i in 1 .. l_hdr.no_records loop
l_row := get_row( l_bfile,
l_offset,
l_hdr,
l_flds, f_bfile, memo_block );
if ( l_row(0) <> '*' ) -- deleted record
then
for i in 1..l_hdr.no_fields loop
dbms_sql.bind_variable( g_cursor,
':bv'||i,
l_row(i),
4000 );
end loop;
--add rownum functionality
if ( p_rownum )
then
l_cnt := l_cnt + 1;
dbms_sql.bind_variable( g_cursor,
':bv'||(l_hdr.no_fields+1),
l_cnt,
4000 );
end if;
if ( dbms_sql.execute( g_cursor ) <> 1 )
then
raise_application_error( -20001,
'Insert failed ' || sqlerrm );
end if;
end if;
end loop;
end if;
dbms_lob.fileclose( l_bfile );
if ( dbms_lob.isopen( f_bfile ) > 0 ) then
dbms_lob.fileclose( f_bfile );
end if;
--exception
-- when others then
-- if ( dbms_lob.isopen( l_bfile ) > 0 ) then
-- dbms_lob.fileclose( l_bfile );
-- end if;
-- if ( dbms_lob.isopen( f_bfile ) > 0 ) then
-- dbms_lob.fileclose( f_bfile );
-- end if;
-- RAISE;
end;
end;
/
