All Forums Tools
rgans_99 11 posts Joined 02/05
09 Dec 2005
Oracle to Teradata Migration

Hi,I tried to load Teradata from Oracle table.Look at the following code---------------------------------------------//To write Oracle table data into a flat file//dept.sql (Oracle)---------------------------------------------- set termout offset newpage 0set space 0set pagesize 0set echo offset feedback offset heading offset linesize 86column DeptNo format 99column DName format A14column LOC format A13spool E:\Tutorials\Teradata\Coding\DeptOra.txtSELECT DeptNo,DNAME,LOC FROM DEPT;spool off----------------------------------//The generated flat file//deptora.txt------------------------------------- ----- 10ACCOUNTING NEW YORK 20RESEARCH DALLAS 30SALES CHICAGO 40OPERATIONS BOSTON 50Manufacturing San Jose ---------------------------------------------//The fast load script:logon DemoTDAT/demo,demo;database demodb;drop table DeptTera;drop Table Derror_1;drop table Derror_2;CT DeptTera (DeptNo smallint format '999' NOT NULL,DName char(14),Loc char(13))Unique Primary Index (DeptNo);set record Unformatted;DEFINE DeptNo (char(3)) ,DName (char(14)) ,LOC (char(13)) ,WINDOWS_CR_LF (CHAR(2)) FILE = E:\Tutorials\Teradata\Coding\DeptOra.txt;show;Begin Loading DeptTera errorfiles Derror_1, Derror_2;insert into DeptTera Values(eptNo,Name,:Loc);END LOADING;LOGOFF;--------------------------------------- -----------//Output and error:-DEPTNO OFFSET = 0 LEN = 3 CHARDNAME OFFSET = 3 LEN = 14 CHARLOC OFFSET = 17 LEN = 13 CHARWINDOWS_CR_LF OFFSET = 30 LEN = 2 CHARTOTAL RECORD LENGTH = 32//Reported errorStarting to send to RDBMS with record 1Incorrect number of bytes returned from a File Read!Expected: 32, Received: 24The last column in the first record width is 13 chars.But it has 8 chars only.I think that the remaining chars are white space.But this txt file was generated by the SQL script.How can we solve this problem?Thanks for the response.

nicole_s 2 posts Joined 07/07
01 Aug 2007

HiDoes anyone know why the following query fails?select len(column_name)from table ;thanks

joedsilva 505 posts Joined 07/05
01 Aug 2007

In Teradata, you need to use CHARACTERS() or CHARACTER_LENGTH()

02 Aug 2007

use SEL CHAR(Col_Name) FROM Table_Name;

You must sign in to leave a comment.