I am trying to export data from oracle to Teradata.I use the following script to load data to flat file
SET SPACE 0
SET LINESIZE 300
SET PAGESIZE 500
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING ON
SET COLSEP '|'
Select col1,col2,col3 from sysadm.ps_test;
The datatypes of all columns is VARCHAR2(5 CHAR)
The output is as follows:
I am using the following script to load the flatfile data into Teradata table
.SET WIDTH 10000
.SET PAGELENGTH 50000
.import vartext '|' file=/home/elavarasan/bteq_test/test.DAT
INSERT INTO EFBI_DEV1_CSPR_T.PS_TEST(COL_1,COL_2,COL_3)
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,
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
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.
.import vartext '|' file="/home/elavarasan/bteq_test/test.DAT" , skip = 2
You might consider to change the oracle export strategy to someting like:
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
Select col1 || '|' ||
col2 || '|' ||
Take a look at the test.DAT file and see how it looks...
Thanks a lot Carlos and Dieter.The Script is working fine now after removing the headers in flat file
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?
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: