Oracle to Teradata Migration

Tools
Enthusiast

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 off
set newpage 0
set space 0
set pagesize 0
set echo off
set feedback off
set heading off
set linesize 86
column DeptNo format 99
column DName format A14
column LOC format A13
spool E:\Tutorials\Teradata\Coding\DeptOra.txt
SELECT 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 CHAR
DNAME OFFSET = 3 LEN = 14 CHAR
LOC OFFSET = 17 LEN = 13 CHAR
WINDOWS_CR_LF OFFSET = 30 LEN = 2 CHAR
TOTAL RECORD LENGTH = 32

//Reported error
Starting to send to RDBMS with record 1
Incorrect number of bytes returned from a File Read!
Expected: 32, Received: 24

The 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.
3 REPLIES

Re: Oracle to Teradata Migration

Hi
Does anyone know why the following query fails?

select len(column_name)
from table ;

thanks
Enthusiast

Re: Oracle to Teradata Migration

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

Re: Oracle to Teradata Migration



use
SEL CHAR(Col_Name) FROM Table_Name;