[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select shutdown_pending into shutdown from v$instance;
select sys_context('USERENV', 'CURRENT_SCHEMA')
into wf_queue.account_name
from sys.dual;
select aa.name,
aa.value_type, -- CONSTANT or ITEMATTR
aa.type, -- NUMBER/TEXT/DATE etc
aa.format,
av.TEXT_VALUE,
av.NUMBER_VALUE,
av.DATE_VALUE
from wf_activity_attr_values av,
wf_activity_attributes aa,
wf_activities a,
wf_process_activities pa
where pa.activity_item_type = a.item_type
and pa.activity_name = a.name
and pa.instance_id=actid
and a.begin_date< startdate and nvl(a.end_date,startdate) >= startdate
and a.item_type = aa.activity_item_type
and a.name = aa.activity_name
and a.version = aa.activity_version
and av.process_activity_id = actid
and av.name=aa.name
order by aa.sequence;
SELECT CWA.COST, CWIAS.BEGIN_DATE
into cost, l_begdate
FROM WF_ITEM_ACTIVITY_STATUSES CWIAS,
WF_PROCESS_ACTIVITIES CWPA,
WF_ITEMS WI,
WF_ACTIVITIES CWA
where CWIAS.ACTIVITY_STATUS = 'DEFERRED'
and CWIAS.PROCESS_ACTIVITY = CWPA.INSTANCE_ID
and CWPA.ACTIVITY_ITEM_TYPE = CWA.ITEM_TYPE
and CWPA.ACTIVITY_NAME = CWA.NAME
and CWIAS.ITEM_TYPE = WI.ITEM_TYPE
and CWIAS.ITEM_KEY = WI.ITEM_KEY
and WI.BEGIN_DATE >= CWA.BEGIN_DATE
and WI.BEGIN_DATE < nvl(CWA.END_DATE, WI.BEGIN_DATE+1)
and CWIAS.ITEM_TYPE = event.itemtype
and CWIAS.ITEM_KEY = event.itemkey
and CWIAS.PROCESS_ACTIVITY = event.actid;
select count(process_activity)
into l_background_occurrence
from wf_item_activity_statuses_h
where item_type = enqueue_event.itemtype
and item_key = enqueue_event.itemkey
and process_activity = enqueue_event.actid
and begin_date >= g_background_begin_date;
select ROOT_ACTIVITY into l_process
from WF_ITEMS
where ITEM_TYPE=itemtype
and ITEM_KEY=itemkey;
** Update the event to let everyone know it expired
*/
l_event.SetErrorMessage(wf_core.translate('WFE_MESSAGE_EXPIRED'));
select name into lsysname
from wf_systems
where guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'));
select wfq.protocol,
wfq.inbound_outbound,
wfq.description,
wfq.queue_count
from wf_queues wfq
where NVL(wfq.disable_flag, 'N') = 'N'
order by wfq.protocol, wfq.inbound_outbound;
wf_core.translate('DELETE')||'',
calign=>'Center',
cattributes=>'id="' || wf_core.translate('DELETE') || '"');
'/wf_queue.generic_queue_confirm_delete?p_protocol='||
queues.protocol||'&p_inbound_outbound='||
queues.inbound_outbound,
ctext=>'
'),
'center', cattributes=>'valign="MIDDLE"
headers="' || wf_core.translate('DELETE') || '"');
SELECT queue_count
INTO l_count
FROM wf_queues
WHERE UPPER(p_protocol) = protocol
AND p_inbound_outbound = inbound_outbound;
l_sql := 'SELECT COUNT(1) FROM WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE';
htp.p('
l_inbound_selected varchar2(1) := 'N';
l_outbound_selected varchar2(1) := 'N';
SELECT description,
queue_count
INTO l_description,
l_queue_count
FROM wf_queues
WHERE protocol = p_protocol
AND inbound_outbound = p_inbound_outbound;
htp.p('
l_inbound_selected := 'Y';
l_outbound_selected := NULL;
l_inbound_selected := NULL;
l_outbound_selected := 'Y';
htp.formSelectOpen(cname=>'p_inbound_outbound',cattributes=>'id="i_inbound_outbound"');
htp.formSelectOption(cvalue=>wf_core.translate('INBOUND'),
cattributes=>'value=INBOUND',
cselected=>l_inbound_selected);
htp.formSelectOption(cvalue=>wf_core.translate('OUTBOUND'),
cattributes=>'value=OUTBOUND',
cselected=>l_outbound_selected);
htp.formSelectClose;
procedure generic_queue_delete_check
(p_protocol in varchar2,
p_inbound_outbound in varchar2,
p_queue_start_range in number,
p_queue_end_range in number) IS
ii NUMBER := 0;
** you delete it.
*/
for ii in p_queue_start_range..p_queue_end_range loop
/*
** Check to see if there are any messages in the specified queue
*/
-- p_protocol and p_inbound was verified before coming here.
-- BINDVAR_SCAN_IGNORE
l_sql := 'SELECT COUNT(1) INTO :a FROM WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE';
Wf_Core.Context('Wf_Queue', 'generic_queue_delete_check',
p_protocol, p_inbound_outbound);
end generic_queue_delete_check;
procedure generic_queue_delete_queues
(p_protocol in varchar2,
p_inbound_outbound in varchar2,
p_queue_start_range in number,
p_queue_end_range in number) IS
ii NUMBER := 0;
** Delete the queues and queue tables
*/
for ii in p_queue_start_range..p_queue_end_range loop
/*
** Stop the queue
*/
dbms_aqadm.stop_queue(queue_name => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||
p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||
to_char(ii)||'_QUEUE');
** Delete the Queues
*/
dbms_aqadm.drop_queue(
queue_name => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||p_protocol||'_'||
substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_QUEUE');
** Delete the Queue Table
*/
dbms_aqadm.drop_queue_table (
queue_table => wf_core.translate('WF_SCHEMA')||'.'||'WF_'||p_protocol||'_'||substr(p_inbound_outbound, 1, 1)||'_'||to_char(ii)||'_TABLE');
Wf_Core.Context('Wf_Queue', 'generic_queue_delete_queues',
p_protocol, p_inbound_outbound);
end generic_queue_delete_queues;
procedure Generic_Queue_Update (
p_protocol IN VARCHAR2 DEFAULT NULL,
p_inbound_outbound IN VARCHAR2 DEFAULT NULL,
p_description IN VARCHAR2 DEFAULT NULL,
p_queue_count IN VARCHAR2 DEFAULT NULL,
p_original_protocol IN VARCHAR2 DEFAULT NULL,
p_original_inbound IN VARCHAR2 DEFAULT NULL
) IS
username varchar2(320); -- Username to query
SELECT count(1)
INTO l_count
FROM wf_queues
WHERE UPPER(p_protocol) = protocol
AND p_inbound_outbound = inbound_outbound;
wf_queue.update_generic_queue (p_protocol=>p_protocol,
p_inbound_outbound => p_inbound_outbound,
p_description => p_description,
p_queue_count => to_number(p_queue_count),
p_original_protocol=> p_original_protocol,
p_original_inbound=> p_original_inbound);
wf_core.context('FND_DOCUMENT_MANAGEMENT', 'Generic_Queue_update');
END Generic_Queue_Update;
select count(1)
into l_count
from wf_queues wfq
where wfq.protocol = p_protocol
and wfq.inbound_outbound = p_inbound_outbound;
insert into wf_queues
(protocol,
inbound_outbound,
description,
queue_count,
disable_flag)
values
(p_protocol,
p_inbound_outbound,
p_description,
p_queue_count,
'N');
** delete a generic queue with the object type of WF_MESSAGE_PAYLOAD_T which
** is basically just a clob
*/
procedure delete_generic_queue
(p_protocol IN VARCHAR2,
p_inbound_outbound IN VARCHAR2) IS
l_queue_count NUMBER := 0;
select queue_count
into l_queue_count
from wf_queues wfq
where wfq.protocol = p_protocol
and wfq.inbound_outbound = p_inbound_outbound;
wf_queue.generic_queue_delete_check (p_protocol, p_inbound_outbound,
1, l_queue_count);
** Delete the queues and queue tables
*/
wf_queue.generic_queue_delete_queues(p_protocol, p_inbound_outbound,
1, l_queue_count);
** delete an entry in WF_QUEUES table
*/
delete from wf_queues
where protocol = p_protocol
and inbound_outbound = p_inbound_outbound;
end delete_generic_queue;
select protocol, inbound_outbound, queue_count
from wf_queues
order by protocol, inbound_outbound;
select queue_table, dequeue_enabled
into l_queue_table, l_dequeue_enabled
from all_queues
where owner = l_schema_name
and name = l_queue_name;
SELECT name
FROM all_queues
WHERE name = queue_name
AND owner = schema
AND ((trim(enqueue_enabled) = 'NO') OR (trim(dequeue_enabled) = 'NO'));
select queue_name
from wf_agents
where system_guid = hextoraw(wf_core.translate('WF_SYSTEM_GUID'))
and name like upper(p_agent);
select owner
from all_queue_tables
where queue_table = l_qt;
select queue_table
from all_queues
where owner = l_schema
and name = l_qname;
l_sqlstmt := 'select msg_state, count(*) from '||l_owner||'.'||'aq$'||l_qt
||' where (queue = :q or queue = :r) group by msg_state';
SELECT TRIM(queue_name), TRIM(queue_handler)
INTO l_queue_name, l_queue_handler
FROM wf_agents
WHERE name = upper(p_agent_name)
AND SYSTEM_GUID = wf_event.local_system_guid;
SELECT TRIM(object_type)
INTO l_object_type
FROM all_queue_tables
WHERE queue_table in
(
SELECT queue_table
FROM all_queues
WHERE name = l_qname
AND owner = l_schema
)
AND owner=l_schema;
SELECT queue_name
INTO l_queue_name
FROM wf_agents
WHERE name = upper(p_agent_name)
AND SYSTEM_GUID = wf_event.local_system_guid;
SELECT TRIM(object_type)
INTO l_data_type
FROM all_queue_tables
WHERE queue_table in
(
SELECT queue_table
FROM all_queues
WHERE name = l_qname
AND owner = l_schema
)
AND owner=l_schema;
SELECT queue_name
INTO l_queue_name
FROM wf_agents
WHERE name = upper(p_agent_name)
AND SYSTEM_GUID = wf_event.local_system_guid;
SELECT TRIM(object_type)
INTO l_data_type
FROM all_queue_tables
WHERE queue_table in
(
SELECT queue_table
FROM all_queues
WHERE name = l_qname
AND owner = l_schema
)
AND owner=l_schema;
'SELECT msg_id FROM '
|| l_qname
|| ' WHERE msg_state = ''' || 'READY'' '
|| ' AND enq_time < (sysdate - :1) '
|| ' AND corr_id like :2 ' using p_age,l_corrid;
'SELECT msg_id FROM '
|| l_qname
|| ' WHERE msg_state = ''' || 'READY'' '
|| ' AND enq_time < (sysdate - :1) ' using p_age;