All Forums Tools
TD_DEV245 20 posts Joined 08/12
14 Feb 2013
FAILURE 2673 SOURCE PARCEL LENGTH DOES NOT MATCH DATA THAT WAS DEFINED

Hi,
I am trying to export data from oracle to Teradata.I use the following script to load data to flat file
 
 
 
CONNECT usr_id/pwd
SET SPACE 0
SET LINESIZE 300
SET PAGESIZE 500
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET COLSEP '|'
Spool /home/dev_team/oracle_test/test.DAT
Select col1,col2,col3  from sysadm.ps_test;
Spool off;
quit;
 
The datatypes of all columns is VARCHAR2(5 CHAR)
The output is as follows:
 
COL_1|COL_2|COL_3
-----|-----|-----
abcde|abcde|abcde
abcd1|abcd1|abcd2
abcd3|abcd1|abcd2
abcd5|abcd1|abcd2
abcd7|abcd1|abcd2
 
I am using the following script to load the flatfile data into Teradata table
bteq <<EOF
.logon 10.105.195.72/tduser,tduser
.SET WIDTH 10000
.SET PAGELENGTH 50000
.import vartext '|' file=/home/elavarasan/bteq_test/test.DAT
.REPEAT *
USING
COL_1 (VARCHAR(5)),
COL_2 (VARCHAR(5)),
COL_3 (VARCHAR(5))
 
INSERT INTO EFBI_DEV1_CSPR_T.PS_TEST(COL_1,COL_2,COL_3)
VALUES(:COL_1,
:COL_2,
:COL_3);
.quit
EOF
exit
 
The table definition of Target table is:   
CREATE SET TABLE efbi_dev1_cspr_t.ps_test ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
COL_1 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
COL_2 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
COL_3 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( COL_1 );  
I am getting a error stating 'FAILURE 2673 SOURCE PARCEL LENGTH DOES NOT MATCH DATA THAT WAS DEFINED'.Please let me know where I am going wrong
 
Thanks,
Vijayshankar
 

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein
KS42982 137 posts Joined 12/12
14 Feb 2013

Use CHAR in USING clause instead of VARCHAR as the length of your data is fixed.

TD_DEV245 20 posts Joined 08/12
15 Feb 2013

Thanks a lot

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein

dnoeth 4628 posts Joined 11/04
15 Feb 2013

Even if the length of the data is fixed you still need to define all VARCHARs for a VARTEXT import.
Your script should work as-is.
 
But you don't want to load the column name and the title dashes. And this is probably the cause for this error, a column name is larger than 5 chars.
Try
 

.import vartext '|' file="/home/elavarasan/bteq_test/test.DAT" , skip = 2

Dieter

Dieter

CarlosAL 512 posts Joined 04/08
15 Feb 2013

You might consider to change the oracle export strategy to someting like:
 
CONNECT usr_id/pwd
set echo off
set feedback off
set heading off
set linesize 32767 (or whatever fits your needs)
set pagesize 0
set termout off
set trimspool on
set verify off
Spool /home/dev_team/oracle_test/test.DAT

Select col1 || '|' ||
          col2 || '|' ||
          col3
from sysadm.ps_test;
Spool off;

quit;
Take a look at the test.DAT file and see how it looks...
HTH.
Cheers.
Carlos.

TD_DEV245 20 posts Joined 08/12
17 Feb 2013

Thanks a lot Carlos and Dieter.The Script is working fine now after removing the headers in flat file
 
Regards,
Vijayshankar

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein

vksingh 1 post Joined 02/14
20 Feb 2014

Hey,
I removed headers while pulling data, but still i am getting the same error.
My Dest table contains multiple data type columns (i.e. DATETIME, NUMERIC and VARCHAR).
I checked multiple times, source and dest data type definitions for each column are same.
Can anyone please tell me what else could be the probable reason for the error? 

sazik 1 post Joined 08/14
21 Aug 2014

I'm having the same issue, but even when I skip the first 3 lines I still get the 2673 error.  I threw in varchar 100 just to ensure there was room for each string as I created the code.  Most fields are 30-50 in length.  Here is my code:
 

 USING

  (merch_nm VARCHAR(100)

  ,merch_path VARCHAR(100)

  ,merch_ownr_cd VARCHAR(50)

  ,merch_cretn_dt VARCHAR(20)

  ,loc_nm VARCHAR(100)

  ,loc_bill_acct VARCHAR(100)

  ,itm_cnt VARCHAR(100)

  ,tot_amt VARCHAR(100)

 )

 

 INSERT INTO UD303.PRODEP_KL_TO_TD

  (merch_nm

  ,merch_path

  ,merch_ownr_cd

  ,merch_cretn_dt

  ,loc_nm

  ,loc_bill_acct

  ,itm_cnt

  ,tot_amt

)

 VALUES 

  (:merch_nm

  ,:merch_path

  ,:merch_ownr_cd

  ,:merch_cretn_dt

  ,:loc_nm

  ,:loc_bill_acct

  ,:itm_cnt

  ,:tot_amt

 );

 *** Starting Row 3 at Thu Aug 21 16:55:21 2014

 

 *** Failure 2673 The source parcel length does not match data that was defi

 ned.

                Statement# 1, Info =1 

 

 *** Exiting BTEQ...

 *** RC (return code) = 8 

 

 

Any ideas what ocule be causing the error?

You must sign in to leave a comment.