Home » RDBMS Server » Server Utilities » LOAD INVALID NUMBER
LOAD INVALID NUMBER [message #187264] Fri, 11 August 2006 09:40 Go to next message
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?
Re: LOAD INVALID NUMBER [message #187662 is a reply to message #187264] Mon, 14 August 2006 17:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
A copy of your control file and some sample data will help troubleshoot the problem.
Re: LOAD INVALID NUMBER [message #187668 is a reply to message #187264] Mon, 14 August 2006 18:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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,
Re: LOAD INVALID NUMBER [message #187671 is a reply to message #187670] Mon, 14 August 2006 20:32 Go to previous messageGo to next message
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


Re: LOAD INVALID NUMBER [message #187672 is a reply to message #187671] Mon, 14 August 2006 20:39 Go to previous messageGo to next message
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 '.,'
Re: LOAD INVALID NUMBER [message #187674 is a reply to message #187672] Mon, 14 August 2006 20:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
What operating system are you using? If UNIX, what shell?
Re: LOAD INVALID NUMBER [message #187677 is a reply to message #187675] Mon, 14 August 2006 20:58 Go to previous messageGo to next message
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!
Re: LOAD INVALID NUMBER [message #187678 is a reply to message #187677] Mon, 14 August 2006 21:01 Go to previous messageGo to next message
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%
'.,'
Re: LOAD INVALID NUMBER [message #187680 is a reply to message #187678] Mon, 14 August 2006 21:07 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Would it be possible for you to upload a copy of your .log and .bad file?
Re: LOAD INVALID NUMBER [message #187683 is a reply to message #187682] Mon, 14 August 2006 21:17 Go to previous messageGo to next message
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.
Re: LOAD INVALID NUMBER [message #187684 is a reply to message #187683] Mon, 14 August 2006 21:21 Go to previous messageGo to next message
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.
Re: LOAD INVALID NUMBER [message #187685 is a reply to message #187684] Mon, 14 August 2006 21:27 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Chief you are a master. Cool

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

Thank you very much
Your friend Alex
Re: LOAD INVALID NUMBER [message #187686 is a reply to message #187685] Mon, 14 August 2006 21:28 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You are quite welcome. Glad we could work through it !!!!
Re: LOAD INVALID NUMBER [message #187688 is a reply to message #187686] Mon, 14 August 2006 21:36 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

If some day you plain to visit Mexico please call me... Smile
Re: LOAD INVALID NUMBER [message #187690 is a reply to message #187264] Mon, 14 August 2006 21:41 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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
Re: LOAD INVALID NUMBER [message #187692 is a reply to message #187690] Mon, 14 August 2006 21:48 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
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.
Re: LOAD INVALID NUMBER [message #187767 is a reply to message #187744] Tue, 15 August 2006 08:49 Go to previous messageGo to next message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

You are welcome anytime.
Re: LOAD INVALID NUMBER [message #187825 is a reply to message #187767] Tue, 15 August 2006 19:17 Go to previous messageGo to next message
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
Re: LOAD INVALID NUMBER [message #187826 is a reply to message #187264] Tue, 15 August 2006 19:31 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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.
Re: LOAD INVALID NUMBER [message #187979 is a reply to message #187825] Wed, 16 August 2006 10:33 Go to previous messageGo to next message
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.
Re: LOAD INVALID NUMBER [message #188262 is a reply to message #187979] Thu, 17 August 2006 12:35 Go to previous message
superoscarin@hotmail.com
Messages: 101
Registered: July 2006
Location: Mexico
Senior Member

Thank guys

I'll try with these.
Greetings.
Alex
Previous Topic: insert records via dump
Next Topic: SQL LOADER
Goto Forum:
  


Current Time: Tue Dec 06 16:03:02 CST 2016

Total time taken to generate the page: 0.10282 seconds