My graph has a Reformat component connected to an OracleDataWriter. When the data being loaded into Oracle contains nulls in the last column, the graph fails to load any records. Below is the excerpt from the log file:
COL1 NEXT * ; CHARACTER
COL2 NEXT * ; CHARACTER
COL3 NEXT * WHT CHARACTER
value used for ROWS parameter changed from 100 to 15
Record 1: Rejected - Error on table DNB_DU, column COL3.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table DNB_DU, column COL3.
Column not found before end of logical record (use TRAILING NULLCOLS)
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table DNB_DU:
0 Rows successfully loaded.
2 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.
What is the solution to this silly little problem? I haven’t been able to find a way to sneak in the TRAILING NULLCOLS syntax into the control file that gets automatically generated.
you are right, this issue is caused by NULL values in the last column. For this issues has been created a ticket in our system (you can keep yourself updated here: https://bug.javlin.eu/browse/CL-2512). Basicaly there are two workarounds that might be considered instead:
1. Set a default value for NULL values in the last column. Create a SQL trigger that checks whether the last inserted row contains this value, and if so set the value as NULL.
2. Load your data into an external temporary file. Set the filepath into the “Loader input file” (the file where you recently saved the data). Write a SQL script into the “Control script” field. Most likely, your script would look as follows:
load data
infile *
append
into table DNB_DU
TRAILING NULLCOLS
(
COL1 TERMINATED BY ';',
COL2 TERMINATED BY ';',
COL3 TERMINATED BY WHITESPACE
)
Thanks for your reply and suggested workarounds. However, #1 would not work for direct path (bulk) loads in Oracle since triggers are disabled prior to load.