[sqlldr] upload number datatype from varchar2 datatype in ctl file

From: chijar <chijar_at_gmail.com>
Date: Thu, 6 Mar 2008 06:35:22 -0800 (PST)
Message-ID: <9755fb16-d797-4405-9793-a5cd2e3a4196@u69g2000hse.googlegroups.com>


dear friends.
This is my datfile...in the last field the data is: |3180.8| or |605.| like you can see...

in the table destination the field have NUMBER(12,2) and i want to upload in this datatype from my control file...

Example of my datfile is:

00000000|0|0601|2000082|6|20131023414|3378|1|MATUTE|40|C|MATUTE|CUADRA
17 DE ISABEL LA CATOLICA|11|03|150115|3180.8|
00000000|0|0601|2000082|1|20131023414|3378|1|MATUTE|40|C|MATUTE|CUADRA
17 DE ISABEL LA CATOLICA|11|03|150115|605.|

My CTL file is:
Load DATA
APPEND
INTO TABLE PLANELEC.PEMVX_DATOSCOMPDOM
fields terminated by "|" TRAILING NULLCOLS
(

V_NUMPAQTE,
V_NUMLOTE,
V_NUMFORM,
N_NUMORDEN,
V_CODDOCIDE,
V_NUMDOCIDE,
N_NUMCORAPO,
N_ESTASGHAB,
V_NOMBREVIA,
V_NUMEROVIA,
V_INTERIOR,
V_NOMZONA,
V_REFERNCIA,
V_CODTVIA,
V_CODZONA,
V_CODUBIGEO,

TEST_INTEGER CONSTANT 0,
TEST_NUMBER (this is ths field where i put the last field of datfile) )

This is my sh. file:
# /bin/bash
export ORACLE_SID=plelpp
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 export PATH=$PATH:$ORACLE_HOME/bin
sqlldr USERID=system, CONTROL=T818COMP.ctl, LOG=T818COMP.log, data=10pemvx_datoscompdom_20080304165740.dat, bad=T818COMP.bad, discard=T818COMP.dsc

And finally, this is my *.log file that show errors when i execute my sh file:

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Mar 6 07:50:46 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: T818COMP.ctl
Data File: 10pemvx_datoscompdom_20080304165740.dat Bad File: T818COMP.bad
Discard File: T818COMP.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50

Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified
Path used: Conventional

Table PLANELEC.PEMVX_DATOSCOMPDOM, loaded from every logical record. Insert option in effect for this table: APPEND TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype

------------------------------ ---------- ----- ---- ----
---------------------
V_NUMPAQTE FIRST * | CHARACTER
V_NUMLOTE NEXT * | CHARACTER
V_NUMFORM NEXT * | CHARACTER
N_NUMORDEN NEXT * | CHARACTER
V_CODDOCIDE NEXT * | CHARACTER
V_NUMDOCIDE NEXT * | CHARACTER
N_NUMCORAPO NEXT * | CHARACTER
N_ESTASGHAB NEXT * | CHARACTER
V_NOMBREVIA NEXT * | CHARACTER
V_NUMEROVIA NEXT * | CHARACTER
V_INTERIOR NEXT * | CHARACTER
V_NOMZONA NEXT * | CHARACTER
V_REFERNCIA NEXT * | CHARACTER
V_CODTVIA NEXT * | CHARACTER

V_CODZONA NEXT * | CHARACTER
V_CODUBIGEO NEXT * | CHARACTER
TEST_INTEGER CONSTANT
Value is '0'
TEST_NUMBER NEXT * | CHARACTER value used for ROWS parameter changed from 64 to 58 Record 1: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM, column TEST_NUMBER.
ORA-01722: numero no valido

Record 2: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM, column TEST_NUMBER.
ORA-01722: numero no valido

Record 3: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM, column TEST_NUMBER.
ORA-01722: numero no valido

Record 4: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM, column TEST_NUMBER.
ORA-01722: numero no valido

Record 5: Rejected - Error on table PLANELEC.PEMVX_DATOSCOMPDOM, column TEST_NUMBER.
ORA-01722: numero no valido

Table PLANELEC.PEMVX_DATOSCOMPDOM:
0 Rows successfully loaded.
5 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

Space allocated for bind array: 254504 bytes(58 rows) Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 5
Total logical records discarded: 0

Run began on Thu Mar 06 07:50:46 2008
Run ended on Thu Mar 06 07:50:49 2008

Elapsed time was: 00:00:02.95
CPU time was: 00:00:00.02



Please,
would you help me ? is very important.
a lot of thanks...
Whtat is the errors???

cesar Received on Thu Mar 06 2008 - 08:35:22 CST

Original text of this message