Oracle DB - CLOB file in English loaded with Chinese characters

 I tried to follow AskTom blog - Load JSON data file into Oracle 12c. Unfortunately my was one of the rare cases that appear where the English file, loaded to CLOB in the Oracle database was loaded in Chinese characters. Since I didn't find detailed solution, I wrote this post.

Readers Digest:  dbms_lob.loadfromfile() does not have a character set parameter. Use dbms_lob.loadclobfromfile () instead.  

Starting position Oracle DB 19 on my Windows laptop with the standard AL32UTF8 character set:

To simplify the story, lets assume I'm loading a simple file. In my case I placed in a directory 'TEST_DIR' the file test.csv with the content of a single string:

I created a table with a CLOB column (my_json is just a name in this case):

create table t2 ( my_json clob);

Running the loading process following the above mentioned post:

declare
    b bfile := bfilename('TEST_DIR','test.csv');
    c clob;
  begin
    dbms_lob.open(b);
    dbms_lob.createtemporary(c,true);

    dbms_lob.loadfromfile(c,b,dbms_lob.getlength(b));
    insert into t2 values (c);
    commit;
    dbms_lob.freetemporary(c);
  end;
  / 

The result is in Chinese:

After a fair amount of digging found a clue hidden in the middle of this stackoverflow case and a detailed syntax example in this very old (8i) oracle-base page.

As I mentioned above the problem is because dbms_lob.loadfromfile() does not have a character set parameter. Using dbms_lob.loadclobfromfile () instead, solved the problem. This has nothing to do with the database NLS_Characterset parameter.

 

The changes are in bold:

 declare
    b bfile := bfilename('TEST_DIR','test.csv');
    c clob;
    l_dest_offset   integer := 1;
    l_src_offset    integer := 1;
    l_bfile_csid    number  := 0;
    l_lang_context  integer := 0;
    l_warning       integer := 0;
  begin
    dbms_lob.open(b);
    dbms_lob.createtemporary(c,true);
    dbms_lob.loadclobfromfile (
    dest_lob      => c,
    src_bfile     => b,
    amount        => dbms_lob.getlength(b),
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
    insert into t2 values (c);
    commit;
    dbms_lob.freetemporary(c);
  end;
  /

If we return to the JSON loading example from AskTom, this is the JSON loading that solved my problem:

create table t ( my_json clob, constraint chk check ( my_json is json ));

declare
    b bfile := bfilename('TEST_DIR','test.json');
    c clob;
  l_dest_offset   integer := 1;
  l_src_offset    integer := 1;
  l_bfile_csid    number  := 0;
  l_lang_context  integer := 0;
  l_warning       integer := 0;
  begin
    dbms_lob.open(b);
    dbms_lob.createtemporary(c,true);
    --dbms_lob.loadclobfromfile(c,b,dbms_lob.getlength(b),1,1,1,873,0);
    dbms_lob.loadclobfromfile (
    dest_lob      => c,
    src_bfile     => b,
    amount        => dbms_lob.getlength(b),
    dest_offset   => l_dest_offset,
    src_offset    => l_src_offset,
    bfile_csid    => l_bfile_csid ,
    lang_context  => l_lang_context,
    warning       => l_warning);
    insert into t values (c);
    commit;
    dbms_lob.freetemporary(c);
  end;
  /