[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) into l_alertCount from FND_LOG_UNIQUE_EXCEPTIONS
where STATUS = 'N';
insert into FND_LOG_TRANSACTION_CONTEXT
(TRANSACTION_CONTEXT_ID,
SESSION_ID,
TRANSACTION_TYPE,
TRANSACTION_ID,
USER_ID,
RESP_APPL_ID,
RESPONSIBILITY_ID,
SECURITY_GROUP_ID,
COMPONENT_TYPE,
COMPONENT_APPL_ID,
COMPONENT_ID,
CREATION_DATE,
PARENT_CONTEXT_ID
) values
(FND_LOG_TRANSACTION_CTX_ID_S.nextval,
nvl(P_SESSION_ID, -1),
P_TRANSACTION_TYPE,
nvl(P_TRANSACTION_ID, -1),
nvl(P_USER_ID, -1),
nvl(P_RESP_APPL_ID, -1),
nvl(P_RESPONSIBILITY_ID, -1),
nvl(P_SECURITY_GROUP_ID, -1),
P_COMPONENT_TYPE,
nvl(P_COMPONENT_APPL_ID, -1),
nvl(P_COMPONENT_ID, -1),
sysdate,
P_PARENT_CONTEXT_ID
) RETURNING TRANSACTION_CONTEXT_ID into l_transaction_context_id;
select substrb(machine,1,60), process, program
into TXN_MACHINE, TXN_PROCESS, TXN_PROGRAM
from v$session
where audsid = TXN_SESSION;
** Inserts extended exception information into FND_LOG_EXCEPTIONS and
** posts the exception / unexpected error to the Business Event System
**
** Arguments:
** Module - Module name (See FND_LOG standards)
** Message_Id - The unique identifier of the message from
** FND_LOG_MESSAGES.Log_Sequence
*/
FUNCTION POST_EXCEPTION ( P_MODULE IN VARCHAR2,
P_LOG_SEQUENCE IN NUMBER,
P_MESSAGE_APP IN VARCHAR2 DEFAULT NULL,
P_MESSAGE_NAME IN VARCHAR2 DEFAULT NULL)
return BOOLEAN is
l_msg_text varchar2(2000);
select MESSAGE_TEXT, TRANSACTION_CONTEXT_ID
into l_enc_msg, l_txn_id
from FND_LOG_MESSAGES
where LOG_SEQUENCE = P_LOG_SEQUENCE;
select transaction_type
into l_transaction_type
from fnd_log_transaction_context
where transaction_context_id = l_txn_id;
select LANGUAGE_CODE
into l_base_lang
from FND_LANGUAGES
where INSTALLED_FLAG = 'B';
select module, action
into l_session_module, l_session_action
from v$session
where audsid = userenv('SESSIONID');
select CATEGORY, SEVERITY
into l_msg_cat, l_msg_sev
from FND_NEW_MESSAGES fnm,
FND_APPLICATION fa
where fnm.APPLICATION_ID = fa.APPLICATION_ID
and fa.APPLICATION_SHORT_NAME = l_msg_app
and fnm.MESSAGE_NAME = l_msg_name
and fnm.LANGUAGE_CODE = l_base_lang;
/* Here we need to insert the translated message text into MESSAGE_TEXT */
/* First we will save the current language, then switch our session to English, */
/* retrieve the English message text, then switch back to the original language */
select value
into l_cur_lang
from v$nls_parameters
where parameter = 'NLS_LANGUAGE';
select value
into l_cur_date_lang
from v$nls_parameters
where parameter = 'NLS_DATE_LANGUAGE';
select value
into l_cur_sort
from v$nls_parameters
where parameter = 'NLS_SORT';
/* If this is the first one, insert a new row into fnd_log_unique_exceptions */
begin
l_is_new_alert := false;
select unique_exception_id, count
into l_ex_id, l_occ_count
from fnd_log_unique_exceptions
where encoded_message = l_enc_msg
and status in ('N', 'O');
update fnd_log_unique_exceptions flue
set flue.count = flue.count + 1
where flue.unique_exception_id = l_ex_id;
select fnd_log_unique_exception_s.nextval
into l_ex_id
from dual;
insert into fnd_log_unique_exceptions (
UNIQUE_EXCEPTION_ID,
ENCODED_MESSAGE,
ENGLISH_MESSAGE,
STATUS,
COUNT,
SEVERITY,
CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values (
l_ex_id,
l_enc_msg,
l_msg_text,
'N',
1,
l_msg_sev,
l_msg_cat,
USER_ID_X,
sysdate,
USER_ID_X,
sysdate,
USER_ID_X);
insert into FND_LOG_EXCEPTIONS (
LOG_SEQUENCE,
SESSION_MODULE,
SESSION_ACTION,
UNIQUE_EXCEPTION_ID,
ACKNOWLEDGED,
MESSAGE_TEXT,
TRANSACTION_CONTEXT_ID
) values
(
P_LOG_SEQUENCE,
substrb(l_session_module,1,48),
substrb(l_session_action,1,32),
l_ex_id,
'N',
l_msg_text,
l_txn_id
);
INSERT INTO FND_LOG_MESSAGES (
ECID_ID,
ECID_SEQ,
CALLSTACK,
ERRORSTACK,
MODULE,
LOG_LEVEL,
MESSAGE_TEXT,
SESSION_ID,
USER_ID,
TIMESTAMP,
LOG_SEQUENCE
) values
(
SYS_CONTEXT('USERENV', 'ECID_ID'),
SYS_CONTEXT('USERENV', 'ECID_SEQ'),
CALL_STACK,
ERR_STACK,
SUBSTRB(MODULE,1,255),
LOG_LEVEL,
SUBSTRB(MESSAGE_TEXT, 1, 4000),
SESSION_ID_Z,
USER_ID_Z,
SYSDATE,
FND_LOG_MESSAGES_S.NEXTVAL
);
PROCEDURE DELETE_BUFFERED_TABLES is
begin
TIMESTAMP_TABLE.delete;
LOG_SEQUENCE_TABLE.delete;
MODULE_TABLE.delete;
LOG_LEVEL_TABLE.delete;
MESSAGE_TEXT_TABLE.delete;
SESSION_ID_TABLE.delete;
USER_ID_TABLE.delete;
ENCODED_TABLE.delete;
THREAD_ID_TABLE.delete;
AUDSID_TABLE.delete;
DB_INSTANCE_TABLE.delete;
TRANSACTION_CONTEXT_ID_TABLE.delete;
l_log_seq := BULK_INSERT_PVT(MODULE_TABLE,
LOG_LEVEL_TABLE,
MESSAGE_TEXT_TABLE,
SESSION_ID_TABLE,
USER_ID_TABLE,
TIMESTAMP_TABLE,
LOG_SEQUENCE_TABLE,
ENCODED_TABLE,
NODE_TABLE,
NODE_IP_ADDRESS_TABLE,
PROCESS_ID_TABLE,
JVM_ID_TABLE,
THREAD_ID_TABLE,
AUDSID_TABLE,
DB_INSTANCE_TABLE,
TRANSACTION_CONTEXT_ID_TABLE,
(G_BUFFER_POS - 1) );
DELETE_BUFFERED_TABLES;
DELETE_BUFFERED_TABLES;
select FND_LOG_MESSAGES_S.NEXTVAL
into LOG_SEQUENCE_TABLE(G_BUFFER_POS)
from dual;
INSERT INTO FND_LOG_MESSAGES (
ECID_ID,
ECID_SEQ,
CALLSTACK,
ERRORSTACK,
MODULE,
LOG_LEVEL,
MESSAGE_TEXT,
SESSION_ID,
USER_ID,
TIMESTAMP,
LOG_SEQUENCE,
ENCODED,
NODE,
NODE_IP_ADDRESS,
PROCESS_ID,
JVM_ID,
THREAD_ID,
AUDSID,
DB_INSTANCE,
TRANSACTION_CONTEXT_ID
) values
(
SYS_CONTEXT('USERENV', 'ECID_ID'),
SYS_CONTEXT('USERENV', 'ECID_SEQ'),
CALL_STACK,
ERR_STACK,
SUBSTRB(MODULE,1,255),
LOG_LEVEL,
SUBSTRB(MESSAGE_TEXT, 1, 4000),
SESSION_ID,
nvl(USER_ID, -1),
SYSDATE,
FND_LOG_MESSAGES_S.NEXTVAL,
ENCODED,
substrb(NODE,1,60),
substrb(NODE_IP_ADDRESS,1,30),
substrb(PROCESS_ID,1,120),
substrb(JVM_ID,1,120),
substrb(THREAD_ID,1,120),
AUDSID,
DB_INSTANCE,
TRANSACTION_CONTEXT_ID
) returning log_sequence into l_log_seq;
* Inserts a empty BLOB for the P_LOG_SEQUENCE
*/
PROCEDURE INSERT_BLOB(P_LOG_SEQUENCE IN NUMBER, PCHARSET IN VARCHAR2,
PMIMETYPE IN VARCHAR2, PENCODING IN VARCHAR2, PLANG IN VARCHAR2,
PFILE_EXTN IN VARCHAR2, PDESC IN VARCHAR2) is
pragma AUTONOMOUS_TRANSACTION;
INSERT INTO FND_LOG_ATTACHMENTS fla
(
LOG_SEQUENCE,
CHARSET,
MIMETYPE,
ENCODING,
LANGUAGE,
FILE_EXTN,
DESCRIPTION,
CONTENT
) values
(
P_LOG_SEQUENCE,
PCHARSET,
PMIMETYPE,
PENCODING,
PLANG,
PFILE_EXTN,
PDESC,
EMPTY_BLOB()
);
select content
into LOG_BLOB
from FND_LOG_ATTACHMENTS fla
where fla.log_sequence = P_LOG_SEQUENCE for UPDATE;
select flm.log_sequence
into l_log_sequence
from fnd_log_messages flm
where flm.log_sequence = P_LOG_SEQUENCE;
INSERT_BLOB(P_LOG_SEQUENCE, P_CHARSET, P_MIMETYPE, P_ENCODING, P_LANG, P_FILE_EXTN, P_DESC);
select content
into LOG_BLOB
from FND_LOG_ATTACHMENTS fla
where fla.log_sequence = P_LOG_SEQUENCE for UPDATE;
insert into FND_LOG_METRICS
(MODULE,
METRIC_CODE,
METRIC_SEQUENCE,
TYPE,
STRING_VALUE,
NUMBER_VALUE,
DATE_VALUE,
TIME,
EVENT_KEY,
TRANSACTION_CONTEXT_ID,
SESSION_MODULE,
SESSION_ACTION,
NODE,
NODE_IP_ADDRESS,
PROCESS_ID,
JVM_ID,
THREAD_ID,
AUDSID,
DB_INSTANCE
) values
(SUBSTRB(MODULE,1,255),
METRIC_CODE,
METRIC_SEQUENCE,
TYPE,
STRING_VALUE,
NUMBER_VALUE,
DATE_VALUE,
SYSDATE,
null,
TRANSACTION_CONTEXT_ID,
substrb(SESSION_MODULE,1,48),
substrb(SESSION_ACTION,1,32),
substrb(NODE,1,60),
substrb(NODE_IP_ADDRESS,1,30),
substrb(PROCESS_ID,1,120),
substrb(JVM_ID,1,120),
substrb(THREAD_ID,1,120),
AUDSID,
DB_INSTANCE
);
select SYSDATE
into FND_LOG_REPOSITORY.G_METRIC_DATE
from dual;
select SYSDATE
into FND_LOG_REPOSITORY.G_METRIC_DATE
from dual;
select module, action
into l_context(11).a_val, l_context(12).a_val
from v$session
where audsid = TXN_SESSION;
select FND_LOG_METRICS_S.NEXTVAL
into l_metric_sequence
from dual;
** session to the Business Event system and updates the pending
** metrics with the event key in an autonomous transaction. The
** metrics will be bundled in an XML message included in the
** event. The event will be named:
** "oracle.apps.fnd.system.metrics"
**
** Arguments:
** CONTEXT_ID - Context id to post metrics for
*/
PROCEDURE METRICS_EVENT_INTERNAL(CONTEXT_ID IN NUMBER) IS
l_event_key number;
select count(1)
into cnt
from FND_LOG_METRICS
where TRANSACTION_CONTEXT_ID = CONTEXT_ID;
select FND_METRICS_EVENT_KEY_S.nextval
into l_event_key
from dual;
update FND_LOG_METRICS
set EVENT_KEY = l_event_key
where EVENT_KEY is NULL
and TRANSACTION_CONTEXT_ID = CONTEXT_ID;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'SERVICE'
and transaction_id = concurrent_process_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'REQUEST'
and transaction_id = conc_request_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'FORM'
and transaction_id = l_transaction_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'SOA_INSTANCE'
and transaction_id = soa_instance_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'ICX'
and transaction_id = icx_sec.g_transaction_id
and session_id = icx_sec.g_session_id
and user_id = to_number(l_context(2).a_val)
and resp_appl_id = to_number(l_context(3).a_val)
and responsibility_id = to_number(l_context(4).a_val)
and security_group_id = to_number(l_context(5).a_val)
and rownum = 1; -- there maybe previous duplicate rows
select function_id
into l_component_id
from icx_transactions
where transaction_id = icx_sec.g_transaction_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'ICX'
and session_id = icx_sec.g_session_id
and transaction_id = -1
and user_id = to_number(l_context(2).a_val)
and resp_appl_id = to_number(l_context(3).a_val)
and responsibility_id = to_number(l_context(4).a_val)
and security_group_id = to_number(l_context(5).a_val)
and rownum = 1; -- there maybe previous duplicate rows
select function_id
into l_component_id
from icx_sessions
where session_id = icx_sec.g_session_id;
select transaction_context_id
into l_transaction_context_id
from fnd_log_transaction_context
where transaction_type = 'UNKNOWN'
and session_id = -1
and transaction_id = -1
and user_id = to_number(l_context(2).a_val)
and resp_appl_id = to_number(l_context(3).a_val)
and responsibility_id = to_number(l_context(4).a_val)
and security_group_id = to_number(l_context(5).a_val)
and rownum = 1; -- there maybe previous duplicate rows
* the context and sequence) and periodically flushes by calling BULK_INSERT_PVT().
*/
PROCEDURE GET_BULK_CONTEXT_PVT (
LOG_SEQUENCE_OUT OUT NOCOPY NUMBER,
TIMESTAMP_OUT OUT NOCOPY DATE,
DBSESSIONID_OUT OUT NOCOPY NUMBER,
DBINSTANCE_OUT OUT NOCOPY NUMBER,
TXN_ID_OUT OUT NOCOPY NUMBER
) is
l_context CONTEXT_ARRAY;
select FND_LOG_MESSAGES_S.NEXTVAL
into LOG_SEQUENCE_OUT
from dual;
FUNCTION BULK_INSERT_PVT(MODULE_IN IN FND_TABLE_OF_VARCHAR2_255,
LOG_LEVEL_IN IN FND_TABLE_OF_NUMBER,
MESSAGE_TEXT_IN IN FND_TABLE_OF_VARCHAR2_4000,
SESSION_ID_IN IN FND_TABLE_OF_NUMBER,
USER_ID_IN IN FND_TABLE_OF_NUMBER,
TIMESTAMP_IN IN FND_TABLE_OF_DATE,
LOG_SEQUENCE_IN IN FND_TABLE_OF_NUMBER,
ENCODED_IN IN FND_TABLE_OF_VARCHAR2_1,
NODE_IN IN varchar2,
NODE_IP_ADDRESS_IN IN varchar2,
PROCESS_ID_IN IN varchar2,
JVM_ID_IN IN varchar2,
THREAD_ID_IN IN FND_TABLE_OF_VARCHAR2_120,
AUDSID_IN IN FND_TABLE_OF_NUMBER,
DB_INSTANCE_IN IN FND_TABLE_OF_NUMBER,
TRANSACTION_CONTEXT_ID_IN IN FND_TABLE_OF_NUMBER,
SIZE_IN IN NUMBER) RETURN NUMBER is
pragma AUTONOMOUS_TRANSACTION;
INSERT INTO FND_LOG_MESSAGES (
MODULE,
LOG_LEVEL,
MESSAGE_TEXT,
SESSION_ID,
USER_ID,
TIMESTAMP,
LOG_SEQUENCE,
ENCODED,
NODE,
NODE_IP_ADDRESS,
PROCESS_ID,
JVM_ID,
THREAD_ID,
AUDSID,
DB_INSTANCE,
TRANSACTION_CONTEXT_ID
) values
(
MODULE_IN(i),
LOG_LEVEL_IN(i),
MESSAGE_TEXT_IN(i),
SESSION_ID_IN(i),
nvl(USER_ID_IN(i), -1),
nvl(TIMESTAMP_IN(i), sysdate),
nvl(LOG_SEQUENCE_IN(i), FND_LOG_MESSAGES_S.NEXTVAL),
ENCODED_IN(i),
l_node,
l_node_ip_address,
l_process_id,
l_jvm_id,
substrb(THREAD_ID_IN(i),1,120),
AUDSID_IN(i),
DB_INSTANCE_IN(i),
TRANSACTION_CONTEXT_ID_IN(i)
);
end BULK_INSERT_PVT;
select fcr.requested_by, fcr.responsibility_application_id,
fcr.responsibility_id, fcr.security_group_id,
'CONCURRENT_PROGRAM', fcr.program_application_id,
fcr.concurrent_program_id
into
p_context_array(CCI_USER_ID).a_val,
p_context_array(CCI_RESP_APPL_ID).a_val,
p_context_array(CCI_RESPONSIBILITY_ID).a_val,
p_context_array(CCI_SECURITY_GROUP_ID).a_val,
p_context_array(CCI_COMPONENT_TYPE).a_val,
p_context_array(CCI_COMPONENT_APPL_ID).a_val,
p_context_array(CCI_COMPONENT_ID).a_val
from fnd_concurrent_requests fcr
where fcr.request_id = p_request_id;
select fcr.requested_by, fcr.oracle_session_id, fcr.os_process_id,
gv.module, gv.action, '-1'
into
p_context_array(CCI_USER_ID).a_val,
p_context_array(CCI_AUDSID).a_val,
p_context_array(CCI_PROCESS_ID).a_val,
p_context_array(CCI_SESSION_MODULE).a_val,
p_context_array(CCI_SESSION_ACTION).a_val,
p_context_array(CCI_SESSION_ID).a_val
from fnd_concurrent_requests fcr,
gv$session gv
where fcr.request_id = p_request_id
and fcr.oracle_session_id = gv.audsid (+);
select transaction_context_id into G_PRX_CHILD_TRANS_CONTEXT_ID
from fnd_log_transaction_context
where transaction_id = p_transaction_id
and transaction_type = p_transaction_type
and parent_context_id = p_parent_context_id;