Re: SQL*Loader question

On 11 Nov 1996 18:40:35 GMT, wolfe001_at_mail.genmills.com (Jerry Wolfe) wrote:

>We are running Oracle 7.3.2.2 on an HP-UX 10.10 system. We have a file
>which was ftp'd from our IBM system and is in EBCDIC format that we want
>to load with SQL*Loader. We are using the CHARACTERSET we8ebcdic500
>parameter on the LOAD DATA statement and use the POSITION parameter to
>identify the starting and ending positions of each column. When we
>execute SQL*Loader we get SQL_Loader error-510 Physical record in datafile
><filename> is longer than the maximum (65535). The same file loads just
>fine into our Redbrick database. Apparently, UNIX doesn't recognize the
>end of record delimiters since the file is EBCDIC because I can't get the
>file with 'vi' either. We also tried the RECSIZE parameter on the
>INFILE statement to specify the size of each record. When we did that we
>got SQL*Loader error-500 Unable to open file <filename>.
>
>The question here is: Has anyone used SQL*Loader to load IBM EBCDIC
>formated datafiles successfully? If so, what are we doing wrong?
>
>We have an open TAR on this with Oracle but so far they haven't given us
>any insight. If anyone can shed some light on this, it will be greatly
>appreciated.
>
>Thank you in advance.
>
>Jerry Wolfe
>General Mills, Inc.
>P.O. Box 1113 M/S 2EW
>Minneapolis, MN 55440-1113
>phone: (612) 540-7257
>email: wolfe001.mail.genmills.com
>

You need to use the FIX keyword in the sqlload control file. Here is an example of what you might expect in the control file.

load data

characterset WE8EBCDIC500

infile 'TBL04811.DAT' "fix 214"

into table SCOTT.REPORT

(

EXP_RPT_DATE position(1) date "yyyy-mm-dd",

LINE_TYPE position(11) char(1),

TRIP_PURPOSE position(12) char(10),

CREDIT_CARD_TYP position(22) char(10),

LINE_AMOUNT position(32:36) decimal(9,2),

PROJECT_NMBR position(37) char(8),

PHASE_NMBR position(45) char(3),

ACCOUNT_DESC position(48) char(60),

ORGANIZATION_DESC position(108) char(60),

EMAIL_ID position(168) char(8),

GL_CORP_ID position(176) char(5),

ORGANIZATION_ID position(181) char(10),

GL_ACCOUNT_NMBR position(191) char(20),

FISCAL_PERIOD position(211) char(4))

Hope this helps,
Jerome Carron Received on Mon Nov 11 1996 - 00:00:00 CET