DSXchange

Which is it - "#XYZ" or "#XYZ#"? Asking since you posted both variations. If it is the latter then I'd guess it is thinking it is a job parameter that it cannot find... but that would leave it intact rather than null.

Shouldn't but you never know... especially since there are many flavors of Linux. May be a bug in your distro/version, hence the advice to check with your SysAdmin.

Syntax looks fine to me. What O/S? Have you talked to your SysAdmin? Not really a DataStage question after all.

DataStage is a client, it interfaces with Oracle (or any other database for that matter) the same as any other client tool. If that database doesn't support something or communicate something back to the client, there's nothing the client can do about it.

Did you try doing an exact search for "code 139"? There are 46 other posts on this subject, did none of them help?

Read your documentation on Runtime Column Propagation. It can be used with the Sequential File stage along with schema files to define the metadata.

ps. Oracle will never tell you which record / column / value is throwing the error - you need to find it. Sometimes you can find them by sorting the VARCHAR2 columns descending, non-numeric values will appear first... depending on their position in the string, of course. You could also play with the...

Well... one way would be to do the conversion inside the job. Part of that conversion could be an IsValid check so that you can easily identify and handle any values that will not convert properly.

ORA-01722 is an "invalid number" error. Depending on what your source SQL looks like, Oracle can throw that error even if your metadata for the result is correct. Any chance you are joining tables in the connectors and doing an implicit cast of types in the join? Just wanted to rule that o...

Luca - what 8.x version are you running? Asking because it would need to be at least 8.5 for 'transformer looping' to be an option for you.

Have you involved your official support provider yet?

What version of DataStage? Have you checked to see if there are any patches / fixpacks that address this issue?

That would depend entirely on the author of the service.

Clarify for us the data type of the target - is it specifically a Timestamp or is it another string? For the latter use TimestampToString after the initial conversion and use '%(HH,u)' to get it back to a string with the time in 12 hour format with an upper case AM/PM.

StringToTimestamp works with any delimiter. You specify it in the format string you pass to the function.