Problem with loading Timestamp filed into Oracle
Moderators: chulett, rschirm, roy
Problem with loading Timestamp filed into Oracle
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 ?
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 ?
Shalini
-
- Premium Member
- Posts: 224
- Joined: Tue Sep 24, 2002 7:32 am
- Location: Denver, CO USA
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
Whale.
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.
Thanks,sshettar wrote:
but it is not loading into the oracle table.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
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!
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!
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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 !!
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 !!
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune