stored procedure query string length problem

[phokat]

Hi,
I have a stored procedure in which I query a table to build an insert string, which is of the nature...
insert into table1234 (col1, col2....col200)
select (col1, col2...col200)
from table4321
where table4321.col201 = table1234.col201

I use a procedure because the table name is passed as a variable, the proc can be called for multiple tables.

My problem is that this query is growing to about 2000+ characters and failing.

When I am trying to debug this query, I print out the length of the query in the loop as well as the query itself using the dbms_output.
As soon as the query crosses about 1000 character length, the query string is reset to an undefined variable, as if the whole query never existed!

Any workarounds ? I appreciate your help,

thank you,
preetesh

 

[sem]

First of all this table is a good candidate for redesigning. Can you provide the code you're using to create query string? Regards, Dima