Solved

Oracle 11gR2 - ORA-00910: specified length too long for its datatype

Posted on 2012-03-16
12
3,950 Views
Last Modified: 2012-03-27
Hi,

In getting this error:

CREATE TABLE Wig_Dynamnt (
            WigDC_Name                       VARCHAR2(50) NOT NULL,
            WigDC_Type                       NUMBER DEFAULT 0 NOT NULL,
            WigDC_InBearer                   NUMBER DEFAULT 2 NOT NULL,
            WigDC_OutBearer                  NUMBER DEFAULT 2 NOT NULL,
            WigDC_Content                    NVARCHAR2(4000) NULL,
            CONSTRAINT XAK1Wig_DynamicContent
            UNIQUE (
             WigDC_Name
            )

                USING INDEX
                PCTFREE 10
                INITRANS 4
                MAXTRANS 255
                TABLESPACE DP_INDEX01
           )
                PCTFREE 10
                PCTUSED 40
                INITRANS 4
                MAXTRANS 255
                TABLESPACE DP_DATA01

Exception message: Error code: 910
                   Oracle error: ORA-00910: specified length too long for its datatype


Which I believe is due to this:
            WigDC_Content                    NVARCHAR2(4000) NULL,

Looking in the internet:

ORA-00910:
 specified length too long for its datatype
Cause:  for datatypes CHAR and RAW, the length specified was > 2000; otherwise, the length specified was > 4000.
Action:  use a shorter length or switch to a datatype permitting a longer length such as a VARCHAR2, LONG CHAR, or LONG RAW


==========

How could I switch to a datatype permitting a longer length such as a VARCHAR2, LONG CHAR, or LONG RAW?

What would it require?

Extra info:
select CLIENT_CHARSET,CLIENT_CONNECTION,CLIENT_OCI_LIBRARY,CLIENT_VERSION,CLIENT_DRIVER,count(*)
from   V$SESSION_CONNECT_INFO
group by CLIENT_CHARSET,CLIENT_CONNECTION,CLIENT_OCI_LIBRARY,CLIENT_VERSION,CLIENT_DRIVER
/

Unknown      Heterogeneous      Unknown      11.2.0.2.0            46
US7ASCII      Homogeneous      Home-based      11.2.0.2.0            10
WE8ISO8859P15      Homogeneous      Full Instant Client      11.2.0.2.0            63
Unknown      Heterogeneous      Unknown      11.2.0.2.0      jdbcthin      21
US7ASCII      Homogeneous      Home-based      11.2.0.2.0      SQL*PLUS      4
0
Comment
Question by:joaotelles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730662
What is your NLS characterset?

From the docs: The maximum length of the column is determined by the national character set definition.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#SQLRF50976

>>How could I switch to a datatype permitting a longer length such as a VARCHAR2, LONG CHAR, or LONG RAW?

I would suggest NCLOB.

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#autoId26

Now what do you mean how do you 'switch'?
0
 
LVL 5

Expert Comment

by:Bajwa
ID: 37730669
Replace NVARCHAR2 to VARCHAR2 or long or nclob and you will be fine

CREATE TABLE Wig_Dynamnt (
            WigDC_Name                       VARCHAR2(50) NOT NULL,
            WigDC_Type                       NUMBER DEFAULT 0 NOT NULL,
            WigDC_InBearer                   NUMBER DEFAULT 2 NOT NULL,
            WigDC_OutBearer                  NUMBER DEFAULT 2 NOT NULL,
            WigDC_Content                    VARCHAR2(4000) NULL,
            CONSTRAINT XAK1Wig_DynamicContent
            UNIQUE (
             WigDC_Name
            )
                USING INDEX
                PCTFREE 10
                INITRANS 4
                MAXTRANS 255
                TABLESPACE USERS_EN
           )
                PCTFREE 10
                PCTUSED 40
                INITRANS 4
                MAXTRANS 255
                TABLESPACE USERS_EN
/

also check this for information about various data types.
http://ss64.com/ora/syntax-datatypes.html
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730677
>>Replace NVARCHAR2 to VARCHAR2 or long and you will be fine

Not if your regular character set is single byte and you need to store multi-byte characters in the NVARCHAR2.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 5

Expert Comment

by:Bajwa
ID: 37730679
True!! I spoke too soon!! I  have edited the comment.
0
 

Author Comment

by:joaotelles
ID: 37730685
What is your NLS characterset?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730688
It still says "Replace NVARCHAR2 to VARCHAR2"

Also changing it to NCLOB makes it a duplicate of my post.
0
 

Author Comment

by:joaotelles
ID: 37730695
What is your NLS characterset?

dpa@appz1 ~> env | grep NLS
NLS_LANG=american_america.WE8ISO8859P15


Is this what you asked?

Or this?

Unknown      Heterogeneous      Unknown      11.2.0.2.0            46
US7ASCII      Homogeneous      Home-based      11.2.0.2.0            10
WE8ISO8859P15      Homogeneous      Full Instant Client      11.2.0.2.0            63
Unknown      Heterogeneous      Unknown      11.2.0.2.0      jdbcthin      21
US7ASCII      Homogeneous      Home-based      11.2.0.2.0      SQL*PLUS      4
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730698
>>What is your NLS characterset?
>>Is this what you asked?
>>Or this?

Neither.

You can see the current NLS settings with V$NLS_PARAMETERS.

http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2073.htm
0
 

Author Comment

by:joaotelles
ID: 37730706
This one:

NLS_LANGUAGE      AMERICAN
NLS_TERRITORY      AMERICA
NLS_CURRENCY      $
NLS_ISO_CURRENCY      AMERICA
NLS_NUMERIC_CHARACTERS      .,
NLS_CALENDAR      GREGORIAN
NLS_DATE_FORMAT      DD-MON-RR
NLS_DATE_LANGUAGE      AMERICAN
NLS_CHARACTERSET      WE8MSWIN1252
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_NCHAR_CHARACTERSET      AL16UTF16
NLS_COMP      BINARY
NLS_LENGTH_SEMANTICS      BYTE
NLS_NCHAR_CONV_EXCP      FALSE


I will try with MCLOB then... as you suggested.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730707
>>>>Is this what you asked?

I was actually asking about your NLS_NCHAR_CHARACTERSET.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37730712
>>NLS_NCHAR_CHARACTERSET      AL16UTF16

I believe this is 2 bytes per character so the MAX length is NVARCAHR2(2000).  Don't quote me on that.  You'll have to look it up in the docs.
0
 
LVL 4

Expert Comment

by:ytarkan
ID: 37732137
If you dont need to store a unicode character (like chineese or georgian) then you dont need 2-byte datatypes, you can simply use VARCHAR2(4000)  or else you have to limit your column to NVARCHAR2(2000) which is the maximum.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question