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

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: