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:22:29 -0800
Additional info: This has been working for months with millions of
transactions with no problems.
Mike
On Wed, Jan 19, 2011 at 2:20 PM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> 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:
