[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select log_table
into l_mlogName
from dba_mview_logs
where master = p_objName
and log_owner = p_schemaName;
SELECT extractValue(l_indexXMLs,'ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME')
INTO l_indexName
FROM DUAL;
l_sqlCond := ' not in ( select index_name from dba_indexes where (index_type=''DOMAIN'' or index_name like ''AQ$_%'') and table_name='''||p_objName||''' and owner='''||p_schemaName||''' ) ';
l_sqlCond := ' not in ( select index_name from dba_indexes where index_type=''DOMAIN'' and table_name='''||p_objName||''' and owner='''||p_schemaName||''' ) ';
l_sqlCond := ' not in ( select index_name from dba_indexes where index_type=''DOMAIN'' and table_name='''||l_mlogName||''' and owner='''||p_schemaName||''' ) ';
SELECT extractValue(l_indexXMLs,'ROWSET/ROW/INDEX_T/SCHEMA_OBJ/NAME')
INTO l_indexName
FROM DUAL;
select ot.name BULK COLLECT INTO l_triggNames
from sys.obj$ ot, sys.obj$ bo, sys.trigger$ t
where t.BASEOBJECT = bo.OBJ#
and bo.NAME = l_objName
and ot.OBJ# = t.OBJ#;
SELECT extractValue(l_triggerXMLs,'ROWSET/ROW/TRIGGER_T/SCHEMA_OBJ/NAME')
INTO l_triggerName
FROM DUAL;
SELECT extractValue(l_sequenceXML,'ROWSET/ROW/SEQUENCE_T/SCHEMA_OBJ/NAME')
INTO l_SeqName
FROM DUAL;
SELECT extractValue(l_policyXML,'ROWSET/ROW/RLS_POLICY_T/NAME')
INTO l_policyName
FROM DUAL;
l_str := 'select fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from fnd_views a, fnd_application b where view_name = :1 ' ||
' and a.application_id = b.application_id ' ||
' and b.application_short_name = :2 ';
' || l_ludate || '
');
select NVL(temporary, 'NO'), NVL(IOT_TYPE, 'NO')
from dba_tables where table_name = p_object_name
and owner = p_schemaName;
select substr(ltrim(rtrim(comments)),1,240)
into l_comments
from dba_tab_comments
where owner = p_owner
and table_name = p_objName;
l_str := 'select table_type as apps_table_type, ' ||
' NVL(a.description,:1) as APPS_DESCRIPTION, ' ||
' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from fnd_tables a, fnd_application b where table_name = :2 ' ||
' and a.application_id = b.application_id ' ||
' and b.application_short_name = :3 ';
' || l_ludate || '
');
l_str := 'select column_name as apps_column_name, ' ||
' nvl((select nvl(substr(ltrim(rtrim(c.comments)), 1, 240),'' '') '||
' from dba_col_comments c ' ||
' where c.owner = a.owner ' ||
' and c.table_name = a.table_name ' ||
' and c.column_name = a.column_name), '' '') as APPS_DESCRIPTION, ' ||
'''N'' as APPS_TRANSLATE_FLAG , ' ||
'''N'' as APPS_FLEXFIELD_USAGE_CODE, ' ||
''' '' as APPS_FLEXFIELD_APP_ID, ' ||
''' '' as APPS_FLEXFIELD_NAME, ' ||
'''SEED'' as APPS_OWNER, ' ||
' to_char(sysdate, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
'FROM dba_tab_cols a where a.table_name = :1 ' ||
'and a.owner = :2 order by column_id';
l_str := 'select column_name as apps_column_name, ' ||
' NVL(description,'' '') as APPS_DESCRIPTION, '
'TRANSLATE_FLAG as APPS_TRANSLATE_FLAG , ' ||
'FLEXFIELD_USAGE_CODE as APPS_FLEXFIELD_USAGE_CODE, ' ||
'NVL(TO_CHAR(FLEXFIELD_APPLICATION_ID),'' '') as APPS_FLEXFIELD_APP_ID, ' ||
'NVL(FLEXFIELD_NAME,'' '') as APPS_FLEXFIELD_NAME, ' ||
' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
'FROM fnd_tables a, fnd_columns b, fnd_application c ' ||
' where a.table_name = :1 ' ||
' and c.application_short_name = :2 ' ||
'and a.table_id = b.table_id ' ||
'and a.application_id = b.application_id ' ||
'and b.application_id = c.application_id ' ||
'order by column_sequence';
l_str := 'select column_name as apps_column_name, '||
' NVL(b.description,'' '') as APPS_DESCRIPTION, '||
'TRANSLATE_FLAG as APPS_TRANSLATE_FLAG , '||
'FLEXFIELD_USAGE_CODE as APPS_FLEXFIELD_USAGE_CODE, '||
'NVL(TO_CHAR(FLEXFIELD_APPLICATION_ID),'' '') as APPS_FLEXFIELD_APP_ID, '||
'NVL(FLEXFIELD_NAME,'' '') as APPS_FLEXFIELD_NAME, '||
' fnd_load_util.owner_name(a.LAST_UPDATED_BY) as APPS_OWNER, '||
' to_char(a.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE '||
' FROM fnd_tables a, fnd_columns b, fnd_application c '||
' where a.table_name = :tabname '||
' and c.application_short_name = :tabowner '||
' and a.table_id = b.table_id '||
' and a.application_id = b.application_id '||
' and b.application_id = c.application_id '||
'UNION '||
' select '||
' dbacol.column_name as apps_column_name, '||
' nvl((select nvl(substr(ltrim(rtrim(c.comments)),1,240),'' '') '||
' from dba_col_comments c '||
' where c.owner = dbacol.owner '||
' and c.table_name = dbacol.table_name '||
' and c.column_name = dbacol.column_name), '' '') as APPS_DESCRIPTION, '||
' ''N'' as APPS_TRANSLATE_FLAG, '||
' ''N'' AS APPS_FLEXFIELD_USAGE_CODE, '||
' '' '' AS APPS_FLEXFIELD_APP_ID, '||
' '' '' AS APPS_FLEXFIELD_NAME, '||
' fnd_load_util.owner_name(fndtab.LAST_UPDATED_BY) AS APPS_OWNER, '||
' to_char(fndtab.LAST_UPDATE_DATE, ''YYYY/MM/DD'') AS APPS_LAST_UPDATE_DATE '||
' from '||
' dba_tab_columns dbacol, fnd_tables fndtab, fnd_application fndapp '||
' where '||
' dbacol.table_name = :tabname '||
' and dbacol.owner = :tabowner '||
' and dbacol.table_name = fndtab.table_name '||
' and fndtab.table_name = :tabname '||
' and fndapp.application_short_name = :tabowner '||
' and fndtab.application_id = fndapp.application_id '||
' and not exists '||
' (select 1 '||
' from fnd_columns fndcol, '||
' fnd_tables fndtab, '||
' fnd_application fndapp '||
' where dbacol.column_name = fndcol.column_name '||
' and fndcol.table_id = fndtab.table_id '||
' and fndcol.application_id = fndtab.application_id '||
' and fndtab.table_name = :tabname '||
' and fndtab.application_id = fndapp.application_id '||
' and fndapp.application_short_name = :tabowner )';
'|| l_ludate ||'
');
l_str := 'select PRIMARY_KEY_NAME as APPS_PRIMARY_KEY_NAME, ' ||
'NVL(P.DESCRIPTION,'' '') as APPS_DESCRIPTION, ' ||
'PRIMARY_KEY_TYPE as apps_primary_key_type, ' ||
'AUDIT_KEY_FLAG as APPS_AUDIT_KEY_FLAG, ' ||
' fnd_load_util.owner_name(P.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(P.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
'from FND_TABLES T, ' ||
'FND_APPLICATION A, ' ||
'FND_PRIMARY_KEYS P ' ||
'where A.APPLICATION_ID = T.APPLICATION_ID ' ||
'and A.APPLICATION_SHORT_NAME = :1 ' ||
'and T.TABLE_NAME = :2 ' ||
'and P.TABLE_ID = T.TABLE_ID ' ||
'and P.APPLICATION_ID = T.APPLICATION_ID ' ||
'order by 1 ';
'|| l_ludate ||'
');
l_str := ' select P.PRIMARY_KEY_NAME AS APPS_PRIMARY_KEY_NAME, ' ||
' C.COLUMN_NAME AS APPS_PK_COLUMN_NAME, ' ||
' PC.PRIMARY_KEY_SEQUENCE AS APPS_PK_COLUMN_SEQUENCE, ' ||
' fnd_load_util.owner_name(PC.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(PC.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from FND_COLUMNS C, ' ||
' FND_PRIMARY_KEYS P, ' ||
' FND_PRIMARY_KEY_COLUMNS PC, ' ||
' FND_TABLES T, ' ||
' FND_APPLICATION A ' ||
'where A.APPLICATION_SHORT_NAME = :1 ' ||
'and T.APPLICATION_ID = A.APPLICATION_ID ' ||
'and T.TABLE_NAME = :2 ' ||
'and P.TABLE_ID = T.TABLE_ID ' ||
'and P.APPLICATION_ID = T.APPLICATION_ID ' ||
'and PC.APPLICATION_ID = P.APPLICATION_ID ' ||
'and PC.TABLE_ID = P.TABLE_ID ' ||
'and PC.PRIMARY_KEY_ID = P.PRIMARY_KEY_ID ' ||
'and C.APPLICATION_ID = PC.APPLICATION_ID ' ||
'and C.TABLE_ID = PC.TABLE_ID ' ||
'and C.COLUMN_ID = PC.COLUMN_ID ' ||
'order by 1 ';
'|| l_ludate ||'
');
l_str := 'select F.FOREIGN_KEY_NAME as APPS_FOREIGN_KEY_NAME, ' ||
' PA.APPLICATION_SHORT_NAME as APPS_PK_APP_SHORT_NAME, ' ||
' PT.TABLE_NAME as APPS_PK_TABLE_NAME, ' ||
' P.PRIMARY_KEY_NAME as APPS_PK_NAME, ' ||
' NVL(F.DESCRIPTION,'' '') as APPS_DESCRIPTION, ' ||
' F.CASCADE_BEHAVIOR as APPS_CASCADE_BEHAVIOR, ' ||
' F.FOREIGN_KEY_RELATION as APPS_FK_RELATION, ' ||
' F.CONDITION AS APPS_CONDITION, ' ||
' fnd_load_util.owner_name(F.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(F.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from FND_TABLES T, ' ||
' FND_APPLICATION A, ' ||
' FND_FOREIGN_KEYS F, ' ||
' FND_APPLICATION PA, ' ||
' FND_TABLES PT, ' ||
' FND_PRIMARY_KEYS P ' ||
' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
' and A.APPLICATION_SHORT_NAME = :1 ' ||
' and T.TABLE_NAME = :2 ' ||
' and F.TABLE_ID = T.TABLE_ID ' ||
' and F.APPLICATION_ID = T.APPLICATION_ID ' ||
' and F.PRIMARY_KEY_APPLICATION_ID = PA.APPLICATION_ID ' ||
' and F.PRIMARY_KEY_TABLE_ID = PT.TABLE_ID ' ||
' and F.PRIMARY_KEY_APPLICATION_ID = PT.APPLICATION_ID ' ||
' and F.PRIMARY_KEY_TABLE_ID = P.TABLE_ID ' ||
' and F.PRIMARY_KEY_APPLICATION_ID = P.APPLICATION_ID ' ||
' and F.PRIMARY_KEY_ID = P.PRIMARY_KEY_ID ' ||
' order by 1 ';
'|| l_ludate ||'
');
l_str := ' select F.FOREIGN_KEY_NAME AS APPS_FK_NAME, ' ||
' C.COLUMN_NAME as APPS_PK_COLUMN_NAME, ' ||
' FC.FOREIGN_KEY_SEQUENCE as APPS_FK_SEQUENCE, ' ||
' FC.CASCADE_VALUE as APPS_CASCADE_VALUE, ' ||
' fnd_load_util.owner_name(FC.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(FC.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from FND_COLUMNS C, ' ||
' FND_FOREIGN_KEYS F, ' ||
' FND_FOREIGN_KEY_COLUMNS FC, ' ||
' FND_TABLES T, ' ||
' FND_APPLICATION A ' ||
' where A.APPLICATION_SHORT_NAME = :1 ' ||
' and T.APPLICATION_ID = A.APPLICATION_ID ' ||
' and T.TABLE_NAME = :2 ' ||
' and F.TABLE_ID = T.TABLE_ID ' ||
' and F.APPLICATION_ID = T.APPLICATION_ID ' ||
' and FC.APPLICATION_ID = F.APPLICATION_ID ' ||
' and FC.TABLE_ID = F.TABLE_ID ' ||
' and FC.FOREIGN_KEY_ID = F.FOREIGN_KEY_ID ' ||
' and C.APPLICATION_ID = FC.APPLICATION_ID ' ||
' and C.TABLE_ID = FC.TABLE_ID ' ||
' and C.COLUMN_ID = FC.COLUMN_ID ' ||
' order by 1 ';
'|| l_ludate ||'
');
l_str := ' select C.COLUMN_NAME AS APPS_COLUMN_NAME, ' ||
' C.PARTITION AS APPS_PARTITION, ' ||
' C.HSIZE AS APPS_HSIZE, ' ||
' fnd_load_util.owner_name(C.LAST_UPDATED_BY) as APPS_OWNER, ' ||
' to_char(C.LAST_UPDATE_DATE, ''YYYY/MM/DD'') as APPS_LAST_UPDATE_DATE ' ||
' from FND_HISTOGRAM_COLS C, ' ||
' FND_TABLES T, ' ||
' FND_APPLICATION A ' ||
' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
' and A.APPLICATION_SHORT_NAME = :1 ' ||
' and T.TABLE_NAME = C.TABLE_NAME ' ||
' and T.TABLE_NAME = :2 ' ||
' and C.APPLICATION_ID = T.APPLICATION_ID ' ||
' order by 1 ';
'|| l_ludate ||'
');
l_str := 'select tablespace_type as APPS_TABLESPACE_CLASSIFICATION ' ||
' FROM FND_OBJECT_TABLESPACES T, ' ||
' FND_APPLICATION A ' ||
' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
' and A.APPLICATION_SHORT_NAME = :1 ' ||
' and object_name = :2 ';
l_str := 'select decode(TABLESPACE_TYPE,''TRANSACTION_TABLES'',''Y'',''N'') '||
' as APPS_TSPACE_TX_TABLES , ' ||
' decode(TABLESPACE_TYPE,''REFERENCE'',''Y'',''N'') '||
' as APPS_TSPACE_REFERENCE , ' ||
' decode(TABLESPACE_TYPE,''INTERFACE'',''Y'',''N'') '||
' as APPS_TSPACE_INTERFACE , ' ||
' decode(TABLESPACE_TYPE,''SUMMARY'',''Y'',''N'') '||
' as APPS_TSPACE_SUMMARY , ' ||
' decode(TABLESPACE_TYPE,''NOLOGGING'',''Y'',''N'') '||
' as APPS_TSPACE_NOLOGGING , ' ||
' decode(TABLESPACE_TYPE,''TRANSACTION_INDEXES'',''Y'',''N'') '||
' as APPS_TSPACE_TX_INDEXES , ' ||
' decode(TABLESPACE_TYPE,''ARCHIVE'',''Y'',''N'') '||
' as APPS_TSPACE_ARCHIVE ' ||
' FROM FND_OBJECT_TABLESPACES T, ' ||
' FND_APPLICATION A ' ||
' where A.APPLICATION_ID = T.APPLICATION_ID ' ||
' and A.APPLICATION_SHORT_NAME = :1 ' ||
' and object_name = :2 ';
l_str := 'select ATTR_NAME as ATTRIBUTE_NAME, ' ||
' NVL(ATTR_TYPE_OWNER,'' '') as ATTR_TYPE_OWNER, ' ||
' ATTR_TYPE_NAME as ATTR_TYPE_NAME, ' ||
' NVL(TO_CHAR(LENGTH),'' '') as LENGTH, ' ||
' NVL(TO_CHAR(PRECISION),'' '') as PRECISION, ' ||
' NVL(TO_CHAR(SCALE),'' '') as SCALE, ' ||
' INHERITED as INHERITED ' ||
' from ALL_TYPE_ATTRS where owner=:1 and type_name=:2 ';
l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
' METHOD_NO as METHOD_NO, ' ||
' METHOD_TYPE as METHOD_TYPE, ' ||
' PARAMETERS as PARAMETERS, ' ||
' RESULTS as RESULTS, ' ||
' FINAL as FINAL, ' ||
' INSTANTIABLE as INSTANTIABLE, ' ||
' OVERRIDING as OVERRIDING, ' ||
' INHERITED as INHERITED ' ||
' from ALL_TYPE_METHODS where owner=:1 and type_name=:2';
l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
' METHOD_NO as METHOD_NO, ' ||
' PARAM_NAME as PARAM_NAME, ' ||
' PARAM_NO as PARAM_NO, ' ||
' PARAM_MODE as PARAM_MODE, ' ||
' PARAM_TYPE_OWNER as PARAM_TYPE_OWNER, ' ||
' PARAM_TYPE_NAME as PARAM_TYPE_NAME ' ||
' from ALL_METHOD_PARAMS where owner=:1 and type_name=:2';
l_str := 'select METHOD_NAME as METHOD_NAME, ' ||
' METHOD_NO as METHOD_NO, ' ||
' NVL(RESULT_TYPE_OWNER,'' '') as RESULT_TYPE_OWNER, ' ||
' RESULT_TYPE_NAME as RESULT_TYPE_NAME ' ||
' from ALL_METHOD_RESULTS where owner=:1 and type_name=:2';