SQL & PL/SQL

Hi all,

I have following proceudre,
erlier version of this procedure was without rollback segment.
Now, I have added two different rollback segments for each differnt operation.

Can anyone suggest whether my approach is correct or not?

If possible, provide solution to my queries( queries have commented below)
please ignore any syntax error in the procedure.


CREATE PROCEDURE mytest
AS

CURSOR mystudent
SELECT student_id, student_name FROM student ; -- table contains 1.5 million records

CURSOR myclass(l_student_id VARCHAR2)
SELECT class_id, student_id
FROM class
WHERE student_id = l_student_id;

BEGIN
COMMIT;
set transaction use rollback segment myrollback; -- my intension here is to open cursor in specific rollback segment
-- but I am not sure whether my approach is correct or not ?and
--what should be the size of this rollback segment with considering
--1.5 milion records in the cursor
OPEN c_main;

LOOP
FETCH c_main BULK COLLECT INTO student_type LIMIT 50000;
--I am fetching 50000 records at a time.
--will that be fine??

COMMIT;

set transaction use rollback segment myrollback_class;
--Here also what should be the size of this rollback segement??
--as expecting maximum of 1.5 million records will be inserted?

FOR lv_index in 1..student_type.COUNT
LOOP
OPEN myclass(student_type(lv_index).student_id);
FETCH myclass INTO l_class;

IF myclass%NOTFOUND
THEN
NULL;
--LOGIC TO INSERT RECORD IN THE class table.
END IF;

END LOOP;
EXIT WHEN c_main%NOTFOUND;
END LOOP;
COMMIT;
WHEN OTHERS
THEN
dbms_output.put_line(substr(sqlerrm,1,120));
END;