Execute immediate error

[Arun Srinivasan]

My SP :

create procedure ARUN.CREATE_TABLE(in schema_name varchar(25),
in tab_name varchar(25),in tgt_schema_name varchar(25), in
tgt_tab_name varchar(25),in tgt_ts_name varchar(25), in tgt_ix_name
varchar(25))
SPECIFIC CREATABEL
MODIFIES SQL DATA EXTERNAL ACTION DETERMINISTIC
begin
declare TXT varchar(100);
for TXT AS
select ddltext from db2admin.ddllog_3
do
begin
insert into db2admin.ddllog_2 (ddltext) values(TXT);
--execute immediate TXT;
end;
end for;
end ;

Just selecting from one table and inserting into another, all I get is
null in the target table. Does Varchar have something to do in this
issue? This is driving me mad!! Please help...

As far as datatypes are concerned , they all are varchar(100)..
the only data i have in target is the foll 2 rows .

DB2ADMIN
DB2

[Arun Srinivasan]

Yes Dave. This was a test SP for a bigger requirement. I tried out the
changed for loop construct that you have supplied.

Instead of
for <>
do
begin
end
end for;

I changed to
for <>
do begin
end for;

It gave me syntax error on creation. Apparantly , every begin needs an
end statement, so before end for, it needs an end ;
The thing that scared me was that when I used cursor logic, with loop
statement, all statements got executed fine...

declare cur1 cursor for <>
open cur1;
lp1:
loop
while <>
..
..
end loop lp1;

but when I used the above logic (with the extra end statement), all I
got in the TXT was a 'null' value. We use that method in some other
procedures and I have to check the data against their run, since the
null may affect the required data, even though they complete fine..

Arun

[Arun Srinivasan]

I found the answer. Thanks again...

Arun