6

I use node-oracle to connect to an Oracle db.

When I select values from tables with cyrillic data, everything is fine, but if I call a procudure like this:

CREATE OR REPLACE PROCEDURE TEST_ENCODING (CUR OUT SYS_REFCURSOR) AS 
BEGIN
  open cur for
    select 'тест' as hello from dual; -- cyrillic hardcoded text
END TEST_ENCODING;

and then call it from node:

connection.execute("call TEST_ENCODING(:1)", [new oracle.OutParam(oracle.OCCICURSOR)],
  function (err, result) {
    console.log(result)
  }
);

Result is:[ { HELLO: 'те' } ] (the string is cut in half).

The database is configured as follows:

NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CHARACTERSET    CL8MSWIN1251
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_RDBMS_VERSION   11.2.0.3.0

In my local env: NLS_LANG=AMERICAN_AMERICA.UTF8 (also tried NLS_LANG=RUSSIAN_RUSSIA.UTF8 and RUSSIAN_RUSSIA.AL32UTF8 with same results)

My configuration:
Mac OS X 10.9
Oracle Client 11.2
node 0.10.22
node-oracle 0.3.4

MT0
  • 143,790
  • 11
  • 59
  • 117
F0RR
  • 1,590
  • 4
  • 16
  • 30
  • There are many possible sources of this problem: improper font choice, bad NLS_LANGUAGE settings or problems with jdbc driver. Please review [this answer](http://stackoverflow.com/a/18464348/232279) and linked questions and let us know if any solution works for you or not. – ThinkJet Nov 25 '13 at 12:11

3 Answers3

1

  1. Seems that for now there are no support for encodings other then UTF8 in node-oracle because node.js dosn't support native encodings (proof).

  2. To handle strings properly you need to set NLS_LANG parameter on the client to same value as in database (CL8MSWIN1251)

So, you can choice from 2 variants:

A) Migrate database to UTF8 encoding.

B) Patch node-oracle source to convert strings and CLOBs to UTF8 before returning it content to node.js and applying conversion from UTF8 to CL8MSWIN1251 before passing it to Oracle. OCI interface have a functions for such conversions. E.g. for your local purpose it's enough to patch OBJ_GET_STRING macro in utils.h

P.S. node-oracle looks very simplistic at the moment, so be prepared for many surprises (e.g. no support for BLOBs and collections, lack of connections settings and so on).

ThinkJet
  • 6,725
  • 24
  • 33
  • Yeah, it does seem that the problem lies somewhere in the node-oracle string conversions, although it looks like that specific macros is used to put make oracle strings from js, not the other way around... – F0RR Nov 28 '13 at 08:07
0

It could be because your database primary charset is CL8MSWIN1251, when local setting specifies UTF8.

NLS_CHARACTERSET    CL8MSWIN1251

The variable NLS_LANG specifies how to interpret your local environment

NLS_LANG = language_territory.charset

The last part of NLS_LANG provides information about local charset and it is used to let Oracle know what character set you are USING on the client side, so Oracle can do the proper conversion. Probably, values from tables are converted properly, when charset of value from dual table is not identified correctly.

Please try to set NLS_LANG variable to AMERICAN_AMERICA.CL8MSWIN1251 (or RUSSIAN_RUSSIA.CL8MSWIN1251, it doesn't really matter)

Eugene Bobkov
  • 1
  • 2
  • Nope, now I get [{HELLO: '����'}], which has the right length, but not the right content. Also every single query from tables with cyrillic data now returns �s – F0RR Nov 22 '13 at 08:27
  • May I ask you to slightly change original procedure and add convert ('тест','UTF8') instead of just 'тест' and run it with UTF8 local charset? – Eugene Bobkov Nov 25 '13 at 06:44
  • Result is { HELLO: 'С‚Р' } (added buffer representation of a string for debugging purposes). – F0RR Nov 25 '13 at 13:49
  • `AMERICAN_AMERICA.CL8MSWIN1251` must be set on client and number of characters is Ok. What happens if you just oputput constant string `'тест'` to console? Is it displayed properly? I mean `console.log('тест')` ... – ThinkJet Nov 25 '13 at 15:06
  • @F0RR, Hi. I have same problem with AMERICAN_AMERICA.CL8MSWIN1251 in node-oracle. Do you have some solution? Thanks in advance. – indapublic Oct 16 '14 at 07:40
  • It seems that there is some sort of error in computing the length of unicode strings if the DB encoding is not UTF8. I overcame the problem by explicitly casting strings: `CAST (value as VARCHAR2(1024))` where 1024 is an arbitrary length, larger than two times the maximum length of `value` – F0RR Oct 17 '14 at 07:33
0

are you sure that your source code has UTF-8 character set?
if problem only with hardcoded symbols maybe your GUI for Oracle development do not support UTF-8
I have similar problem with special characters like ¥ in my package and sql*plus that convert special characters into some unreadable

Galbarad
  • 461
  • 3
  • 16