1

Is there any difference between java.sql.Clob and java.sql.NClob? There is no new method for java.sql.NClob interface. I tried the following:

The setup SQL:

create table tab(id number(2), clobcol clob, nclobcol nclob)
insert into tab values (1, to_clob('你好'), to_nclob('你好'))

JDBC code: 

conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from tab");
rs.next();
Clob c = rs.getClob(2);
NClob nc = rs.getNClob(3);
InputStream inputStream1 = c.getAsciiStream();
InputStream inputStream2 = nc.getAsciiStream();
System.out.println(inputStream1.available());
System.out.println(inputStream2.available());
c.free();
nc.free();

I have also tried some other methods, looks like there is no difference from the output. Is there a specific I can see some differences ?

Added the supported character set in the database:

SELECT parameter, value
  FROM v$nls_parameters
  3   WHERE parameter LIKE '%CHARACTERSET';

PARAMETER             VALUE
--------------------------------- --------------------
NLS_CHARACTERSET          AL32UTF8
NLS_NCHAR_CHARACTERSET        AL16UTF16
user2018791
  • 1,143
  • 15
  • 29
  • NCLOBs columns uses National Character set of your database and CLOB uses Character Set of your database. When you have special kind of data which is not supported by default character set of your database then you can create N(CLOB,VARCHAR2, etc) columns to store the data. http://stackoverflow.com/questions/6854609/what-is-the-difference-between-clob-and-nclob – atokpas Feb 21 '17 at 04:29
  • Yeah, I read that thread before posting this question. I can not come up with a example to see the difference. – user2018791 Feb 21 '17 at 04:59
  • There is no difference if both character sets are able to store the characters you enter. If I'm not mistaken there are only very few characters that can be represented in UTF-16 that can not be represented in UTF-8. There would be a difference if `NLS_CHARACTERSET` was a single byte character set. –  Feb 21 '17 at 07:26

1 Answers1

2

In the old days (80s) many Databases were created using US7ASCII (in the US) or ISOLATIN1 (in Europe) as the character set. For these Databases that still exist today (after many upgrades), the only way to store non-ASCII character String data is to use the special types NVARCHAR or NCLOB. These Nxxx types are not used by newer Databases that were created directly using UTF8 (now the default in Oracle) as the encoding.

Jean de Lavarene
  • 3,461
  • 1
  • 20
  • 28
  • Are there any side-effects/downsides with Nxxx type columns? We plan to use it to store non-ascii characters since our databases are configured to be single byte for performance reasons. – Andy Dufresne Nov 29 '18 at 07:25