Job aborted with oracle stage.......
Moderators: chulett, rschirm, roy
Job aborted with oracle stage.......
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.
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.
Re: Job aborted with oracle stage.......
Did you?kumar444 wrote:please see the loader logfile:
/opt/IBM/InformationServer/Server/Scratch/ora.19206.704488.0.log
for details.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
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.
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.
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?
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?
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.