7022558: Vibe database upgrade on Oracle fails with error ORA-02273: this unique/primary key is referenced by some foreign keys

This document (7022558) is provided subject to the disclaimer at the end of this document.

Environment

Micro Focus Vibe 4.0.3

Micro Focus Vibe 4.0.4

Situation

When updating from an earlier version of Vibe to Micro Focus Vibe 4.0.3, 4.0.4 or newer with an Oracle database, the database update step (as documented in the Vibe 4.0.3 Update Guide) fails with an error that looks like:

SEVERE 9/22/17 11:33 AM:liquibase: Change Set scripts/changelog/oracle-changelog-quartz-1.8-to-2.2.xml::6-4::jong failed. Error: Error executing SQL alter table SSQRTZ_JOB_DETAILS drop primary key drop index: ORA-02273: this unique/primary key is referenced by some foreign keys

liquibase.exception.DatabaseException: Error executing SQL alter table SSQRTZ_JOB_DETAILS drop primary key drop index: ORA-02273: this unique/primary key is referenced by some foreign keys

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:62)

at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:104)

at liquibase.database.AbstractDatabase.execute(AbstractDatabase.java:1091)

…..

…..

Resolution

A fix for this issue is available for Vibe 4.0.3 / 4.0.4. Please contact Micro Focus Customer Care with reference to this TID for further assistance.

Disclaimer

This Support Knowledgebase provides a valuable tool for NetIQ/Novell/SUSE customers and parties interested in our products and solutions to acquire information, ideas and learn from one another. Materials are provided for informational, personal or non-commercial use within your organization and are presented “AS IS” WITHOUT WARRANTY OF ANY KIND.

Related:

Is this safe against SQL injection?

Is the below form safe against SQL injection? I have to do this because the p_select_statement would be a simple select * from table where lastModifiedTime > :p_asof and <conditions> , but the code that calls getByFilter expects the columns to be in the order col1, col2, col3 while select * may return …

Related:

Bug in db2? Count(*) not behaving correctly after alter table

Hi everyone, I noticed that count(*) is giving 0 for dangling tuples in a query with a group by, when it should give 1. This is happening only in some situations, in particular after an alter table where I define a foreign key. Here is a sample code to show what I mean:

CREATE TABLE TABLE_1
( X INT NOT NULL PRIMARY KEY);
CREATE TABLE TABLE_2
( Y INT NOT NULL );
INSERT INTO TABLE_1
VALUES (1),
(2),
(3),
(4),
(5);
INSERT INTO TABLE_2
VALUES (1),
(1),
(2);
If I now do this query:

SELECT T1.X, COUNT (*) as c1
from TABLE_1 T1 left join TABLE_2 T2 on (T1.X = T2.Y)
group by T1.X;
I get the expected result with at least 1 for every row.
But the result set changes after this after table followed by a runstats:

ALTER TABLE TABLE_2
ADD CONSTRAINT fk FOREIGN KEY (Y) REFERENCES TABLE_1 ;
RUNSTATS ON TABLE TABLE_1;
Now the same query gives me 0 in c1 for the dangling tuples which it’s not correct.

UPDATE:

I noticed that the same problem happens when I do the following: create the tables, insert some values, define the foreign key WITHOUT executing runstats, executing

REORG TABLE TABLE_1;
REORG TABLE TABLE_2;
And execute the query written above with the select count(*). This bring to the same WRONG result set with some zeros for dangling tuples.

BUT it’s even more weird that if I execute the query at least once before executing REORG TABLE, the problem doesn’t show up (neither in the query before REORG nor in the result of the query after REORG) and the result set is the correct one. It seems like the first query changes the behaviour of REORG TABLE.

An user on stackoverflow, where I posted this [question][1], suggested me to ask here to get some more information.

I would like to know if you guys also think that this is a bug or if I’m making a mistake somewhere.

NOTE: this is happening on a v9.7 server, using both v9.7 and v11.1 clients (clients running on windows 10 64bit, while the server is the university’s one, running on Linux 64bit).

[1]: https://stackoverflow.com/questions/47268933/select-count-not-behaving-correctly-after-alter-table?noredirect=1

Related:

IN (vs) EXISTS and NOT IN (vs) NOT EXISTS

I have the most bizarre CBO’ness (for what of a better word!) that I can not fathom. Below are the tear down and set up needed.

select * from v$banner

BANNER

—————————————————————-

Oracle9i Enterprise Edition Release 9.2.0.4.0 – 64bit Production

PL/SQL Release 9.2.0.4.0 – Production

CORE 9.2.0.3.0 Production

TNS for HPUX: Version 9.2.0.4.0 – Production

NLSRTL Version 9.2.0.4.0 – Production




— Clear down



drop table HAS_PERMISSION_UNIT_TEST

/

drop table PLT_NODE_PERMISSIONS

/

drop table PLT_SECURITY_NODES

/

drop table PLT_USER_DELEGATES

/

drop table PLT_XDI_PERMISSIONS

/

CREATE TABLE PLT_SECURITY_NODES

( NODE_ID VARCHAR2(16) NOT NULL,

PARENT_NODE_ID VARCHAR2(16),

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PSN_PSN_FK_I ON PLT_SECURITY_NODES

(PARENT_NODE_ID)

/


CREATE UNIQUE INDEX PSN_PK ON PLT_SECURITY_NODES

(NODE_ID)

/


CREATE INDEX NODE_AND_PARENT_NODE ON PLT_SECURITY_NODES

(NODE_ID, PARENT_NODE_ID)

/


ALTER TABLE PLT_SECURITY_NODES ADD (

CONSTRAINT PSN_PK PRIMARY KEY (NODE_ID))

/


ALTER TABLE PLT_SECURITY_NODES ADD (

CONSTRAINT PSN_PSN_FK FOREIGN KEY (PARENT_NODE_ID)

REFERENCES PLT_SECURITY_NODES (NODE_ID))

/


————————————————————————————————


CREATE TABLE PLT_NODE_PERMISSIONS

( NODE_ID VARCHAR2(32) NOT NULL,

USER_ID VARCHAR2(32) NOT NULL,

ROLE_ID VARCHAR2(32) NOT NULL,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE UNIQUE INDEX PNP_PK ON PLT_NODE_PERMISSIONS

(NODE_ID, USER_ID, ROLE_ID)

/


CREATE INDEX NODE_AND_USER ON PLT_NODE_PERMISSIONS

(NODE_ID, USER_ID)

/


CREATE INDEX PNP_PSN_FK_I ON PLT_NODE_PERMISSIONS

(NODE_ID)

/


CREATE INDEX PNP_PPR_FK_I ON PLT_NODE_PERMISSIONS

(ROLE_ID, USER_ID)

/


ALTER TABLE PLT_NODE_PERMISSIONS ADD (

CONSTRAINT PNP_PK PRIMARY KEY (NODE_ID, USER_ID, ROLE_ID))

/


ALTER TABLE PLT_NODE_PERMISSIONS ADD (

CONSTRAINT PNP_PSN_FK FOREIGN KEY (NODE_ID)

REFERENCES PLT_SECURITY_NODES (NODE_ID))

/

—————————————————————————-


CREATE TABLE PLT_USER_DELEGATES

( USER_ID VARCHAR2(32) NOT NULL,

DELEGATE_USER_ID VARCHAR2(32) NOT NULL,

EFFECTIVE_FROM DATE NOT NULL,

EFFECTIVE_TO DATE,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PUD_PP_FK_I ON PLT_USER_DELEGATES

(USER_ID)

/


CREATE INDEX PUD_PP_DELEGATE_FK_I ON PLT_USER_DELEGATES

(DELEGATE_USER_ID)

/


CREATE UNIQUE INDEX PUD_PK ON PLT_USER_DELEGATES

(USER_ID, DELEGATE_USER_ID, EFFECTIVE_FROM)

/


ALTER TABLE PLT_USER_DELEGATES ADD (

CONSTRAINT PUD_PK PRIMARY KEY (USER_ID, DELEGATE_USER_ID, EFFECTIVE_FROM))

/

—————————————————————————————


CREATE TABLE PLT_XDI_PERMISSIONS

(

XDI VARCHAR2(255) NOT NULL,

USER_ID VARCHAR2(32) NOT NULL,

ROLE_ID VARCHAR2(16) NOT NULL,

IS_DELETED NUMBER NOT NULL,

IS_SYSTEM NUMBER NOT NULL,

LAST_MODIFIED_DATETIME DATE,

LAST_MODIFIED_USER_ID VARCHAR2(32)

)

/


CREATE INDEX PXP_PPR_FK_I ON PLT_XDI_PERMISSIONS

(ROLE_ID, USER_ID)

/


CREATE UNIQUE INDEX PXP_PK ON PLT_XDI_PERMISSIONS

(XDI, USER_ID, ROLE_ID)

/


ALTER TABLE PLT_XDI_PERMISSIONS ADD (

CONSTRAINT PXP_PK PRIMARY KEY (XDI, USER_ID, ROLE_ID))

/

begin



— Set up 4 delegates for this user to have access via



for idx in 1..4

loop

insert into plt_user_delegates

( user_id, delegate_user_id, effective_from, effective_to, is_deleted, is_system, last_modified_datetime, last_modified_user_id)

values

(‘MJONES’, ‘MJONES_DELEGATE_’||idx, sysdate -100, sysdate + 100, 0, 0, sysdate, ‘MJONES’);



end loop;



— Loop 500 times to create some realistic volume of data that this user has



for idx in 1..500

loop

insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_TOP_LVL’, null, 0, 1, sysdate, ‘MJONES’);



insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_LEVEL_2’, ‘NODE_’||idx||’_TOP_LVL’, 0, 1, sysdate, ‘MJONES’);



insert into plt_security_nodes

( node_id, parent_node_id, is_deleted, is_system, last_modified_datetime, last_modified_user_id )

values

(‘NODE_’||idx||’_LVL_3’, ‘NODE_’||idx||’_LEVEL_2’, 0, 1, sysdate, ‘MJONES’);



— Create permission into Node



insert into plt_node_permissions ( node_id, user_id, role_id, is_deleted, is_system ) values (

‘NODE_’||idx||’_LVL_3’, ‘MJONES’, ‘UNIT_TEST’, 0, 0);

end loop;



— Now wedge up some XDI Permissions. Have a total of 100 XDI’s



for idx in 1..100

loop

insert into plt_xdi_permissions

( xdi, user_id, role_id, is_deleted, is_system)

values

( idx||’_JONES_THE_XDI’, ‘MJONES’, ‘UNIT_TEST’, 0, 0);

end loop;

end;

/

analyze table plt_node_permissions compute statistics for table for all indexes for all indexed columns

/

analyze table plt_security_nodes compute statistics for table for all indexes for all indexed columns

/

analyze table plt_user_delegates compute statistics for table for all indexes for all indexed columns

/

analyze table plt_xdi_permissions compute statistics for table for all indexes for all indexed columns

/



— Now build up a table of 100,000 rows to base all this malarkey on… Set the access up to start with so that we have access too all 100,000 records in bunches of 200 (500 access records we have access to)



create table has_permission_unit_test as

select

to_char(rownum) primary_key,

owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status, temporary, generated, secondary,

‘NODE_’||(mod(rownum,200)+1)||’_LVL_3’ security_node_id,

–decode(mod(rownum,1000),0, mod(rownum,1000)||’JONES_THE_XDI’,rownum||’_XDI_’||object_name) xdi,

rownum||’_XDI_’||object_name xdi,

0 is_deleted,

sysdate last_modified_datetime,

‘BOB’ last_modified_user_id

from

( select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

union all

select * from all_objects

)

where rownum <= 100000– 100K

/



alter table has_permission_unit_test modify primary_key varchar2(32)

/



alter table has_permission_unit_test add primary key (primary_key)

/



analyze table has_permission_unit_test compute statistics for table for all indexes for all indexed columns

/



— END OF SET UP


The query that behaves strangley is…

select count(*)

from has_permission_unit_test main

where main.primary_key <= :i_volume

and

(

main.security_node_id in

( select /*use_hash(sn) index_ffs( sn node_and_parent_node ) use_hash(np) index_ffs(np node_and_user)*/ np.node_id

from plt_security_nodes sn, plt_node_permissions np

where sn.node_id = np.node_id

and np.user_id in

( select user_id

from plt_user_delegates ud_first

where ud_first.delegate_user_id = ‘MJONES’

and sysdate between ud_first.effective_from and ud_first.effective_to

and ud_first.is_deleted = 0

union all

select ‘MJONES’ user_id

from dual dual_first

)

connect by prior sn.parent_node_id = sn.node_id

)

— COMMENT OUT FROM HERE

or

( main.xdi in

( select xi.xdi

from plt_xdi_permissions xi

where xi.user_id in

( select user_id

from plt_user_delegates ud_second

where ud_second.delegate_user_id = ‘MJONES’

and sysdate between ud_second.effective_from and ud_second.effective_to

and ud_second.is_deleted = 0

union all

select ‘MJONES’ user_id

from dual dual_second

)

)

)

— DOWN TO HERE

)


Running as is with :i_volume = 1000 takes around 4 seconds, and the matches are being done with the security nodes (I beleive), but if you comment out the bottom bit the plan radically changes to start FFS with a couple of indexes I can not see why the “or” changes this. Even if I change this to “or 1 in (select null from dual where 1=0) it still won’t FFS. I tried hinting (the + is removed so it won’t pick it up to no avail. Why has the performance so dropped off when I stick any OR in?

Thanks in advance, and hope this is enough info.

Mike.

Related:

Removing deadlock

Hi Tom,

*** 2005-10-10 11:42:28.655

*** ACTION NAME:() 2005-10-10 11:42:28.642

*** MODULE NAME:(SQL*Plus) 2005-10-10 11:42:28.642

*** SERVICE NAME:(SYS$USERS) 2005-10-10 11:42:28.642

*** SESSION ID:(1057.27841) 2005-10-10 11:42:28.642

DEADLOCK DETECTED

Current SQL statement for this session:

INSERT INTO MATCH_TERM VALUES (:B2 ,:B1 )

—– PL/SQL Call Stack —–

object line object

handle number name

0x9e49a5e0 37 procedure PARIS_USER.PUMP_DATA_IN_10

0x9f1f5a10 1 anonymous block

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

———Blocker(s)——– ———Waiter(s)———

Resource Name process session holds waits process session holds waits

TX-0004001b-00000b1b 19 1057 X 50 1060 S

TX-00050026-00000324 50 1060 X 19 1057 S

session 1057: DID 0001-0013-00002933 session 1060: DID 0001-0032-00000069

session 1060: DID 0001-0032-00000069 session 1057: DID 0001-0013-00002933

Rows waited on:

Session 1060: obj – rowid = 00016C83 – AAAWyDAAIAADlrLAAA

(dictionary objn – 93315, file – 8, block – 940747, slot – 0)

Session 1057: obj – rowid = 00016C83 – AAAWyDAAIAACJnIAAA

(dictionary objn – 93315, file – 8, block – 563656, slot – 0)

Information on the OTHER waiting sessions:

Session 1060:

pid=50 serial=19143 audsid=89755 user: 138/PARIS_USER

O/S info: user: oracle, term: pts/1, ospid: 32084, machine: search3.internal

program: sqlplus@search3.internal (TNS V1-V3)

application name: SQL*Plus, hash value=3669949024

Current SQL Statement:

INSERT INTO MATCH_TERM VALUES (:B2 ,:B1 )

End of information on OTHER waiting sessions.

===================================================

is the trace of the file. Can you please let me know why the deadlock is coming at INSERT INTO MATCH_TERM – the parent table of both the other tables. Both the Child tables have INDEXED foreign keys.Some of the DDL statements for these tables are:

CREATE TABLE Match_term(

Match_term_id NUMBER(10, 0) NOT NULL,

Match_term_text VARCHAR2(255) NOT NULL,

PRIMARY KEY (Match_term_id)

);

CREATE TABLE Match_terms_daily(

Match_term_id NUMBER(10, 0) NOT NULL,

Aggr_Date DATE NOT NULL,

Request_Count NUMBER(10, 0),

Sum_Impr_Requested NUMBER(10, 0),

Sum_impr_Requested_Sqr NUMBER(10, 0),

Max_impr_requested NUMBER(10, 0),

PRIMARY KEY (Match_term_id, Aggr_Date)

);

CREATE TABLE Match_terms_position_daily(

Match_term_id NUMBER(10, 0) NOT NULL,

Aggr_Date DATE NOT NULL,

Position_Num NUMBER(10, 0) NOT NULL,

Impr_Count NUMBER(10, 0) NOT NULL,

Click_count NUMBER(10, 0) NOT NULL,

Max_bid NUMBER(20, 0),

Sum_bid NUMBER(20, 0),

Sum_bid_Sqr NUMBER(20, 0),

Max_CPC_Charged NUMBER(20, 0),

Sum_CPC_Charged NUMBER(20, 0),

Sum_CPC_Charged_Sqr NUMBER(20, 0),

Sum_Impr_Score NUMBER(20, 0),

Sum_Impr_Score_Sqr NUMBER(20, 0),

PRIMARY KEY (Match_term_id, Aggr_Date, Position_Num)

);

ALTER TABLE Match_terms_daily ADD CONSTRAINT FK_MATCH_TERMS_D_MT_ID

FOREIGN KEY (Match_term_id)

REFERENCES Match_term(Match_term_id);

ALTER TABLE Match_terms_position_daily ADD CONSTRAINT FK_MATCH_TERMS_POS_D_MT_ID

FOREIGN KEY (Match_term_id)

REFERENCES Match_term(Match_term_id);

Please let me know why Oracle is throwing Deadlock errors. Confused at this behaviour!

Related:

Create column with derived values from another column

Hello,

How do I create a column in the customer/ patient table called “ACCOUNT_BALANCE” that keeps the sum of the amounts from the Billing table?

The billing table bills each service, so each customer will have multiple records in the billing table. So we need to create a new column in the customer table called “account_balance”, this column will always generate the sum of all billing amount for each customer.


Please advise.


Example:

CREATE TABLE PATIENT

(“PATIENT_ID” NUMBER (10,0) NOT NULL ENABLE,

“LOCATION_ID” NUMBER(10,0) CONSTRAINT P_LOCATION_ID_fk

REFERENCES LOCATION NOT NULL ENABLE,

“FIRST_NAME” VARCHAR(25) NOT NULL ENABLE,

“LAST_NAME” VARCHAR(25) NOT NULL ENABLE,

“MIDDLE_NAME” VARCHAR(30),

“ACCOUNT_BALANCE” NUMBER(10,4) NOT NULL ENABLE,

“CREATE_DATE” DATE DEFAULT SYSDATE NOT NULL ENABLE,

PRIMARY KEY (“PATIENT_ID”)

);


CREATE TABLE PATIENT_BILLING

( “ACCOUNT_ID” NUMBER(10,0) NOT NULL ENABLE,

“VISIT_ID” NUMBER(10,0) CONSTRAINT B_VISIT_ID_fk

REFERENCES MEDICAL_VISIT NOT NULL ENABLE,

“PAITENT_ID” NUMBER(10,0) CONSTRAINT B_PAITENT_ID_fk

REFERENCES PATIENT NOT NULL ENABLE,

“BILLING_DATE” DATE NOT NULL ENABLE,

“BILLING_AMOUNT” NUMBER(10,4) NOT NULL ENABLE

CONSTRAINT check_BILLING_AMOUNT CHECK (BILLING_AMOUNT >= 1),

— “PATIENT_BALANCE” NUMBER(10,4) NOT NULL ENABLE,

“CREATE_DATE” DATE DEFAULT SYSDATE NOT NULL ENABLE,

PRIMARY KEY (“ACCOUNT_ID”)

);


We were trying to do something like this, but it is not working.

alter table PATIENT MODIFY “ACCOUNT_BALANCE” NUMBER(10,4) GENERATED ALWAYS as

(SUM(PATIENT_BILLING.BILLING_AMOUNT) WHERE PATIENT.BILLING_AMOUNT=PATIENT_BILLING.BILLING_AMOUNT),

Related:

Fast Refresh on Materialized View seems not working with OUTER JOIN in some conditions

Hi guys,

it’s a pleasure to write here for the first time. I’ll try to explain my problem that seems simple.

I create 2 tables, TableFather and TableChild, linked by foreign key.

I create Materialized View Log for both.

I create Materialized View with fast refresh as left join from TableFather to TableChild.

Result is

1. Adding a child record, Materialized View is refreshed

2. Modifying a child field, Materialized View is refreshed

3. Adding a father record, Materialized View is NOT refreshed

This is the code

 -- Tables CREATE TABLE TABLE_FATHER ( ID NUMBER(10, 0), TEXT NVARCHAR2(50), CONSTRAINT PK__TABLE1 PRIMARY KEY (ID) ); CREATE TABLE TABLE_CHILD ( ID NUMBER(10, 0), TEXT NVARCHAR2(50), ID_FATHER NUMBER(10, 0), CONSTRAINT PK__TABLE2 PRIMARY KEY (ID), CONSTRAINT FK_TABLE_FATHER FOREIGN KEY (ID_FATHER) REFERENCES TABLE_FATHER (ID) ); -- Some record before materialized view creation INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(1, 'father1'); INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(1, 'child1', 1); INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(2, 'child2', 1); -- Logs CREATE MATERIALIZED VIEW LOG on TABLE_FATHER WITH PRIMARY KEY, ROWID; CREATE MATERIALIZED VIEW LOG on TABLE_CHILD WITH PRIMARY KEY, ROWID; -- Materialized View CREATE MATERIALIZED VIEW TABLE_MV REFRESH FAST ON COMMIT AS SELECT TABLE_FATHER.ID ID_FATHER, TABLE_FATHER.TEXT TEXT_FATHER, TABLE_CHILD.ID ID_CHILD, TABLE_CHILD.TEXT TEXT_CHILD, TABLE_FATHER.ROWID FATHER_ROWID, TABLE_CHILD.ROWID CHILD_ROWID FROM TABLE_FATHER, TABLE_CHILD WHERE TABLE_FATHER.ID = TABLE_CHILD.ID_FATHER (+); 

At this point you can verify, first result

 INSERT INTO TABLE_CHILD(ID, TEXT, ID_FATHER) VALUES(3, 'child3', 1); COMMIT; SELECT * FROM TABLE_MV; 

Then second result

UPDATE TABLE_CHILD SET TEXT = 'child33' WHERE ID = 3; COMMIT; SELECT * FROM TABLE_MV;

Then third result

 INSERT INTO TABLE_FATHER(ID, TEXT) VALUES(2, 'father2'); COMMIT; SELECT * FROM TABLE_MV; 

As you will see (it’s what happens to me), Materialized View is not refreshed in the last case.

I would like to know what i’m doing wrong.

Thank you for your help

Related:

ORA-02292: integrity constraint with FK reference with same table PK

Hello Tom,

Need Ur help.

DB : 12.1.0.2.0

While execution script we are facing below issue:

— Table have PK and FK (FK refer to same table PK).

While we execute script for delete some data , But some how we get the error of

ORA-02292: integrity constraint (CRESTELSYSTEMCRESTEL62308.FK_ACTION_ACTION_1) violated – child record found

This is the query:

delete from TBLMACTION WHERE SCREENID IN ( SELECT SCREENID FROM TBLSSCREEN where alias=’PRODUCT_MANAGER’)


This is the Table structure:

CREATE TABLE “TBLMACTION”

( “ACTIONID” CHAR(8 BYTE),

“NAME” VARCHAR2(100 BYTE),

“SCREENID” CHAR(6 BYTE),

“ALIAS” VARCHAR2(50 BYTE),

“DESCRIPTION” VARCHAR2(255 BYTE),

“ACTIONTYPEID” CHAR(5 BYTE),

“PARENTACTIONID” CHAR(8 BYTE),

“ACTIONLEVEL” NUMBER(2,0),

“SYSTEMGENERATED” CHAR(1 BYTE),

“MODULE” VARCHAR2(100 BYTE),

“LINK” VARCHAR2(255 BYTE),

“ACTIONCLASS” VARCHAR2(200 BYTE),

“DISPLAYORDER” NUMBER(*,0) DEFAULT 0,

“HYPERLINKTYPE” VARCHAR2(20 BYTE) DEFAULT ‘FIXED’,

“AVPAIR” VARCHAR2(100 BYTE),

CONSTRAINT “NN_ANID_AN” CHECK (ACTIONID IS NOT NULL) ENABLE,

CONSTRAINT “NN_NAME_AN” CHECK (NAME IS NOT NULL) ENABLE,

CONSTRAINT “NN_SCREENID_AN” CHECK (SCREENID IS NOT NULL) ENABLE,

CONSTRAINT “NN_ALIAS_AN” CHECK (ALIAS IS NOT NULL) ENABLE,

CONSTRAINT “NN_ANTYPEID_AN” CHECK (ACTIONTYPEID IS NOT NULL) ENABLE,

CONSTRAINT “NN_ANLEVEL_AN” CHECK (ACTIONLEVEL IS NOT NULL) ENABLE,

CONSTRAINT “NN_SYGN_AN” CHECK (SYSTEMGENERATED IS NOT NULL) ENABLE,

CONSTRAINT “NN_MOD_AN” CHECK (MODULE IS NOT NULL) ENABLE,

CONSTRAINT “NN_DISPLAYORDER_AN” CHECK (DISPLAYORDER IS NOT NULL) ENABLE,

CONSTRAINT “PK_ACTION” PRIMARY KEY (“ACTIONID”),

CONSTRAINT “FK_ACTION_ACTION_1” FOREIGN KEY (“PARENTACTIONID”)

REFERENCES “TBLMACTION” (“ACTIONID”) ENABLE

) ;

We check data and all things,

Actually, Main Problem is that, Same script is successfully execute with oracle version 11.2.0.3 without any changes, But some how it is not execute with version 12.1.0.2.

Please let me know is there any related changes or BUG with 12c. Or I have to configure any parameter for same.

Related:

ILOG ODM Enterprise intermittent data corruption issue

Hi, I am using IBM ILOG ODM Enterprise – ODM Planner studio version 3.8 for supply planning business. We are experiencing the intermittent and unusual error which “Check Data” process is failed due to missing foreign key/ missing mandatory field. The steps are below.
1. Create a new scenario
2. “Check Data” process is passed and “Solve” process is completed.
3. When execute “Check Data” again, it fails as missing data (Foreign key or mandatory field).

We have checked all the log files in Opti server and Data Server and couldn’t find any error during “Solve” process. We suspect that populating data for saving back to database has some issue and it cause the corrupted data. As said earlier, the error happens intermittently.
Please help advise how to fix it.

Related: