Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Varchars by SQL*Loader -- Thanks!

RE: Varchars by SQL*Loader -- Thanks!

From: Javier Morales <Javier_at_infojobs.net>
Date: Fri, 10 Nov 2000 13:03:09 +0100
Message-Id: <10676.121698@fatcity.com>


Thank you all...
:-)
It finally worked !! ;-)

-----Mensaje original-----
De: David Barbour [mailto:DBarbour_at_connectsouth.com] Enviado el: jueves 9 de noviembre de 2000 18:26 Para: Multiple recipients of list ORACLE-L Asunto: RE: Varchars by SQL*Loader

Okay, here's the long answer, I think --

You don't need to define the data types and size in the control file, they're defined in the data base. If you try to load a record that has the wrong datatype or size, you'll get a message in the log file and the record will appear in the bad file.

Structure you control file as follows and I think you'll have a little more success:

load infile 'e:\oracle\paco\esp_ij011.txt' into table infile_traducciones_esp
append
fields terminated by '|'
(pagina_cfm,
 orden,
 id_txt,
 texto)

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
Sent: Thursday, November 09, 2000 9:41 AM To: Multiple recipients of list ORACLE-L

when I did, the result was even worst :( I explained in my mail... that was the result... :(

so, that's the best I got from it. I tried to change the ID_TXT field to "integer", and "char terminated by '|') but the result is even worst...

SVRMGR> select * from infile_traducciones_esp where rownum=1;

PAGINA_CFM                   ORDEN      ID_TXT     TEXTO
--------------------------------------- ---------- ----------
-----------------------------------------------
acuerdos.cfm                    100      17585 foJobs.net - Premios y
menciones

-----Mensaje original-----
De: David Barbour [mailto:DBarbour_at_connectsouth.com] Enviado el: jueves 9 de noviembre de 2000 15:37 Para: Multiple recipients of list ORACLE-L Asunto: RE: Varchars by SQL*Loader

Aren't you missing a 'terminated by' after your id_txt field?

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
Sent: Thursday, November 09, 2000 4:26 AM To: Multiple recipients of list ORACLE-L

Hi,

I try to load from a file that contains one field up to 250 chars, I tried that controlfile...

load data
infile 'e:\oracle\paco\esp_ij011.txt'
append
into table infile_traducciones_esp
(pagina_cfm char terminated by '|', orden char terminated by '|',  id_txt char(5), texto varchar)

and the file format is that...

acuerdos.cfm|100|17585|InfoJobs.net - Premios y menciones
acuerdos.cfm|200|17586|Portada &gt; Sobre InfoJobs.net &gt; Acuerdos
acuerdos.cfm|300|17587|Sobre InfoJobs.net
acuerdos.cfm|400|17588|&nbsp; Acuerdos
acuerdos.cfm|500|17589|InfoJobs.net desarrolla en julio del 2000 los
servicios de bolsa de empleo para e-moci&oacute;n, el portal wap de Telef&oacute;nica. Este servicio consta de un mne&uacute; principal desde el que se puede acceder a los tres apartados disponibles: ofertas destacadas, buscador de ofertas y datos y estad&iacute;sticas, cuya informaci&oacute;n se actualiza en tiempo real.

The problem is that I find the data truncated BY THE BEGINNING !! :( ... that way...)

SVRMGR> select * from infile_traducciones_esp where rownum<=5;

PAGINA_CFM                   ORDEN      ID_TXT     TEXTO
--------------------------------------- ---------- ----------
-----------------------------------------------
acuerdos.cfm                     100      17585 nfoJobs.net - Premios y
menciones
acuerdos.cfm                     200      17586 ortada &gt; Sobre
InfoJobs.net &gt; Acuerdos
acuerdos.cfm                     300      17587 obre InfoJobs.net

acuerdos.cfm                     400      17588 nbsp; Acuerdos

acuerdos.cfm                     500      17589 nfoJobs.net desarrolla en
julio del 2000 los servicios de bolsa de empleo para e

so, that's the best I got from it. I tried to change the ID_TXT field to "integer", and "char terminated by '|') but the result is even worst...

SVRMGR> select * from infile_traducciones_esp where rownum=1;

PAGINA_CFM                   ORDEN      ID_TXT     TEXTO
--------------------------------------- ---------- ----------
-----------------------------------------------
acuerdos.cfm                    100      17585 foJobs.net - Premios y
menciones

any suggestions???

Thanks!



 Javier Morales - javier_at_infojobs.net
 AdministraciĠn Bases de Datos Oracle - InfoJobs.net  Tel. +34 902 10 60 90 Ext. 2067
 Fax. +34 (93) 580 56 60  

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Javier Morales
  INET: Javier_at_infojobs.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: David Barbour
  INET: DBarbour_at_connectsouth.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Javier Morales
  INET: Javier_at_infojobs.net
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: David Barbour
  INET: DBarbour_at_connectsouth.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in Received on Fri Nov 10 2000 - 06:03:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US