RE: Duplicate timestamp(6): How is this possible?
Date: Wed, 19 Jan 2011 17:17:09 -0500
Message-ID: <D4C8B99EB96F2C42B4E19A3B87664F5EB371A7_at_NSTMC612PEX.ubsamericas.net>
Still not clear - where and how often does this get called - especially given that this is an autonomous trans
My testing was on a rhel 11gr2 env
What is the OS and hardware where you are getting this error
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Moore
Sent: Wednesday, January 19, 2011 5:12 PM
To: Niall Litchfield
Cc: oracle_l
Subject: Re: Duplicate timestamp(6): How is this possible?
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_at_gmail.com> wrote:
how is the code called?
On Wed, Jan 19, 2011 at 9:30 PM, Michael Moore <michaeljmoore_at_gmail.com> 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.infoReceived on Wed Jan 19 2011 - 16:17:09 CST
Please visit our website at http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html for important disclosures and information about our e-mail policies. For your protection, please do not transmit orders or instructions by e-mail or include account numbers, Social Security numbers, credit card numbers, passwords, or other personal information. -- http://www.freelists.org/webpage/oracle-l