Home » SQL & PL/SQL » SQL & PL/SQL » losing NATIONAL CHARACTERS(blob->clob->table). unistr?
losing NATIONAL CHARACTERS(blob->clob->table). unistr? [message #188920] Tue, 22 August 2006 07:03 Go to next message
tom01
Messages: 29
Registered: July 2006
Junior Member
Hello!

I have a problem with national characters. My example is as follows:

1. A csv file is uploaded from disk to htmldb_application_files
2. This BLOB is then converted to CLOB with dbms_lob.converttoclob()
3. Data from this CLOB is copied to PL/SQL array.
4. From PL/SQL array to table in database.

The problem: Either data copied to table in database loses national characters (display strange characters instead of national), or if I set my national character set id as an argument of dbms_lob.converttoclob() function I have an error - says that file is inconvertible.

What is wrong? How can I solve my problem? Can unistr() help somewhere? Any ideas?

Tom
Re: losing NATIONAL CHARACTERS(blob->clob->table). unistr? [message #189229 is a reply to message #188920] Wed, 23 August 2006 13:43 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
1.)confirm the characterset of your database:
select * from v$nls_parameters where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY', 'NLS_CHARACTERSET');

NLS_LANGUAGE         AMERICAN
NLS_TERRITORY        AMERICA
NLS_CHARACTERSET     AL32UTF8

The NLS_LANG is determined as follows:
NLS_LANG=<NLS_LANGUAGE>_<NLS_TERRITORY>.<NLS_CHARACTERSET>
i.e.
NLS_LANG=AMERICAN_AMERICA.AL32UTF8


2.)Set the NLS_LANG in the environment before your application server starts to be compatible with the database setting (or maybe just specify the correct characterset in your DAD).

3.) examine the clob data in the table after uploading to see if the error occurs when uploading - or just displaying it. Don't rely on Windows apps to be able correctly display non-ascii characters.

select substr(my_clob, 1, 80),
dump(substr(my_clob, 1, 80)),
dump(substr(my_clob, 1, 80), 1010),
dump(substr(my_clob, 1, 80), 1016) from my_table;
Re: losing NATIONAL CHARACTERS(blob->clob->table). unistr? [message #189614 is a reply to message #189229] Fri, 25 August 2006 04:33 Go to previous messageGo to next message
tom01
Messages: 29
Registered: July 2006
Junior Member
Hello Andrew!!

Thanks for your response!

Unfortunately I have problems with your solution. Look:

declare
v_text_file clob;
v_binary_file blob;
v_dest_offset INTEGER := 1;
v_src_offset INTEGER := 1;
v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
v_warning INTEGER;
csid number(3) := dbms_lob.default_csid;

begin

select blob_content into v_binary_file from htmldb_application_files where filename = 'test.csv';
DBMS_LOB.createtemporary (v_text_file, TRUE);
DBMS_LOB.converttoclob (v_text_file,v_binary_file, DBMS_LOB.lobmaxsize, v_dest_offset, v_src_offset, csid, v_lang_context, v_warning);

select substr(v_text_file, 1, 80),
dump(substr(v_text_file, 1, 80)),
dump(substr(v_text_file, 1, 80), 1010),
dump(substr(v_text_file, 1, 80), 1016) from dual;

end;

And I got an error:

ORA-06550: line 16, column 6:
PL/SQL: ORA-00932: inconsistent datatypes: expected - got CLOB

Can you help me?

Best regards,
Tom
Re: losing NATIONAL CHARACTERS(blob->clob->table). unistr? [message #189721 is a reply to message #189614] Fri, 25 August 2006 19:10 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Sorry - I didn't test the code. Try this:

JServer Release 9.2.0.6.0 - Production

SQL> CREATE TABLE CLOB_TAB
  2  (  PK          NUMBER,
  3    CLOB_COL    CLOB);

Table created.

SQL> -- Unicode codepoint 20AC is a Euro sign (stored as 3 bytes in UTF8)
SQL> -- 1010 is Decimal dump
SQL> -- 1016 is Hex dump
SQL> -- AL32UTF8 confirms what codepage the data is stored in
SQL> insert into clob_tab values (1, 'abc'||unistr('\20ac')||'123');

1 row created.

SQL> commit;

Commit complete.

SQL> select dbms_lob.substr(clob_col, 80, 1),
  2  dump(dbms_lob.substr(clob_col, 80, 1)),
  3  dump(dbms_lob.substr(clob_col, 80, 1), 1010),
  4  dump(dbms_lob.substr(clob_col, 80, 1), 1016) from clob_TAB;

DBMS_LOB.SUBSTR(CLOB_COL,80,1)
--------------------------------------------------------------------------------
DUMP(DBMS_LOB.SUBSTR(CLOB_COL,80,1))
--------------------------------------------------------------------------------
DUMP(DBMS_LOB.SUBSTR(CLOB_COL,80,1),1010)
--------------------------------------------------------------------------------
DUMP(DBMS_LOB.SUBSTR(CLOB_COL,80,1),1016)
--------------------------------------------------------------------------------
abc¤123
Typ=1 Len=9: 97,98,99,226,130,172,49,50,51
Typ=1 Len=9 CharacterSet=AL32UTF8: 97,98,99,226,130,172,49,50,51
Typ=1 Len=9 CharacterSet=AL32UTF8: 61,62,63,e2,82,ac,31,32,33


SQL> 
Previous Topic: Language Text Conversion to UTF8 -
Next Topic: Dates and Sorting Data
Goto Forum:
  


Current Time: Fri Dec 09 17:47:58 CST 2016

Total time taken to generate the page: 0.31634 seconds