SQL Loader Control File Truncated

piyu · Post by **piyu** » Thu Apr 02, 2009 3:20 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 02, 2009 3:30 am

How did you come to the conclusion? What errors did you obtain?

Did you check the ctl file from OS level ?

piyu · Post by **piyu** » Thu Apr 02, 2009 3:39 am

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.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 02, 2009 3:42 am

Did you look at the sql loader control file from operating system level?

piyu · Post by **piyu** » Thu Apr 02, 2009 3:47 am

Unfortunately I cannot check the control file on the oracle server installation.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Apr 02, 2009 3:49 am

SQL loader control file is different from control file of Oracle.

They are text files created in OS.

piyu · Post by **piyu** » Thu Apr 02, 2009 3:56 am

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

chulett · Post by **chulett** » Thu Apr 02, 2009 7:45 am

Sounds like a bug and something you'd need to contact your official support provider for to see if there is a patch available.

piyu · Post by **piyu** » Thu Apr 02, 2009 11:45 pm

Thought the same! Version 8 n bugs!

GavMagill · Post by **GavMagill** » Tue Jul 28, 2009 6:10 pm

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

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 29, 2009 1:59 am

Gavin,

There is another similar post with information about the patch. Please search.