[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE recover_insert_rows(p_stored_rows_to_insert NUMBER) IS
BEGIN
--
-- loop through rows still to insert one at a time
--
FOR i IN 1..p_stored_rows_to_insert LOOP
--
-- Trap unique constraint errors
--
BEGIN
--
-- @@ Code specific to this view/table below
-- @@ INTRUCTION TO DEVELOPER:
-- @@ 1/ For each column in your view put a column in the insert
-- @@ statement below.
-- @@ 2/ Prefix each column in the VALUE clause with g_
-- @@ 3/ make sure (i) is at the end of each column in the value clause
--
INSERT INTO hri_mb_cmptnc_rqrmnt_ct
(row_indicator
,core_cmptnc_indicator
,essntl_cmptnc_indicator
,organization_rqrmnt_indicator
,job_rqrmnt_indicator
,grade_rqrmnt_indicator
,position_rqrmnt_indicator
,effective_start_date
,effective_end_date
,business_group_id
,organization_id
,job_id
,grade_id
,position_id
,enterprise_id
,competence_id
,competence_element_id
,low_level_id
,high_level_id
,essntl_cmptnc_flag_code
,last_change_date)
VALUES
(g_row_indicator(i)
,g_core_cmptnc_indicator(i)
,g_essntl_cmptnc_indicator(i)
,g_org_rqrmnt_indicator(i)
,g_job_rqrmnt_indicator(i)
,g_grade_rqrmnt_indicator(i)
,g_pos_rqrmnt_indicator(i)
,g_effective_start_date(i)
,g_effective_end_date(i)
,g_business_group_id(i)
,g_organization_id(i)
,g_job_id(i)
,g_grade_id(i)
,g_position_id(i)
,g_enterprise_id(i)
,g_competence_id(i)
,g_competence_element_id(i)
,g_low_level_id(i)
,g_high_level_id(i)
,g_essntl_cmptnc_flag_code(i)
,g_last_change_date(i));
output('Single insert error: ' || to_char(g_competence_element_id(i)));
END recover_insert_rows;
PROCEDURE bulk_insert_rows(p_stored_rows_to_insert NUMBER) IS
--
BEGIN
--
-- insert chunk of rows
--
-- @@ Code specific to this view/table below
-- @@ INTRUCTION TO DEVELOPER:
-- @@ 1/ For each column in your view put a column in the insert statement
-- below.
-- @@ 2/ Prefix each column in the VALUE clause with g_
-- @@ 3/ make sure (i) is at the end of each column in the value clause
--
FORALL i IN 1..p_stored_rows_to_insert
INSERT INTO hri_mb_cmptnc_rqrmnt_ct
(row_indicator
,core_cmptnc_indicator
,essntl_cmptnc_indicator
,organization_rqrmnt_indicator
,job_rqrmnt_indicator
,grade_rqrmnt_indicator
,position_rqrmnt_indicator
,effective_start_date
,effective_end_date
,business_group_id
,organization_id
,job_id
,grade_id
,position_id
,enterprise_id
,competence_id
,competence_element_id
,low_level_id
,high_level_id
,essntl_cmptnc_flag_code
,last_change_date)
VALUES
(g_row_indicator(i)
,g_core_cmptnc_indicator(i)
,g_essntl_cmptnc_indicator(i)
,g_org_rqrmnt_indicator(i)
,g_job_rqrmnt_indicator(i)
,g_grade_rqrmnt_indicator(i)
,g_pos_rqrmnt_indicator(i)
,g_effective_start_date(i)
,g_effective_end_date(i)
,g_business_group_id(i)
,g_organization_id(i)
,g_job_id(i)
,g_grade_id(i)
,g_position_id(i)
,g_enterprise_id(i)
,g_competence_id(i)
,g_competence_element_id(i)
,g_low_level_id(i)
,g_high_level_id(i)
,g_essntl_cmptnc_flag_code(i)
,g_last_change_date(i));
recover_insert_rows(p_stored_rows_to_insert);
END bulk_insert_rows;
SELECT
row_indicator
,core_cmptnc_indicator
,essntl_cmptnc_indicator
,organization_rqrmnt_indicator
,job_rqrmnt_indicator
,grade_rqrmnt_indicator
,position_rqrmnt_indicator
,effective_start_date
,effective_end_date
,business_group_id
,organization_id
,job_id
,grade_id
,position_id
,enterprise_id
,competence_id
,competence_element_id
,low_level_id
,high_level_id
,essntl_cmptnc_flag_code
,last_change_date
FROM hri_mb_cmptnc_rqrmnt_v
WHERE effective_start_date BETWEEN g_start_date AND g_end_date
OR g_start_date BETWEEN effective_start_date AND effective_end_date;
bulk_insert_rows (l_rows_fetched);