Problems in OCI remote con when setting NLS_LANG to AL32UTF8

[mrjoltcola]

When I set my client environment NLS_LANG=AMERICAN_AMERICA.AL32UTF8 and connect to a remote database (which has the same NLS settings as my client environment), it causes some queries to return strings with \0\0\0...

Direct mode is ok, it is OCI mode that is the problem.

I found the problem when executing "SELECT DBMS_METADATA.GET_DDL(...) FROM DUAL" on some objects. It will return something like this:

"\n CREATE OR REPLACE TRIGGER \"SCOTT\".\"ACCT_CREATE\" after in\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"

1) It only happens on a remote connection so far, local connection doesn't do it (which doesn't make sense)
2) Happens on 10.2 and 11.2 remote databases
3) Local OCI is 11.2, dotConnect is 6.10
4) sqlplus and other tools work fine
5) Happens for functions that return LONG

Removing NLS_LANG in my local environment causes the problem to go away, but this isn't acceptable to all of my users.

If I change the above query to CAST(... AS VARCHAR2) it returns ok (truncated however).

-ms

[Shalex]

Try using the "Unicode=true;" connection string parameter.

If this doesn't help, give us the following information:
1) the NLS_LANGUAGE, NLS_CHARACTERSET, and NLS_NCHAR_CHARACTERSET parameters of your remote database;
2) your connection string (roughly, without credentials);
3) the DDL script of your ACCT_CREATE trigger to recreate it in our environment;
4) the code you are using to reproduce the problem.

Please post this information here or sent it to us via our contact form: http://www.devart.com/company/contact.html.

[mrjoltcola]

I am not aware of any ODP.NET parameter "Unicode=true".

I always set NLS_LANG in my environment according to Oracle convention.

I’m using OCI mode. So I should not have to “specially detect” Unicode on the remote and set Unicode = true.

None of the other Oracle tools that use OCI work that way. Nor does ODP.NET.

It looks like a bug in DotConnect.

[mrjoltcola]

Just for grins I tried Unicode=true, it has no effect.

[Shalex]

I have tried the following code with dotConnect for Oracle v 6.30.165 and both unicode Oracle server (NLS_CHARACTERSET=AL32UTF8) and Oracle client (NLS_LANG=AMERICAN_AMERICA.AL32UTF8). The issue you have described persists only with "Unicode=false;" but code works OK with "Unicode=true;". Please try this code in your environment and notify us about the results:

Code: Select all

    //CREATE or replace FUNCTION func(param1 IN NUMBER) 
    //   RETURN long 
    //   IS a long;
    //BEGIN 
    //   return a; 
    //END;

    using (OracleConnection conn = new OracleConnection()) {
        conn.ConnectionString = "server=orcl1120u;uid=scott;pwd=tiger;home=OraDb11g_home1;Unicode=true;";
        conn.Open();
        OracleCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT DBMS_METADATA.GET_DDL('FUNCTION','FUNC') FROM DUAL";
        OracleDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
            Console.WriteLine(reader[0].ToString());
    }

[mrjoltcola]

Unicode=true is not a solution for this tool. I do not know what my user's databases may be.

1) Unicode=true is not an ODP.NET parameter. There is no need to use it with ODP.NET
2) Most importantly! ODP.NET works correctly in the same environment that dotConnect fails. When using ODP.NET or OCI there is no need to autodetect character sets, Oracle client works, so please don't suggest I set Unicode=true as a solution, this is against years of Oracle documentation.

I have created a test project that includes both ODP.NET 11.2 and dotConnect 6.30 side by side, demonstrating the problem. ODP.NET works in same environment that dotConnect fails.

Output when NLS_LANG is set

Code: Select all

dotConnect (no Unicode): 
  CREATE OR REPLACE FUNCTION "MSMITH

dotConnect (Unicode=true): 
  CREATE OR REPLACE FUNCTION "MSMITH"."FUNC" (param1 IN NUMBER)
         RETURN long
         IS a long;
      BEGIN
         return a;
      END;
 
ODP.NET: 
  CREATE OR REPLACE FUNCTION "MSMITH"."FUNC" (param1 IN NUMBER)
         RETURN long
         IS a long;
      BEGIN
         return a;
      END;

Output when NLS_LANG is not set:

Code: Select all

dotConnect (no Unicode): 
  CREATE OR REPLACE FUNCTION "MSMITH"."FUNC" (param1 IN NUMBER)
         RETURN long
         IS a long;
      BEGIN
         return a;
      END;

dotConnect (Unicode=true): 
  CREATE OR REPLACE FUNCTION "MSMITH"."FUNC" (param1 IN NUMBER)
         RETURN long
         IS a long;
      BEGIN
         return a;
      END;
 
ODP.NET: 
  CREATE OR REPLACE FUNCTION "MSMITH"."FUNC" (param1 IN NUMBER)
         RETURN long
         IS a long;
      BEGIN
         return a;
      END;

I cannot attach the project here, I will send via support email.

[Shalex]

We have received your test project and are investigating the issue now.

[mrjoltcola]

For now I've put temporary code to check NLS_LANG settings that contain UTF8 or UTF16 prior to connection.

[Shalex]

The bug with reading CLOB value with multibyte character set in OCI mode is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Alladin]

I can't believe after 5 years of struggling for autodetect Unicode-based database - you guys are actually decided to fix it!!!

Cudos!!!

[Shalex]

New build of dotConnect for Oracle 6.30.185 is available for download!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21452 .