Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Connor McDonald

Thanks for the question, Xiaohe.

Asked: February 23, 2017 - 10:04 am UTC

Last updated: February 24, 2017 - 7:46 am UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Sir,

I want to know when I would you a NClob and can not use a Clob ? The character of the database is now US7ASCII. I test using some Chinese and Japanese characters. Looks like I can get back what I inserted on the Clob column, but for NClob column I see some question marks.

SQL> create table test(a clob, b nclob);
SQL> insert into test values (to_clob('你あ'), to_nclob('你あ'));
SQL> col a for a10
SQL> col b for a10
SQL> select * from test;

A    B
---------- ----------
你あ    ??????

SQL> select parameter, value from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER                   VALUE
--------------------------- --------------------
NLS_CHARACTERSET            US7ASCII
NLS_NCHAR_CHARACTERSET      UTF8


Also using JDBC, why the length method of both Clob and NClob seems to return the bytes not the number of characters ? I don't get what I inserted as well. (See some unreadable characters).

public static void main(String[] args) {
    try {
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1522/pdb1", "restdev", "restdev");
      Statement stmt = conn.createStatement();
      stmt.execute("insert into test values (to_clob('你あ'), to_nclob('你あ'))");
      ResultSet rs = stmt.executeQuery("select * from test");
      rs.next();
      Clob c = rs.getClob(1);
      NClob nc = rs.getNClob(2);
      System.out.println(c.length());
      System.out.println(nc.length());
      System.out.println(c.getSubString(1, 10));
      System.out.println(nc.getSubString(1, 10));
      stmt.close();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }


The output is:
6
6
￤ﾽﾠ￣ﾁﾂ
������


Thanks,
Xiaohe

and Connor said...

You need to be very careful when dealing with "lossy" character sets, ie, ones that cannot hold all the information you need. Just because we store the data does not mean it will be correctly represented later.

For example, consider the following 8bit characters (stored in my database which is WE8MSWIN1252, ie, an 8bit charset).

NLS_CHARS

If I select them back to a client with NLS set to US7ASCII, then I'll get 'a' for all three results, ie, I'll lose all the accents etc. Similarly, if I stored them in a US7ASCII database, they would still probably be stored fine, and possibly even displayed fine, but that is "good luck" more than anything else - we're relying on some bits to align. If you need to style multibytes characters, then you want the entire database to be a multibyte charset (like UTF8) or you would use the N-datatype columns (nvarchar2,nclob).

The example you gave for example, is fine in both cases for a *multibyte* database (eg this is my UTF8 database)

NLS_CHARS2

For Java, check out MOS note 2190241.1. It's got a series of examples on specifying/inserting/updating characters for nclob.

Is this answer out of date? If it is, please let us know via a Comment