ORA-12899: value too large for column actual 12, maximum 10

dsex100 · Post by **dsex100** » Tue May 10, 2011 9:48 pm

Hello

Datastage version: 7.5x2
Platform: windows
Oracle version: 10g r2

Server job description
-----------------------------
sequential file stage -> transformer stage -> ORAOCI9 stage (oracle)

Oracle NLS values
-------------------------

SELECT * FROM NLS_DATABASE_PARAMETERS

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

Datage NLS values
----------------------------

Client/Server map in use: MS1252-CS
Current ANSI code page: 1252
Project default: ISO8859-1
Sequential file stage NLS: Project default (ISO8859-1)
ORAOCI9 stage NLS: Project default (ISO8859-1)

Oracle table name: EMP
Oracle table definition:
-----------------------------------
EMPNO NUMBER(4,0)
ENAME VARCHAR2(10 BYTE)
JOB VARCHAR2(9 BYTE)
MGR NUMBER(4,0)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2,0)

Datastage metadata definition:
-------------------------------------
Column Name SQL Type Length Scale Display
EMPNO Decimal 4 6
ENAME Varchar 10 10
JOB Varchar 9 9
MGR Decimal 4 6
HIREDATE Timestamp 38 20
SAL Decimal 7 2 9
COMM Decimal 7 2 9
DEPTNO Decimal 2 4

I run the job and get:

The value of the row is: EMPNO = 8889 ENAME = ROSS JOB = SLEEPER MGR = 7902 HIREDATE = 17-DEC-01 SAL = 8000 COMM = 88 DEPTNO = 20

ORA-12899: value too large for column "SCOTT"."EMP"."ENAME" (actual: 12, maximum: 10)

The value of ENAME is "ROSS" which is only 4 characters.

What do I need to do to get this working smoothly?

prasad.bodduluri · Post by **prasad.bodduluri** » Wed May 11, 2011 5:17 am

try to increase column length , it will slove your issue.

chulett · Post by **chulett** » Wed May 11, 2011 7:28 am

What is your source characterset? "ROSS" may be only four characters but your target only supports 10 bytes and "ROSS" seems to be 12 bytes.

ray.wurlod · Post by **ray.wurlod** » Wed May 11, 2011 4:20 pm

"ROSS" could very well be 12 bytes.

dsex100 · Post by **dsex100** » Thu May 12, 2011 9:55 pm

chulett wrote:What is your source characterset? "ROSS" may be only four characters but your target only supports 10 bytes and "ROSS" seems to be 12 bytes.

Hello,

My source character set is Project default (ISO8859-1).

dsex100 · Post by **dsex100** » Thu May 12, 2011 9:56 pm

ray.wurlod wrote:"ROSS" could very well be 12 bytes. ...

Hello,

Is there a way to use a user defined datastage environment variable NLS_LANG to make it work?