Problem with loading Timestamp filed into Oracle

sshettar · Post by **sshettar** » Mon Dec 04, 2006 9:54 am

i have a field in my job which is in the format 2006-11-30-10.28.15.817238 and the data type for this field is Char(26)
and the field in Oracle table is of the format 2006-12-01 16:23:11 and the data type is Timestamp
well i have formatted my field in the job to the format in oracle by removing the milli seconds from the field and trimming the field .
now it is in the same format as oracle . but it is not loading into the oracle table.
does anybody knows what the problem could be ?

ds_developer · Post by **ds_developer** » Mon Dec 04, 2006 10:35 am

What is the length of the Timestamp field? It should be 38. Try it.

John

sshettar · Post by **sshettar** » Mon Dec 04, 2006 12:27 pm

well the length is 38 itself .
is there ant other problem that you could think about ?

I_Server_Whale · Post by **I_Server_Whale** » Mon Dec 04, 2006 12:38 pm

Welcome Aboard! :D

What is the logic and code that you used to convert the original timestamp into the Oracle timestamp?

Also, it would be helpful to provide the error messages of the job run.

Rather than posting messages like below from which zilch can be inferred.

sshettar wrote:
but it is not loading into the oracle table.

Thanks,
Whale.

chandra · Post by **chandra** » Mon Dec 04, 2006 12:46 pm

Check you MM and DD formats ! may be it got swaped by mistake .

DSguru2B · Post by **DSguru2B** » Mon Dec 04, 2006 1:13 pm

Try loading to a sequential file first and see whether , really, you got the conversion successful. Most of the time in such situations, the blank space between the date part and time part of the timestamp is missing. Also, if you post the derivation, it would help us pin point the mistake, if there is any.

sshettar · Post by **sshettar** » Mon Dec 04, 2006 1:37 pm

well as i already mentioned the filed i want to load in oracle is of the format 2006-11-30-10.28.15.817238 and of type char (26)
i formatted this field in the follwoing way
Left(l_to_Tform.BMC_TS,19)
Trim(DateFormat,"-","A")
Trim(TrimD1,".","A")
TrimD2 [1,4] :'-' : TrimD2 [5,2] :'-' : TrimD2 [7,2] :' ' : TrimD2 [9,2] :':' : TrimD2 [11,2] :':' : TrimD2 [13,2]

now the field in the oracle is of this format 2006-12-04 14:26:24 and of type Timestamp

well the job is running succesfully but no rows loading into the oracle due to this field

the warning messages are as follows

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: The value of the row is: PROCESS_ID = 2 SUBPROCESS_ID = 17 MEASUREMENT_ID = 50 PROCESS_TIMESTAMP = 2006-11-30 10:28:33 SUBPROCESS_TIMESTAMP = 2006-12-04 14:26:23 MEASUREMENT_TIMESTAMP = 2006-12-04 14:26:24 MEASUREMENT_VALUE = 39 MEASUREMENT_RESULT = PASS

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: ORA-00001: unique constraint (ETQ.PK_VALIDATION_VALUES) violated

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DBMS.CODE=ORA-00001

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: The value of the row is: PROCESS_ID = 2 SUBPROCESS_ID = 17 MEASUREMENT_ID = 52 PROCESS_TIMESTAMP = 2006-11-30 10:28:33 SUBPROCESS_TIMESTAMP = 2006-12-04 14:26:23 MEASUREMENT_TIMESTAMP = 2006-12-04 14:26:24 MEASUREMENT_VALUE = 39 MEASUREMENT_RESULT = PASS

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: ORA-00001: unique constraint (ETQ.PK_VALIDATION_VALUES) violated

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DBMS.CODE=ORA-00001

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DSD.INTClose Active stage finishing.
1 rows read from I_SBN_Merch_Contr_Rules_RCount
1 rows written to I_SBN_Merch_Contr_Rules_Valid_Sub_TXN
0 rows written to I_ETL_CONTRACT_RL_COUNT
0 rows written to I_ETL_CONTRACT_RL_SELECT_COUNT

first 6 are warnings and 7th one is an Info

well i have removed all primary keys in the table . none of the fields are primary key

but still the waring messages remain the same

i hope i have given all the nessary information.

i would really appreciate your help with this issue

Thanks
shettar

sshettar · Post by **sshettar** » Mon Dec 04, 2006 1:38 pm

well i have tried loading into sequential file and the conversion looks fine .
Any other suggestions?

chulett · Post by **chulett** » Mon Dec 04, 2006 1:47 pm

You're correct in your conversion of the timestamp. What makes you think it is in any way related to your constraint violation problem?

You need to look at the definition of the constraint being violated - like ETQ.PK_VALIDATION_VALUES for example - and see what field(s) make up that constraint. All you know at this point is the fact that some combination of fields in each record must be unique and that isn't happening. Focusing on the format of the timestamp is a Red Herring.

Note that the violation can happen not only from an Insert but that an Update could cause it as well!

sshettar · Post by **sshettar** » Tue Dec 05, 2006 2:30 pm

well i tried all possible combinations of primary keys . but still it doesn't work . well i have
process_id
subprocess_id
measurement_id
processtimestamp
subprocesstimestamp
measurementtimestamp
measurement_value
measurement_result
of which most of the fields have values hardcoded
it is just the process timestamp field and measurement_value field which gets the value depending on the job
the subprocesstimestamp gets the value from getjobstarttimestamp
and measurementtimestamp gets the value from curent timestamp.
well curernt timestamp was initially not in the format of the timestamp as oracle ,so i made the changes and that works
but it is only with the processtimestamp that even after all conversion and formatting it doesnt work

Could somebody help me with this issue

I_Server_Whale · Post by **I_Server_Whale** » Tue Dec 05, 2006 2:33 pm

Can you list the constraints on your target table?

Whale.

chulett · Post by **chulett** » Tue Dec 05, 2006 2:52 pm

This is getting crazy. Are you and beaditya the same person? You both seem to be working on the exact same problem, same table, same PK violation, same confusion - same everything.

Can the two of you get together and start a single new thread where your remaining issue is clearly defined now that all the other silliness is out of the way? Sheesh.

ps. Yes, I purposefully posted this in both threads.

I_Server_Whale · Post by **I_Server_Whale** » Tue Dec 05, 2006 3:54 pm

My two cents !

I have seen this happening with other users as well.

Same issue being posted under different user_names.

AND THAT's NOT A GOOD WAY TO GO !!

baglasumit21 · Post by **baglasumit21** » Thu Dec 07, 2006 3:53 am

sshettar wrote:well i tried all possible combinations of primary keys . but still it doesn't work .

Just change the update action to 'Update existing rows only'

DSguru2B · Post by **DSguru2B** » Thu Dec 07, 2006 8:28 am

Go to your DBA. Get him/her involved. It doesnt make sense that even after dropping all the primary keys your getting this error. See what he/she has to say.