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?
 
sridharthirunagariConnect With a Mentor Commented:
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
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.