Job aborted with oracle stage.......

[kumar444]

I get the below warnings and my job is aborted. Help needed to get rid of them.Source and target is oracle EE stage between them is a transformer where i am handling stringtotimestamp, decimaltodecimal and null string fields to null timestamp conversion and some more.My source has 710867 records. Log shows 710868. Options in Oracle EE target stage as write mode: truncate and write method: load. Am i doing something wrong? Really appreciate any help.

Copy_of_Transformer_1,0: Conversion error calling conversion

routine decimal_from_string data may have been lost

Copy_of_Transformer_1,0: Conversion error calling conversion

routine timestamp_from_string data may have been lost

Copy_of_Transformer_1,0: No more conversion warnings will be issued

Copy_of_Oracle_Enterprise_0,0: Export complete; 710868 records

exported successfully, 0 rejected.

Copy_of_Oracle_Enterprise_0,0: Load completed - logical record

count 710868.

Copy_of_Oracle_Enterprise_0,0: Rows not loaded due to data errors:

37

Copy_of_Oracle_Enterprise_0,0: The call to sqlldr failed; the

return code = 2;
please see the loader logfile:

/opt/IBM/InformationServer/Server/Scratch/ora.19206.704488.0.log

for details.

Copy_of_Oracle_Enterprise_0,0: The runLocally() of the operator

failed.

Copy_of_Oracle_Enterprise_0,0: Input 0 consumed 1 records.

Copy_of_Oracle_Enterprise_0,0: Operator terminated abnormally:

runLocally() did not return APT_StatusOk

main_program: Step execution finished with status = FAILED.

[chulett]

please see the loader logfile:

/opt/IBM/InformationServer/Server/Scratch/ora.19206.704488.0.log

for details.

Did you?

[kumar444]

Thanks Craig for your prompt reply.

I have seen a log file and all 37 rows rejected are from the same field that is timestamp datatype.
Even though the job is aborted ,I am able get to all the other records in the target table in the database. Loaded records are displaying 19th century dates. How can i handle the century issue in the transformer?

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

[kumar444]

Century issued is solved. I have used %1980yy for year part.
But unable to find out why those 37 records are getting rejected with the below error.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


This field is coming as a string(6) with the format 'mmddyy'and converted to timestamp with the following code.
If isnull(link.incol) then setnull() else stringtotimestamp(linl.incol,"%mm%dd%1980yy").

This code worked for other similar fields in the same job.

[kumar444]

I found that rejected 37 rows are with the data '000000'
I think this cant be converted to any date. How do i handle this?

[chulett]

Convert it to a null or an 'in-band' null value if the target doesn't support nulls. Failing that, they get rejected.

[kumar444]

Thanks Craig.
I have successfully loaded all the records. I have handled the rejected records which are coming as '000000' with the below code
If IsNull(incolumn) Then SetNull() Else if Trim((incolumn),'0') = "" Then SetNull() Else StringToTimestamp(incolumn,"%mm%dd%1980yy")

I need to eliminate this conversion warnings:
Transformer_1,0: Conversion error calling conversion routine decimal_from_string data may have been lost.

Any idea on this?

[jwiles]

What length is your string data and what length is the decimal you are putting it into? If the string data is longer than the decimal, then you can possibly lose information and hence the warning.

How do you want to handle the warning? You can reduce the size of the string you convert, you can increase the size of the decimal you convert into or you can use Director to implement a message handler.

Regards,

[kumar444]

Thanks jwiles.
field 1 --> char(4000) to decimal(15,2)
field 2 --> char(20) to decimal(15,2)

Used stringtodecimal().
Source is a prod. I think i may not change it.

I have used a message handler to demote it to informational.