Re: Duplicate timestamp(6): How is this possible?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: rajendra.pande@xxxxxxx
- Date: Wed, 19 Jan 2011 14:50:42 -0800
Sorry about not being clear. Okay, how to describe this?
Procedure A which is run via a cron job, calls my packaged procedure call it
P.X . My packaged procedure (P.X) does some business logic, but it also
needs to put data into an audit/debugging/log table. This table is the one I
described on previous posts (tcom_return_adj_audit). In order for P.X to log
various messages, it does not INSERT directly into the table.. Instead, it
calls :
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;
PROCEDURE log_audit_info is a procedure in the same package as P.X.
HTH
Mike
On Wed, Jan 19, 2011 at 2:17 PM, <rajendra.pande@xxxxxxx> wrote:
> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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@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
>
>
>
> 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:
