Invalid NVARCHAR2 length if MaxLength not specified

Invalid NVARCHAR2 length if MaxLength not specified

Postby Dennis Wanke » Fri 11 Apr 2014 12:18

If MaxLength facet in not specified for a NVARCHAR2 property in a SSDL, the length of 16383 is used in the generated database script, causing "ORA-00910: specified length too long for its datatype".
As one can see in the provider manifest, the MaxLength is defined with DefaultValue="2000", which is perfectly correct (if it would have effect):
Code: Select all
    <Type Name="NVARCHAR2" PrimitiveTypeKind="String">
      <FacetDescriptions>
        <MaxLength Minimum="1" Maximum="4000" DefaultValue="2000" Constant="false" />
        ...
      </FacetDescriptions>
    </Type>
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Shalex » Tue 15 Apr 2014 16:12

Dennis Wanke wrote:If MaxLength facet is not specified for a NVARCHAR2 property in a SSDL, the length of 16383 is used in the generated database script

Your target server is Oracle 12c, isn't it? If yes, this is a designed behaviour. For more information, refer to http://www.oracle-base.com/articles/12c/extended-data-types-12cR1.php.

Dennis Wanke wrote:causing "ORA-00910: specified length too long for its datatype"

Please set the MAX_STRING_SIZE parameter of your server to EXTENDED to fix the issue.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Dennis Wanke » Tue 15 Apr 2014 17:12

Yes, it was reproduced with Oracle 12c, but our product has to support several server versions (at least the two latest). And because it's a retail product, we neither can set the MAX_STRING_SIZE parameter ourselves nor can we dictate the customer to do so. The product should just work with any reasonable database configuration of any version supported (say, 11g and 12c).
Of course we can specify MaxLength explicitly for each and every NVARCHAR2 property in our SSDL, but firstly it's very uncomfortable (as there are lot of such properties) - e.g. comparing to SQL Server for that we haven't to do this; and secondly, doing so would have a slightly different meaning: "use always exactly the given length" instead of "use the maximal possible length that guaranteed supported by the target database system" (which is our real intention).
It all raises the question: is there any way for dotConnect to automatically detect if Extended Data Types are enabled on the server? If not, it should be an option to tell dotConnect about it (using a config-file, as the customer cannot recompile the product).
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Shalex » Thu 17 Apr 2014 09:51

We will investigate the question and notify you about the result.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Shalex » Thu 24 Apr 2014 17:02

The behaviour is changed in the new 8.3.146 build of dotConnect for Oracle: now by default the Code-First CreateDatabase()/CreateDatabaseScript()/Code-First Migrations functionality generates the VARCHAR2(4000) and NVARCHAR2(2000) columns for Oracle 12c if the size of the corresponding columns is not set explicitly.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Dennis Wanke » Thu 24 Apr 2014 17:02

The issue seems to be resolved with the version 8.3.146
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: Invalid NVARCHAR2 length if MaxLength not specified

Postby Shalex » Fri 16 May 2014 11:18

The config.DatabaseScript.Column.MaxStringSize configuration option (default value is OracleMaxStringSize.Standard) is added in the latest (8.3.161) build of dotConnect for Oracle to provide the possibility of determining a default size of the VARCHAR2 and NVARCHAR2 columns for Oracle 12c if it was not set explicitly in the Code-First CreateDatabase()/Code-First Migrations functionality.

It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=29592.
Shalex
Devart Team
 
Posts: 7793
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle