From: "Francesco Principe" <francesco.principe@gruppocm.it>
Newsgroups: comp.databases.oracle.server
Subject: Re: Trying to "SQLLOAD" COMP-3 fields.
Date: Fri, 2 Feb 2001 15:52:54 +0100
Organization: I.NET S.p.A.
Lines: 89
Message-ID: <95ehp3$52im$1@stargate1.inet.it>
References: <95d2gs$ql3$1@nnrp1.deja.com>
NNTP-Posting-Host: 212.239.115.145
X-Trace: stargate1.inet.it 981125731 166486 212.239.115.145 (2 Feb 2001 14:55:31 GMT)
X-Complaints-To: usenet@stargate1.inet.it
NNTP-Posting-Date: 2 Feb 2001 14:55:31 GMT
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600


I've to made a question.

Have you tryed to set NLS_NUMERIC_CHARACTER?

<rcarvalho1474@my-deja.com> ha scritto nel messaggio
news:95d2gs$ql3$1@nnrp1.deja.com...
> I'm trying to use SQL Loader for loading an Oracle table from a Cobol
> generated file which has CHAR and COMP-3 fields and I always get the
> same error for all the input file's records.
>
> The table is defined as
> SQL> desc test_rescbsi1
>  Name                            Null?    Type
>  ------------------------------- -------- ----
>  NR_ACC                                   VARCHAR2(16)
>  NR_PHONE                                 NUMBER(10)
>  DT_CALL                                  NUMBER(6)
>     ....................
>     ....................
>
> The file has the following layout:
> 01 rec-res.
> 03  acc_number           pic x(16).
>       03  phone_number         pic 9(10).
>       03  filler               pic x(24).
>       03  call_date    comp-3  pic s9(6).
>       ..................................
>       ..................................
>
> The control file defines the fields as:
> LOAD DATA
> CHARACTERSET WE8EBCDIC285
> INFILE 'cbsi1' "fix 350"
> BADFILE 'cbsi1.bad'
> DISCARDFILE 'cbsi1.dsc'
> INTO TABLE TEST_RESCBSI1
> (
> NR_ACC                          POSITION(01:16)       CHAR,
> NR_PHONE                        POSITION(17:26)       CHAR,
> DT_CALL                         POSITION(51:54)       DECIMAL(6,0)
> )
>
> Whenever I try to load, I get the following message from SQL Loader:
>
> SQL*Loader: Release 8.1.5.0.0 - Production on Wed Jan 31 12:18:46 2001
>
> (c) Copyright 1999 Oracle Corporation.  All rights reserved.
>
> Control File:   cbsi1_2.ctl
> Character Set WE8EBCDIC285 specified for all input.
>
> Data File:      reseller.cbsi1  File processing option string: "fix 350"
>   Bad File:     cbsi1.bad
>   Discard File: cbsi1.dsc
>  (Allow all discards)
>
> Number to load: ALL
> Number to skip: 0
> Errors allowed: 100
> Bind array:     64 rows, maximum of 65536 bytes
> Continuation:    none specified
> Path used:      Conventional
>
> Table TEST_RESCBSI1, loaded from every logical record.
> Insert option in effect for this table: INSERT
>
>    Column Name                  Position   Len  Term Encl Datatype
> ------------------------------ ---------- ----- ---- ---- --------------
> -------
> NR_ACC                               1:16    16           CHARACTER
> NR_PHONE                            17:26    10           CHARACTER
> DT_CALL                             51:54     4           PACKED
> DECIMAL (6, 0)
>
> Record 1: Rejected - Error on table TEST_RESCBSI1, column DT_CALL.
> Invalid zoned decimal nibble.
> Record 2: Rejected - Error on table TEST_RESCBSI1, column DT_CALL.
> Invalid zoned decimal nibble.
>
> (..... and so on for every record )
>
>
> Can somebody help me ?
>
>
> Sent via Deja.com
> http://www.deja.com/