Home » RDBMS Server » Server Utilities » SQLLDR doesn't work when last columns are not null (Oracle 10gR2)
SQLLDR doesn't work when last columns are not null [message #327424] Mon, 16 June 2008 06:29 Go to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
Dear all,

I am inserting data to the table which has 180 fields. I also tried to add "USE TRAILING NULLCOLS" but still no effect. Its control file is:

load data infile '$DATAFILE_PATH' append into table T4000 fields terminated by "," optionally enclosed by '"' (C1,C2,C3 DATE "YYYY-MM-DD HH24:MI:SS",C4,C5 DATE "YYYY-MM-DD HH24:MI:SS",C6,C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34 DATE "YYYY-MM-DD HH24:MI:SS",C35,C36,C37,C38,C39,C40,C41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,C54,C55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,C68,C69,C70,C71,C72,C73,C74,C75,C76,C77,C78,C79,C80,C81,C82,C83,C84,C85,C86,C87,C88,C89,C90,C91,C92,C93,C94,C95,C96,C97,C98,C99,C100,C101,C102,C103,C104,C105,C106,C107,C108,C109,C110,C111,C112,C113,C114,C115,C116,C117,C118,C119,C120,C121,C122,C123,C124,C125,C126,C127,C128,C129,C130,C131,C132,C133,C134,C135,C136,C137,C138,C139,C140,C141,C142 DATE "YYYY-MM-DD HH24:MI:SS",C143,C144,C145,C146,C147,C148,C149,C150,C151,C152,C153,C154,C155,C156,C157,C158,C159,C160,C161,C162,C163,C164,C165,C166,C167,C168,C169,C170,C171,C172,C173,C174,C175,C177,C179 DATE "YYYY-MM-DD HH24:MI:SS",C180 DATE "YYYY-MM-DD HH24:MI:SS")


Following error occurs when C179 is not null.
Record 289: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 308: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 463: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 695: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 910: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1010: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1033: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1193: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1225: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1341: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Record 1455: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month

Specify SKIP=1454 when continuing the load.

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table T4000:
  1444 Rows successfully loaded.
  11 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.


Sample data is given below:

"99","99","2008-09-06 06:07:22",,"","AAAAAAAAA","0",,"","BBBBBBBB","","0","","","","","","","","","","","","","","",999,,,0,"AAAAAAAAA","","","2008-09-06 06:07:22","","","","","","","","","","","","","","","","","","","","","","","","","","",,"","","","","","",,,,,"","","","","","","","","","","","","AAAAAAAAAAAAAAAAA","AAAAAAAAAAAAAAAAAA","AAAAAAA","",1,"AAAAAAAAAAA","BBBBBBBBBBB","BBBBBBBB","BBBBBBB","",9999,,,,9,9,9,9,9999,"0","9999","",0,0,"","","",,"","","","",,,,,,,"",,,,,"",,,1,"","",1,"","","","",,,,,"",,,"","","",,,,"",,"","","","","","","","",,,"","999999999",,,,,99.99,9,,"","99999","","",,,"","2008-09-08 23:59:59","2008-09-08 23:59:59"


Table description is here:

Column Name	ID	Pk	Null?	Data Type	Default	Histogram
						
C1	1		Y	VARCHAR2 (30 Byte)		Yes
C2	2		Y	VARCHAR2 (30 Byte)		Yes
C3	3		N	DATE		Yes
C4	4		Y	INTEGER		Yes
C5	5		Y	DATE		Yes
C6	6		Y	VARCHAR2 (30 Byte)		Yes
C7	7		Y	VARCHAR2 (30 Byte)		Yes
C8	8		Y	INTEGER		Yes
C9	9		Y	VARCHAR2 (30 Byte)		Yes
C10	10		Y	VARCHAR2 (30 Byte)		Yes
C11	11		Y	VARCHAR2 (30 Byte)		Yes
C12	12		Y	VARCHAR2 (30 Byte)		Yes
C13	13		Y	VARCHAR2 (30 Byte)		Yes
C14	14		Y	VARCHAR2 (30 Byte)		Yes
C15	15		Y	VARCHAR2 (30 Byte)		Yes
C16	16		Y	VARCHAR2 (30 Byte)		Yes
C17	17		Y	VARCHAR2 (30 Byte)		Yes
C18	18		Y	VARCHAR2 (30 Byte)		Yes
C19	19		Y	VARCHAR2 (30 Byte)		Yes
C20	20		Y	VARCHAR2 (30 Byte)		Yes
C21	21		Y	VARCHAR2 (30 Byte)		Yes
C22	22		Y	VARCHAR2 (30 Byte)		Yes
C23	23		Y	VARCHAR2 (30 Byte)		Yes
C24	24		Y	VARCHAR2 (30 Byte)		Yes
C25	25		Y	VARCHAR2 (30 Byte)		Yes
C26	26		Y	VARCHAR2 (10 Byte)		Yes
C27	27		Y	INTEGER		Yes
C28	28		Y	INTEGER		Yes
C29	29		Y	INTEGER		Yes
C30	30		Y	INTEGER		Yes
C31	31		Y	VARCHAR2 (30 Byte)		Yes
C32	32		Y	VARCHAR2 (30 Byte)		Yes
C33	33		Y	VARCHAR2 (30 Byte)		Yes
C34	34		Y	DATE		Yes
C35	35		Y	VARCHAR2 (30 Byte)		Yes
C36	36		Y	VARCHAR2 (30 Byte)		Yes
C37	37		Y	VARCHAR2 (30 Byte)		Yes
C38	38		Y	VARCHAR2 (30 Byte)		Yes
C39	39		Y	VARCHAR2 (30 Byte)		Yes
C40	40		Y	VARCHAR2 (48 Byte)		Yes
C41	41		Y	VARCHAR2 (30 Byte)		Yes
C42	42		Y	VARCHAR2 (30 Byte)		Yes
C43	43		Y	VARCHAR2 (30 Byte)		Yes
C44	44		Y	VARCHAR2 (30 Byte)		Yes
C45	45		Y	VARCHAR2 (30 Byte)		Yes
C46	46		Y	VARCHAR2 (30 Byte)		Yes
C47	47		Y	VARCHAR2 (30 Byte)		Yes
C48	48		Y	VARCHAR2 (30 Byte)		Yes
C49	49		Y	VARCHAR2 (30 Byte)		Yes
C50	50		Y	VARCHAR2 (30 Byte)		Yes
C51	51		Y	VARCHAR2 (30 Byte)		Yes
C52	52		Y	VARCHAR2 (30 Byte)		Yes
C53	53		Y	VARCHAR2 (30 Byte)		Yes
C54	54		Y	VARCHAR2 (30 Byte)		Yes
C55	55		Y	VARCHAR2 (30 Byte)		Yes
C56	56		Y	VARCHAR2 (30 Byte)		Yes
C57	57		Y	VARCHAR2 (30 Byte)		Yes
C58	58		Y	VARCHAR2 (30 Byte)		Yes
C59	59		Y	VARCHAR2 (30 Byte)		Yes
C60	60		Y	VARCHAR2 (30 Byte)		Yes
C61	61		Y	INTEGER		Yes
C62	62		Y	VARCHAR2 (30 Byte)		Yes
C63	63		Y	VARCHAR2 (30 Byte)		Yes
C64	64		Y	VARCHAR2 (30 Byte)		Yes
C65	65		Y	VARCHAR2 (30 Byte)		Yes
C66	66		Y	VARCHAR2 (30 Byte)		Yes
C67	67		Y	VARCHAR2 (30 Byte)		Yes
C68	68		Y	INTEGER		Yes
C69	69		Y	INTEGER		Yes
C70	70		Y	INTEGER		Yes
C71	71		Y	INTEGER		Yes
C72	72		Y	VARCHAR2 (30 Byte)		Yes
C73	73		Y	VARCHAR2 (30 Byte)		Yes
C74	74		Y	VARCHAR2 (30 Byte)		Yes
C75	75		Y	VARCHAR2 (30 Byte)		Yes
C76	76		Y	VARCHAR2 (30 Byte)		Yes
C77	77		Y	VARCHAR2 (30 Byte)		Yes
C78	78		Y	VARCHAR2 (30 Byte)		Yes
C79	79		Y	VARCHAR2 (30 Byte)		Yes
C80	80		Y	VARCHAR2 (30 Byte)		Yes
C81	81		Y	VARCHAR2 (30 Byte)		Yes
C82	82		Y	VARCHAR2 (30 Byte)		Yes
C83	83		Y	VARCHAR2 (30 Byte)		Yes
C84	84		Y	VARCHAR2 (30 Byte)		Yes
C85	85		Y	VARCHAR2 (30 Byte)		Yes
C86	86		Y	VARCHAR2 (30 Byte)		Yes
C87	87		Y	VARCHAR2 (30 Byte)		Yes
C88	88		Y	INTEGER		Yes
C89	89		Y	VARCHAR2 (30 Byte)		Yes
C90	90		Y	VARCHAR2 (30 Byte)		Yes
C91	91		Y	VARCHAR2 (30 Byte)		Yes
C92	92		Y	VARCHAR2 (30 Byte)		Yes
C93	93		Y	VARCHAR2 (30 Byte)		Yes
C94	94		Y	INTEGER		Yes
C95	95		Y	INTEGER		Yes
C96	96		Y	INTEGER		Yes
C97	97		Y	INTEGER		Yes
C98	98		Y	INTEGER		Yes
C99	99		Y	INTEGER		Yes
C100	100		Y	INTEGER		Yes
C101	101		Y	INTEGER		Yes
C102	102		Y	INTEGER		Yes
C103	103		Y	VARCHAR2 (30 Byte)		Yes
C104	104		Y	VARCHAR2 (30 Byte)		Yes
C105	105		Y	VARCHAR2 (30 Byte)		Yes
C106	106		Y	INTEGER		Yes
C107	107		Y	INTEGER		Yes
C108	108		Y	VARCHAR2 (30 Byte)		Yes
C109	109		Y	VARCHAR2 (30 Byte)		Yes
C110	110		Y	VARCHAR2 (30 Byte)		Yes
C111	111		Y	INTEGER		Yes
C112	112		Y	VARCHAR2 (30 Byte)		Yes
C113	113		Y	VARCHAR2 (30 Byte)		Yes
C114	114		Y	VARCHAR2 (30 Byte)		Yes
C115	115		Y	VARCHAR2 (30 Byte)		Yes
C116	116		Y	INTEGER		Yes
C117	117		Y	INTEGER		Yes
C118	118		Y	INTEGER		Yes
C119	119		Y	INTEGER		Yes
C120	120		Y	INTEGER		Yes
C121	121		Y	INTEGER		Yes
C122	122		Y	VARCHAR2 (30 Byte)		Yes
C123	123		Y	INTEGER		Yes
C124	124		Y	INTEGER		Yes
C125	125		Y	INTEGER		Yes
C126	126		Y	INTEGER		Yes
C127	127		Y	VARCHAR2 (30 Byte)		Yes
C128	128		Y	INTEGER		Yes
C129	129		Y	INTEGER		Yes
C130	130		Y	INTEGER		Yes
C131	131		Y	VARCHAR2 (30 Byte)		Yes
C132	132		Y	VARCHAR2 (30 Byte)		Yes
C133	133		Y	INTEGER		Yes
C134	134		Y	VARCHAR2 (30 Byte)		Yes
C135	135		Y	VARCHAR2 (30 Byte)		Yes
C136	136		Y	VARCHAR2 (30 Byte)		Yes
C137	137		Y	VARCHAR2 (30 Byte)		Yes
C138	138		Y	INTEGER		Yes
C139	139		Y	INTEGER		Yes
C140	140		Y	INTEGER		Yes
C141	141		Y	INTEGER		Yes
C142	142		Y	DATE		Yes
C143	143		Y	INTEGER		Yes
C144	144		Y	INTEGER		Yes
C145	145		Y	VARCHAR2 (30 Byte)		Yes
C146	146		Y	VARCHAR2 (30 Byte)		Yes
C147	147		Y	VARCHAR2 (30 Byte)		Yes
C148	148		Y	INTEGER		Yes
C149	149		Y	INTEGER		Yes
C150	150		Y	INTEGER		Yes
C151	151		Y	VARCHAR2 (30 Byte)		Yes
C152	152		Y	INTEGER		Yes
C153	153		Y	VARCHAR2 (30 Byte)		Yes
C154	154		Y	VARCHAR2 (30 Byte)		Yes
C155	155		Y	VARCHAR2 (30 Byte)		Yes
C156	156		Y	VARCHAR2 (30 Byte)		Yes
C157	157		Y	VARCHAR2 (30 Byte)		Yes
C158	158		Y	VARCHAR2 (30 Byte)		Yes
C159	159		Y	VARCHAR2 (30 Byte)		Yes
C160	160		Y	VARCHAR2 (30 Byte)		Yes
C161	161		Y	INTEGER		Yes
C162	162		Y	INTEGER		Yes
C163	163		Y	VARCHAR2 (30 Byte)		Yes
C164	164		Y	VARCHAR2 (30 Byte)		Yes
C165	165		Y	FLOAT		Yes
C166	166		Y	FLOAT		Yes
C167	167		Y	FLOAT		Yes
C168	168		Y	FLOAT		Yes
C169	169		Y	FLOAT		Yes
C170	170		Y	INTEGER		Yes
C171	171		Y	INTEGER		Yes
C172	172		Y	VARCHAR2 (3 Byte)		Yes
C173	173		Y	VARCHAR2 (10 Byte)		Yes
C174	174		Y	VARCHAR2 (10 Byte)		Yes
C175	175		Y	VARCHAR2 (10 Byte)		Yes
C176	176		Y	INTEGER		Yes
C177	177		Y	FLOAT		Yes
C178	178		Y	VARCHAR2 (30 Byte)		Yes
C179	179		Y	DATE		Yes
C180	180		Y	DATE		Yes


Thank you very much

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327426 is a reply to message #327424] Mon, 16 June 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327428 is a reply to message #327426] Mon, 16 June 2008 06:56 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
Attaching files.
  • Attachment: test_data.ctl
    (Size: 1.04KB, Downloaded 1377 times)

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327429 is a reply to message #327428] Mon, 16 June 2008 06:57 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
Table create script

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327430 is a reply to message #327429] Mon, 16 June 2008 06:58 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
SQLLDR log file
  • Attachment: test_data.log
    (Size: 16.45KB, Downloaded 1310 times)

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327432 is a reply to message #327424] Mon, 16 June 2008 07:02 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member
Hi,

From your log file its clear that the problem exists with few records but not all. so check the data file and in particular the c179 column for those records. The problem is with the date format of c179 column in the input data file.

Thanks & Regards
KK

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327439 is a reply to message #327432] Mon, 16 June 2008 07:20 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
Thank you for your reply,

Log file says:

Record 289: Rejected - Error on table T4000, column C179.
ORA-01843: not a valid month


But I see date format is correct.

In control file:
...C179 DATE "YYYY-MM-DD HH24:MI:SS",C180 DATE "YYYY-MM-DD HH24:MI:SS"

Data file:
..."2008-09-08 23:59:59","2008-09-08 23:59:59"

Please tell me any idea

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327441 is a reply to message #327424] Mon, 16 June 2008 07:34 Go to previous messageGo to next message
kkinapps
Messages: 29
Registered: November 2006
Location: Hyderabad
Junior Member
Check the 289th record..and 308th record..463rd record... post the data of those records here...

Thanks
KK

Report message to a moderator

Re: SQLLDR doesn't work when last columns are not null [message #327550 is a reply to message #327441] Mon, 16 June 2008 20:41 Go to previous message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member
Thank you all,

I missed C176, C178 fields in my control file. Now all works fine.

Thanks again

Report message to a moderator

Previous Topic: Import Successful with warnings....ORA-01658 error..
Next Topic: copying data from production to archival db
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 24 11:27:51 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.