pl/SQL For Loop

I'm  trying to execute a simple sql statement to insert some rows of data.  The following works fine if I execute it through Aqua Data Studio, but when I try to use Oracle Enterprise Manager I end up getting the infamous pls-00103 error.  This statement is not run inside a procedure,  package, or function.  The sole purpose of this is to initialize a table with data.  The statement preceding this would be the table create statement.

I've tried putting in a slash on the end, removing the semi-colons, taking out the begin..end.  Nothing is working.  This shouldn't be that difficult.  if anyone can point me in the right direction I'd much appreciate it.

Begin
for i in 1..32000 Loop
insert into caldates(caldate)
values(to_date('01-JAN-2001')+i);
End Loop;
End;
ericmcooperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

jameso99Commented:
try:

begin
  for _____
  loop

  begin
    insert ______
  end;
  end loop;
end

Maybe try a WHILE loop instead of a FOR?
declare i number;
begin
  i:=0;
  while i < 32000 loop
    insert _____
    i:=i+1;
  end loop;
end;
0
sridharthirunagariCommented:
Try this:

Begin
for i in 1..32000 Loop
insert into caldates(caldate)
values(to_date('01-JAN-2001','dd-mon-yyyy')+i);
End Loop;
End;
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
ericmcooperAuthor Commented:
Sorry, but neither of the above work.  The insert is fine the way I have it written. I can issue simple insert statement. The problem is when I try to put it inside a loop.  In all of the suggestions I get the same error:

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier> The symbol ";" was substituted for "end-of-file" to continue.

Its almost like I have to create a stored procedure and do this inside of that, but for a one time only initialization of data, that's a real waste considering I'd just turn right around and drop the sproc.
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!

sridharthirunagariCommented:
may be the application you are using asks for the forward slash '/' at the end of the sub-program..try this

Begin
for i in 1..32000 Loop
insert into caldates(caldate)
values(to_date('01-JAN-2001')+i);
End Loop;
End;
/
0
jameso99Commented:
It might be the way you are inserting the date's value with to_date('01-JAN-2001')+i
Maybe try

insert into caldates(caldate)
values(trunc(to_date('01-JAN-2001')+ i));

0
ericmcooperAuthor Commented:
Oh man, I can't believe it.  I had to have a declare statement first

declare i number;
begin
for i in 1..32000 Loop
insert into caldates(caldate)
values(to_date('01-JAN-2001','dd-mon-yyyy')+i);
End Loop;
end;

Once I put this in, it worked fine.
0
sridharthirunagariCommented:
but for an Oracle PL/SQL engine you do not need to initialize the parameter of the FOR loop.
I doubt that was the issue..
0
ericmcooperAuthor Commented:
That's what I thought too, but when I copied and pasted your statement into the sql query window for Enterprise manager I got the same error.  I then took what I thought was my statement and put an declare in front of it and it worked.  When I went back and double checked just a minute ago I realized that I had used your statement, so your solution was the most correct.  I still don't understand why I need the declare though.
0
ericmcooperAuthor Commented:
The syntax for the date was correct, but for some reason I needed to have the declare statement to make it work.
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.