asked on

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;

jameso99

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;

ASKER CERTIFIED SOLUTION

sridharthirunagari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ericmcooper

ASKER

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.

sridharthirunagari

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;
/

jameso99

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));

ericmcooper

ASKER

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.

sridharthirunagari

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

ericmcooper

ASKER

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.

ericmcooper

ASKER

The syntax for the date was correct, but for some reason I needed to have the declare statement to make it work.