OraFAQ Forum: Server Utilities » LOAD INVALID NUMBER

Home » RDBMS Server » Server Utilities » LOAD INVALID NUMBER

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

LOAD INVALID NUMBER [message #187264]

Fri, 11 August 2006 09:40

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Hi :

I'm trying to laod data with SQL Loader but when i want to load any NUMBER (10,2) datatype, i get the error ORA-01722:INVALID NUMBER.

I've tried:

Load with '.' and ',' even changing the variable
set nls_numeric_characters='.,'

Change de datatype in my control file with CHAR, DECIMAL EXTERNAL, field "replace (:field, '.',',')" and always i have the same error.

I only can load numbers without decimals. Here a desc of my table

SQL> desc dm08_datos_trabajo;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID_PERSONA NOT NULL VARCHAR2(17)
ID_POLIZA NUMBER(10)
ID_RETENEDOR NOT NULL NUMBER(7)
ID_UNIDAD_PAGO NOT NULL NUMBER(7)
UNIDAD_ADMON_O_EMPRESA VARCHAR2(80)
ID_NIVEL_GOBIERNO NUMBER(5)
ID_TIPO_PODER NUMBER(5)
SECTOR VARCHAR2(20)
NUM_SUBGRUPO NUMBER(3)
AREA VARCHAR2(50)
TIPO_CONTRATO VARCHAR2(30)
PUESTO VARCHAR2(35)
NIVEL_LABORAL VARCHAR2(25)
FECHA_INGRESO DATE
FECHA_PRIMER_COT DATE
SUELDO_BASE NUMBER(10,2)
SUELDO_NETO NUMBER(10,2)
SEGURO_GMM NUMBER(10,2)
ID_PERSONA_POLIZA VARCHAR2(17)

The problem is only with my NUMBER (10,2) fields.

Here my session parameters.

PARAMETER VALUE

NLS_LANGUAGE MEXICAN SPANISH
NLS_TERRITORY MEXICO
NLS_CURRENCY $
NLS_ISO_CURRENCY MEXICO
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE MEXICAN SPANISH
NLS_SORT WEST_EUROPEAN
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Any ideas o suggestions?

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187662 is a reply to message #187264]

Mon, 14 August 2006 17:22

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

I TRY WITH:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ".,";

BUT I DON'T HAVE AN ANSWER

I'M REALLY DESPERATE

[Updated on: Mon, 14 August 2006 17:24]

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187667 is a reply to message #187662]

Mon, 14 August 2006 18:17

ebrian
Messages: 2794
Registered: April 2006

Senior Member

A copy of your control file and some sample data will help troubleshoot the problem.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187668 is a reply to message #187264]

Mon, 14 August 2006 18:24

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

LOAD DATA
CHARACTERSET UTF8
INTO TABLE "DM08_POLIZA"
APPEND
REENABLE DISABLED_CONSTRAINTS

FIELDS
TERMINATED BY '|'

TRAILING NULLCOLS

(
ID_BASE_ORIGEN boundfiller ,
ID_ORIGEN boundfiller ,
ID_PERSONA "LPAD(:ID_BASE_ORIGEN,2,0)||LPAD(:ID_ORIGEN,15,0)" ,
FILLER_ID_BASE_ORIGEN FILLER POSITION (1) INTEGER EXTERNAL ,
FILLER_ID_ORIGEN FILLER CHAR,
"ID_POLIZA" INTEGER EXTERNAL ,
FILLER_ID_RETENEDOR FILLER INTEGER EXTERNAL ,
FILLER_ID_UNIDAD_PAGO FILLER INTEGER EXTERNAL ,
FILLER_UNIDAD_ADMON FILLER CHAR ,
FILLERID_NIVEL_GOBIERNO FILLER CHAR ,
FILLER_ID_TIPO_PODER FILLER CHAR ,
FILLER_SECTOR FILLER CHAR ,
FILLER_NUM_SUBGPO FILLER INTEGER EXTERNAL ,
FILLER_AREA FILLER CHAR ,
FILLER_TIPO_CONTRATO FILLER CHAR ,
FILLER_PUESTO FILLER CHAR ,
FILLER_NIVEL_LABORAL FILLER CHAR ,
FILLER_FECHA_INGRESO FILLER DATE ,
FILLER_FECHA_PRIMER_COT FILLER DATE ,
FILLER_SUELDO_BASE FILLER CHAR ,
FILLER_SUELDO_NETO FILLER CHAR ,
FILLER_SEGURO_GMM FILLER CHAR ,
"CANAL" CHAR ,
ID_TIPO_PLAN "LPAD(:ID_BASE_ORIGEN,2,0)||LPAD(:ID_TIPO_PLAN,5,0)",
"NUM_CATEGORIA" INTEGER EXTERNAL ,
PRIMA_ANUAL "TO_NUMBER(':PRIMA_ANUAL')",
"SUM_ASEGURADA" CHAR,
"PRIMA_ANUAL_DESC" CHAR,
"EXTRA_PRIMA" CHAR,
"PRIMA_BASE" CHAR,
"PRIMA_BASE_CT" CHAR,
"PRIMA_POTENCIADA" CHAR,
"PRIMA_POTENCIADA_CT" CHAR,
"PRIMA_BENEF_ADICIONAL" CHAR

)

and the numbers that i´m trying to load are -2.29, 347.67

Here uns desc of my table...

SQL> desc dm08_poliza;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID_PERSONA NOT NULL VARCHAR2(17)
ID_POLIZA NOT NULL NUMBER(10)
CANAL VARCHAR2(20)
ID_TIPO_PLAN VARCHAR2(7)
NUM_CATEGORIA NUMBER(3)
PRIMA_ANUAL NUMBER(10,2)
SUM_ASEGURADA NUMBER(10,2)
PRIMA_ANUAL_DESC NUMBER(10,2)
EXTRA_PRIMA NUMBER(10,2)
PRIMA_BASE NUMBER(10,2)
PRIMA_BASE_CT NUMBER(10,2)
PRIMA_POTENCIADA NUMBER(10,2)
PRIMA_POTENCIADA_CT NUMBER(10,2)
PRIMA_BENEF_ADICIONAL NUMBER(10,5)

Here mi data:
1|16736677|1|10|999||0|0|||||||||0.00|0.00|0.00|DXN|PP99||-0.02|47.20|0.00|0.00|-0.02|0.00|0.00|0.00|0.00000
1|16210792|2|5|999||0|0|||||||||0.00|0.00|0.00|DXN|PV99||0.08|47.20|0.00|0.00|0.08|0.00|0.00|0.00|0.00000
1|15416661|3|11|133||0|0|||||||||0.00|0.00|0.00|DXN|PV99||0.11|94.40|0.00|0.00|0.11|0.00|0.00|0.00|0.00000
1|16025427|5|9|25||0|0|||||||||0.00|0.00|0.00|DXN|PT20||-58.29|76000.00|0.00|0.00|-58.29|0.00|0.00|0.00|0.00000
1|16990761|6|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||-46.27|100000.00|0.00|0.00|-46.27|0.00|0.00|0.00|0.00000
1|15824362|7|257|211||0|0|||||||||0.00|0.00|0.00|DXN|PT20||91.70|320000.00|0.00|0.00|91.70|0.00|0.00|0.00|0.00000
1|16988401|8|11|111||0|0|||||||||0.00|0.00|0.00|DXN|PT20||110.14|280000.00|0.00|0.00|110.14|0.00|0.00|0.00|0.00000
1|16087438|9|104|11||0|0|||||||||0.00|0.00|0.00|DXN|PT20||35.91|20000.00|0.00|0.00|35.91|0.00|0.00|0.00|0.00000
1|16080870|11|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||32.59|100000.00|0.00|0.00|32.59|0.00|0.00|0.00|0.00000
1|16802228|12|132|21||0|0|||||||||0.00|0.00|0.00|DXN|PT20||34.65|100000.00|0.00|0.00|34.65|0.00|0.00|0.00|0.00000
1|16272846|13|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||44.25|140000.00|0.00|0.00|44.25|0.00|0.00|0.00|0.00000

I tried with a table with varchar2 for all fields and the data is inserted in the right field.

Thanks

[Updated on: Mon, 14 August 2006 19:17]

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187670 is a reply to message #187668]

Mon, 14 August 2006 20:16

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You should modify the column for PRIMA_ANUAL in the control file as follows:

from

PRIMA_ANUAL "TO_NUMBER(':PRIMA_ANUAL')",

to

PRIMA_ANUAL DECIMAL external,

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187671 is a reply to message #187670]

Mon, 14 August 2006 20:32

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

i did it. And i did too:

Change the datatype in my control file with CHAR, DECIMAL EXTERNAL, field "replace (:field, '.',',')" and always i have the same error.

set nls_numeric_characters= '.,';

and alter session set nls_numeric_characters= '.,';

and nothing happens. Always i have the same error.

But i´m going to try again.
Thanks

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187672 is a reply to message #187671]

Mon, 14 August 2006 20:39

ebrian
Messages: 2794
Registered: April 2006

Senior Member

I simply modified the PRIMA_ANUAL to 'DECIMAL external' and the file loaded successfully for me.

My NLS_NUMERIC_CHARACTERS is defaulted to '.,'

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187674 is a reply to message #187672]

Mon, 14 August 2006 20:52

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

How did you set your nls_numeric_characters??

I'm in a client and don't have DBA privileges.

[Updated on: Mon, 14 August 2006 20:53]

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187675 is a reply to message #187674]

Mon, 14 August 2006 20:56

ebrian
Messages: 2794
Registered: April 2006

Senior Member

What operating system are you using? If UNIX, what shell?

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187677 is a reply to message #187675]

Mon, 14 August 2006 20:58

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Win 2000.

I did:

set nls_numeric_characters='.,';

Last week i did the same load and works fine. I don't know what happens now!

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187678 is a reply to message #187677]

Mon, 14 August 2006 21:01

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Don't use the semicolon. It should be set at the command line window as follows:

C:\>set nls_numeric_characters='.,'

Can verify the setting with:

C:\>echo %nls_numeric_characters%
'.,'

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187680 is a reply to message #187678]

Mon, 14 August 2006 21:07

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

No men, It doesn't work. What else do you need to know?

Your set it's ok but the load fails. With the same error.

[Updated on: Mon, 14 August 2006 21:07]

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187682 is a reply to message #187680]

Mon, 14 August 2006 21:13

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Would it be possible for you to upload a copy of your .log and .bad file?

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187683 is a reply to message #187682]

Mon, 14 August 2006 21:17

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

MY LOG:

SQL*Loader: Release 9.2.0.1.0 - Production on Lun Ago 14 21:01:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Control File: D:\Espentia\DM08_Client_centricity\procedimiento_carga\ctls\DM08_POLIZA.CTL
Character Set UTF8 specified for all input.

Data File: D:\Espentia\DM08_Client_centricity\procedimiento_carga\DATOS.TXT
Bad File: D:\Espentia\DM08_Client_centricity\procedimiento_carga\ctls\DATOS.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 500
Bind array: 1500000 rows, maximum of 1500000 bytes
Continuation: none specified
Path used: Conventional

Table "DM08_POLIZA", 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
------------------------------ ---------- ----- ---- ---- ---------------------
ID_BASE_ORIGEN FIRST * | CHARACTER
(BOUNDFILLER FIELD)
ID_ORIGEN NEXT * | CHARACTER
(BOUNDFILLER FIELD)
ID_PERSONA NEXT * | CHARACTER
SQL string for column : "LPAD(:ID_BASE_ORIGEN,2,0)||LPAD(:ID_ORIGEN,15,0)"
FILLER_ID_BASE_ORIGEN 1 * | CHARACTER
(FILLER FIELD)
FILLER_ID_ORIGEN NEXT * | CHARACTER
(FILLER FIELD)
"ID_POLIZA" NEXT * | CHARACTER
FILLER_ID_RETENEDOR NEXT * | CHARACTER
(FILLER FIELD)
FILLER_ID_UNIDAD_PAGO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_UNIDAD_ADMON NEXT * | CHARACTER
(FILLER FIELD)
FILLERID_NIVEL_GOBIERNO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_ID_TIPO_PODER NEXT * | CHARACTER
(FILLER FIELD)
FILLER_SECTOR NEXT * | CHARACTER
(FILLER FIELD)
FILLER_NUM_SUBGPO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_AREA NEXT * | CHARACTER
(FILLER FIELD)
FILLER_TIPO_CONTRATO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_PUESTO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_NIVEL_LABORAL NEXT * | CHARACTER
(FILLER FIELD)
FILLER_FECHA_INGRESO NEXT * | DATE DD/MM/RR
(FILLER FIELD)
FILLER_FECHA_PRIMER_COT NEXT * | DATE DD/MM/RR
(FILLER FIELD)
FILLER_SUELDO_BASE NEXT * | CHARACTER
(FILLER FIELD)
FILLER_SUELDO_NETO NEXT * | CHARACTER
(FILLER FIELD)
FILLER_SEGURO_GMM NEXT * | CHARACTER
(FILLER FIELD)
"CANAL" NEXT * | CHARACTER
ID_TIPO_PLAN NEXT * | CHARACTER
SQL string for column : "LPAD(:ID_BASE_ORIGEN,2,0)||LPAD(:ID_TIPO_PLAN,5,0)"
"NUM_CATEGORIA" NEXT * | CHARACTER
"PRIMA_ANUAL" NEXT * | CHARACTER
"SUM_ASEGURADA" NEXT * | CHARACTER
"PRIMA_ANUAL_DESC" NEXT * | CHARACTER
"EXTRA_PRIMA" NEXT * | CHARACTER
"PRIMA_BASE" NEXT * | CHARACTER
"PRIMA_BASE_CT" NEXT * | CHARACTER
"PRIMA_POTENCIADA" NEXT * | CHARACTER
"PRIMA_POTENCIADA_CT" NEXT * | CHARACTER
"PRIMA_BENEF_ADICIONAL" NEXT * | CHARACTER

Record 1: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 2: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 3: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 4: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 5: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 6: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 7: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 8: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 9: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 10: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 11: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 12: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 13: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 14: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 15: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 16: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 17: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 18: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 19: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 20: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 21: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 22: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 23: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 24: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 25: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 26: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 27: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 28: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 29: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 30: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 31: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 32: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 33: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 34: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 35: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 36: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 37: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 38: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 39: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 40: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 41: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 42: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 43: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 44: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 45: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 46: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 47: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 48: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 49: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 50: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Record 51: Rejected - Error on table "DM08_POLIZA", column "PRIMA_ANUAL".
ORA-01722: invalid number

Table "DM08_POLIZA":
0 Rows successfully loaded.
51 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: 1498464 bytes(363 rows)
Read buffer bytes: 1500000

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

Run began on Lun Ago 14 21:01:36 2006
Run ended on Lun Ago 14 21:01:39 2006

Elapsed time was: 00:00:02.93
CPU time was: 00:00:00.05

MY .BAD

1|16736677|1|10|999||0|0|||||||||0.00|0.00|0.00|DXN|PP99||-0.02|47.20|0.00|0.00|-0.02|0.00|0.00|0.00|0.00000
1|16210792|2|5|999||0|0|||||||||0.00|0.00|0.00|DXN|PV99||0.08|47.20|0.00|0.00|0.08|0.00|0.00|0.00|0.00000
1|15416661|3|11|133||0|0|||||||||0.00|0.00|0.00|DXN|PV99||0.11|94.40|0.00|0.00|0.11|0.00|0.00|0.00|0.00000
1|16025427|5|9|25||0|0|||||||||0.00|0.00|0.00|DXN|PT20||-58.29|76000.00|0.00|0.00|-58.29|0.00|0.00|0.00|0.00000
1|16990761|6|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||-46.27|100000.00|0.00|0.00|-46.27|0.00|0.00|0.00|0.00000
1|15824362|7|257|211||0|0|||||||||0.00|0.00|0.00|DXN|PT20||91.70|320000.00|0.00|0.00|91.70|0.00|0.00|0.00|0.00000
1|16988401|8|11|111||0|0|||||||||0.00|0.00|0.00|DXN|PT20||110.14|280000.00|0.00|0.00|110.14|0.00|0.00|0.00|0.00000
1|16087438|9|104|11||0|0|||||||||0.00|0.00|0.00|DXN|PT20||35.91|20000.00|0.00|0.00|35.91|0.00|0.00|0.00|0.00000
1|16080870|11|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||32.59|100000.00|0.00|0.00|32.59|0.00|0.00|0.00|0.00000
1|16802228|12|132|21||0|0|||||||||0.00|0.00|0.00|DXN|PT20||34.65|100000.00|0.00|0.00|34.65|0.00|0.00|0.00|0.00000
1|16272846|13|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||44.25|140000.00|0.00|0.00|44.25|0.00|0.00|0.00|0.00000
1|15048045|14|153|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||105.79|100000.00|0.00|0.00|105.79|0.00|0.00|0.00|0.00000
1|15148171|15|153|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||61.23|250000.00|0.00|0.00|61.23|0.00|0.00|0.00|0.00000
1|16126581|16|153|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||73.47|300000.00|0.00|0.00|73.47|0.00|0.00|0.00|0.00000
1|15288948|17|11|116||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.00|370000.00|0.00|0.00|0.00|0.00|0.00|0.00|0.00000
1|16380141|18|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||62.37|90000.00|0.00|0.00|62.37|0.00|0.00|0.00|0.00000
1|15231225|19|68|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||39.11|60000.00|0.00|0.00|39.11|0.00|0.00|0.00|0.00000
1|16427447|20|68|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||65.74|100000.00|0.00|0.00|65.74|0.00|0.00|0.00|0.00000
1|16847526|21|68|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||72.88|80000.00|0.00|0.00|72.88|0.00|0.00|0.00|0.00000
1|15526051|22|11|116||0|0|||||||||0.00|0.00|0.00|DXN|PT20||90.00|200000.00|0.00|0.00|90.00|0.00|0.00|0.00|0.00000
1|15073520|23|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||139.36|100000.00|0.00|0.00|139.36|0.00|0.00|0.00|0.00000
1|16104825|24|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||79.46|100000.00|0.00|0.00|79.46|0.00|0.00|0.00|0.00000
1|16139096|25|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||145.79|160000.00|0.00|0.00|145.79|0.00|0.00|0.00|0.00000
1|16931694|26|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||62.73|80000.00|0.00|0.00|62.73|0.00|0.00|0.00|0.00000
1|15161077|27|7|999||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.01|80000.00|0.00|0.00|0.01|0.00|0.00|0.00|0.00000
1|15256659|28|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||72.06|50000.00|0.00|0.00|72.06|0.00|0.00|0.00|0.00000
1|15477396|29|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.01|100000.00|0.00|0.00|0.01|0.00|0.00|0.00|0.00000
1|15703938|30|38|16||0|0|||||||||0.00|0.00|0.00|DXN|PT20||72.07|65000.00|0.00|0.00|72.07|0.00|0.00|0.00|0.00000
1|16182462|31|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||104.88|140000.00|0.00|0.00|104.88|0.00|0.00|0.00|0.00000
1|16228031|32|8|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||258.25|152000.00|0.00|0.00|258.25|0.00|0.00|0.00|0.00000
1|16393879|33|11|116||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.02|220000.00|0.00|0.00|0.02|0.00|0.00|0.00|0.00000
1|16579266|34|11|116||0|0|||||||||0.00|0.00|0.00|DXN|PT20||533.75|555000.00|0.00|0.00|533.75|0.00|0.00|0.00|0.00000
1|16696945|35|68|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.01|60000.00|0.00|0.00|0.01|0.00|0.00|0.00|0.00000
1|15890133|36|201|9||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.01|60000.00|0.00|0.00|0.01|0.00|0.00|0.00|0.00000
1|16249462|39|12|13||0|0|||||||||0.00|0.00|0.00|DXN|PT20||-153.52|100000.00|0.00|0.00|-153.52|0.00|0.00|0.00|0.00000
1|15021089|40|259|297||0|0|||||||||0.00|0.00|0.00|DXN|PT20||109.87|280000.00|0.00|0.00|109.87|0.00|0.00|0.00|0.00000
1|16362093|41|259|297||0|0|||||||||0.00|0.00|0.00|DXN|PT20||148.18|350000.00|0.00|0.00|148.18|0.00|0.00|0.00|0.00000
1|16084073|42|159|311||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.00|100000.00|0.00|0.00|0.00|0.00|0.00|0.00|0.00000
1|16802493|43|257|229||0|0|||||||||0.00|0.00|0.00|DXN|PT20||95.91|400000.00|0.00|0.00|95.91|0.00|0.00|0.00|0.00000
1|15803712|45|12|28||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.01|100000.00|0.00|0.00|0.01|0.00|0.00|0.00|0.00000
1|16928706|46|12|28||0|0|||||||||0.00|0.00|0.00|DXN|PT20||89.77|50000.00|0.00|0.00|89.77|0.00|0.00|0.00|0.00000
1|16935296|47|11|134||0|0|||||||||0.00|0.00|0.00|DXN|PP99||191.44|150000.00|0.00|0.00|191.44|0.00|0.00|0.00|0.00000
1|15802127|50|202|91||0|0|||||||||0.00|0.00|0.00|DXN|PT20||112.33|20000.00|0.00|0.00|112.33|0.00|0.00|0.00|0.00000
1|16773983|52|12|15||0|0|||||||||0.00|0.00|0.00|DXN|PT20||81.24|200000.00|0.00|0.00|81.24|0.00|0.00|0.00|0.00000
1|16458724|53|12|15||0|0|||||||||0.00|0.00|0.00|DXN|PT20||69.74|200000.00|0.00|0.00|69.74|0.00|0.00|0.00|0.00000
1|15909943|55|7|999||0|0|||||||||0.00|0.00|0.00|DXN|PT20||95.31|150000.00|0.00|0.00|95.31|0.00|0.00|0.00|0.00000
1|16981869|56|11|108||0|0|||||||||0.00|0.00|0.00|DXN|PT20||0.02|250000.00|0.00|0.00|0.02|0.00|0.00|0.00|0.00000
1|15652564|57|11|108||0|0|||||||||0.00|0.00|0.00|DXN|PT20||74.62|130000.00|0.00|0.00|74.62|0.00|0.00|0.00|0.00000
1|15147264|59|11|108||0|0|||||||||0.00|0.00|0.00|DXN|PT20||441.27|539151.00|0.00|0.00|441.27|0.00|0.00|0.00|0.00000
1|16090364|60|11|108||0|0|||||||||0.00|0.00|0.00|DXN|PT20||59.00|110000.00|0.00|0.00|59.00|0.00|0.00|0.00|0.00000
1|15286356|61|7|999||0|0|||||||||0.00|0.00|0.00|DXN|PT20||52.54|120000.00|0.00|0.00|52.54|0.00|0.00|0.00|0.00000

Thanks again.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187684 is a reply to message #187683]

Mon, 14 August 2006 21:21

ebrian
Messages: 2794
Registered: April 2006

Senior Member

OK...this should definitely work for you.

When you set nls_numeric_characters at the command prompt, DO NOT use the single quotes around the .,

Set it as follows:

C:\>set nls_numeric_characters=.,

Notice NO quotes around ., above.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187685 is a reply to message #187684]

Mon, 14 August 2006 21:27

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Chief you are a master. Cool

Cool

Can you post some links for me, please?
You win mexican beers. (un carton de chelas)mexican expression Razz

Razz

Thank you very much
Your friend Alex

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187686 is a reply to message #187685]

Mon, 14 August 2006 21:28

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You are quite welcome. Glad we could work through it !!!!

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187688 is a reply to message #187686]

Mon, 14 August 2006 21:36

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

If some day you plain to visit Mexico please call me... Smile

Smile

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187690 is a reply to message #187264]

Mon, 14 August 2006 21:41

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>If some day you plain to visit Mexico please call me
Where in Mexico?
It is a fairly large & beautiful country

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187692 is a reply to message #187690]

Mon, 14 August 2006 21:48

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Mexico City, but we can go some places near. There are a lot of places to go. And to drink much more. Laughing

Laughing

Where are you from??

Well I see you later its time to rest a few (thanks to you)before to work again.

Greetings
Alex

[Updated on: Mon, 14 August 2006 21:50]

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187744 is a reply to message #187692]

Tue, 15 August 2006 06:25

ebrian
Messages: 2794
Registered: April 2006

Senior Member

superoscarin@hotmail.com wrote on Mon, 14 August 2006 21:48

Mexico City, but we can go some places near. There are a lot of places to go. And to drink much more.

Where are you from??

Never know when I might have to take you up on that offer !!

I'm from Virginia - US.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187767 is a reply to message #187744]

Tue, 15 August 2006 08:49

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

You are welcome anytime.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187825 is a reply to message #187767]

Tue, 15 August 2006 19:17

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

One more question:

Are there any way to load data faster with sql loader?? I'm loading about 7 millions of records and it takes 4 hours and it still doesn't finish...

You can review my code...
Thanks

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187826 is a reply to message #187264]

Tue, 15 August 2006 19:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Are there any way to load data faster with sql loader??
Possibly, but 1st you need to KNOW where all the time is being consumed.
Run SQL_TRACE during the load & then run the trace file thru TKPROF to see where the bottleneck exists.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #187979 is a reply to message #187825]

Wed, 16 August 2006 10:33

ebrian
Messages: 2794
Registered: April 2006

Senior Member

As anacedent pointed out, you first may need to consider where your time is being consumed.

7 million rows really isn't alot of data. May help to determine if you need to do a CONVENTIONAL or DIRECT path load. This page provides some good performance tuning tips for both methods Conventional and Direct Path Loads.

Report message to a moderator

Re: LOAD INVALID NUMBER [message #188262 is a reply to message #187979]

Thu, 17 August 2006 12:35

superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico

Senior Member

Thank guys

I'll try with these.
Greetings.
Alex

Report message to a moderator

Previous Topic:	insert records via dump
Next Topic:	SQL LOADER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jun 26 13:53:50 CDT 2024