Formatted length of fields & record<declared length-W
Moderators: chulett, rschirm, roy
Formatted length of fields & record<declared length-W
Hi,
I am getting the following warning in datastage director log:
oraInsertData: When checking operator: Formatted length of fields and record (272) less than declared length (273)
oraInsertData: is the name of oracle stage in the job.
In my job, I am reading data using a sequential file, performing transformations using a transformer and then loading data into oracle.
Job is running successfully and the data is also loaded properly into the table. But why is the job giving this warning??
Thank you.
Regards,
ssunda.
I am getting the following warning in datastage director log:
oraInsertData: When checking operator: Formatted length of fields and record (272) less than declared length (273)
oraInsertData: is the name of oracle stage in the job.
In my job, I am reading data using a sequential file, performing transformations using a transformer and then loading data into oracle.
Job is running successfully and the data is also loaded properly into the table. But why is the job giving this warning??
Thank you.
Regards,
ssunda.
Hi Arnd,
I have copy pasted the error as it is. Yes, it is referring to the oracle stage for the error in the director log .
In the transformer, I am converting some decimal fields to date type( decimal->string->date) and some decimal fields to varchar. Just FYI.
I have copy pasted the error as it is. Yes, it is referring to the oracle stage for the error in the director log .
In the transformer, I am converting some decimal fields to date type( decimal->string->date) and some decimal fields to varchar. Just FYI.
Last edited by ssunda6 on Tue Jul 10, 2007 3:16 am, edited 1 time in total.
Hi,
I am using "LOAD" option for write method in oracle stage.( So, this will use SQL Loader ). Total there are 24 columns in the Input file and 16 columns in the target table.
Total length is neither 272 nor 273.
The following is the metadata of table.
Regards,
ssunda.
I am using "LOAD" option for write method in oracle stage.( So, this will use SQL Loader ). Total there are 24 columns in the Input file and 16 columns in the target table.
Total length is neither 272 nor 273.
The following is the metadata of table.
Code: Select all
BUS_DT NOT NULL DATE
STORE_NUM NOT NULL NUMBER
PROD_NUM NOT NULL VARCHAR2(20)
CSHR_PRTNR_NUM NOT NULL NUMBER
LINE_ITEM_TM NOT NULL NUMBER(6)
ACTUAL_DT DATE
EDIT_CSHR_PRTNR_NUM NUMBER
EDIT_ACTUAL_DT DATE
INVTY_MKOUT_QTY NUMBER
POS_VOID_CD VARCHAR2(1)
COGS_LCL_AMT NUMBER(19,2)
RETAIL_LCL_AMT NUMBER(19,2)
EDIT_LINE_ITEM_TM NUMBER(6)
COST_TYPE_CD VARCHAR2(1)
COFFEE_WT_PND_MEAS NUMBER(5,2)
INVTY_MKOUT_RSN_CD VARCHAR2(3)
ssunda.
Hi,
Please see if the following additional info will help to find a solution.
I checked the statistics on oracle table and the statistics are:
Avg Record length is 70.
And column wise also it listed avg column length. The summation value of all the columns length is 77.
But none of these values are matching with the values listed in the waring. (272 or 273).
Any inputs like what might be the problem will help me a lot.
Regards,
ssunda.
Please see if the following additional info will help to find a solution.
I checked the statistics on oracle table and the statistics are:
Avg Record length is 70.
And column wise also it listed avg column length. The summation value of all the columns length is 77.
But none of these values are matching with the values listed in the waring. (272 or 273).
Any inputs like what might be the problem will help me a lot.
Regards,
ssunda.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Does the source file perhaps come from a Windows system, so that there's a two character line terminator rather than the single character line terminator expected by the "UNIX" record delimiter property setting?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Arnd,
I have 6 similar jobs and all them are having this warning(with diff record lengths) when using 'LOAD' option.
I checked the control file created in /opt/etl/scratch directory in unix.
The record length calculated by sql loader is 272.
table definition in the control file is as follows:
I have 6 similar jobs and all them are having this warning(with diff record lengths) when using 'LOAD' option.
I checked the control file created in /opt/etl/scratch directory in unix.
The record length calculated by sql loader is 272.
table definition in the control file is as follows:
Code: Select all
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
BUS_DT 1:10 10 DATE YYYY/MM/DD
STORE_NUM 11:50 40 CHARACTER
PROD_NUM 51:70 20 CHARACTER
CSHR_PRTNR_NUM 71:110 40 CHARACTER
LINE_ITEM_TM 111:114 4 INTEGER
ACTUAL_DT 115:124 10 DATE YYYY/MM/DD
NULL if 115:124 = BLANKS
EDIT_CSHR_PRTNR_NUM 125:164 40 CHARACTER
NULL if 125:164 = BLANKS
EDIT_ACTUAL_DT 165:174 10 DATE YYYY/MM/DD
NULL if 165:174 = BLANKS
INVTY_MKOUT_QTY 175:214 40 CHARACTER
NULL if 175:214 = BLANKS
POS_VOID_CD 215:215 1 CHARACTER
NULL if 215:215 = BLANKS
COGS_LCL_AMT 216:236 21 CHARACTER
NULL if 216:236 = BLANKS
RETAIL_LCL_AMT 237:257 21 CHARACTER
NULL if 237:257 = BLANKS
EDIT_LINE_ITEM_TM 258:261 4 INTEGER
NULL if 258:261 = 0X80000000(character '')
COST_TYPE_CD 262:262 1 CHARACTER
NULL if 262:262 = BLANKS
COFFEE_WT_PND_MEAS 263:269 7 CHARACTER
NULL if 263:269 = BLANKS
INVTY_MKOUT_RSN_CD 270:272 3 CHARACTER
NULL if 270:272 = BLANKS