[sqlldr] upload number datatype from varchar2 datatype in ctl file
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