OraFAQ Forum: Server Utilities » SQL LOADER ORA 01772

Home » RDBMS Server » Server Utilities » SQL LOADER ORA 01772

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

SQL LOADER ORA 01772 [message #204679]

Tue, 21 November 2006 12:09

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

Report message to a moderator

Re: SQL LOADER ORA 01772 [message #204683 is a reply to message #204679]

Tue, 21 November 2006 12:38

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: SQL LOADER ORA 01772 [message #204684 is a reply to message #204683]

Tue, 21 November 2006 12:45

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..

Report message to a moderator

Previous Topic:	Type object not imported during schema import
Next Topic:	Multiple references to same table in control file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 12:39:35 CST 2024