tOracleOutputBulkExec not generating a valid CTL file

Anonymous — Sat, 16 Nov 2024 12:09:49 GMT

Hello everyone,
I'm trying to load over 4 millions of row into a specific table and i'm using a tOracleOutputBulkExec to load them. I'm experiencing a problem with a specific field only which contains a Date. For some reason, the others date field works fine but only this one I can't get ahold of what's going on.
In the Schema the Field is defined as:
SYS_TIMESTAMP Type: Date DB Type: TIMESTAMP Mask: "dd-MM-yyyy"
However in the generated CTL file, this field is identified as CHARACTER while the others date field (which are defined the same way in the schema appears as TIMESTAMP "dd-MM-yyyy"
This is the generated ctl file for one table:
OPTIONS ()
LOAD DATA
CHARACTERSET 'WE8ISO8859P15'
INFILE '/opt/talend/data/INT/BusinessIntelligence//Stage_rms9_products/desc_look.csv'
BADFILE '/opt/talend/data/INT/BusinessIntelligence//Stage_rms9_products/desc_look.csv.bad'
DISCARDFILE '/opt/talend/data/INT/BusinessIntelligence//Stage_rms9_products/desc_look.csv.dsc'
INTO TABLE STG.RMS9_DESC_LOOK
INSERT
FIELDS TERMINATED BY ";"
TRAILING NULLCOLS
("SKU",
"CLASS",
"DEPT",
"SUBCLASS",
"DESC_UP",
"SYSTEM_IND",
"WASTE_TYPE",
"WASTE_PCT",
"DEFAULT_WASTE_PCT",
"SYS_TIMESTAMP",
"SYS_REJECT_ID")
I've done some changed and got it to work by manually modifying the ctl file to :
...
"SYS_TIMESTAMP" TIMESTAMP "dd-MM-yyyy",
...
However, as soon as I reorganized the job files (using multiple tOracleOutputBulkExec) I can't use the manually defined CTL file option because it is looking in the wrong place for the CSV file...
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
"SKU" FIRST * ; CHARACTER
"CLASS" NEXT * ; CHARACTER
"DEPT" NEXT * ; CHARACTER
"SUBCLASS" NEXT * ; CHARACTER
"DESC_UP" NEXT * ; CHARACTER
"SYSTEM_IND" NEXT * ; CHARACTER
"WASTE_TYPE" NEXT * ; CHARACTER
"WASTE_PCT" NEXT * ; CHARACTER
"SYS_TIMESTAMP" NEXT * ; DATETIME dd-MM-yyyy
"DEFAULT_WASTE_PCT" NEXT * ; CHARACTER
SQL*Loader-500: Unable to open file (/opt/talend/data/INT/BusinessIntelligence/out.csv)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
The file is being created as DESC_LOOK.csv in a subfolder /opt/talend/data/INT/BusinessIntelligence/StagingImport/products/desc_look.csv
Is there any way I can get this component to work ? 😕
Also, I've tried checking/unchecking the Use Date Format from the Schema to load with no change...
Thanks

Re: tOracleOutputBulkExec not generating a valid CTL file

Anonymous — Fri, 26 Oct 2012 16:35:23 GMT

Problem solved, for some reason TIMESTAMP doesn't go in the CTL file while DATE is going... Just changed DB Type to DATE and it works.

topic tOracleOutputBulkExec not generating a valid CTL file in Talend Studio

tOracleOutputBulkExec not generating a valid CTL file

Re: tOracleOutputBulkExec not generating a valid CTL file