Syntax for Loop in Oracle

I want to do a loop in Oracle to cover a range of dates and run a query using each date as a parameter - something like the following.

for d in [a range of date]:
    INSERT INTO aTable SELECT * FROM anotherTable WHERE date = $d

Can anyone help me to translate the above pseudo code into valid Oracle PL/SQL?  Thanks.
thomaszhwangAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
for d in 0.. (end_date - start_date ) loop
    INSERT INTO aTable SELECT * FROM anotherTable WHERE date = start_date + d;
end loop;

but, even better would be to NOT use a loop

INSERT INTO aTable SELECT * FROM anotherTable WHERE date >= start_date and date <= end_date;
0
sdstuberCommented:
note, dates have time components

so, if you're incrementing in whole days, and your time portions might be 00:00:01 through 23:59:59 for a given day then your date values may not be equal.

if so, then you'll want something like this...


for d in 0.. ((trunc(end_date) - trunc(start_date) )) loop
    INSERT INTO aTable SELECT * FROM anotherTable WHERE date => start_date + d
and date < start_date + d + 1;
end loop;


or, using a single sql

INSERT INTO aTable SELECT * FROM anotherTable WHERE date >= trunc(start_date) and date < trunc(end_date) + 1;

note also,  >=  and < are intentionally different
0
thomaszhwangAuthor Commented:
Actually the dates in my table are whole days.  The reason why I want to do the loop is because the data for each day is so big and I don't want to have the translation log grows too quickly and too big.  I suppose the transaction log cannot truncate itself in this case, unless Oracle has some other way to manage this.

This might also cause table lock on my source table.  I said "might" is because I'm not sure if Oracle has such issue, but I know Microsoft SQL Server does and Vertica doesn't.

I will test your loop statement tomorrow and let you know how it goes.  I will leave the ticket open for now.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sdstuberCommented:
inserts generate very little undo information,  the indexes (if any) can generate more though.

however, if you don't put a commit in the loop, then all of the inserts are still inside a single transaction anyway and will actually use slightly more redo  because there are more statements to maintain, not just the data itself.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thomaszhwangAuthor Commented:
Then I will do a COMMIT after each day's INSERT. :)
0
thomaszhwangAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.