I don't understand why this record is not loaded. Column SENTENCIA have enought space to hold the thirdth field. Any sugestion?
Jose Luis.
fanlfac@madsfeapp21 [/tmp/ext]# cat SNOL_RUTINA.dat
45^^RUTINA MDM MODELO GESTION TEXTO LIBRE^^select decode(substr(parametros,instr(parametros,'#',1,1)+1,instr(parametros,'#',1,2)-instr(parametros,'#',1,1)-1),'AU','AUTOGESTIONADO','AD','ADMINISTRADO','AA','ADMINISTRADO AVANZADO','ADCGP','ADMINISTRADO CGP','ADACGP','ADM. AVANZADO CGP') from tggs_servicios_fact where cod_servicio=:V1^^^^t151009^^20170329112914^^t151009^^20170601174553|
fanlfac@madsfeapp21 [/tmp/ext]# od -c SNOL_RUTINA.dat
0000000 4 5 ^ ^ R U T I N A M D M M
0000020 O D E L O G E S T I O N T E
0000040 X T O L I B R E ^ ^ s e l e c
0000060 t d e c o d e ( s u b s t r (
0000100 p a r a m e t r o s , i n s t r
0000120 ( p a r a m e t r o s , ' # ' ,
0000140 1 , 1 ) + 1 , i n s t r ( p a r
0000160 a m e t r o s , ' # ' , 1 , 2 )
0000200 - i n s t r ( p a r a m e t r o
0000220 s , ' # ' , 1 , 1 ) - 1 ) , ' A
0000240 U ' , ' A U T O G E S T I O N A
0000260 D O ' , ' A D ' , ' A D M I N I
0000300 S T R A D O ' , ' A A ' , ' A D
0000320 M I N I S T R A D O A V A N Z
0000340 A D O ' , ' A D C G P ' , ' A D
0000360 M I N I S T R A D O C G P ' ,
0000400 ' A D A C G P ' , ' A D M . A
0000420 V A N Z A D O C G P ' ) f r
0000440 o m t g g s _ s e r v i c i o
0000460 s _ f a c t w h e r e c o d
0000500 _ s e r v i c i o = : V 1 ^ ^ ^
0000520 ^ t 1 5 1 0 0 9 ^ ^ 2 0 1 7 0 3
0000540 2 9 1 1 2 9 1 4 ^ ^ t 1 5 1 0 0
0000560 9 ^ ^ 2 0 1 7 0 6 0 1 1 7 4 5 5
0000600 3 | \n
0000603
fanlfac@madsfeapp21 [/tmp/ext]# cat SNOL_RUTINA.ctl
LOAD DATA
INFILE SNOL_RUTINA.dat "STR '|\n'"
TRUNCATE
INTO TABLE SNOL_RUTINA
FIELDS TERMINATED BY '^^'
TRAILING NULLCOLS
(
COD_RUTINA ,
DES_RUTINA ,
SENTENCIA ,
FEC_BAJA DATE 'yyyymmddhh24miss',
COD_USUALT ,
FEC_ALTASIS DATE 'yyyymmddhh24miss',
COD_USUMOD ,
FEC_ULTMOD DATE 'yyyymmddhh24miss'
)
SQL> desc SNOL_RUTINA
Name Null? Type
----------------------------------------- -------- ----------------------------
COD_RUTINA NOT NULL VARCHAR2(5)
DES_RUTINA NOT NULL VARCHAR2(40)
SENTENCIA NOT NULL VARCHAR2(3000)
FEC_BAJA DATE
COD_USUALT NOT NULL VARCHAR2(30)
FEC_ALTASIS NOT NULL DATE
COD_USUMOD VARCHAR2(30)
FEC_ULTMOD DATE
fanlfac@madsfeapp21 [/tmp/ext]# cat SNOL_RUTINA.log
SQL*Loader: Release 10.2.0.1.0 - Production on Lun Dic 2 23:30:49 2019
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: SNOL_RUTINA.ctl
Data File: SNOL_RUTINA.dat
File processing option string: "STR '|
'"
Bad File: SNOL_RUTINA.bad
Discard File: none specified
(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 "FACTNAT"."SNOL_RUTINA_1", loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COD_RUTINA FIRST * CHARACTER
Terminator string : '^^'
DES_RUTINA NEXT * CHARACTER
Terminator string : '^^'
SENTENCIA NEXT * CHARACTER
Terminator string : '^^'
FEC_BAJA NEXT * DATE yyyymmddhh24miss
Terminator string : '^^'
COD_USUALT NEXT * CHARACTER
Terminator string : '^^'
FEC_ALTASIS NEXT * DATE yyyymmddhh24miss
Terminator string : '^^'
COD_USUMOD NEXT * CHARACTER
Terminator string : '^^'
FEC_ULTMOD NEXT * DATE yyyymmddhh24miss
Terminator string : '^^'
Record 1: Rejected - Error on table "FACTNAT"."SNOL_RUTINA_1", column SENTENCIA.
Field in data file exceeds maximum length
Table "FACTNAT"."SNOL_RUTINA_1":
0 Rows successfully loaded.
1 Row 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: 132096 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 1
Total logical records rejected: 1
Total logical records discarded: 0
Run began on Lun Dic 02 23:30:49 2019
Run ended on Lun Dic 02 23:30:52 2019
Elapsed time was: 00:00:02.94
CPU time was: 00:00:00.02