ORA-06550: PLS-00103: Encountered the symbol "end-of-file"

[LeeFarrant]

could someone give me some pointer to what is causing this error please.

Error:

ORA-06550: line 27, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

begin declare end exception exit for goto if loop mod null
pragma raise return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>


=====================================================
Code:

declare
l_stmt varchar2(20000) := null;
l_subid pls_integer;
l_id pls_integer;
l_old pls_integer;
l_count pls_integer := 0;
begin
select subid ,custid into l_subid, l_id from ( select subid ,count(custid) idd from custin group by custid order by 2 desc) old_table where rownum < 2 order by 2;
for i in 1..l_id
loop
l_stmt := l_stmt || 'subid' ||i ||' '||'varchar2(20) ,';
end loop;
l_Stmt := substr(l_stmt, 1, length(l_stmt) -1); -- remove additional comma
l_stmt := 'create table new_table ( '|| l_stmt ||' )';
execute immediate l_stmt;
begin
for c in (select * from old_table order by 2) loop
count := count + 1;
if c.custid <> l_old then l_count := 1;
if count = 1 then l_stmt := 'insert into new_table(custid,subid' || l_count ||') values('||c.custid||','||c.subid||');commit;';
else l_stmt := 'update new_table set subid'||l_count|| ' = ' || c.subid ||'where custid = '||c.custid ||';commit';
end if;
execute immediate l_stmt;
l_old := c.custid;
end if;
end loop;
=======================================================

thanks

Lee

 

[Turkbear]

Looks like you missed an END for your BEGIN...

 

[LeeFarrant]

Turkbear,

thank you for taking the time out to reply to my post.

I have placed an end; at the bottom but I get the same error message

Lee

 

[Turkbear]

Ok, check and count your

IF - End If

pairs especially those within loops, since the loop condition may affect the proper start and end ..

It is probably something easy to overlook...
I don't see any obvious ones, but....

 

[karluk]

I see two begins in your code. Maybe adding a second end will fix the problem

 

[LeeFarrant]

magic, thanks it was the second end; that did it!
thank you all again . I have some other problems with it now but will try and sort it out myself first.

thanks again

Lee

 

[LeeFarrant]

Hi, I have sorted some problems out but I am tuck on this one. I have marked line 13 with a *. The data in custin table are all integer.

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 13

==================================================

declare
l_stmt varchar2(20000) := null;
l_subid pls integer;
l_id pls_integer;
l_old pls_integer;
l_count pls_integer := 0;
custid pls_integer;

begin
select subid ,custid into l_subid, l_id from ( select subid ,count(custid) idd from custin group by custid, subid order by 2 desc) old_table where rownum < 2 order by 2;
for i in 1..l_id
loop
* l_stmt := l_stmt || 'subid' ||i ||' '||'varchar(20) ,';
end loop;
l_Stmt := substr(l_stmt, 1, length(l_stmt) -1); -- remove additional comma
l_stmt := 'create table new_table ( '|| l_stmt ||' )';
execute immediate l_stmt;
begin
for c in (select * from custin order by 2) loop
l_count := l_count + 1;
if c.custid <> l_old then l_count := 1;
if l_count = 1 then l_stmt := 'insert into new_table(custid,subid' || l_count ||') values('||c.custid||','||c.subid||');commit;';
else l_stmt := 'update new_table set subid'||l_count|| ' = ' || c.subid ||'where custid = '||c.custid ||';commit';
end if;
execute immediate l_stmt;
l_old := c.custid;
end if;
end loop;
end;
end;

 

[jee]

The i variable needs to be converted to character before it can be part of the string. Try something like:

l_stmt := l_stmt || 'subid' ||TO_CHAR(i) ||' '||'varchar(20) ,';

 

[LeeFarrant]

Jee,

thank you for your reply,

I changed the line to this

l_stmt := l_stmt || 'subid' || TO_CHAR(i) ||' '||'varchar(20) ,';

but stil get this error , strange as I thought that must be it! I am just running this code interactivaly and not in a procedure, thats ok isnt it?

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 14

 

[karluk]

One cause of ORA-06502 is trying to assign a value to a variable that is too small to hold it. Maybe your loop is executing so often that your concatenation gets so large that it overflows l_stmt.

When I execute your loop 20 times, it works. When I set it to execute 20000 times, I get the ORA-06502 error.

 

[karluk]

Incidentally, my test also indicates that using to_char(i) is unnecessary. Oracle appears to do the conversion implicitly.

 

[gnamachivayam]

Hi Lee,

I think you may try changing VARCHAR(20) to VARCHAR2(20) in the marked statement and give a try.

Ganesh

 

[gnamachivayam]

I guess this will solve

1 create or replace procedure ganesh_test
2 is
* 3 l_stmt VARCHAR2(5000);
4 begin
5 for i in 1..100
6 loop
7 -- l_stmt := i;
8 l_stmt := l_stmt || 'subid' ||i ||' '||'varchar(20) ,';
9 end loop;
10* end;
SQL> /

Procedure created.

SQL> exec ganesh_test

PL/SQL procedure successfully completed.

 

[LeeFarrant]

Hi
I have spent most of the week trying to get the whole thing working. I have got bits working eg , its created the table if I replace the I_id with an actual number in the loop.

My manager will kill me if I spend any more time on it. I am not really a programmer but usually I can pick code up and debug it but this has got the better of me. I have asked for some assistance where I work.

I would just like to say thank you to everyone who has brought me this far.

Regards

Lee