Home » RDBMS Server » Server Utilities » SQL LOADER ORA 01772
SQL LOADER ORA 01772 [message #204679] Tue, 21 November 2006 12:09 Go to next message
centurez
Messages: 5
Registered: August 2006
Location: USA
Junior Member
CAN ANY ONE HELP ME WITH THIS...

I AM USING SQLLOADER TO LOAD DATA INTO ONE OF MY TABLES..

SYNTAX :- sqlldr user/password control=control.ctl

TABLE STRUCTURE :-
SQL> desc engagements
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 ENGAGEMENTNUMBER                          NOT NULL NUMBER(6)
 STARTDATE                                          DATE
 ENDDATE                                            DATE
 STARTTIME                                          DATE
 STOPTIME                                           DATE
 CONTRACTPRICE                                      NUMBER(10,2)
 CUSTOMERID                                NOT NULL NUMBER(7)
 AGENTID                                   NOT NULL NUMBER(5)
 ENTERTAINERID                             NOT NULL NUMBER(10)

CONTROL FILE:-
LOAD DATA
INFILE '/home/oracle/laxman/engagements.csv'
INSERT INTO TABLE laxman.engagements
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ENGAGEMENTNUMBER,
STARTDATE date 'mm/dd/yyyy',
ENDDATE date 'mm/dd/yyyy',
STARTTIME date 'hh24:mi:ss',
STOPTIME date 'hh24:mi:ss',
CONTRACTPRICE,
CUSTOMERID,
AGENTID,
ENTERTAINERID
)

MY CSV FILE SAMPLE:-

1,7/1/99,7/4/99,13:00:00,15:00:00,170.00,10014,1,1009
2,7/1/99,7/5/99,13:00:00,15:00:00,200.00,10006,4,1004
3,7/10/99,7/15/99,13:00:00,15:00:00,590.00,10001,3,1005
4,7/11/99,7/17/99,20:00:00,0:00:00,470.00,10007,3,1004
5,7/11/99,7/14/99,16:00:00,19:00:00,1130.00,10006,5,1003
6,7/10/99,7/14/99,15:00:00,21:00:00,2300.00,10014,7,1008


ERROR I AM GETTING IN LOG FILE:-

SQL*Loader: Release 9.2.0.4.0 - Production on Tue Nov 21 01:21:20 2006

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

Control File: engagements.ctl
Data File: /home/oracle/laxman/temp/engagements.csv
Bad File: engagements.bad
Discard File: none specified

(Allow all discards)

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

Table LAXMAN.ENGAGEMENTS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENGAGEMENTNUMBER FIRST * , O(") CHARACTER
STARTDATE NEXT * , O(") DATE mm/dd/yyyy
ENDDATE NEXT * , O(") DATE mm/dd/yyyy
STARTTIME NEXT * , O(") DATE hh24:mi:ss
STOPTIME NEXT * , O(") DATE hh24:mi:ss
CONTRACTPRICE NEXT * , O(") CHARACTER
CUSTOMERID NEXT * , O(") CHARACTER
AGENTID NEXT * , O(") CHARACTER
ENTERTAINERID NEXT * , O(") CHARACTER

Record 1: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number

Record 2: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number

Record 3: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number

Record 4: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number

Record 5: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number

Record 6: Rejected - Error on table LAXMAN.ENGAGEMENTS, column ENTERTAINERID.
ORA-01722: invalid number


MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table LAXMAN.ENGAGEMENTS:
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: 148608 bytes(64 rows)
Read buffer bytes: 1048576

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

Run began on Tue Nov 21 01:21:20 2006
Run ended on Tue Nov 21 01:21:21 2006

Elapsed time was: 00:00:00.97
CPU time was: 00:00:00.03


CAN ANYONE HELP HOW DO I GET RID OF THIS PROBLEM??
MY NLS_NUMERIC_CHARACTER = .,

I have tried loading it in other boxes where it is successful but this box is the only one that is giving problems..

Please help
Re: SQL LOADER ORA 01772 [message #204683 is a reply to message #204679] Tue, 21 November 2006 12:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I cannot reproduce your case.
Post the DDL of table.
I Assumed the DDL from your post and tried with this and it worked as intended
scott@9i > desc sample
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ENGAGEMENTNUMBER                                               NUMBER
 STARTDATE                                                      DATE
 ENDDATE                                                        DATE
 STARTTIME                                                      DATE
 STOPTIME                                                       DATE
 CONTRACTPRICE                                                  NUMBER
 CUSTOMERID                                                     NUMBER
 AGENTID                                                        NUMBER
 ENTERTAINERID                                                  NUMBER
Re: SQL LOADER ORA 01772 [message #204684 is a reply to message #204683] Tue, 21 November 2006 12:45 Go to previous message
centurez
Messages: 5
Registered: August 2006
Location: USA
Junior Member
Mahesh that looks good..

I am also able to do it in other databases... but this problem persists in this particular database...

I am wondering if I have to set some environments to make this work..
Previous Topic: Type object not imported during schema import
Next Topic: Multiple references to same table in control file
Goto Forum:
  


Current Time: Thu Dec 08 22:27:54 CST 2016

Total time taken to generate the page: 0.14771 seconds