Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-16
12
Medium Priority
?
4,578 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 78

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 78

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 78

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 78

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 78

Expert Comment

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

I was actually asking about your NLS_NCHAR_CHARACTERSET.
0
 
LVL 78

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

916 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