carlino70
asked on
SQL LOADER issue with incomplete char data
Hi experts, when I do an insert/append with sqlldr I see incomplete data in a CHAR column:
Here de control.ctl
Thank in advanced
Regards
Here de control.ctl
Load DATA
CHARACTERSET WE8ISO8859P1
INFILE '60.dat'
BADFILE '60.bad'
DISCARDFILE '60.dsc'
INTO TABLE TEMAS
Append
fields terminated by "|"
OPTIONALLY ENCLOSED BY '"'
trailing NULLCOLS
(ID Integer "temas_id.nextval",
NOMBRE CHAR(100),
INTERPRETE CHAR NULLIF INTERPRETE=' ',
TITULO CHAR NULLIF TITULO=' ',
ALBUM CHAR NULLIF ALBUM=' ',
FECHA Date "DD-MM-YYYY HH24:Mi:SS",
GENERO CHAR NULLIF GENERO=' ',
TAMAÑO CHAR NULLIF TAMAÑO=' ',
DURACION CHAR NULLIF DURACION=' ',
VELOCIDAD CHAR NULLIF VELOCIDAD=' ',
COMENTARIOS CHAR NULLIF COMENTARIOS=' ',
PATH CHAR NULLIF PATH=' ')
here a portion of data:|60 - uno.mp3 |||||||||||
|60 - dos.mp3 |||||||||||
|60 - tres.mp3 |||||||||||
After append, I hope to view:select nombre from temas;
Result:60 - uno.mp3
60 - dos.mp3
60 - tres.mp3
But I see:- uno.mp3
- dos.mp3
- tres.mp3
The definition of the table is:CREATE TABLE TEMAS
(
ID INTEGER,
NOMBRE VARCHAR2(100 BYTE),
INTERPRETE VARCHAR2(50 BYTE),
TITULO VARCHAR2(100 BYTE),
ALBUM VARCHAR2(50 BYTE),
FECHA DATE DEFAULT TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS'),
GENERO VARCHAR2(30 BYTE),
TAMAÑO VARCHAR2(60 BYTE),
DURACION VARCHAR2(20 BYTE),
VELOCIDAD VARCHAR2(20 BYTE),
COMENTARIOS VARCHAR2(200 BYTE),
PATH VARCHAR2(200 BYTE)
)
TABLESPACE USERS;
What it is wrong?Thank in advanced
Regards
ASKER
Here the answer:
ID|NOMBRE
341|- donald - tiritando.mp3
342|- los naufragos - de boliche e
343|- los naufragos - estoy hecho
344|- palito ortega - bienvenido a
without SUBSTR, should be:ID|NOMBRE
341|- donald - tiritando.mp3
342|- los naufragos - de boliche en boliche.mp3
343|- los naufragos - estoy hecho un demonio.mp3
344|- palito ortega - bienvenido amor.mp3
note: I dont understand why appears the "|" between columns (?)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent!,
The inclusion of the data type INTEGER, somehow confused the sql loader.
Thanks
The inclusion of the data type INTEGER, somehow confused the sql loader.
Thanks
Just a theory:
I think sqlloader was looking for an integer in the data before looking at the seq.nextval.
Without the data type, it took it as a literal value and ignored the data.
I think sqlloader was looking for an integer in the data before looking at the seq.nextval.
Without the data type, it took it as a literal value and ignored the data.
SELECT id, SUBSTR(nombre,1,30) nombre
FROM temas
WHERE ROWNUM < 5
/