不同字符集的 utl_raw.cast_to_varchar2 行为

Question

我正在使用一个名为 pl_fpdf 的 Oracle pl/sql pdf 包来动态创建 pdf（这是我目前必须使用的）。它适用于一个数据库，但不适用于另一个数据库。我相信在尝试将图像二进制文件转换为 ascii (base64) 时，我已将问题范围缩小到字符集和 utl_raw.cast_to_varchar2 行为的差异。

工作字符集是 WE8MSWIN1252 ，另一个是 AL32UTF8 （现在似乎更常见）

我的问题是，如何制作 utl_raw.cast_to_varchar2 对 AL32UTF8 的行为与对 WE8MSWIN1252 的行为相同，因此生成的 base64 图像数据是正确的？

这是我认为问题所在的代码。如果我在这里完全错了，请告诉我。

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;

Answer 1

什么是 p_out ？围绕 dbms_output.put_line 的包装器？

这可能是客户端字符集问题吗？根据 utl_raw.cast_to_varchar2 文档 ：

“转换为 VARCHAR2 时，当前的全球化支持字符集用于其中的字符VARCHAR2。”

例如

$ export NLS_LANG=AMERICAN_AMERICA.UTF8
$ sqlplus
SQL> select utl_raw.cast_to_varchar2('80') from dual;

UTL_RAW.CAST_TO_VARCHAR2('80')
--------------------------------------------------------------------------------
€

SQL>

但是

$ unset NLS_LANG
$ sqlplus
SQL> select utl_raw.cast_to_varchar2('80') from dual;

UTL_RAW.CAST_TO_VARCHAR2('80')
--------------------------------------------------------------------------------
?

SQL>

当数据库字符集是

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               WE8MSWIN1252
NLS_NCHAR_CHARACTERSET         AL16UTF16

Answer 2

我解决了我自己的问题。事实证明，我用来导入 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;

极大地说明了根本原因（最上面的情况在我的 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;
/