Home » RDBMS Server » Server Utilities » Running into issues when using TOAD to load data using SQL Loader Wizard
Running into issues when using TOAD to load data using SQL Loader Wizard [message #542397] Tue, 07 February 2012 02:29 Go to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
Toad is generating this control file

OPTIONS ( ERRORS=1000, SILENT=(HEADER))
LOAD DATA
INFILE 'C:\WR73088\ISR_owners_test.dat' "Str ',' "
BADFILE 'C:\WR73088\ISR_owners_test.bad'
DISCARDFILE 'C:\WR73088\ISR_owners_test.dsc'
DISCARDMAX 1000

INTO TABLE "BOSE"."TMP_ONETIME_ISR_EZSTAT"
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ISR_QUOTE_ID,
CUST_NAME,
STATUS,
FIRST_NAME,
LAST_NAME,
PHONE_NBR,
EMAIL_ADDR,
PRIVILEGE,
CREATE_DT,
CREATE_BY_FIRST_NAME,
CREATE_BY_LAST_NAME,
MODIFY_DT)

I have 2 sample rows

392448,Intel,D,Lang,Pathi,703-863-5788,bose.kamineni@gmail.com,OrderManager,2/3/2012 14:19,Lang,Pathi,2/3/2012 14:19
392256,TEST ISR,D,Jaime,Cedeno,703-863-5788,roy.kamineni@gmail.com,Sales,2/2/2012 14:28,Jaime,Cedeno,2/2/2012 16:09

---------------
Log file is

Record 2: Rejected - Error on table "BOSE"."TMP_ONETIME_ISR_EZSTAT", column ISR_QUOTE_ID.
ORA-01722: invalid number

Record 3: Rejected - Error on table "BOSE"."TMP_ONETIME_ISR_EZSTAT", column ISR_QUOTE_ID.
ORA-01722: invalid number

Record 4: Rejected - Error on table "BOSE"."TMP_ONETIME_ISR_EZSTAT", column ISR_QUOTE_ID.
ORA-01722: invalid number

Record 5: Rejected - Error on table "BOSE"."TMP_ONETIME_ISR_EZSTAT", column ISR_QUOTE_ID.
ORA-01722: invalid number

Record 6: Rejected - Error on table "BOSE"."TMP_ONETIME_ISR_EZSTAT", column ISR_QUOTE_ID.
ORA-01722: invalid number

Only one record gets inserted-data is in 1 field ISR_QUOTE_ID with value of 392448.

I am not able to understand why data is getting split into several records- and data is getting loaded into first column of my database.

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542402 is a reply to message #542397] Tue, 07 February 2012 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you also post CREATE TABLE statement (so that we could try to load these records)?

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542403 is a reply to message #542402] Tue, 07 February 2012 02:44 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
CREATE TABLE tmp_onetime_isr_ezstat(
isr_quote_id NUMBER(38),
cust_name VARCHAR2(1024),
status CHAR,
first_name VARCHAR2(100),
last_name VARCHAR2(100),
phone_nbr VARCHAR2(20),
email_addr VARCHAR2(1024),
privilege VARCHAR2(100),
create_dt TIMESTAMP,
create_by_first_name VARCHAR2(100),
create_by_last_name VARCHAR2(100),
modify_dt TIMESTAMP);

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542406 is a reply to message #542397] Tue, 07 February 2012 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I think Oracle is correct and most likely there is something that you don't see in your file.

Use an hexadecimal editor to see and take a closer look to what is at the end of the first line and the beginning of the next one. I bet there is a non printable character like CR (chr(13)).

Regards
Michel

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542408 is a reply to message #542406] Tue, 07 February 2012 02:57 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is result of my test with your table and control file:
Table TMP_ONETIME_ISR_EZSTAT:
  2 Rows successfully loaded.
  0 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:                 198144 bytes(64 rows)
Read   buffer bytes: 1048576

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

Everything seems to be OK ...

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542410 is a reply to message #542408] Tue, 07 February 2012 03:02 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
Did you use TOAD or used SQL Loader on unix?

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542412 is a reply to message #542410] Tue, 07 February 2012 03:20 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Command prompt on MS Windows.

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542414 is a reply to message #542410] Tue, 07 February 2012 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
bkamineni wrote on Tue, 07 February 2012 10:02
Did you use TOAD or used SQL Loader on unix?


Did you investigate what I said?

Regards
Michel

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542419 is a reply to message #542414] Tue, 07 February 2012 03:50 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
I am testing the data file with only 1 record but still I get the errors- for some reason it thinks that data has to be loaded into the first field.

I had used XVI32 to view the file in hex and there is no weird character at the end of line

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542422 is a reply to message #542419] Tue, 07 February 2012 03:57 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to do it the oldfashioned way? You know, simply by typing
> sqlldr scott/tiger@ora10 control=test.ctl log=test.log
(of course, you'd adjust it according to your environment).

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542423 is a reply to message #542422] Tue, 07 February 2012 04:03 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
I tried that also and I get the similar error- only data is loaded into 1st field and for the rest of the data in the file it assumes that it has to load into first database column.

I will try to use another machine at work and see if it happening on my laptop only

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542425 is a reply to message #542423] Tue, 07 February 2012 04:05 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might also try to switch from a comma (as a delimiter) to something else (semi-colon, hash, whatever), just to see what happens.

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542433 is a reply to message #542425] Tue, 07 February 2012 04:20 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
I changed the delimiter to | and still get the similar errors. Let me test it on other machine and see what might be the issue.

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542497 is a reply to message #542433] Tue, 07 February 2012 09:12 Go to previous messageGo to next message
bkamineni
Messages: 7
Registered: September 2011
Junior Member
I was able to identify the problem

INFILE 'C:\WR73088\ISR_owners_test.dat' "Str ',' "

"Str ',' " is making loader to assume that each record is separated by "," so after reading the first field in 1st row of the data file - it attempts to map the second field of 1st row again to firt column of the data base table.

By removing "Str ',' " it attaempt to map the fields to appropriate columns.

I had to formatting of the input data values to map to database date columns.

Now I had successfully loaded data into test database table.

Report message to a moderator

Re: Running into issues when using TOAD to load data using SQL Loader Wizard [message #542528 is a reply to message #542497] Tue, 07 February 2012 13:46 Go to previous message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you for letting us know!

Report message to a moderator

Previous Topic: EXPDP: how exclude table
Next Topic: ORACLE EXP IMP problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Feb 07 00:45:25 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.