Link to home
Start Free TrialLog in
Avatar of cookiejar
cookiejarFlag for United States of America

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_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;
/
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cookiejar

ASKER

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