OraFAQ Forum: Client Tools » SQL*Loader using TOAD

Home » Other » Client Tools » SQL*Loader using TOAD

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader using TOAD [message #230889]

Fri, 13 April 2007 04:02

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.

Report message to a moderator

Re: SQL*Loader using TOAD [message #231028 is a reply to message #230889]

Fri, 13 April 2007 13:27

flyboy
Messages: 1903
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.

Report message to a moderator

Re: SQL*Loader using TOAD [message #231427 is a reply to message #230889]

Tue, 17 April 2007 01:00

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 13:59:02 CDT 2024