Not able to write reject record

Karine · Post by **Karine** » Sun Feb 18, 2007 4:14 am

I have the following scenario:

I have a sequential file which consist of a date field in yyyy-mm-dd format. The file has 2 rows as followed. Row 2 is an invalid date.

2005-10-14
2007-25-12

I want to insert the data into an oracle table which has a date field.

I passed the seq file straight through a transformer stage and for the invalid record I want to write it to a seqential file via the reject link.

The input date field is defined as varchar length 10.
The output date field is defined as DATE length 10.

seq file
|
|
V
transformer ---------> Oracle Enterprise Stage
|
| reject
V
seq file

When the job ran, it inserted the only the first row to the Oracle table as expected, the second row was rejected (confirmed by the sqlldr log).

However, I was not able to get the rejected row to write to my reject file.

I tried using StringToDate function but the results are the same.

1) Why did my job aborted and not writing the rejected record?
2) Ideally I want the job to do what I intended and give a warning msg in the log to indicate there are some rejects rather than give a fatal error and abort. Can it be done?

ArndW · Post by **ArndW** » Sun Feb 18, 2007 4:25 am

The "reject" link in PX needs to come out of the Oracle Enterprise Stage, not the transformer (as it would be done in server jobs).

Karine · Post by **Karine** » Sun Feb 18, 2007 4:41 am

Thank you for the prompt response.
When I added a reject link from Oracle enterprise stage, i got the msg:
"Error: Reject Link Ora_ENT.DSLink9 is not allowed wiht the current setting (or lack ) of the 'Output Reject records' property".

I don't seem to be able to set any reject link property from the Oracle Ent stage.

Can you tell me where the settings are?

ArndW · Post by **ArndW** » Sun Feb 18, 2007 4:56 am

One of the switches in the Oracle stage lets you enable the reject link, look for it towards the bottom of the list.

Karine · Post by **Karine** » Sun Feb 18, 2007 5:43 am

ArndW wrote:One of the switches in the Oracle stage lets you enable the reject link, look for it towards the bottom of the list. ...

I found in the documentation that I have to change the Write Method to 'Upsert' to enable the reject link.

when I did that I am getting the following error:

Message:
Oracle_Ent,0: Un-handled conversion error on field "DATE_FLD " from source type "date" to dest type "raw[7]": source value="**********": Result is non-nullable
and there is no handle_null to specify a default value

kumar_s · Post by **kumar_s** » Sun Feb 18, 2007 6:11 am

Check the field DATE_FLD . It has been changed from Date data type do a different. And more over, it has been changed to Not nullable. Where a null value is comming and is converted to *'s by default. As specified, you can handle the null by using handle_null function in modify stage or the null handling functions in transformer stage.

Karine · Post by **Karine** » Sun Feb 18, 2007 6:44 am

OK I tried to put a null handling function in the transformer stage, yet still getting the same error.

I used NullToValue(Lnk_in.Field1,'1999-01-01') in the transformer derivation.

I also changed all the fields to non nullable, but still no avail.

Could you explain why the DATE_FLD has been changed by dstage to be non nullable and what can i do?

Karine · Post by **Karine** » Sun Feb 18, 2007 7:06 am

I also tried Edit Column Meta Data but Date Field type has 'Non available' in properties and the Value field is greyed out which means I can't specify the Null value. Any idea anyone?

ray.wurlod · Post by **ray.wurlod** » Sun Feb 18, 2007 2:02 pm

There is an additional property that you have to set to allow the use of a reject link. However, such a reject link can not capture bad rows from sqlldr. DataStage creates the control and data files then invokes sqlldr; control is actually passed to sqlldr. To determine what rows were rejected you need to design to inspect the sqlldr "bad file".