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

Question

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

Littlefoot · Accepted Answer · 2020-06-04 07:21:31Z

2

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';

answered Jun 4, 2020 at 7:21

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Faizan Over a year ago

i have multiple values which is returned from this query almost 12-13 rows it return which i want to store in vtm and insert all the returned values which is getting from such query for a single value it is fine but for 12-13 values i need loop 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';

Faizan Over a year ago

bro check about image hope you will understant what is my objective click on above image description to see the fetched result which is required to insert on single click button process

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related