cookiejar
asked on
ORACLE 10G Error PLS-00305 Previous user of ARRAY
I am trying to use bulk collection for the first time to insert records.
I would like the ability to insert data into two different tables using bulk collection.
In my declaration I am trying to set up the definition for the tables that I will be inserting data into. I am a novice using PL/SQL. I did the following:
TYPE ARRAY IS TABLE OF skill_cat_list%ROWTYPE;
TYPE ARRAY IS TABLE OF qual_cert_level%ROWTYPE; -- ERROR PREVIOUS USE OF ARRAY CONFLICT WITH PREVIOUS USE
This is a snippet of the code that has not compiled correctly. Please excuse my lack of knowledge.
CREATE OR REPLACE PROCEDURE test_proc (p_skill_list IN VARCHAR2, p_log_id IN VARCHAR2, p_user_id IN VARCHAR2, p_dept IN VARCHAR2)
IS
TYPE ARRAY IS TABLE OF skill_cat_list%ROWTYPE;
TYPE ARRAY IS TABLE OF qual_cert_level%ROWTYPE; -- ERROR PREVIOUS USE OF ARRAY CONFLICT WITH PREVIOUS USE
l_data ARRAY;
em_rec := employees.emp_id%TYPE;
v_exp_susp VARCHAR2(1);
v_cnt NUMBER;
CURSOR c IS
SELECT replace(txt, '''', NULL) skill_cat_id, p_log_id
FROM (SELECT REGEXP_SUBSTR (p_skill_list, '[^,]+', 1, LEVEL) txt, p_log_id FROM DUAL
CONNECT BY LEVEL <= LENGTH (p_skill_list) - LENGTH (REPLACE (p_skill_list, ',')) + 1);
CURSOR get_people IS
SELECT DISTINCT emp_id
FROM acess_check1_vw rcw,
skill_per_cat_vw
WHERE skill_per_cat_vw.dept like '%' || p_dept || '%'
AND rcw.usr_user_id = p_user_id
AND skill_per_cat_vw.dept = rcw.dept
AND skill_per_cat_vw. mdm_skill_category_id IN (select skill_cat_id from skill_cat_list where login_name = p_log_id);
CURSOR skill_exp_susp IS
SELECT accred_name,
name_rate_rank,
title,
org_uic,
skill_per_cat_vw.sort_orde r sort_category,
code,
( select v_exp_susp from dual) exp_susp,
1 color_code,
(select p_log_id from dual) login_name
FROM skill_per_cat_vw,
mtl_skill_cat_levels,
mdm_skill_levels
WHERE skill_per_cat_vw.mtl_skill _cat_level _id = mtl_skill_cat_levels.mtl_s kill_cat_l evel_id
AND mdm_skill_levels.mdm_skill _level_id = mtl_skill_cat_levels.mdm_s kill_level _id
AND skill_per_cat_vw.mtl_accre ditation_i d = p_mtl_accreditation_id
AND emp_id = emp_rec -- EMP_REC VARIABLE- NOT SURE THIS WILL WORK
AND skill_per_cat_vw.mdm_skill _category_ id IN (select skill_cat_id from skill_cat_list where login_name = p_log_id)
GROUP BY accred_name, name_rate_rank, title, org_uic, skill_per_cat_vw.sort_orde r, code
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data;
FORALL i IN 1..l_data.COUNT
INSERT INTO skill_cat_list VALUES l_data(i);
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
-- Get people who have the selected skills
OPEN get_people;
LOOP
FETCH get_people INTO emp_rec;
-- Check to if skills or accreds have expired:
SELECT exp_susp, COUNT(exp_susp) into v_exp_susp, v_cnt FROM skill_per_cat_vw
WHERE emp_id = emp_rec
AND exp_susp IN ('E','S')
AND mdm_skill_category_id IN (select skill_cat_id from skill_cat_list where login_name = p_log_id)
GROUP by exp_susp;
IF v_cnt > 0 and v_exp_susp IN ('E','S') THEN
OPEN skill_exp_susp;
LOOP -- Second table that I attempting to add data into
FETCH skill_exp_susp BULK COLLECT INTO l_data;
FORALL i IN 1..l_data.COUNT
INSERT INTO qual_cert_level VALUES l_data(i);
COMMIT;
EXIT WHEN skill_exp_susp%NOTFOUND;
END LOOP;
CLOSE skill_exp_susp;
ELSE
null; -- Add code later
END IF;
EXIT WHEN get_people%NOTFOUND;
END LOOP;
END test_proc;
/
I would like the ability to insert data into two different tables using bulk collection.
In my declaration I am trying to set up the definition for the tables that I will be inserting data into. I am a novice using PL/SQL. I did the following:
TYPE ARRAY IS TABLE OF skill_cat_list%ROWTYPE;
TYPE ARRAY IS TABLE OF qual_cert_level%ROWTYPE; -- ERROR PREVIOUS USE OF ARRAY CONFLICT WITH PREVIOUS USE
This is a snippet of the code that has not compiled correctly. Please excuse my lack of knowledge.
CREATE OR REPLACE PROCEDURE test_proc (p_skill_list IN VARCHAR2, p_log_id IN VARCHAR2, p_user_id IN VARCHAR2, p_dept IN VARCHAR2)
IS
TYPE ARRAY IS TABLE OF skill_cat_list%ROWTYPE;
TYPE ARRAY IS TABLE OF qual_cert_level%ROWTYPE; -- ERROR PREVIOUS USE OF ARRAY CONFLICT WITH PREVIOUS USE
l_data ARRAY;
em_rec := employees.emp_id%TYPE;
v_exp_susp VARCHAR2(1);
v_cnt NUMBER;
CURSOR c IS
SELECT replace(txt, '''', NULL) skill_cat_id, p_log_id
FROM (SELECT REGEXP_SUBSTR (p_skill_list, '[^,]+', 1, LEVEL) txt, p_log_id FROM DUAL
CONNECT BY LEVEL <= LENGTH (p_skill_list) - LENGTH (REPLACE (p_skill_list, ',')) + 1);
CURSOR get_people IS
SELECT DISTINCT emp_id
FROM acess_check1_vw rcw,
skill_per_cat_vw
WHERE skill_per_cat_vw.dept like '%' || p_dept || '%'
AND rcw.usr_user_id = p_user_id
AND skill_per_cat_vw.dept = rcw.dept
AND skill_per_cat_vw. mdm_skill_category_id IN (select skill_cat_id from skill_cat_list where login_name = p_log_id);
CURSOR skill_exp_susp IS
SELECT accred_name,
name_rate_rank,
title,
org_uic,
skill_per_cat_vw.sort_orde
code,
( select v_exp_susp from dual) exp_susp,
1 color_code,
(select p_log_id from dual) login_name
FROM skill_per_cat_vw,
mtl_skill_cat_levels,
mdm_skill_levels
WHERE skill_per_cat_vw.mtl_skill
AND mdm_skill_levels.mdm_skill
AND skill_per_cat_vw.mtl_accre
AND emp_id = emp_rec -- EMP_REC VARIABLE- NOT SURE THIS WILL WORK
AND skill_per_cat_vw.mdm_skill
GROUP BY accred_name, name_rate_rank, title, org_uic, skill_per_cat_vw.sort_orde
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT INTO l_data;
FORALL i IN 1..l_data.COUNT
INSERT INTO skill_cat_list VALUES l_data(i);
COMMIT;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
-- Get people who have the selected skills
OPEN get_people;
LOOP
FETCH get_people INTO emp_rec;
-- Check to if skills or accreds have expired:
SELECT exp_susp, COUNT(exp_susp) into v_exp_susp, v_cnt FROM skill_per_cat_vw
WHERE emp_id = emp_rec
AND exp_susp IN ('E','S')
AND mdm_skill_category_id IN (select skill_cat_id from skill_cat_list where login_name = p_log_id)
GROUP by exp_susp;
IF v_cnt > 0 and v_exp_susp IN ('E','S') THEN
OPEN skill_exp_susp;
LOOP -- Second table that I attempting to add data into
FETCH skill_exp_susp BULK COLLECT INTO l_data;
FORALL i IN 1..l_data.COUNT
INSERT INTO qual_cert_level VALUES l_data(i);
COMMIT;
EXIT WHEN skill_exp_susp%NOTFOUND;
END LOOP;
CLOSE skill_exp_susp;
ELSE
null; -- Add code later
END IF;
EXIT WHEN get_people%NOTFOUND;
END LOOP;
END test_proc;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER