Problems in OCI remote con when setting NLS_LANG to AL32UTF8

Problems in OCI remote con when setting NLS_LANG to AL32UTF8

Postby mrjoltcola » Fri 20 May 2011 17:20

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
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Mon 23 May 2011 13:07

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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby mrjoltcola » Thu 02 Jun 2011 02:07

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
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby mrjoltcola » Thu 02 Jun 2011 02:48

Just for grins I tried Unicode=true, it has no effect.
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Fri 03 Jun 2011 12:43

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());
    }
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby mrjoltcola » Fri 03 Jun 2011 16:17

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.
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Mon 06 Jun 2011 09:37

We have received your test project and are investigating the issue now.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby mrjoltcola » Mon 06 Jun 2011 17:36

For now I've put temporary code to check NLS_LANG settings that contain UTF8 or UTF16 prior to connection.
mrjoltcola
 
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Postby Shalex » Fri 01 Jul 2011 12:46

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.
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44

Postby Alladin » Wed 06 Jul 2011 16:54

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

Cudos!!!
Alladin
 
Posts: 149
Joined: Mon 27 Nov 2006 16:18

Postby Shalex » Mon 11 Jul 2011 06:47

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 .
Shalex
Devart Team
 
Posts: 7377
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle