[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
B_BASE_PRIORITY_DELETES CONSTANT NUMBER := 100;
B_BASE_PRIORITY_UPDATES CONSTANT NUMBER := 300;
B_DELETE_WEIGHT_MODIFIER CONSTANT NUMBER := 1.01; --according to explain plan cost, delete is a little more than a one column update
TYPE b_dml_update_segment_def_type IS RECORD
(
table_owner VARCHAR2(30) := NULL,
table_name VARCHAR2(30) := NULL,
column_name VARCHAR2(30) := NULL,
new_column_value VARCHAR2(4000) := NULL,
where_clause VARCHAR2(4000) := NULL,
weight_modifier NUMBER := 1
);
TYPE b_dml_update_segments_type IS TABLE OF b_dml_update_segment_def_type INDEX BY BINARY_INTEGER;
TYPE b_dml_delete_stmt_def_type IS RECORD
(
table_owner VARCHAR2(30) := NULL,
table_name VARCHAR2(30) := NULL,
where_clause VARCHAR2(4000) := NULL,
weight NUMBER := NULL
);
TYPE b_dml_delete_stmts_type IS TABLE OF b_dml_delete_stmt_def_type INDEX BY BINARY_INTEGER;
update_map b_where_clause_column_map_type,
delete_map b_where_clause_map_type,
plsqls b_number_list_type := b_number_list_type()
);
b_dml_update_segments b_dml_update_segments_type;
b_dml_delete_stmts b_dml_delete_stmts_type;
FUNCTION VALIDATE_DML_UPDATE_SEGMENT(px_object IN OUT NOCOPY b_object_def_type,
p_segment IN b_dml_update_segment_def_type)
RETURN BOOLEAN
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_DML_UPDATE_SEGMENT';
PROCEDURE CACHE_DML_UPDATE_SEGMENT(px_object IN OUT NOCOPY b_object_def_type,
px_object_props IN OUT NOCOPY b_properties_type)
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'CACHE_DML_UPDATE_SEGMENT';
l_dml_update_segment b_dml_update_segment_def_type;
l_dml_update_segment.table_owner := l_prop.canonical_value;
l_dml_update_segment.table_name := l_prop.canonical_value;
l_dml_update_segment.column_name := l_prop.canonical_value;
l_dml_update_segment.new_column_value := l_prop.canonical_value;
l_dml_update_segment.where_clause := l_prop.canonical_value;
l_dml_update_segment.weight_modifier := FND_OAM_DSCFG_UTILS_PKG.CANONICAL_TO_NUMBER(l_prop.canonical_value);
IF VALIDATE_DML_UPDATE_SEGMENT(px_object,
l_dml_update_segment) THEN
--if it's valid, do some cleanup
l_dml_update_segment.table_owner := UPPER(TRIM(l_dml_update_segment.table_owner));
l_dml_update_segment.table_name := UPPER(TRIM(l_dml_update_segment.table_name));
IF l_dml_update_segment.where_clause IS NOT NULL THEN
CLEANUP_WHERE_CLAUSE(l_dml_update_segment.where_clause);
px_object.primary_domain := l_dml_update_segment.table_owner||'.'||l_dml_update_segment.table_name;
b_dml_update_segments(px_object.object_id) := l_dml_update_segment;
FUNCTION VALIDATE_DML_DELETE_STMT(px_object IN OUT NOCOPY b_object_def_type,
p_stmt IN b_dml_delete_stmt_def_type)
RETURN BOOLEAN
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'VALIDATE_DML_DELETE_STMT';
PROCEDURE CACHE_DML_DELETE_STMT(px_object IN OUT NOCOPY b_object_def_type,
px_object_props IN OUT NOCOPY b_properties_type)
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'CACHE_DML_DELETE_STMT';
l_dml_delete_stmt b_dml_delete_stmt_def_type;
l_dml_delete_stmt.table_owner := l_prop.canonical_value;
l_dml_delete_stmt.table_name := l_prop.canonical_value;
l_dml_delete_stmt.where_clause := l_prop.canonical_value;
l_dml_delete_stmt.weight := FND_OAM_DSCFG_UTILS_PKG.CANONICAL_TO_NUMBER(l_prop.canonical_value);
IF VALIDATE_DML_DELETE_STMT(px_object,
l_dml_delete_stmt) THEN
--if it's valid, do some cleanup
l_dml_delete_stmt.table_owner := UPPER(TRIM(l_dml_delete_stmt.table_owner));
l_dml_delete_stmt.table_name := UPPER(TRIM(l_dml_delete_stmt.table_name));
IF l_dml_delete_stmt.where_clause IS NOT NULL THEN
CLEANUP_WHERE_CLAUSE(l_dml_delete_stmt.where_clause);
px_object.primary_domain := l_dml_delete_stmt.table_owner||'.'||l_dml_delete_stmt.table_name;
b_dml_delete_stmts(px_object.object_id) := l_dml_delete_stmt;
WHEN FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_UPDATE_SEGMENT THEN
CACHE_DML_UPDATE_SEGMENT(px_object,
px_object_props);
WHEN FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_DELETE_STMT THEN
CACHE_DML_DELETE_STMT(px_object,
px_object_props);
SELECT object_id, object_type, target_type, target_id
BULK COLLECT INTO l_object_ids, l_object_types, l_target_types, l_target_ids
FROM fnd_oam_dscfg_objects
WHERE config_instance_id = p_config_instance_id
AND object_type IN (FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_UPDATE_SEGMENT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_DELETE_STMT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_TRUNCATE_STMT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_PLSQL_TEXT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_RUN,
FND_OAM_DSCFG_API_PKG.G_OTYPE_BUNDLE,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DOMAIN_METADATA)
ORDER BY object_id DESC;
SELECT property_id, parent_id, property_name, datatype, canonical_value
BULK COLLECT INTO l_prop_ids, l_prop_object_ids, l_prop_names, l_datatypes, l_canonical_values
FROM fnd_oam_dscfg_properties
WHERE parent_type = FND_OAM_DSCFG_API_PKG.G_TYPE_OBJECT
AND parent_id in (SELECT object_id
FROM fnd_oam_dscfg_objects
WHERE config_instance_id = p_config_instance_id
AND object_type IN (FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_UPDATE_SEGMENT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_DELETE_STMT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_TRUNCATE_STMT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_PLSQL_TEXT,
FND_OAM_DSCFG_API_PKG.G_OTYPE_RUN,
FND_OAM_DSCFG_API_PKG.G_OTYPE_BUNDLE,
FND_OAM_DSCFG_API_PKG.G_OTYPE_DOMAIN_METADATA))
ORDER BY parent_id DESC, property_id DESC;
l_curr_object_props.DELETE;
b_dependency_groups.DELETE(p_from_group_id);
SELECT FND_OAM_DSCRAM_RUNS_S.NEXTVAL
INTO b_run.run_id
FROM dual;
IF NOT FND_OAM_DSCRAM_UTILS_PKG.DELETE_RUN(b_run.run_id) THEN
l_msg := 'Failed to delete previously compiled run: '||b_run.run_id;
SELECT UPPER(name)
INTO l_dbname
FROM v$database
WHERE rownum < 2;
INSERT INTO FND_OAM_DSCRAM_RUNS_B (RUN_ID,
RUN_STATUS,
RUN_MODE,
TARGET_DBNAME,
CONFIG_INSTANCE_ID,
VALID_CHECK_INTERVAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(b_run.run_id,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
b_run.run_mode,
l_dbname,
p_config_instance_id,
b_run.valid_check_interval,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING run_id INTO b_run.run_id;
SELECT name, description, language
INTO l_display_name, l_description, l_language
FROM fnd_oam_dscfg_instances
WHERE config_instance_id = p_config_instance_id;
fnd_oam_debug.log(1, l_ctxt, 'Inserting runs_tl row');
INSERT INTO FND_OAM_DSCRAM_RUNS_TL (RUN_ID,
DISPLAY_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(b_run.run_id,
l_display_name||'('||b_run.run_id||')',
l_description,
l_language,
l_language,
--FND_GLOBAL.CURRENT_LANGUAGE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
SELECT UPPER(host_name)
BULK COLLECT INTO l_host_names
FROM gv$instance;
l_host_names.DELETE(j);
l_host_names.DELETE(l_host_names.FIRST);
l_host_names.DELETE(l_host_names.FIRST);
INSERT INTO FND_OAM_DSCRAM_BUNDLES (BUNDLE_ID,
RUN_ID,
BUNDLE_STATUS,
TARGET_HOSTNAME,
WORKERS_ALLOWED,
WORKERS_ASSIGNED,
BATCH_SIZE,
MIN_PARALLEL_UNIT_WEIGHT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(FND_OAM_DSCRAM_BUNDLES_S.NEXTVAL,
b_run.run_id,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
b_bundles(k).target_hostname,
b_bundles(k).workers_allowed,
0,
b_bundles(k).batch_size,
b_bundles(k).min_parallel_unit_weight,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING bundle_id INTO b_bundles(k).bundle_id;
SELECT FND_OAM_DSCRAM_UNITS_S.NEXTVAL
INTO px_unit_id
FROM dual;
INSERT INTO FND_OAM_DSCRAM_UNITS (UNIT_ID,
TASK_ID,
CONCURRENT_GROUP_UNIT_ID,
UNIT_TYPE,
UNIT_STATUS,
PHASE,
PRIORITY,
WEIGHT,
SUGGEST_WORKERS_ALLOWED,
WORKERS_ASSIGNED,
UNIT_OBJECT_OWNER,
UNIT_OBJECT_NAME,
BATCH_SIZE,
ERROR_FATALITY_LEVEL,
SUGGEST_DISABLE_SPLITTING,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(px_unit_id,
p_task_id,
p_concurrent_group_unit_id,
p_unit_type,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
p_phase,
p_priority,
p_weight,
p_workers_allowed,
0,
p_unit_object_owner,
p_unit_object_name,
p_batch_size,
p_error_fatality_level,
p_disable_splitting,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING UNIT_ID INTO l_unit_id;
SELECT FND_OAM_DSCRAM_UNITS_S.NEXTVAL
INTO l_retval
FROM DUAL;
INSERT INTO FND_OAM_DSCRAM_DMLS (DML_ID,
UNIT_ID,
PRIORITY,
WEIGHT,
DML_STMT,
DML_WHERE_CLAUSE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(FND_OAM_DSCRAM_DMLS_S.NEXTVAL,
p_unit_id,
p_priority,
p_weight,
p_stmt,
p_where_clause,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING DML_ID INTO l_dml_id;
FUNCTION ADD_ENGINE_DELETE_DML(p_unit_id IN NUMBER,
p_object_id IN NUMBER,
x_weight OUT NOCOPY NUMBER)
RETURN BOOLEAN
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_ENGINE_DELETE_DML';
l_delete_def b_dml_delete_stmt_def_type;
l_delete_def := b_dml_delete_stmts(p_object_id);
l_stmt := 'DELETE FROM '||l_delete_def.table_owner||'.'||l_delete_def.table_name;
l_weight := CEIL(NVL(l_delete_def.weight, B_DELETE_WEIGHT_MODIFIER *
FND_OAM_DSCFG_UTILS_PKG.GET_TABLE_WEIGHT(l_delete_def.table_owner,
l_delete_def.table_name)));
l_delete_def.where_clause,
p_priority => B_BASE_PRIORITY_DELETES,
p_weight => l_weight,
x_dml_id => l_dml_id);
FUNCTION ADD_ENGINE_UPDATE_DMLS(p_unit_id IN NUMBER,
p_table_owner IN VARCHAR2,
p_table_name IN VARCHAR2,
p_where_clause IN VARCHAR2,
px_column_map IN OUT NOCOPY b_column_name_map_type,
x_logical_weight OUT NOCOPY NUMBER,
x_physical_weight OUT NOCOPY NUMBER)
RETURN BOOLEAN
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'ADD_ENGINE_UPDATE_DMLS';
l_prefix := 'UPDATE '||p_table_owner||'.'||p_table_name;
l_set_snippet := l_column||'='||b_dml_update_segments(px_column_map(l_column)).new_column_value;
IF b_dml_update_segments(px_column_map(l_column)).weight_modifier IS NOT NULL THEN
l_weight := l_weight * b_dml_update_segments(px_column_map(l_column)).weight_modifier;
p_priority => B_BASE_PRIORITY_UPDATES,
p_weight => l_weight,
x_dml_id => l_dml_id);
p_priority => B_BASE_PRIORITY_UPDATES,
p_weight => l_weight,
x_dml_id => l_dml_id);
INSERT INTO FND_OAM_DSCRAM_PLSQLS (PLSQL_ID,
UNIT_ID,
PRIORITY,
WEIGHT,
PLSQL_TEXT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(FND_OAM_DSCRAM_PLSQLS_S.NEXTVAL,
p_unit_id,
p_priority,
p_weight,
p_plsql_text,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING PLSQL_ID INTO l_plsql_id;
l_has_dmls := (l_table_objects_def.update_map.COUNT > 0) OR (l_table_objects_def.delete_map.COUNT > 0);
j := l_table_objects_def.update_map.COUNT;
l_where_clause := l_table_objects_def.update_map.FIRST;
IF ADD_ENGINE_UPDATE_DMLS(l_dml_unit_id,
l_table_owner,
l_table_name,
l_where_clause,
l_table_objects_def.update_map(l_where_clause),
l_logical_weight,
l_physical_weight) THEN
INTEGRATE_WEIGHTS(l_unit_logical_weight,
l_unit_physical_weight,
l_logical_weight,
l_physical_weight);
l_where_clause := l_table_objects_def.update_map.NEXT(l_where_clause);
j := l_table_objects_def.delete_map.COUNT;
l_where_clause := l_table_objects_def.delete_map.FIRST;
IF ADD_ENGINE_DELETE_DML(l_dml_unit_id,
l_table_objects_def.delete_map(l_where_clause),
l_weight) THEN
INTEGRATE_WEIGHTS(l_unit_logical_weight,
l_unit_physical_weight,
l_weight,
l_weight);
l_where_clause := l_table_objects_def.delete_map.NEXT(l_where_clause);
l_segment b_dml_update_segment_def_type;
l_delete_stmt b_dml_delete_stmt_def_type;
WHEN FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_UPDATE_SEGMENT THEN
l_segment := b_dml_update_segments(l_object_id);
IF l_table_objects_def.update_map.EXISTS(l_where_clause) THEN
--see if the column name's been seen before
IF l_table_objects_def.update_map(l_where_clause).EXISTS(l_segment.column_name) THEN
--see if the values are the same, if not this is a conflict otherwise it's just a dupe
l_object_id2 := l_table_objects_def.update_map(l_where_clause)(l_segment.column_name);
IF (b_dml_update_segments(l_object_id2).new_column_value <> l_segment.new_column_value) THEN
MARK_OBJECT_AS_ERRORED(l_object_id,
'New Column Value conflicts with Object ID: '||l_object_id2);
l_table_objects_def.update_map(l_where_clause)(l_segment.column_name) := l_object_id;
l_column_map.DELETE;
l_table_objects_def.update_map(l_where_clause) := l_column_map;
WHEN FND_OAM_DSCFG_API_PKG.G_OTYPE_DML_DELETE_STMT THEN
l_delete_stmt := b_dml_delete_stmts(l_object_id);
l_delete_stmt.table_owner,
l_delete_stmt.table_name);
l_where_clause := NVL(l_delete_stmt.where_clause, '');
IF l_table_objects_def.delete_map.EXISTS(l_where_clause) THEN
--can't delete the same table/where clause twice, mark it as a dupe
MARK_OBJECT_WITH_WARNING(l_object_id,
l_ctxt,
'Duplicate of Object ID: '||l_table_objects_def.delete_map(l_where_clause));
l_table_objects_def.delete_map(NVL(l_where_clause, '')) := l_object_id;
l_table_bound_map(l_delete_stmt.table_owner)(l_delete_stmt.table_name) := l_table_objects_def;
IF l_table_objects_def.delete_map.EXISTS(NULL) THEN
--can't delete the same table/where clause twice, mark it as a dupe
MARK_OBJECT_WITH_WARNING(l_object_id,
l_ctxt,
'Duplicate of Object ID: '||l_table_objects_def.delete_map(NULL));
l_table_objects_def.delete_map(NULL) := l_object_id;
l_truncate_table_name_map.DELETE;
SELECT FND_OAM_DSCRAM_TASKS_S.NEXTVAL
INTO l_task_id
FROM dual;
INSERT INTO FND_OAM_DSCRAM_TASKS (TASK_ID,
BUNDLE_ID,
TASK_STATUS,
WEIGHT,
WORKERS_ASSIGNED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(l_task_id,
b_bundles(l_bundle_object_id).bundle_id,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_UNPROCESSED,
l_weight,
0,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
PROCEDURE UPDATE_RUN_BUNDLE_WEIGHTS
IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_ctxt VARCHAR2(60) := PKG_NAME||'UPDATE_RUN_BUNDLE_WEIGHTS';
UPDATE fnd_oam_dscram_bundles
SET weight = l_weight
WHERE bundle_id = b_bundles(k).bundle_id;
UPDATE fnd_oam_dscram_runs_b
SET weight = l_weight
WHERE run_id = b_run.run_id;
UPDATE fnd_oam_dscfg_objects
SET errors_found_flag = b_objects(k).new_errors_found_flag,
message = b_objects(k).new_message,
target_type = b_objects(k).target_type,
target_id = b_objects(k).target_id,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.USER_ID
WHERE object_id = b_objects(k).object_id;
b_objects.DELETE;
b_dml_update_segments.DELETE;
b_dml_delete_stmts.DELETE;
b_dml_truncate_stmts.DELETE;
b_primary_domains.DELETE;
b_domain_metadata_map.DELETE;
b_dependency_groups.DELETE;
b_domain_to_group_map.DELETE;
b_bundles.DELETE;
UPDATE_RUN_BUNDLE_WEIGHTS;
SELECT NVL(to_number(value), 1) - 1
INTO B_DEFAULT_WORKERS_ALLOWED
FROM v$system_parameter
WHERE name = 'cpu_count';