Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-16
12
Medium Priority
?
4,299 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 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