how can i loop through by month 1

[sql99]

Hello everyone,

I have the following program:

DECLARE
v_start_date DATE;
v_end_date DATE;
BEGIN
v_begin_date := '&BEGIN_DATE';
v_end_date := '&END_DATE';
--If parameters entered are not NULL then start insert
IF (v_begin_date IS NOT NULL) AND (v_end_date IS NOT NULL)
THEN
--
INSERT INTO student
SELECT *
FROM student_temp
WHERE admission_dt BETWEEN v_begin_date AND v_end_date;
END;
--
/


Since I have millions of records in the student_temp table, I get the following error upon doing insert - ORA-1628: max # extents 5000 reached for rollback segment

Can you please assist me with the loop I need to use to loop through each month based on the start date (parameter) and commiting each time instead of inserting in one big chunk?

Thanks for any help you can provide...

Jacky

 

[SantaMufasa]

SQL,

Here is code that processes the STUDENT_TEMP table, by admission_dt month, with COMMITs between months. The way I have it written, you need to itemize your STUDENT columns for the INSERT.

Even if you process by month, is that enough to guarantee that your rollback segment is large enough? You may wish to create a rollback segment as:
"CREATE ROLLBACK SEGMENT RBSx TABLESPACE RBS STORAGE (initial 5m next 5m maxextents unlimited);

...then...
COMMIT;
set transaction use rollback segment RBSx;
<execute your script here>

That gives you 32GB for your rollback segment just in case.

Here is your code:

accept begin_date prompt &quot;Enter the earliest admin date to process (DD-MON-YY): &quot;
accept end_date prompt &quot;Enter the last admin date to process (DD-MON-YY): &quot;
DECLARE
  v_begin_date     DATE;
  v_end_date       DATE;
BEGIN
  v_begin_date := '&BEGIN_DATE';
  v_end_date   := '&END_DATE';
--If parameters entered are not NULL then start insert
  IF (v_begin_date IS NOT NULL) AND (v_end_date IS NOT NULL) THEN
	for i in 1..12 loop
		for r in (SELECT * FROM student_temp
			  WHERE	admission_dt BETWEEN v_begin_date AND v_end_date
			    and to_char(admission_dt,'MM')=i) loop
			insert into student values
				(r.last_name, r.first_name, r.admission_dt);
		end loop;
		commit;
	end loop;
   end if;
END;
--
/

Let me know if this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:56 (15Dec03) GMT, 14:56 (15Dec03) Mountain Time)

 

[SantaMufasa]

SQL,

In my post, above, I had intended to make the INITIAL 1M and NEXT 1M, which total 32GB (versus the 5M each that I listed). Sorry.

Also, in your original code, you declare "v_start_date", but always refer to it as "v_begin_date", so you'll want to fix the declaration.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:05 (15Dec03) GMT, 15:05 (15Dec03) Mountain Time)

 

[sql99]

Hi Dave,

Thanks for all your replies. I should of been more clear in my first post but what I wanted was for it to loop per month starting with the v_begin_date parameter. For example the v_begin_date = 03/01/2001 and v_end_date = 04/15/2003, I would like for it to loop through any admission_dt that fall between 3/1/2001 and 4/1/2001 and the second loop from 4/2/2001 to 5/2/2001, etc....I know that I need to use the ADD_MONTHS function to get one month from the initial date but I'm not sure how the general syntax for the loop works....

Are there any functions out there to automatically calculate and loop per month based on a begin and end parameter value? Or would we have to manually set some variable to force it?

I hope I didn't confuse you...Your previous post was very helpful and very creative however there are many admission dates that fall within the same month and that may still cause problems with the max extents problem that I am experiencing....

Thanks!

Jacky