[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE_BLOCK CONSTANT NUMBER := 1000;
s_rows_deleted_flm NUMBER := 0; --FND_LOG_MESSAGES;
s_rows_deleted_fen NUMBER := 0; --FND_EXCEPTION_NOTES
s_rows_deleted_fle NUMBER := 0; --FND_LOG_EXCEPTIONS
s_rows_deleted_flmt NUMBER := 0; --FND_LOG_METRICS
s_rows_deleted_flue NUMBER := 0; --FND_LOG_UNIQUE_EXCEPTIONS
s_rows_deleted_fobsn NUMBER := 0; --FND_OAM_BIZEX_SENT_NOTIF
s_rows_deleted_fltc NUMBER := 0; --FND_LOG_TRANSACTION_CONTEXT
function delete_by_user_pre1159(
X_USER_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
delete from fnd_log_messages
where rownum <= 1000
and USER_ID = X_USER_ID
and TRANSACTION_CONTEXT_ID is null;
function delete_by_session_pre1159(
X_SESSION_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
delete from fnd_log_messages
where rownum <= 1000
and SESSION_ID = X_SESSION_ID
and TRANSACTION_CONTEXT_ID is null;
function delete_by_user_session_pre1159(
X_USER_ID IN VARCHAR2 ,
X_SESSION_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
delete from fnd_log_messages
where rownum <= 1000
and USER_ID = X_USER_ID
and SESSION_ID = X_SESSION_ID
and TRANSACTION_CONTEXT_ID is null;
function delete_by_module(
X_MODULE IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
delete from fnd_log_messages
where rownum <= 1000
and module like X_MODULE
and TRANSACTION_CONTEXT_ID is null;
function delete_by_date_range_pre1159(
X_START_DATE IN DATE ,
X_END_DATE IN DATE ) return NUMBER is
rowcount number := 0;
return delete_all;
delete from fnd_log_messages
where rownum <= 1000
and timestamp <= X_END_DATE
and TRANSACTION_CONTEXT_ID is null;
delete from fnd_log_messages
where rownum <= 1000
and timestamp >= X_START_DATE
and TRANSACTION_CONTEXT_ID is null;
delete from fnd_log_messages
where rownum <= 1000
and timestamp >= X_START_DATE
and timestamp <= X_END_DATE
and TRANSACTION_CONTEXT_ID is null;
fnd_file.put_line(fnd_file.log, dbms_utility.get_time || ' delete_by_date_range_pre1159: ' ||
'Deleted rows from fnd_log_messages ' || rowcount);
/* Deletes messages at level and all levels below.*/
function delete_by_max_level(
X_LEVEL IN NUMBER) return NUMBER is
rowcount number := 0;
/* For performance just delete all if we would anyway*/
if (X_LEVEL <= 1) then
return delete_all;
delete from fnd_log_messages
where rownum <= 1000
and level <= X_LEVEL
and TRANSACTION_CONTEXT_ID is null;
function delete_all_pre1159 return NUMBER is
rowcount number := 0;
delete from fnd_log_messages
where rownum <= 1000
and TRANSACTION_CONTEXT_ID is null;
s_rows_deleted_flm := 0; --FND_LOG_MESSAGES;
s_rows_deleted_fen := 0; --FND_EXCEPTION_NOTES
s_rows_deleted_fle := 0; --FND_LOG_EXCEPTIONS
s_rows_deleted_flmt := 0; --FND_LOG_METRICS
s_rows_deleted_flue := 0; --FND_LOG_UNIQUE_EXCEPTIONS
s_rows_deleted_fobsn := 0; --FND_OAM_BIZEX_SENT_NOTIF
s_rows_deleted_fltc := 0; --FND_LOG_TRANSACTION_CONTEXT
select count(*) into l_count from FND_LOG_MESSAGES;
select count(*) into l_count from FND_EXCEPTION_NOTES;
select count(*) into l_count from FND_LOG_EXCEPTIONS;
select count(*) into l_count from FND_LOG_METRICS;
select count(*) into l_count from FND_LOG_UNIQUE_EXCEPTIONS;
select count(*) into l_count from FND_OAM_BIZEX_SENT_NOTIF;
select count(*) into l_count from FND_LOG_TRANSACTION_CONTEXT;
select count(*) into l_count from FND_LOG_ATTACHMENTS;
l_retu := 'select distinct fltc.TRANSACTION_CONTEXT_ID '
|| ' from FND_LOG_TRANSACTION_CONTEXT fltc ';
l_part2 := ' and not exists (select null from FND_CONCURRENT_REQUESTS fcr ';
l_part2 := ' and not exists (select null from FND_CONCURRENT_PROCESSES fcp ';
l_part3 := ' (select NULL from GV$SESSION where AUDSID= fltc.TRANSACTION_ID )';
|| ' (select null from ICX_TRANSACTIONS it where it.TRANSACTION_ID=fltc.TRANSACTION_ID'
|| ' and SYSDATE-1 > it.LAST_CONNECT ))';
l_part3 := ' (select it1.LAST_CONNECT from ICX_SESSIONS it1 where it1.SESSION_ID=fltc.SESSION_ID))';
l_part4 := ' or (NOT EXISTS ( SELECT null FROM ICX_SESSIONS it1 where it1.SESSION_ID=fltc.SESSION_ID)'
|| ' ))'
;
procedure upDateRetCode(pCodeExist in out NOCOPY number, pCodeNew in number)
IS
BEGIN
if (pCodeExist < pCodeNew) then
pCodeExist := pCodeNew;
END upDateRetCode;
function getDeleteBlock(pCurrentIndex in number, pTotalSize in number) return number
IS
l_retu NUMBER;
l_retu := DELETE_BLOCK;
l_retu := pCurrentIndex + DELETE_BLOCK;
END getDeleteBlock;
procedure DELETE_EXCEPTIONS_INFO(p_logSeqList in LogSeqListTyp
, pRetCode out NOCOPY number)
IS
l_table VARCHAR2(25);
fdebug('In:FND_BE_UTIL.DELETE_EXCEPTIONS_INFO rec=' || p_logSeqList.count);
l_end := getDeleteBlock(l_end, p_logSeqList.count);
delete from FND_LOG_EXCEPTIONS flem where flem.LOG_SEQUENCE=p_logSeqList(ii)
and NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle, FND_LOG_UNIQUE_EXCEPTIONS flue where
fle.LOG_SEQUENCE = flem.LOG_SEQUENCE
and flue.UNIQUE_EXCEPTION_ID = fle.UNIQUE_EXCEPTION_ID
and flue.STATUS <> 'C');
s_rows_deleted_fle := s_rows_deleted_fle + sql%rowcount; --FND_LOG_EXCEPTIONS
delete from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE=p_logSeqList(ii)
and NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle where
fle.LOG_SEQUENCE = flm.LOG_SEQUENCE
);
s_rows_deleted_flm := s_rows_deleted_flm + sql%rowcount; --FND_LOG_MESSAGES;
delete from FND_LOG_ATTACHMENTS fla where fla.LOG_SEQUENCE=p_logSeqList(ii)
and NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle where fle.LOG_SEQUENCE = p_logSeqList(ii))
and NOT EXISTS
(select null from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = p_logSeqList(ii));
fdebug('Failed in DELETE_EXCEPTIONS_INFO');
fdebug('OUT:FND_BE_UTIL.DELETE_EXCEPTIONS_INFO');
END DELETE_EXCEPTIONS_INFO;
procedure DELETE_UNIQUE_EXCEPTIONS_INFO(p_UEXList UExcIdListTyp
, pRetCode out NOCOPY number)
IS
l_table VARCHAR2(25);
fdebug('In:FND_BE_UTIL.DELETE_UNIQUE_EXCEPTIONS_INFO rec:' || p_UEXList.count);
l_end := getDeleteBlock(l_end, p_UEXList.count);
DELETE FROM FND_EXCEPTION_NOTES fen
WHERE fen.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
and NOT EXISTS
(SELECT null from FND_LOG_EXCEPTIONS fle
where fle.UNIQUE_EXCEPTION_ID = fen.UNIQUE_EXCEPTION_ID
);
s_rows_deleted_fen := s_rows_deleted_fen + sql%rowcount; --FND_EXCEPTION_NOTES
DELETE FROM FND_OAM_BIZEX_SENT_NOTIF fobsf
WHERE fobsf.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
and NOT EXISTS
(SELECT null from FND_LOG_EXCEPTIONS fle
where fle.UNIQUE_EXCEPTION_ID = fobsf.UNIQUE_EXCEPTION_ID
);
s_rows_deleted_fobsn := s_rows_deleted_fobsn + sql%rowcount; --FND_OAM_BIZEX_SENT_NOTIF
DELETE FROM FND_LOG_UNIQUE_EXCEPTIONS flue
WHERE flue.UNIQUE_EXCEPTION_ID = p_UEXList(ii)
and NOT EXISTS
(SELECT null from FND_LOG_EXCEPTIONS fle
where fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
);
s_rows_deleted_flue := s_rows_deleted_flue + sql%rowcount; --FND_LOG_UNIQUE_EXCEPTIONS
fdebug('Failed in DELETE_UNIQUE_EXCEPTIONS_INFO');
fdebug('Out:FND_BE_UTIL.DELETE_UNIQUE_EXCEPTIONS_INFO');
END DELETE_UNIQUE_EXCEPTIONS_INFO;
procedure DELETE_UNIQUEA_EXCEPTIONS_INFO(pRetCode out NOCOPY number)
IS
l_table VARCHAR2(25);
fdebug('In:FND_BE_UTIL.DELETE_UNIQUEA_EXCEPTIONS_INFO ');
select flue.UNIQUE_EXCEPTION_ID BULK COLLECT into l_UEXList
from
FND_LOG_UNIQUE_EXCEPTIONS flue
where
NOT EXISTS
(SELECT null from FND_LOG_EXCEPTIONS fle
where fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
);
DELETE_UNIQUE_EXCEPTIONS_INFO(l_UEXList,pRetCode);
fdebug('Out:FND_BE_UTIL.DELETE_UNIQUEA_EXCEPTIONS_INFO');
END DELETE_UNIQUEA_EXCEPTIONS_INFO;
procedure DELETE_EXCEPTIONS_NULL_TRID(pRetCode out NOCOPY number
,X_START_DATE IN DATE, X_END_DATE IN DATE )
IS
l_EXList LogSeqListTyp;
fdebug('In:FND_BE_UTIL.DELETE_EXCEPTIONS_NULL_TRID');
select fle.LOG_SEQUENCE BULK COLLECT into l_EXList
from
FND_LOG_EXCEPTIONS fle
,FND_LOG_UNIQUE_EXCEPTIONS flue
where
fle.UNIQUE_EXCEPTION_ID = flue.UNIQUE_EXCEPTION_ID
and fle.TRANSACTION_CONTEXT_ID is null
and flue.STATUS = 'C'
and (X_START_DATE is null or flue.CREATION_DATE >= X_START_DATE)
and (X_END_DATE is null or flue.CREATION_DATE <= X_END_DATE);
DELETE_EXCEPTIONS_INFO(l_EXList ,pRetCode);
fdebug('Out:DELETE_EXCEPTIONS_NULL_TRID');
END DELETE_EXCEPTIONS_NULL_TRID;
procedure DELETE_MESSAGES_INFO(p_logSeqList in LogSeqListTyp
, pRetCode out NOCOPY number, X_START_DATE IN DATE, X_END_DATE IN DATE)
IS
l_table VARCHAR2(25);
fdebug('In:FND_LOG_ADMIN.DELETE_MESSAGES_INFO rec=' || p_logSeqList.count);
l_end := getDeleteBlock(l_end, p_logSeqList.count);
delete from FND_LOG_MESSAGES flm where
flm.TRANSACTION_CONTEXT_ID=p_logSeqList(ii)
and (X_START_DATE is null or flm.TIMESTAMP >= X_START_DATE)
and (X_END_DATE is null or flm.TIMESTAMP <= X_END_DATE);
s_rows_deleted_fle := s_rows_deleted_fle + sql%rowcount;
fdebug('Failed in DELETE_MESSAGES_INFO');
fdebug('OUT:FND_LOG_ADMIN.DELETE_MESSAGES_INFO');
END DELETE_MESSAGES_INFO;
procedure DELETE_MESSAGES_INVALID_TRID(pRetCode out NOCOPY number
,X_START_DATE IN DATE, X_END_DATE IN DATE )
IS
l_EXList LogSeqListTyp;
fdebug('In:FND_LOG_ADMIN.DELETE_MESSAGES_INVALID_TRID');
select flm.TRANSACTION_CONTEXT_ID BULK COLLECT into l_EXList
from
FND_LOG_MESSAGES flm
where
not exists (select null from FND_LOG_TRANSACTION_CONTEXT fltc
where flm.TRANSACTION_CONTEXT_ID = fltc.TRANSACTION_CONTEXT_ID)
and (X_START_DATE is null or flm.TIMESTAMP >= X_START_DATE)
and (X_END_DATE is null or flm.TIMESTAMP <= X_END_DATE);
DELETE_MESSAGES_INFO(l_EXList ,pRetCode, X_START_DATE, X_END_DATE);
fdebug('Out:DELETE_MESSAGES_INVALID_TRID');
END DELETE_MESSAGES_INVALID_TRID;
l_end := getDeleteBlock(l_end, p_TrList.count);
delete from FND_LOG_METRICS flm where flm.TRANSACTION_CONTEXT_ID=p_TrList(ii)
and NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=flm.TRANSACTION_CONTEXT_ID);
s_rows_deleted_flmt := s_rows_deleted_flmt + sql%rowcount; --FND_LOG_METRICS
delete from FND_LOG_TRANSACTION_CONTEXT where TRANSACTION_CONTEXT_ID=p_TrList(ii)
and NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle WHERE fle.TRANSACTION_CONTEXT_ID=p_TrList(ii))
and NOT EXISTS
(select null from FND_LOG_MESSAGES flm WHERE flm.TRANSACTION_CONTEXT_ID=p_TrList(ii));
s_rows_deleted_fltc := s_rows_deleted_fltc + sql%rowcount; --FND_LOG_TRANSACTION_CONTEXT
DELETE_EXCEPTIONS_INFO(pLogSeqList, l_retCode);
upDateRetCode(pRetCode, l_retCode);
DELETE_UNIQUE_EXCEPTIONS_INFO(pUEXList, l_retCode);
upDateRetCode(pRetCode, l_retCode);
upDateRetCode(pRetCode, l_retCode);
select distinct flue.UNIQUE_EXCEPTION_ID BULK COLLECT into l_UEXList1
from
FND_LOG_EXCEPTIONS fle
, FND_LOG_UNIQUE_EXCEPTIONS flue
where
fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
and flue.UNIQUE_EXCEPTION_ID = fle.UNIQUE_EXCEPTION_ID ---
and flue.STATUS = 'C';
select LOG_SEQUENCE BULK COLLECT into l_LogSeqList1
from
(
select LOG_SEQUENCE from FND_LOG_MESSAGES flm
where flm.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
union
select LOG_SEQUENCE from FND_LOG_EXCEPTIONS fle
where fle.TRANSACTION_CONTEXT_ID = l_TrnCtxIdList(ii)
and NOT EXISTS(select null from FND_LOG_MESSAGES flm1 where flm1.LOG_SEQUENCE=fle.LOG_SEQUENCE)
);
upDateRetCode(pRetCode, l_retCode);
upDateRetCode(pRetCode, l_retCode);
function delete_by_date_range(
X_START_DATE IN DATE ,
X_END_DATE IN DATE ) return NUMBER is
rowcount number := 0;
fdebug ('In - delete_by_date_range -calling old');
s_rows_deleted_flm := delete_by_date_range_pre1159(X_START_DATE, X_END_DATE);
return delete_all;
DELETE_EXCEPTIONS_NULL_TRID(l_retCode, X_START_DATE, X_END_DATE);
delete from FND_LOG_ATTACHMENTS fla where NOT EXISTS
(select null from FND_LOG_EXCEPTIONS fle where fle.LOG_SEQUENCE = fla.LOG_SEQUENCE)
and NOT EXISTS (select null from FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = fla.LOG_SEQUENCE );
DELETE_UNIQUEA_EXCEPTIONS_INFO(l_retCode);
DELETE_MESSAGES_INVALID_TRID(l_retCode, X_START_DATE, X_END_DATE);
fdebug ('Out - delete_by_date_range');
fdebug ('in error delete_by_date_range: ' || SQLCODE);
end delete_by_date_range;
function delete_by_user(
X_USER_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
fdebug ('In - delete_by_user');
s_rows_deleted_flm := delete_by_user_pre1159(X_USER_ID);
fdebug ('Out - delete_by_user');
end delete_by_user;
function delete_by_session(
X_SESSION_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
fdebug ('In - delete_by_session');
s_rows_deleted_flm := delete_by_session_pre1159(X_SESSION_ID);
fdebug ('Out - delete_by_session');
fdebug ('in error delete_by_session: ' || SQLCODE);
end delete_by_session;
function delete_by_user_session(
X_USER_ID IN VARCHAR2,
X_SESSION_ID IN VARCHAR2 ) return NUMBER is
rowcount number := 0;
fdebug ('In - delete_by_user_session');
s_rows_deleted_flm := delete_by_user_session_pre1159(X_USER_ID, X_SESSION_ID);
fdebug ('Out - delete_by_user_session');
fdebug ('in error delete_by_user_session: ' || SQLCODE);
end delete_by_user_session;
function delete_all return NUMBER is
rowcount number := 0;
fdebug ('In - delete_all');
s_rows_deleted_flm := delete_all_pre1159;
fdebug ('Out - delete_all');
fdebug ('in error delete_all: ' || SQLCODE);
end delete_all;
procedure delete_by_date_i( errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2,
last_date in varchar2 ) is
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_BY_DATE_I';
'returning from delete_by_date with date exception. last_date='
||last_date);
/* Delete from the date back in time */
numrows := delete_by_date_range(NULL, last_dt);
'returning from delete_by_date_i. numrows='||numrows);
'returning from delete_by_date with exception. numrows='||numrows);
end delete_by_date_i;
/* Delete data from fnd_log_messages only - Used by Java UI */
function delete_by_sequence(
pLogSeqList IN VARCHAR2) return NUMBER is
begin
fdebug ('In - delete_by_Sequence');
delete from FND_LOG_MESSAGES flm
where
flm.LOG_SEQUENCE = pLogSeqList;
fdebug ('Out - delete_by_Sequence');
end delete_by_sequence;
function delete_by_seqarray(numArrayList IN FND_ARRAY_OF_NUMBER_25) return NUMBER is
ii number := 0;
fdebug ('In - delete_by_seqarray');
delete from
FND_LOG_MESSAGES flm where flm.LOG_SEQUENCE = numArrayList(ii);
fdebug ('Out - delete_by_seqarray, deleted ' || numArrayList.COUNT || ' rows');
end delete_by_seqarray;
select u.user_id
into l_user_id
from fnd_user u
where u.user_name = 'SYSADMIN';
select r.application_id,
r.responsibility_id
into l_resp_appl_id,
l_resp_id
from fnd_application a,
fnd_responsibility r
where r.application_id = a.application_id
and a.application_short_name = 'SYSADMIN'
and r.responsibility_key = 'SYSTEM_ADMINISTRATOR';
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(test_date, test_mask), 62202999);
rows := fnd_log_admin.delete_by_user(62202999);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(test_date, test_mask), 62202999);
rows := fnd_log_admin.delete_by_session(62202999);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(SYSDATE+500, test_mask), 62202999);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(SYSDATE+500, test_mask), 62202999);
rows := fnd_log_admin.delete_by_date_range(SYSDATE+499, SYSDATE+501);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(SYSDATE+500, test_mask), 62202999);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 1,
'This is a test log message', 62202999, 62202999,
to_date(test_date, test_mask), 62202999);
delete from fnd_log_messages where user_id = 62202999;
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(test_date, test_mask), 62202999);
rows := fnd_log_admin.delete_by_user_session(62202999, 62202999);
insert into fnd_log_messages
(module, log_level, message_text, session_id, user_id,
timestamp, log_sequence)
values
('fnd.src.dict.afdict.afdwarn.tom_test_module', 5,
'This is a test log message', 62202999, 62202999,
to_date(test_date, test_mask), 62202999);
rows := fnd_log_admin.delete_by_module(
'fnd.src.dict.afdict.afdwarn.tom_test_module');