RE: Duplicate timestamp(6): How is this possible?
- From: <rajendra.pande@xxxxxxx>
- To: <niall.litchfield@xxxxxxxxx>, <michaeljmoore@xxxxxxxxx>
- Date: Wed, 19 Jan 2011 17:05:57 -0500
Good question
If I create the table as mentioned and run a quick test with code as
below I have no issues
Need to test with the autonomous transaction
declare
x integer := 0;
begin
for i in 1..1000 loop
INSERT INTO tcom_return_adj_audit (record_number, msg, run_date)
VALUES (i , 'inserting ' || i , sysdate);
end loop;
end;
/
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Niall Litchfield
Sent: Wednesday, January 19, 2011 4:46 PM
To: michaeljmoore@xxxxxxxxx
Cc: oracle_l
Subject: Re: Duplicate timestamp(6): How is this possible?
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
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.
Other related posts:
