[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_owner_sql := 'select user from dual';
SELECT 1
FROM ALL_TABLES
WHERE table_name = p_table_name
AND owner = g_owner_name;
SELECT column_name, data_type, data_length, data_precision, data_scale
FROM ALL_TAB_COLUMNS
WHERE table_name = c_table_name
AND owner = g_owner_name
ORDER BY column_id;
/* Build the SELECT statement to be executed */
FOR indx IN 1 .. l_column_count
LOOP
IF indx = 1
THEN
l_column_list := l_column_name(indx);
l_SQL_stmt := 'SELECT ' || l_column_list;
l_update_stmt VARCHAR2(10000);
l_insert_stmt VARCHAR2(10000);
l_update_clause VARCHAR2(10000);
l_insert_clause VARCHAR2(10000);
l_update_count INTEGER;
/* Build the Update and Insert Statements and Parse them */
l_update_clause := 'UPDATE ' || l_table_name || ' SET ';
l_insert_clause := 'INSERT INTO ' || l_table_name ||'(';
l_update_clause := l_update_clause || ' ' || l_file_column_name(indx)
|| '=:' || l_file_column_name(indx) || ',';
l_insert_clause := l_insert_clause || l_file_column_name(indx) || ',';
l_update_clause := SUBSTR(l_update_clause,1,LENGTH(l_update_clause)-1);
l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
DBMS_SQL.PARSE(cur_err_upd,l_update_stmt,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
/* Update the record into table */
FOR indx IN 1 .. l_count
LOOP
IF is_string(l_col_type_by_name(l_file_column_name(indx)))
THEN
l_string_value := l_file_column_value(indx);
l_update_count := DBMS_SQL.EXECUTE(cur_err_upd);
/* Insert the record into table if Update fails */
IF l_update_count = 0
THEN
FOR indx IN 1 .. l_count
LOOP
IF is_string(l_col_type_by_name(l_file_column_name(indx)))
THEN
l_string_value := l_file_column_value(indx);
l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
l_insert_stmt VARCHAR2(10000);
l_insert_clause VARCHAR2(10000);
l_update_count INTEGER;
/* Build the Insert Statement and Parse them */
l_insert_clause := 'INSERT INTO ' || l_table_name ||'(';
l_insert_clause := l_insert_clause || l_file_column_name(indx) || ',';
l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
DBMS_SQL.PARSE(cur_err_ins,l_insert_stmt,DBMS_SQL.NATIVE);
/* Insert the record into table */
FOR indx IN 1 .. l_count
LOOP
IF is_string(l_col_type_by_name(l_file_column_name(indx)))
THEN
l_string_value := l_file_column_value(indx);
l_update_count := DBMS_SQL.EXECUTE(cur_err_ins);
l_select_stmt VARCHAR2(10000);
l_update_stmt VARCHAR2(10000);
l_insert_stmt VARCHAR2(10000);
l_update_clause VARCHAR2(10000);
l_insert_clause VARCHAR2(10000);
l_delete_stmt VARCHAR2(1000);
l_update_count INTEGER;
/* Build the SELECT statement to be executed */
FOR indx IN 1 .. l_src_column_count
LOOP
IF indx = 1
THEN
l_src_column_list := l_src_column_name(indx);
l_select_stmt := 'SELECT ' || l_src_column_list;
l_select_stmt := l_select_stmt || ' FROM ' || l_src_table_name;
l_select_stmt := l_select_stmt || l_where_clause;
/* Build the Update and Insert Statements and Parse them */
l_update_clause := 'UPDATE ' || l_tgt_table_name || ' SET ';
l_insert_clause := 'INSERT INTO ' || l_tgt_table_name ||'(';
l_update_clause := l_update_clause || ' ' || l_tgt_column_name(indx)
|| '=:' || l_tgt_column_name(indx) || ',';
l_insert_clause := l_insert_clause || l_tgt_column_name(indx) || ',';
l_update_clause := SUBSTR(l_update_clause,1,LENGTH(l_update_clause)-1);
l_insert_clause := SUBSTR(l_insert_clause,1,LENGTH(l_insert_clause)-1);
l_update_stmt := l_update_clause || ' WHERE REC_ID=:REC_ID';
l_insert_stmt := l_insert_clause || ') ' || l_values_clause || ')';
DBMS_SQL.PARSE(cur_tgt_upd,l_update_stmt,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(cur_tgt_ins,l_insert_stmt,DBMS_SQL.NATIVE);
DBMS_SQL.PARSE(cur_src,l_select_stmt,DBMS_SQL.NATIVE);
l_update_count := DBMS_SQL.EXECUTE(cur_tgt_upd);
/* Insert the record into table if Update fails */
IF l_update_count = 0
THEN
l_update_count := DBMS_SQL.EXECUTE(cur_tgt_ins);
/* Delete Records from Target table for records marked with "ACTION_FLAG = 'D'" in Source Error table */
l_delete_stmt := 'DELETE FROM ' || l_tgt_table_name || ' WHERE REC_ID IN (SELECT REC_ID FROM ' || l_src_table_name;
l_delete_stmt := l_delete_stmt || ' WHERE ACTION_FLAG = ''D'' ' || l_rest_where_clause || ')';
EXECUTE IMMEDIATE l_delete_stmt;
/* Delete Transfered Records (i.e. ACTION_FLAG = 'Y') as well as records marked with "ACTION_FLAG = 'D'"
from Source Error table */
l_delete_stmt := 'DELETE FROM ' || l_src_table_name || ' WHERE ACTION_FLAG IN (''Y'',''D'') ' || l_rest_where_clause;
EXECUTE IMMEDIATE l_delete_stmt;
SELECT ddr_u_mv_rfrsh_seq.nextval INTO v_seq FROM DUAL;
INSERT INTO ddr_u_mv_rfrsh_log(refresh_job_id
,refresh_sequence
,mv_name
,refresh_method
,error_message
,refreshed_by
,start_date
,end_date)
VALUES(p_job_id
,v_seq
,p_list
,p_method
,NULL
,p_refreshed_by
,SYSDATE
,NULL);
UPDATE ddr_u_mv_rfrsh_log
SET end_date = SYSDATE
WHERE refresh_job_id = p_job_id
AND refresh_sequence = v_seq
AND mv_name = p_list;
UPDATE ddr_u_mv_rfrsh_log
SET end_date = SYSDATE,
error_message = x_message
WHERE refresh_job_id = p_job_id
AND refresh_sequence = v_seq
AND mv_name = p_list;
SELECT ddr_u_mv_rfrsh_seq.nextval INTO v_seq FROM DUAL;
INSERT INTO ddr_u_mv_rfrsh_log(refresh_job_id
,refresh_sequence
,mv_name
,refresh_method
,error_message
,refreshed_by
,start_date
,end_date)
VALUES(p_job_id
,v_seq
,p_mv_log_name
,'TRUNCATE'
,p_refreshed_by
,NULL
,SYSDATE
,NULL);
UPDATE ddr_u_mv_rfrsh_log
SET end_date = SYSDATE
WHERE refresh_job_id = p_job_id
AND refresh_sequence = v_seq
AND mv_name = p_mv_log_name;
UPDATE ddr_u_mv_rfrsh_log
SET end_date = SYSDATE,
error_message = x_message
WHERE refresh_job_id = p_job_id
AND refresh_sequence = v_seq
AND mv_name = p_mv_log_name;
SELECT dep.name, dep.referenced_name
FROM dba_dependencies dep
WHERE 1=1
AND ((dep.referenced_name<> p_exclude_1 OR p_exclude_1 IS NULL) AND
(dep.referenced_name <> p_exclude_2 OR p_exclude_2 IS NULL) AND
(dep.referenced_name <> p_exclude_3 OR p_exclude_3 IS NULL)
)
AND ((dep.referenced_name NOT LIKE p_exclude_like_1 OR p_exclude_like_1 IS NULL)
AND
(dep.referenced_name NOT LIKE p_exclude_like_2 OR p_exclude_like_2 IS NULL)
AND
(dep.referenced_name NOT LIKE p_exclude_like_3 OR p_exclude_like_3 IS NULL)
)
AND ((dep.name LIKE p_include_like_1 AND p_mv_name IS NULL) OR (p_include_like_1 IS NULL AND
dep.name = p_mv_name)
)
AND dep.type = 'MATERIALIZED VIEW'
AND dep.referenced_type = 'MATERIALIZED VIEW'
AND dep.owner = v_schema;