[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_column
(
table_name IN VARCHAR2
, new_col_name IN VARCHAR2
, old_col_name IN VARCHAR2
, new_value IN NUMBER
, old_value IN NUMBER) IS
table_not_found exception;
EXECUTE IMMEDIATE 'SELECT ' ||old_col_name ||' from '|| table_name
|| ' WHERE ' || old_col_name || ' = :y '
|| ' FOR UPDATE ' USING old_value ;
EXECUTE IMMEDIATE 'UPDATE ' || table_name
|| ' SET ' || new_col_name || ' = :x '
|| ' WHERE ' || old_col_name || ' = :y ' USING
new_value, old_value;
raise_application_error (-20000, sqlerrm || '. Could not update column.') ;
END UPDATE_COLUMN;
v_old_seq_stmt:='SELECT '||schema_name||'.'||old_sequence_name||'.NEXTVAL from dual';
v_old_seq_stmt:='SELECT '||schema_name||'.'||old_sequence_name||'.NEXTVAL from dual';
v_new_seq_stmt:='SELECT '||schema_name||'.'||sequence_name||'.NEXTVAL from dual';
SELECT column_name
FROM all_tab_columns
WHERE table_name = old_table_name
and owner=(Select user from dual);
l_delete_string VARCHAR2 (200);
l_delete_string := 'DELETE FROM ' || new_table_name;
EXECUTE IMMEDIATE l_delete_string;
l_string := ' INSERT INTO ' || new_table_name
|| ' SELECT ' || l_string || ' FROM ' || old_table_name ;
PROCEDURE delete_fnd_lookups( lookupType IN VARCHAR2
,lookupCode IN VARCHAR2
)
IS
CURSOR csr_installed_langs IS
SELECT distinct language FROM fnd_lookup_values
WHERE lookup_type=lookupType;
l_string VARCHAR2(20) := 'DELETE_FND_LOOKUPS';
delete_fnd_lookups( lookupType,lookupCode, lang);
PROCEDURE delete_fnd_lookups( lookupType IN VARCHAR2
, lookupCode IN VARCHAR2
, lang IN VARCHAR2)
IS
l_string VARCHAR2(25) := 'DELETE_FND_LOOKUPS-LANG';
DELETE fnd_lookup_values
WHERE lookup_type=lookupType
AND language = lang;
DELETE FND_LOOKUPS
WHERE lookup_type=lookupType;
DELETE fnd_lookup_values
WHERE lookup_type=lookupType
AND lookup_code = lookupCode
AND language = lang;
DELETE FND_LOOKUPS
WHERE lookup_type=lookupType
AND lookup_code = lookupCode;
update cct_middleware_values
set name='MW_SERVER_IPADDRESS'
where name='OT_SERVER_IPADDRESS'
or name='OPENTEL_SERVER_NAME';
update cct_middleware_values
set name='MW_SERVER_INFO_1'
where name='OT_SERVER_INFO1'
or name='OT_SERVER_INFO_1';
update cct_middleware_values
set name='MW_SERVER_INFO_2'
where name='OT_SERVER_INFO2'
or name='OT_SERVER_INFO_2';
update cct_middleware_values
set name='MW_SERVER_INFO_3'
where name='OT_SERVER_INFO3'
or name='OT_SERVER_INFO_3';
update cct_middleware_values
set name='MW_SERVER_INFO_4'
where name='OT_SERVER_INFO4'
or name='OT_SERVER_INFO_4';
update cct_middleware_values
set name='MW_SERVER_INFO_5'
where name='OT_SERVER_INFO5'
or name='OT_SERVER_INFO_5';
delete cct_middleware_values
where name='CTI_ENABLER_NAME'
or name='LOCAL_CALL_DATA_FILE';
Procedure delete_prospect_aspect
IS
Cursor c_mw
is
Select a.middleware_id
from cct_middlewares a,cct_middleware_types b
where a.middleware_type_id=b.middleware_type_id
and b.middleware_type='PROSPECT_ASPECT';
delete cct_middleware_values
where middleware_id=v_mw.middleware_id;
delete jtf_rs_ResourcE_values
where value_type=v_mw.middleware_id;
delete cct_middlewares
where middleware_id=v_mw.middleware_id;
delete cct_middleware_params
where middleware_type_id=(Select middleware_Type_id
from cct_middleware_types
where middleware_type='PROSPECT_ASPECT');
delete jtf_rs_resource_params
where param_type=(Select middleware_Type_id
from cct_middleware_types
where middleware_type='PROSPECT_ASPECT');
delete cct_middleware_types
where middleware_type='PROSPECT_ASPECT';
Select a.middleware_value_id,a.name,b.middleware_param_id
from cct_middleware_values a, cct_middleware_params b,
cct_middlewares c
where a.name=b.name
and a.middleware_param_id is null
and a.middleware_id=c.middleware_id
and c.middleware_type_id=b.middleware_type_id;
update cct_middleware_values
set middleware_param_id=v_mvp.middleware_param_id
where middleware_value_id=v_mvp.middleware_value_id;
Procedure update_agparam_in_agvalues(p_old_type IN VARCHAR2,p_old_param_name IN VARCHAR2,p_new_type IN VARCHAR2,p_new_param_name IN VARCHAR2)
IS
Cursor c_value
is
Select resource_param_value_id
from jtf_rs_resource_values v,jtf_rs_resource_params p,cct_middleware_types t
where t.middleware_type=p_old_type
and p.param_type=t.middleware_type_id
and p.name=p_old_param_name
and v.resource_param_id=p.resource_param_id;
Select resourcE_param_id
into l_new_param_id
from jtf_rs_resource_params p,cct_middleware_types t
where t.middleware_type=p_new_type
and p.param_type=t.middleware_type_id
and p.name=p_new_param_name;
update jtf_rs_resource_values
set resourcE_param_id=l_new_param_id
where resource_param_value_id=v_valueID.resource_param_value_id;
Procedure update_mwparam_in_mwvalues(p_old_type IN VARCHAR2,p_old_param_name IN VARCHAR2,p_new_type IN VARCHAR2,p_new_param_name IN VARCHAR2)
IS
Cursor c_value
is
Select middleware_value_id
from cct_middleware_values v,cct_middleware_params p,cct_middleware_types t
where t.middleware_type=p_old_type
and p.middleware_type_id=t.middleware_type_id
and p.name=p_old_param_name
and v.middleware_param_id=p.middleware_param_id;
Select middleware_param_id
into l_new_param_id
from cct_middleware_params p,cct_middleware_types t
where t.middleware_type=p_new_type
and p.middleware_type_id=t.middleware_type_id
and p.name=p_new_param_name;
update cct_middleware_values
set middleware_param_id=l_new_param_id
where middleware_value_id=v_mvalueID.middleware_value_id;
Procedure update_mtype_in_mware(p_middleware_id IN Number,p_old_type in VARCHAR2, p_new_type IN VARCHAR2)
IS
Cursor c_middleware
is
Select m.middleware_id
from cct_middlewares m,cct_middleware_types t
where t.middleware_type=p_old_type
and t.middleware_type_id=m.middleware_type_id
and m.middleware_id=p_middleware_id;
Select middleware_type_id
into l_new_type_id
from cct_middleware_types
where middleware_type=p_new_type;
update cct_middlewares
set middleware_Type_id=l_new_type_id
where middleware_id=p_middleware_id;
Procedure delete_middleware_value(p_middleware_type in VARCHAR2,p_param_name in VARCHAR2)
is
Cursor c_mvalue
is
Select v.middleware_value_id
from cct_middleware_values v,cct_middleware_params p,cct_middleware_types t
where t.middleware_type=p_middleware_type
and t.middleware_type_id=p.middleware_type_id
and p.name=p_param_name
and v.middleware_param_id=p.middleware_param_id;
update cct_middleware_values
set f_deletedflag='D'
where middleware_value_id=v_value.middleware_value_id;
Procedure delete_middleware_type(p_middleware_type_id in Number)
IS
Cursor c_mw
is
Select a.middleware_id
from cct_middlewares a,cct_middleware_types b
where a.middleware_type_id=b.middleware_type_id
and b.middleware_type_id=p_middleware_type_id;
delete cct_middleware_values
where middleware_id=v_mw.middleware_id;
delete jtf_rs_ResourcE_values
where value_type=v_mw.middleware_id;
delete cct_middlewares
where middleware_id=v_mw.middleware_id;
delete cct_middleware_params
where middleware_type_id=(Select middleware_Type_id
from cct_middleware_types
where middleware_type_id=p_middleware_type_id);
delete jtf_rs_resource_params
where param_type=(Select middleware_Type_id
from cct_middleware_types
where middleware_type_id=p_middleware_type_id);
delete cct_middleware_types
where middleware_type_id=p_middleware_type_id;
SELECT b.middleware_value_id, b.VALUE
FROM cct_middleware_params a, cct_middleware_values b
WHERE a.middleware_param_id = b.middleware_param_id
AND a.domain_lookup_type = 'CCT_AO_FLAG'
AND (b.f_deletedflag <> 'D' OR b.f_deletedflag IS NULL);
update cct_middleware_values
set value= decode(v_mvp.value,'Y', 'YES', 'N','NO','' )
where middleware_value_id=v_mvp.middleware_value_id;
v_old_seq_stmt:='SELECT '||schema_name||'.'||sequence_name||'.NEXTVAL from dual';
Procedure update_agent_values(p_agent_param IN VARCHAR2,p_middleware_type_id IN VARCHAR2,p_new_agent_param_id In NUMBER)
IS
Cursor c_values(p_param_id Number)
is
Select resource_param_value_id
from jtf_rs_resource_values
where resource_param_id=p_param_id;
Select resource_param_id
into l_old_param_id
from jtf_rs_resource_params
where name=p_agent_param
and param_type=p_middleware_type_id
and resource_param_id<>p_new_agent_param_id;
-- if there is an incorrect param then get all the resource values for that param and update it with the correct param id (from JTFRSPAR.ldt file)
For v_values in c_values(l_old_param_id) LOOP
--dbms_output.put_line('updating Resource Value id='||to_char(v_values.resource_param_value_id));
Update jtf_rs_ResourcE_values
set resource_param_id=p_new_agent_param_id
where resource_param_value_id=v_values.resource_param_value_id;
-- Delete the incorrect resource param
delete jtf_rs_resource_params
where resource_param_id=l_old_param_id;
--dbms_output.put_line('deleted l_old_param_id='||to_char(l_old_param_id));
raise_application_error(-20000, sqlerrm || '. Could not update agent param values') ;
Select cct_interaction_keys_s.nextval
into current_val
from dual;
Select cct_interaction_keys_s.nextval
into current_val
from dual;