How to load the table which has primary key as a timestamp field from oracle source sytem?

[Former Member]

Hello Experts,

We are using SLT to load data from Oracle to Hana system, Development and release of the same till quality is done and was working fine, when it was released in Production, we faced locking issues in few tables when users were using application. Due to which production application went down.

Table ABC

Keys for Table ABC are: id,cusId,flag,cmpltd_DTTM.

Our Oracle Expert from application side has reviewed the triggers which SLT system created, and says it is the date field cmpltd_dtttm which is a key a field in oracle as mentioned above, which is causing locks to application tables.

The explanation which he provided is given below,

Need your help (An urgent Issue).

The point that he mentioned is that, the view that is created on logging table, is causing lock, when it is trying to load change record ids into logging tables.

The logic is:

1. On insert trigger code, which will insert the record in View ""/1CADMC/00000687" INSERT INTO "/1CADMC/00000687" ("IUUC_SEQUENCE", "IUUC_TIMESTAMP", "IUUC_PROCESSED", "INT_VCN_NO", "INT_WQ_NO", "INT_JOB_NO", "JOB_TYPE", "MV_NO", "DL_IND", "CMPLTN_DTTM", "INT_TMNL_NO", "IUUC_OPERAT_FLAG") VALUES ("TOS_USR"."SEQ_/1CADMC/00000687".NEXTVAL, to_char(SYS_EXTRACT_UTC(SYSTIMESTAMP), 'YYYYMMDDHH24MISSFF'), ' ', :NEW."INT_VCN_NO", :NEW."INT_WQ_NO", :NEW."INT_JOB_NO", :NEW."JOB_TYPE", :NEW."MV_NO", :NEW."DL_IND", :NEW."CMPLTN_DTTM", :NEW."INT_TMNL_NO", 'I');

2. View definition of "/1CADMC/00000687" CREATE OR REPLACE VIEW "/1CADMC/00000687" AS SELECT "IUUC_SEQUENCE", "IUUC_TIMESTAMP", "IUUC_PROCESSED", "INT_VCN_NO", "INT_WQ_NO", "INT_JOB_NO", "JOB_TYPE", "MV_NO", "DL_IND", TO_CHAR("CMPLTN_DTTM", 'YYYY-MM-DD HH24:MI:SS'), "INT_TMNL_NO", "IUUC_OPERAT_FLAG" FROM "/1CADMC/V0000687";

Where CMPLTN_DTTM is converted as CHAR by using TO_CHAR function, which is creating virtual column definition in the view.

This is the main reason for exception/error, and the error is "ERROR IS: ORA-01733: virtual column not allowed here"

3. View "/1CADMC/00000687" is created on table "/1CADMC/V0000687" where column CMPLTN_DTTM is DATE column.

4. When I changed the definition of view by removing TO_CHAR and date format (simplty used the CMPLTN_DTTM), Then insert statement worked.

We need a solution which eliminates the issue of locking.