Solved

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

Posted on 2012-03-16
12
3,345 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)
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 76

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

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

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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37730707
>>>>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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Clone Oracle 12c Database 5 43
Converting a row into a column 2 44
Performance issue with case statement in oracle 11G 7 47
SQL Developer 6 33
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

895 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

18 Experts available now in Live!

Get 1:1 Help Now