Help with "timestamp" for OCI-9

ady · Post by **ady** » Fri Dec 01, 2006 3:55 pm

Changing the keys dosent work , I changed the key to the current system time column. it dosent work too .

Any suggestions ?

I_Server_Whale · Post by **I_Server_Whale** » Fri Dec 01, 2006 4:23 pm

If PROCESS_TIMESTAMP is the column that the modification was applied on. Then,

How is the column "PROCESS_TIMESTAMP" defined in your target?

Is it a key or have a unique constraint on it? I mean in your Oracle DB.

ady · Post by **ady** » Mon Dec 04, 2006 10:25 am

Any clue as of why this is happening?

I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.

Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".

Am I missing something here ?..

ady · Post by **ady** » Mon Dec 04, 2006 12:32 pm

Any clue as of why this is happening?

I tried changing the key of the oracle table but it dosent work. The key previously was the MACRO "DSJobStartTimeStamp" I'v chosen this one because the job start time is never the same usually.

Now i have changed it to the current time stamp using OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") but the same warning pops up "Unique Constraint Violated".

Am I missing something here ?..

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

Ofcourse, it is a "UNIQUE KEY VIOLATON".

DSJobStartTimestamp is the timestamp at the start of a dsjob run and will be the same for the all the records that are being processed by the job for that particular run.

Whale.

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

There is only 1 record being inserted. We are inserting a row into a table everytime we run a specific job. It kinda leaves a log stating it has been when it was run.

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

Well !

You got me !.

Can you please post your target table definiton and the error messages from the director log?

Whale.

paddu · Post by **paddu** » Mon Dec 04, 2006 1:31 pm

beaditya ,

get rid of the index on that particular field.

If it is not a primary key and you had index on it then it is fine for you to remove the index on that particular column .

If you have access to TOAD or other Oracle query tool you can do it .
If not involve DBA to get rid of the index on column .

ray.wurlod · Post by **ray.wurlod** » Mon Dec 04, 2006 4:26 pm

Without prior deletion you can only ever INSERT a particular key once into an Oracle table that has a primary key defined.

Perhaps you need to revisit the design of that table if there are to be more than one run with the same process date. Or maybe you're just not generating the seconds part of the timestamp.

ady · Post by **ady** » Tue Dec 05, 2006 2:40 pm

@ PADDU,

That field is not the primary key for the table and as of now but it was marked as the key when I loaded the table definition. There were 4 keys when loaded the table definition but we didnt want them as keys so I changed the "KEY" option to "NO" for all of them and I use a column which has current "date and timestamp" as a derivation as a key for that table now just to keep it unique.

I have no idea abt the INDEX you are talkin about. I do not have permissions to change anything on the database so if you are sure abt the INDEX problem I will escalate the situation to the DBA.

Thanks

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

This is getting crazy. Are you and sshettar 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. Do not escalate this to your DBA. This is your issue, not theirs.

ady · Post by **ady** » Tue Dec 05, 2006 3:24 pm

@Chulett ... U got us

... We work on the same team.... just on different jobs and locations, and thats why u guys have 2 confusing threads !

Yeah, we're workin on it as of now and NO, we didnt escalate the situation to the DBA.... and YES, i think we have figured out a solution .... will keep you posted when we get somewhere with this ! :D

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

beaditya wrote: TESTBMC2..Transformer_11: ORA-00001: unique constraint (ETQ.PK_VALIDATION_VALUES) violated

The table i'm inserting into is "ETQ.VALIDATION_VALUES"

The above error is due to the Primary key voilation. U need to check the key combination of the target table. More than one rows are having the same key combination

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

beaditya wrote:There is only 1 record being inserted. We are inserting a row into a table everytime we run a specific job. It kinda leaves a log stating it has been when it was run.

If u are inserting only one record in the target table for log purpose the its better to have a update action of 'Update Existing rows only' instead of any other option

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

Ok, i posted on the other thread without going through this one first. Same suggestion as getting your DBA involved. Do come back and let us know what was the core of this chase.