The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_stmt := 'INSERT INTO '||p_owner||'.'||p_table_name||' (C1, C2, C3, C4, C5) VALUES (:1, :2, :3, :4, :5)';
fnd_oam_debug.log(1, l_ctxt, 'Using Serial Insert method...');
AS SELECT * FROM '||p_src_owner||'.'||p_src_table_name;
l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name;
fnd_oam_debug.log(1, l_ctxt, 'Error selecting row count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
l_stmt := 'SELECT SUM(C2) FROM '||p_owner||'.'||p_table_name;
fnd_oam_debug.log(1, l_ctxt, 'Error selecting C2 sum: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name||' WHERE C3 <> CONCAT(''ROWNAME'',to_char(round((C1/20),0)))';
fnd_oam_debug.log(1, l_ctxt, 'Error selecting C3 bad count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
l_stmt := 'SELECT COUNT(ROWID) FROM '||p_owner||'.'||p_table_name||' WHERE C4 <> :1';
fnd_oam_debug.log(1, l_ctxt, 'Error selecting C4 bad count: Error Code('||SQLCODE||'), Message: "'||SQLERRM||'"');
SELECT SUM(to_number(canonical_value))
INTO l_c2_sum
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_c2_arg_id
AND valid_value_flag = FND_API.G_TRUE;
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_c2_arg_id;
SELECT SUM(to_number(canonical_value))
INTO l_c3_bad_count
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_c3_arg_id
AND valid_value_flag = FND_API.G_TRUE;
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_c3_arg_id;
SELECT SUM(to_number(canonical_value))
INTO l_c4_bad_count
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_c4_arg_id
AND valid_value_flag = FND_API.G_TRUE;
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_c4_arg_id;
PROCEDURE DELETE_ALL_DIAGNOSTIC_RUNS(x_verdict OUT NOCOPY VARCHAR2)
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_ALL_DIAGNOSTIC_RUNS';
SELECT run_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_runs_b
WHERE run_id between 0 and 999;
IF NOT FND_OAM_DSCRAM_UTILS_PKG.DELETE_RUN(l_ids(k)) THEN
l_retbool := FALSE;
FUNCTION DELETE_GLOBAL_ARGS(p_global_arg_names IN DBMS_SQL.VARCHAR2_TABLE)
RETURN BOOLEAN
IS
l_ctxt VARCHAR2(60) := PKG_NAME||'DELETE_GLOBAL_ARGS';
DELETE FROM fnd_oam_dscram_args_b
WHERE parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_GLOBAL
AND arg_name = l_arg_name
RETURNING arg_id INTO l_arg_id;
DELETE FROM fnd_oam_dscram_args_tl
WHERE arg_id = l_arg_id;
DELETE FROM fnd_oam_dscram_arg_values
WHERE arg_id = l_arg_id;
SELECT finished_ret_sts, rows_processed
INTO l_status, l_rows_processed
FROM fnd_oam_dscram_dmls
WHERE dml_id = p_dml_id;
SELECT finished_ret_sts
INTO l_status
FROM fnd_oam_dscram_plsqls
WHERE plsql_id = p_plsql_id;
SELECT unit_status, workers_assigned
INTO l_status, l_workers_assigned
FROM fnd_oam_dscram_units
WHERE unit_id = p_unit_id;
SELECT task_status, workers_assigned
INTO l_status, l_workers_assigned
FROM fnd_oam_dscram_tasks
WHERE task_id = p_task_id;
SELECT bundle_status, workers_assigned
INTO l_status, l_workers_assigned
FROM fnd_oam_dscram_bundles
WHERE run_id = p_run_id
AND bundle_id = p_bundle_id;
SELECT run_status, last_run_stat_id
INTO l_status, l_run_stat_id
FROM fnd_oam_dscram_runs_vl
WHERE run_id = p_run_id;
SELECT dml_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_dmls
WHERE unit_id = p_unit_id;
SELECT plsql_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_plsqls
WHERE unit_id = p_unit_id;
SELECT unit_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_units
WHERE task_id = p_task_id
AND concurrent_group_unit_id IS NULL;
SELECT task_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_tasks
WHERE bundle_id = p_bundle_id;
SELECT bundle_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_bundles
WHERE run_id = p_run_id;
INSERT INTO FND_OAM_DSCRAM_ARGS_B (ARG_ID,
ARG_NAME,
PARENT_TYPE,
PARENT_ID,
ENABLED_FLAG,
INITIALIZED_SUCCESS_FLAG,
ALLOW_OVERRIDE_SOURCE_FLAG,
BINDING_ENABLED_FLAG,
PERMISSIONS,
WRITE_POLICY,
DATATYPE,
VALID_VALUE_FLAG,
CANONICAL_VALUE,
SOURCE_TYPE,
SOURCE_TEXT,
SOURCE_WHERE_CLAUSE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES
(fnd_oam_dscram_args_s.nextval,
p_arg_name,
p_parent_type,
p_parent_id,
FND_API.G_TRUE,
p_init_success_flag,
p_allow_override_source,
p_binding_enabled_flag,
p_permissions,
p_write_policy,
p_datatype,
p_valid_value_flag,
p_canon_value,
p_src_type,
p_src_text,
p_src_where_clause,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING ARG_ID INTO l_retval;
INSERT INTO FND_OAM_DSCRAM_ARGS_TL (ARG_ID,
DISPLAY_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(l_retval,
'ARG'||l_retval,
'DESC'||l_retval,
FND_GLOBAL.CURRENT_LANGUAGE,
FND_GLOBAL.CURRENT_LANGUAGE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
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_dml_stmt,
p_where_clause,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING DML_ID INTO l_retval;
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_retval;
INSERT INTO FND_OAM_DSCRAM_UNITS (UNIT_ID,
TASK_ID,
CONCURRENT_GROUP_UNIT_ID,
UNIT_TYPE,
UNIT_STATUS,
PHASE,
PRIORITY,
WEIGHT,
SUGGEST_WORKERS_ALLOWED,
ACTUAL_WORKERS_ALLOWED,
WORKERS_ASSIGNED,
UNIT_OBJECT_OWNER,
UNIT_OBJECT_NAME,
BATCH_SIZE,
ERROR_FATALITY_LEVEL,
SUGGEST_DISABLE_SPLITTING,
ACTUAL_DISABLE_SPLITTING,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(fnd_oam_dscram_units_s.nextval,
p_task_id,
p_conc_unit_id,
p_unit_type,
p_status,
p_phase,
p_priority,
p_weight,
p_sug_workers_allowed,
p_act_workers_allowed,
0,
p_unit_obj_owner,
p_unit_obj_name,
p_batch_size,
p_fatality_level,
p_sug_disable_splitting,
p_act_disable_splitting,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING UNIT_ID INTO l_retval;
INSERT INTO FND_OAM_DSCRAM_TASKS (TASK_ID,
BUNDLE_ID,
TASK_STATUS,
PRIORITY,
WEIGHT,
WORKERS_ASSIGNED,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(fnd_oam_dscram_tasks_s.nextval,
p_bundle_id,
p_status,
p_priority,
p_weight,
0,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID)
RETURNING TASK_ID INTO l_retval;
INSERT INTO FND_OAM_DSCRAM_BUNDLES (BUNDLE_ID,
RUN_ID,
BUNDLE_STATUS,
WEIGHT,
WORKERS_ALLOWED,
WORKERS_ASSIGNED,
BATCH_SIZE,
MIN_PARALLEL_UNIT_WEIGHT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(p_bundle_id,
p_run_id,
p_status,
p_weight,
p_workers_allowed,
0,
l_batch_size,
p_min_par_weight,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
SELECT 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,
WEIGHT,
VALID_CHECK_INTERVAL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(p_run_id,
p_status,
p_mode,
l_dbname,
p_weight,
p_check_interval,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
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
(p_run_id,
l_run_name,
p_desc,
FND_GLOBAL.CURRENT_LANGUAGE,
FND_GLOBAL.CURRENT_LANGUAGE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);
UPDATE fnd_oam_dscram_runs_b
SET last_run_stat_id = l_run_stat_id
WHERE run_id = p_run_id;
SELECT 1
INTO l_temp
FROM DUAL
WHERE EXISTS (SELECT 1
FROM fnd_oam_dscram_bundles
WHERE run_id = p_run_id
AND (workers_assigned > 0 OR bundle_status NOT IN (FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_PROCESSED,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_STOPPED,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_SKIPPED,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_FATAL,
FND_OAM_DSCRAM_UTILS_PKG.G_STATUS_ERROR_UNKNOWN)));
SELECT 1
INTO l_retval
FROM fnd_oam_dscram_runs_b
WHERE run_id = p_run_id;
l_retbool := DELETE_GLOBAL_ARGS(p_global_arg_names);
p_src_text => 'SELECT SYSDATE FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT to_char(SYSTIMESTAMP, ''HH24:MI:SS.FF'') FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT ROWID FROM DUAL',
x_arg_id => l_retval) THEN
RAISE INIT_FAILED;
SELECT task_id
BULK COLLECT INTO l_ids
FROM fnd_oam_dscram_tasks
WHERE bundle_id = p_bundle_id
ORDER BY task_id ASC;
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + 1',
p_where_clause => NULL,
x_dml_id => l_dml_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_arg_id) THEN
RAISE INIT_FAILED;
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_c2_final_arg_id;
SELECT SUM(to_number(canonical_value))
INTO l_c2_run_sum
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_c2_run_arg_id
AND valid_value_flag = FND_API.G_TRUE
AND rownum < 2;
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + 1',
p_where_clause => NULL,
x_dml_id => l_dml_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_arg_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_final_arg_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT FND_DATE.CANONICAL_TO_DATE('''||l_c4_val||''') FROM dual',
p_src_where_clause => NULL,
x_arg_id => l_c4_run_in_id) THEN
RAISE INIT_FAILED;
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + C1 + :DS__TEST7_C2_IN, C3 = :DS__TEST7_C3_IN, C4 = :DS__TEST7_C4_IN',
p_where_clause => NULL,
x_dml_id => l_upd_dml_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_arg_out_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT '''||l_c3_val||''' FROM DUAL',
x_arg_id => l_c3_arg_in_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT COUNT(ROWID) FROM '||l_test_table_name,
p_src_where_clause => 'C3 IS NOT NULL AND C3 <> '''||l_c3_val||'''',
x_arg_id => l_c3_arg_out_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT COUNT(ROWID) FROM '||l_test_table_name,
p_src_where_clause => 'C4 IS NOT NULL AND C4 <> FND_DATE.CANONICAL_TO_DATE('''||l_c4_val||''')',
x_arg_id => l_c4_arg_out_id) THEN
RAISE INIT_FAILED;
p_dml_stmt => 'DELETE FROM '||l_test_table_name,
p_where_clause => '(MOD(C1, 2) = 1 OR C1 > '||l_test_table_num_rows/2||')',
x_dml_id => l_del_dml_id) THEN
RAISE INIT_FAILED;
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_verdict_arg_id;
SELECT run_mode
INTO l_run_mode
FROM fnd_oam_dscram_runs_b
WHERE run_id = p_run_id;
SELECT batch_size, workers_allowed
INTO l_batch_size, l_workers_allowed
FROM fnd_oam_dscram_bundles
WHERE run_id = p_run_id
AND bundle_id = p_bundle_id;
SELECT task_id
INTO l_id
FROM fnd_oam_dscram_tasks
WHERE bundle_id = p_bundle_id
AND task_id = p_task_id;
SELECT actual_disable_splitting, actual_workers_allowed, unit_object_owner, unit_object_name, batch_size
INTO l_disable_splitting, l_workers_allowed, l_unit_object_owner, l_unit_object_name, l_batch_size
FROM fnd_oam_dscram_units
WHERE task_id = p_task_id
AND unit_id = p_unit_id;
SELECT plsql_id
INTO l_id
FROM fnd_oam_dscram_plsqls
WHERE unit_id = p_unit_id
AND plsql_id = p_plsql_id;
SELECT arg_id
INTO l_id
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_arg_id
AND parent_type = FND_OAM_DSCRAM_UTILS_PKG.G_TYPE_PLSQL
AND parent_id = p_plsql_id;
SELECT COUNT(ROWID)
INTO l_count
FROM fnd_oam_dscram_arg_values
WHERE arg_id = p_verdict_arg_id
AND (valid_value_flag IS NULL OR valid_value_flag <> FND_API.G_TRUE)
AND (canonical_value IS NULL OR canonical_value <> FND_API.G_TRUE);
SELECT valid_value_flag, canonical_value
INTO l_valid_value_flag, l_canonical_value
FROM fnd_oam_dscram_args_b
WHERE arg_id = p_c2_run_arg_id;
l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_plsql_range_arg_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_plsql_inter_arg_id) THEN
RAISE INIT_FAILED;
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = C2 + :'|| l_c2_inter_arg_name,
p_where_clause => NULL,
x_dml_id => l_dml_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_dml_range_arg_id) THEN
RAISE INIT_FAILED;
l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 7*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 11*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
l_stmt := 'SELECT COUNT(ROWID) FROM '||p_unit_object_owner||'.'||p_unit_object_name||' WHERE ROWID BETWEEN :1 AND :2';
l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 3*C2 + 1 + :1 WHERE ROWID BETWEEN :2 AND :3';
l_stmt := 'UPDATE '||p_unit_object_owner||'.'||p_unit_object_name||' SET C2 = 2*C2 + 1 WHERE ROWID BETWEEN :1 AND :2';
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = 13*C2 + 1',
p_where_clause => NULL,
x_dml_id => l_dml_id) THEN
RAISE INIT_FAILED;
p_src_text => 'SELECT SUM(C2) FROM '||l_test_table_name,
x_arg_id => l_c2_final_arg_id) THEN
RAISE INIT_FAILED;
p_dml_stmt => 'UPDATE '||l_test_table_name||' SET C2 = 5*(C2 - (SELECT COUNT(ROWID) FROM '||l_test_table_name||' WHERE ROWID BETWEEN :p_rowid_lbound AND :p_rowid_ubound)) + 1',
p_where_clause => NULL,
x_dml_id => l_dml_id) THEN
RAISE INIT_FAILED;