Home » SQL & PL/SQL » Client Tools » SQL*Loader using TOAD
SQL*Loader using TOAD [message #230889] Fri, 13 April 2007 04:02 Go to next message
bzguru10
Messages: 2
Registered: April 2007
Junior Member
Hi everyone,

I'm trying to load data from a text file into oracle using SQL*Loader in TOAD but I'm encountering a problem.

CONTROL:

LOAD DATA 
INFILE 'input.txt' 
BADFILE 'input.bad'
DISCARDFILE 'input.dsc'

APPEND
INTO TABLE "TABLE_NAME"

  (UPDATE_DATE  POSITION(1:9)  DATE(9) "DD/MM/YYYY"
, 
   UPDATE_USER  POSITION(10:15)  CHAR(6)
, 
   UPDATE_PROGRAM  POSITION(16:19)  CHAR(4)
, 
   CODE_STA  POSITION(20:20)  INTEGER(1)
, 
   DESCRIPTION  POSITION(21:50)  CHAR(30)
, 
   LAST_STA  POSITION(51:51)  INTEGER(1)
)


My input file is like this:
13/4/2007MANUALCONV0DESCA 0
13/4/2007MANUALCONV0DESCB 0
13/4/2007MANUALCONV0DESCC 0
13/4/2007MANUALCONV0DESCD 0


The first error is:

Record 1: Rejected - Error on table "TABLE_NAME", column LAST_STA.
ORA-01438: value larger than specified precision allows for this column

Please take note of these:
LAST_STA is of data type NUMBER(1)
CODE_STA is of data type NUMBER (2).

When I excluded the last column (LAST_STA), I was able to load the data. But when I checked the database, the data stored for CODE_STA is 48 instead of 0 (zero).

So, it seems that the zero from my input file is converted to 48 once stored in the database.

Do you have any idea what I can about this? Thanks in advance.
Re: SQL*Loader using TOAD [message #231028 is a reply to message #230889] Fri, 13 April 2007 13:27 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

you should study sqlldr field list reference in documentation.

As for CODE_STA, as you did not use EXTERNAL format, your number is treated binary ('0' = CHR(48)).
As for LAST_STA, I do not know. Seems to be the same case as CODE_STA. However your sample row contains only 27 characters, so from 51st position nothing can be extracted.

By the way, how will you represent dates from October to December (months 10-12). Does not to be a good case of a positional file to me.
Re: SQL*Loader using TOAD [message #231427 is a reply to message #230889] Tue, 17 April 2007 01:00 Go to previous message
bzguru10
Messages: 2
Registered: April 2007
Junior Member
Thanks a lot.

I missed some columns when I pasted the input data, it should really be much longer.

The dates are constant for now, so no need to handle the 2-digit months.

Thanks again for the help.

[Updated on: Tue, 17 April 2007 01:01]

Report message to a moderator

Previous Topic: parameters to SQLPLUS
Next Topic: Spool SQL*PLUS Output - No Whitespace
Goto Forum:
  


Current Time: Sun Dec 04 16:52:53 CST 2016

Total time taken to generate the page: 0.15354 seconds