Page 1 of 1
SQL Loader Control File Truncated
Posted: Thu Apr 02, 2009 3:20 am
by piyu
Hi,
The problem I'm facing is: I am using LOAD in Oracle Enterprise stage and the job is failing because the control file is being truncated. The same job runs fine with another schema which has relatively less columns compared to the one failing. The DBA says its something Datastage may be doing. If that is true, is there a way to over-ride it?
The text control file was truncated exactly at 10KB and the director log shows the last characters of the file which does confirm truncation.
Any help will be greatly appreciated.
Thanks.
Posted: Thu Apr 02, 2009 3:30 am
by Sainath.Srinivasan
How did you come to the conclusion? What errors did you obtain?
Did you check the ctl file from OS level ?
Posted: Thu Apr 02, 2009 3:39 am
by piyu
Yup. The DBA provided the control file and it is truncated.
Here is the error :
ORA_MOPC_DB_LOAD,1: SQL*Loader-350: Syntax error at line 142.
ORA_MOPC_DB_LOAD,1: Expecting valid column specification, "," or ")",
found "PO".
ORA_MOPC_DB_LOAD,1: "LEG2_ACCRUAL3" PO
ORA_MOPC_DB_LOAD,1: ^
The control file last two characters are "PO" coz it truncated the word POSITION which defines the schema.
Posted: Thu Apr 02, 2009 3:42 am
by Sainath.Srinivasan
Did you look at the sql loader control file from operating system level?
Posted: Thu Apr 02, 2009 3:47 am
by piyu
Unfortunately I cannot check the control file on the oracle server installation.
Posted: Thu Apr 02, 2009 3:49 am
by Sainath.Srinivasan
SQL loader control file is different from control file of Oracle.
They are text files created in OS.
Posted: Thu Apr 02, 2009 3:56 am
by piyu
Here is the snapshot of the control file for this table.
OPTIONS(DIRECT=TRUE, PARALLEL=TRUE, SKIP_INDEX_MAINTENANCE=YES)
LOAD DATA length semantics byte INFILE 'ora.12285.313228.fifo.0' "FIX 1967"
APPEND INTO TABLE PT_MXG_TRADE_TRNRPPL_IRS1
(
"MTM_CCY" POSITION (1:5) varchar(3),
"MTM_DATE" POSITION (6:24) date "YYYY-MM-DD HH24:MI:SS" NULLIF (6:24) = BLANKS,
.
.
.
.
.
"LEG2_ACCRUAL1" POSITION (1848:1856) decimal(16,2) NULLIF (1848:1856) = BLANKS,
"LEG2_ACCRUAL3" PO
Posted: Thu Apr 02, 2009 7:45 am
by chulett
Sounds like a bug and something you'd need to contact your official support provider for to see if there is a patch available.
Posted: Thu Apr 02, 2009 11:45 pm
by piyu
Thought the same! Version 8 n bugs!
Posted: Tue Jul 28, 2009 6:10 pm
by GavMagill
Hi there Piyu.
Did you ever report this problem to IBM and get a response?
I just hit the same issue with our install of V8.1.
Would appreciate any update you can give us.
Thanks
Gavin
Posted: Wed Jul 29, 2009 1:59 am
by Sainath.Srinivasan
Gavin,
There is another similar post with information about the patch. Please search.