OracleDataWriter component support for TRAILING NULLCOLS

pensky

• September 12, 2012 00:00

• Answered

Follow

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.

Thanks in advance!

• 

  slechtaj

  • September 14, 2012 14:17

  0

  Comment actions Permalink

  Hi Pensky,
  
  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
  )
  

• 

  pensky

  • September 24, 2012 21:40

  0

  Comment actions Permalink

  Jan,
  
  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.
  
  Regards,
  
  Dennis.

Please sign in to leave a comment.