Dropping Tables for Deleted Record Definitions

While application designer makes it easy to delete record definitions, it doesn't drop the table or view in the database after the record definition has been deleted. So you can be left with orphaned tables or views that have no application designer record definition.

To identify such cases use the following query in Oracle:

select
    RDEL.RECNAME,
    RDEL.VERSION,
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) as TABLE_VIEW_NAME
from 
    PSRECDEL RDEL left outer join ALL_TABLES AT
    on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
    left outer join ALL_VIEWS AV
    on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')    
where
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;

The PSRECDEL table stores record definitions that have been deleted through application designer.

If you want, you can use the following query to automatically generate drop table and drop view statements for any orphaned tables or views that are found:

select    
    (case when AT.TABLE_NAME is null 
     then 'drop view ' || AV.VIEW_NAME || ';'
     else 'drop table ' || AT.TABLE_NAME || ';'
     end) as DROP_STATEMENT
from 
    PSRECDEL RDEL left outer join ALL_TABLES AT
    on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
    left outer join ALL_VIEWS AV
    on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')    
where
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;
Page tags: application-designer best-practice
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License