Re: Duplicate timestamp(6): How is this possible?

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
  • Date: Wed, 19 Jan 2011 14:20:44 -0800

This might be helpful. it is a small sample of data from the table. As you
can see, the timestamp does not appear to be truncated as the result of an
OS precision limitation.


1/19/2011 7:57:41.014856 AM    1023206804    p_ordernumber_i =1023206804
p_calling_program_i =PBILLING2 p_leadreturnstatus2tcode_i =34650
1/18/2011 11:57:13 PM

1/19/2011 7:57:41.120702 AM    1023206804    a2#
v_commissionable_type=DoNotPay    1/18/2011 11:57:13 PM
1/19/2011 7:57:53.108784 AM    665693897    p_ordernumber_i =665693897
p_calling_program_i =PBILLING2 p_leadreturnstatus2tcode_i =34650
1/18/2011 11:57:13 PM

1/19/2011 7:57:53.193272 AM    665693897    ORD# 665693897 ORA-20993: Either
TAFFILIATE or TACCOUNTPROGRAM was affil_not_allowed_returns_yn = Y.
1/18/2011 11:57:13 PM

p_leadreturnstatus2tcode_i =34650    1/18/2011 11:57:13 PM

On Wed, Jan 19, 2011 at 2:11 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:

> The code is a procedure which exists only in the package body (i.e. no api
> in the spec for this procedure).
>
> v_read_cnt and v_run_date are global package variables.
>
> Sample call:
>
> log_audit_info ('p1# STARTING get_commissionable_type');
>
> thanks,
> Mike
>
>
>
> On Wed, Jan 19, 2011 at 1:45 PM, Niall Litchfield <
> niall.litchfield@xxxxxxxxx> wrote:
>
>> how is the code called?
>>
>> On Wed, Jan 19, 2011 at 9:30 PM, Michael Moore 
>> <michaeljmoore@xxxxxxxxx>wrote:
>>
>>> The problem is duplicate on primary key which is timestamp(6);
>>> CREATE TABLE TCOM_RETURN_ADJ_AUDIT
>>> (
>>>   TIME_D         TIMESTAMP(6)                   DEFAULT
>>> current_timestamp         NULL,
>>>   RECORD_NUMBER  NUMBER(10)                     NOT NULL,
>>>   MSG            VARCHAR2(4000 BYTE)                NULL,
>>>   RUN_DATE       DATE                               NULL
>>> );
>>>
>>> CREATE UNIQUE INDEX TCOM_RETURN_ADJ_AUDIT_PK ON TCOM_RETURN_ADJ_AUDIT
>>> (TIME_D)
>>> LOGGING
>>> TABLESPACE COM_INDX_DS
>>> NOPARALLEL;
>>>
>>> ALTER TABLE TCOM_RETURN_ADJ_AUDIT ADD (
>>>   CONSTRAINT TCOM_RETURN_ADJ_AUDIT_PK
>>>   PRIMARY KEY
>>>   (TIME_D)
>>>   USING INDEX TCOM_RETURN_ADJ_AUDIT_PK);
>>>
>>>
>>> Program does not insert the TIME_D value. Program does not provide
>>> TIME_D; actual value is obtained from table definition which has DEFAULT
>>> current_timestamp (see above);
>>>
>>> The code that does the insert:
>>>
>>>    PROCEDURE log_audit_info (p_msg_i IN VARCHAR2)
>>>    IS
>>>       PRAGMA AUTONOMOUS_TRANSACTION;
>>>    BEGIN
>>>       INSERT INTO tcom_return_adj_audit (record_number, msg, run_date)
>>>            VALUES (v_read_cnt, p_msg_i, v_run_date);
>>>
>>>       COMMIT;
>>>    END log_audit_info;
>>>
>>>
>>> THE ERROR
>>>
>>> ORA-00001: unique constraint (COM_APP.TCOM_RETURN_ADJ_AUDIT_PK) violated
>>>
>>>
>>>
>>> The server clock was not changed, already looked into this as a
>>> possibility.
>>>
>>> Thanks,
>>>
>>> Mike
>>>
>>
>>
>>
>> --
>> Niall Litchfield
>> Oracle DBA
>> http://www.orawin.info
>>
>
>

Other related posts: