Trying to store multiple values into a variable using loop and insertion also using that particular variable

Viewed 114

I want to write pl/sql to store multiple values into a variable and behalf of that variable I want to insert the values which are returned in variable. But it does not work - what can I do? Please help me out. My first select statement always returns 12-13 rows which I want to store into vtm variable.

DECLARE
   vtm  NUMBER (38);

   -- vtm student.gr_number%TYPE;
   tab  apex_application_global.vc_arr2;
BEGIN
   tab := APEX_UTIL.string_to_table (vtm);

   SELECT s.gr_number
     INTO vtm
     FROM student s
          LEFT JOIN class_time ct
             ON     ct.class_id = s.class_id
                AND INSTR (s.class_time, ct.class_time) > 0
    WHERE     UPPER (TRIM (ct.class_id)) = UPPER (TRIM ( :APP_USER))
          AND s.gr_number IS NOT NULL
          AND is_active_flg = 'Y';

   DBMS_OUTPUT.put_line (vtm);

   FOR i IN vtm .. tab.COUNT
   LOOP
      BEGIN
         INSERT INTO student_class_attend (gr_number,
                                           student_id,
                                           period_next_day_flg,
                                           attend_date,
                                           period_start_dt,
                                           period_end_dt,
                                           period_duration,
                                           course_name,
                                           class_time,
                                           branch_id,
                                           shift_id,
                                           teacher_id,
                                           class_id,
                                           marked_by,
                                           course_id,
                                           class_uid)
              VALUES ( :P7_GR_NUMBER,
                      :P7_STUDENT_ID,
                      :P7_PERIOD_NEXT_DAY_FLG,
                      TO_DATE (UPPER ( :P7_ATTEND_DATE), 'DD-MON-YYYY'),
                      :P7_PERIOD_START_DT,
                      :P7_PERIOD_END_DT,
                      :P7_PERIOD_DURATION,
                      :P7_COURSE_NAME,
                      :P7_CLASS_TIME,
                      :P7_BRANCH_ID,
                      :P7_SHIFT_ID,
                      :P7_TEACHER_ID,
                      :P7_CLASS_ID,
                      :v_employee_id,
                      :P7_COURSE_ID,
                      :P7_CLASS_UID);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            DBMS_OUTPUT.put_line ('there is no data..');
      END;
   END LOOP;

   COMMIT;
END;

see gr images list which i want to store into vtm and insert all the record on one click button enter image description here

1 Answers

To cut a long story short, you're doing it in a wrong manner, overcomplicating things. Everything can be done in a single SQL statement; no need for arrays, loops ... nothing.

INSERT INTO STUDENT_CLASS_ATTEND (gr_number,
                                  student_id,
                                  PERIOD_NEXT_DAY_FLG,
                                  attend_date,
                                  period_start_dt,
                                  period_end_dt,
                                  PERIOD_DURATION,
                                  course_name,
                                  class_time,
                                  branch_id,
                                  shift_id,
                                  teacher_id,
                                  class_id,
                                  marked_by,
                                  course_id,
                                  class_uid)
   SELECT :P7_GR_NUMBER,
          :P7_STUDENT_ID,
          :P7_PERIOD_NEXT_DAY_FLG,
          TO_DATE (UPPER ( :P7_ATTEND_DATE), 'DD-MON-YYYY'),
          :P7_PERIOD_START_DT,
          :P7_PERIOD_END_DT,
          :P7_PERIOD_DURATION,
          :P7_COURSE_NAME,
          :P7_CLASS_TIME,
          :P7_BRANCH_ID,
          :P7_SHIFT_ID,
          :P7_TEACHER_ID,
          :P7_CLASS_ID,
          s.gr_number,       --> this is what you wanted to put into VTM
          :P7_COURSE_ID,
          :P7_CLASS_UID
     FROM student s
          LEFT JOIN class_time ct
             ON     ct.class_id = s.class_id
                AND INSTR (s.class_time, ct.class_time) > 0
    WHERE     UPPER (TRIM (ct.class_id)) = UPPER (TRIM ( :APP_USER))
          AND s.gr_number IS NOT NULL
          AND is_active_flg = 'Y';
Related