[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure UpdateAttrValues(WEB_HOST in varchar2,
DOMAIN in varchar2,
WEB_PORT in varchar2,
SID in varchar2,
URL_PROTO in varchar2 )
is
source_web_agent varchar2(2000);
select text
into source_web_agent
from wf_resources
where name = 'WF_WEB_AGENT'
and language = 'US';
update WF_ITEM_ATTRIBUTE_VALUES wiav
set wiav.text_value =
replace(wiav.text_value,source_web_agent,target_web_agent)
where (wiav.item_type, wiav.name) =
(select wia.item_type, wia.name
from WF_ITEM_ATTRIBUTES wia
where wia.type = 'URL'
and wia.item_type = wiav.item_type
and wia.name = wiav.name)
and wiav.text_value is not null
and instr(wiav.text_value,source_web_agent) > 0 ;
update WF_ITEM_ATTRIBUTES
set text_default = replace(text_default,source_web_agent,target_web_agent)
where type ='URL'
and text_default is not null
and instr(text_default,source_web_agent) > 0 ;
update wf_activity_attributes
set text_default = replace(text_default,source_web_agent,target_web_agent)
where type ='URL'
and text_default is not null
and instr(text_default,source_web_agent)> 0;
update wf_activity_attr_values waav
set waav.text_value = replace(waav.text_value,source_web_agent,target_web_agent)
where (waav.process_activity_id,waav.name) =(
select wpa.instance_id ,waa.name
from wf_process_activities wpa,wf_activity_attributes waa
where waa.activity_item_type = wpa.activity_item_type
and waa.activity_name = wpa.activity_name
and wpa.instance_id = waav.process_activity_id
and waa.name = waav.name
and waa.activity_version = wpa.process_version
and waa.type = 'URL')
and waav.text_value is not null
and waav.value_type = 'CONSTANT'
and instr(text_value,source_web_agent) > 0;
update wf_notification_attributes
set TEXT_VALUE = replace(text_value,source_web_agent,target_web_agent)
where instr(text_value,source_web_agent)> 0;
update wf_message_attributes
set text_default = replace(text_default,source_web_agent,target_web_agent)
where type='URL'
and value_type = 'CONSTANT'
and text_default is not null
and instr(text_default,source_web_agent) > 0;
raise_application_error(-20000, 'Error : WF_CLONE.UpdateAttrValues -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
procedure UpdateSysGuid as
source_guid raw(16);
savepoint wf_update_guid;
select text
into source_guid
from wf_resources
where name = 'WF_SYSTEM_GUID'
and language = 'US';
update wf_resources
set text = target_guid
where name = 'WF_SYSTEM_GUID';
select name
into source_name
from wf_systems
where guid = source_guid;
select global_name
into target_name
from global_name;
update wf_agents
set address = substr(address,1,instr(address,'@',1))||target_name
where address = substr(address,1,instr(address,'@',1))||source_name;
update wf_agents
set system_guid = target_guid
where system_guid = source_guid;
update wf_event_subscriptions
set SYSTEM_GUID = target_guid
where SYSTEM_GUID = source_guid;
update wf_systems
set name = target_name
where name = source_name;
update wf_systems
set guid = target_guid
where guid = source_guid;
rollback to wf_update_guid;
raise_application_error(-20000, 'Error : WF_CLONE.UpdateSysGuid -: Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
procedure UpdateMailer(WEB_HOST in varchar2,
DOMAIN in varchar2,
WEB_PORT in varchar2,
SID in varchar2,
URL_PROTO in varchar2 )
is
source_web_agent varchar2(2000);
select text
into source_web_agent
from wf_resources
where name = 'WF_WEB_AGENT'
and language = 'US';
update wf_mailer_parameters
set VALUE = l_url_proto||'://'||WEB_HOST||'.'||DOMAIN||':'||WEB_PORT||'/pls/'||SID
where parameter = 'HTMLAGENT';
update wf_mailer_parameters
set VALUE = substr(VALUE,1,instr(VALUE,'@'))||DOMAIN
where parameter = 'REPLYTO';
raise_application_error(-20000, 'Error : WF_CLONE.UpdateMailer -: Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
procedure UpdateResource(WEB_HOST in varchar2,
DOMAIN in varchar2,
WEB_PORT in varchar2,
SID in varchar2,
URL_PROTO in varchar2 )
is
target_web_agent varchar2(2000);
update wf_resources
set text = target_web_agent
where name = 'WF_WEB_AGENT';
raise_application_error(-20000, 'Error : WF_CLONE.UpdateResource -:Oracle Error = '||to_char(sqlcode)||' -'||sqlerrm);
select text
into source_agent
from wf_resources
where name = 'WF_WEB_AGENT'
and language = 'US';
wf_clone.UpdateAttrValues(p_web_host,p_domain, p_web_port,p_sid,p_url_proto);
wf_clone.UpdateSysGuid;
wf_clone.UpdateMailer(p_web_host, p_domain, p_web_port,p_sid,p_url_proto );
wf_clone.UpdateResource(p_web_host,p_domain, p_web_port,p_sid,p_url_proto );
select queue_name , name
from wf_agents
where type ='AGENT';
select que.queue_table , que.owner
into l_queue_tab , l_owner
from all_queues que
where que.name = QTableTruncate.QName ;
select qtab.RECIPIENTS ,qtab.queue_table
into l_consumer , l_qTable
from dba_queue_tables qtab , dba_queues aq
where aq.name = QDequeue.Qname
and aq.owner = QDequeue.owner
and qtab.queue_table = aq.queue_table
and qtab.owner = aq.owner ;
select sys_context('USERENV', 'CURRENT_SCHEMA')
into l_dequeue_options.consumer_name
from sys.dual;
l_sql := 'select msgid from '||QDequeue.owner||'.'||l_qTable||' where q_name ='||''''||QDequeue.Qname||''''||' and state=1';