We help IT Professionals succeed at work.

ORACLE 10G Error PLS-00305 Previous user of ARRAY

cookiejar
cookiejar used Ask the Experts™
on
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_order 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_skill_cat_level_id      
          AND  mdm_skill_levels.mdm_skill_level_id                                            =  mtl_skill_cat_levels.mdm_skill_level_id  
          AND  skill_per_cat_vw.mtl_accreditation_id = 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_order, 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;
/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You cannot declare the same collection type twice, you have to do something like this:

 TYPE skill_cat_list_ARRAY IS TABLE OF  skill_cat_list%ROWTYPE;
 TYPE qual_cert_level_ARRAY IS TABLE OF  qual_cert_level%ROWTYPE;  
     
 l_data skill_cat_list_ARRAY;
 l_data2 skill_cat_list_ARRAY;

Then load the two above declared collections...

Author

Commented:
Can ref cursor be used as a source for bulk collection?