Getting the job stream failure error due to procedure node!!

[anjidwh.cognos]

Hi

UDA-SQL-0107 A general exception has occurred during the operation "open result".
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-20101: Cannot start another ETL run if previous run is in a status of: Failure

Can anybody explain why it is getting failed, because it is succeeded sometime and it is getting failed sometime. Not sure what is the reason behind!!!!

Appreciated your help!!

Regards

[MFGF]

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

This seems to be the crux of your issue. Somewhere you are trying to convert a character string (eg "2014") to a number (eg 2014), and this is producing an error - possibly because the source on one or more rows is something such as "Y2014"? It's almost certainly a data quality issue. Sometimes all the values convert nicely to numbers and sometimes there may be an alphabetic value?

MF.

[anjidwh.cognos]

Thanks for giving brief information. If you have an idea how to resolve these kind of issues, could plz explain elaborately.

But when we remove Procedure node from job stream and if we ran, it is got succeeded. I think so it was only happening with ETL_control table.

Appreciated your help!!

Regards

[MFGF]

Thanks for giving brief information. If you have an idea how to resolve these kind of issues, could plz explain elaborately.

But when we remove Procedure node from job stream and if we ran, it is got succeeded. I think so it was only happening with ETL_control table.

Appreciated your help!!

Regards

The simple answer is that you need to do more data quality checking. You can't convert a string value to a number unless you are 100% sure the string contains only numeric characters. If you don't do these kinds of checks, you end up getting intermittent failures - exactly as you describe - when the string contains alphabetic characters.

I don't have access to your catalog and your data so I can only give you general advice here. It's valid for any ETL type processing, though - not just Data Manager.

Good luck!

MF.