Solved

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

Posted on 2012-03-16
12
3,228 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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
 
LVL 5

Expert Comment

by:Bajwa
Comment Utility
True!! I spoke too soon!! I  have edited the comment.
0
 

Author Comment

by:joaotelles
Comment Utility
What is your NLS characterset?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
It still says "Replace NVARCHAR2 to VARCHAR2"

Also changing it to NCLOB makes it a duplicate of my post.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:joaotelles
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>>>Is this what you asked?

I was actually asking about your NLS_NCHAR_CHARACTERSET.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now