Wednesday, February 12, 2020

How to find EBS CPU Patch Level in R12.2 ?

How to find EBS CPU Patch Level in R12.2 
For 12.2 use the below script. 

  
set linesize 141
set pagesize 100;
col "Patch" format A10;
col "Description" format A70;
select distinct bug_number Patch, decode(bug_number,
    '28421544','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2018',
    '28018169','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JUL 2018',
    '27468058','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR APR 2018',
    '27040860','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JAN 2018',
    '26574498','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2017',
    '25982922','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JUL 2017',
    '25449173','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR APR 2017',
    '25032335','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JAN 2017',
    '24390794','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2016',
    '23144508','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JUL 2016',
    '22614473','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR APR 2016',
    '22133451','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JAN 2016',
    '21507429','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2015',
    '20953338','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JUL 2015',
    '20406630','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR APR 2015',
    '19873050','ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR JAN 2015') Description,
     CREATION_DATE Applied
from ad_bugs
where bug_number in
('28421544','28018169','27468058','27040860','26574498','25982922','25449173','25032335','24390794','23144508','22614473','22133451','21507429','20953338','20406630','19873050') order by Description desc;

output
--------



Tuesday, February 4, 2020

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

ORA-02266: unique/primary keys in table referenced by enabled foreign keys


Error Message:

SQL> truncate table ABCDEF
truncate table ABCDEF
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

-- Find the referenced foreign key constraints.

Disable constraints script

SQL> select 'alter table '||a.owner||'.'||a.table_name||' disable constraint '||a.constraint_name||';'
   from all_constraints a, all_constraints b
  where a.constraint_type = 'R'
   and a.r_constraint_name = b.constraint_name
  and a.r_owner  = b.owner
  and b.table_name = '&TABLE_NAME';


Enable the foreign keys back

SQL> select 'alter table '||a.owner||'.'||a.table_name||' enable constraint '||a.constraint_name||';'
   from all_constraints a, all_constraints b
  where a.constraint_type = 'R'
   and a.r_constraint_name = b.constraint_name
   and a.r_owner  = b.owner
   and b.table_name = '&TABLE_NAME';