SQL Loader Control File Truncated
Moderators: chulett, rschirm, roy
SQL Loader Control File Truncated
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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
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
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom