Conversion error calling conversion routine timestamp_from_s

[samuel81]

Hi ,
I know that this topic has been covered several times in this forum but i couldnt find the exact match.

here is my scenario

I am reading from a csv file and writing to a oracle table

date(varchar 8 )in seq file
date(timestamp) in oracle(no size specified)

When there is date value in the csv file it writes it to the oracle table(i append the time stamp info :"00 00 00") ,but the problem is when it has a null or " " value ,i tried passing a empty string to oracle "" and using setnull ,but it doesn not work and am getting an error ,i need to write a null to the table when it is null or " " in the csv.

can u kindly help me out .

thanks and regards,
Sam

[chulett]

but it doesn not work and am getting an error

Any error in particular?

[ray.wurlod]

Welcome aboard.

Currently your question falls into "my car won't go, what's wrong?" class. You have not provided enough information.

Please post the exact error message from your job log.

[samuel81]

Hi Ray,Chullett,

The error in the job log is "Conversion error calling conversion routine timestamp_from string ".

Thanks,
Sam

[chulett]

Please don't hand type it in but copy/paste the entire message directly from the logs. And post your syntax so we can see what you've tried.

[samuel81]

Hi Chullete,
the error mesg as in the log

"APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost"
since i could not pass a null to the OCI stage i used this logic in the transformer ,i hardcoded the date if the input is null, still am getting the same error

IF ((Lnk_Smac_Transaction_Read.TRADEDATE)=" " or ISNULL(Lnk_Smac_Transaction_Read.TRADEDATE)=1)then '1961-01-01 00:00:00' else Lnk_Smac_Transaction_Read.TRADEDATE [1,4]: "-" : Lnk_Smac_Transaction_Read.TRADEDATE [5,2]: "-" : Lnk_Smac_Transaction_Read.TRADEDATE [7,2] :" 00:00:00"

[ray.wurlod]

This message is probably simply an alert because your target expects fractional sections in timestamps yet your string does not supply the same.

[samuel81]

Hi Ray ,

Did you mean the timestamp part should have values ? could you please let me know how to modify my statement .

thanks,
Sam

[samuel81]

Hi Ray ,

i tried this code and it works fine without warnings when writing to a oracle table ,however for testing i was writing to a sequential file and was getting the same error why is that so ? should the syntax be different when writing to a seq file ?

IF ((Lnk_Smac_Transaction_Read.SETTLEDATE)="" or ISNULL(Lnk_Smac_Transaction_Read.SETTLEDATE)=1)then
timestamptostring('1900-01-01 00:00:00',"%yyyy-%mm-%dd %hh-%nn-%ss.5") else
Lnk_Smac_Transaction_Read.SETTLEDATE [1,4]: "-" : Lnk_Smac_Transaction_Read.SETTLEDATE [5,2]: "-" :
Lnk_Smac_Transaction_Read.SETTLEDATE [7,2] :" 00:00:00"

thanks ,
Sam

[ray.wurlod]

Sequential files don't have/enforce data types - they only have "text".

In the ELSE side of your IF expression you are providing no fractional seconds whereas in the THEN side you are providing five. No wonder Oracle is confused.

What is the data type of this column in the Oracle table, as imported through orchdbutil utility?

[samuel81]

Thanks a lot Ray ,problem solved .

Regards,
Sam