CREATE OR REPLACE  FUNCTION TEMP.tools_join_base 
      ( STR_FROM	VARCHAR(2000), SIMB VARCHAR(10))
        RETURNS TABLE  ( STR_RESULT	VARCHAR(32000) )
      BEGIN  
         -- STR_FROM = 'select id from TEMP.TEST'
         declare STR_EXEC   varchar(3000);
         declare STR_RESULT varchar(32000);
         -- динамическая строка 
         set STR_EXEC =  STR_FROM;
         RETURN EXECUTE IMMEDIATE STR_EXEC ;
      END;

CREATE OR REPLACE  FUNCTION TEMP.t_j
      ( STR_FROM	VARCHAR(2000))
        RETURNS VARCHAR(32000)
      BEGIN 
           DECLARE STR_RESULT VARCHAR(32000);
           DECLARE c1 CURSOR FOR s1;
           PREPARE s1 FROM STR_FROM;
           OPEN c1;
                   FETCH c1 INTO STR_RESULT;
           CLOSE c1;
           RETURN STR_RESULT;
      END;

CREATE OR REPLACE  FUNCTION TEMP.t_j_b 
      ( STR_FROM	VARCHAR(4000), SIMB VARCHAR(10))
        RETURNS TABLE  ( STR_RES	   VARCHAR(32000) )
      BEGIN ATOMIC
           DECLARE STR_EXEC   VARCHAR(32000); 
           DECLARE STR_RESULT VARCHAR(32000);
           
          set STR_EXEC = 'select ' || 'listagg(' ||'cast( '
          || substr (STR_FROM, (values(locate('select',STR_FROM)) + 6), (values(locate('from',STR_FROM)) -   (values(locate('select',STR_FROM)) + 6)) )  
          ||' as varchar(32000)) '
          || ',' || '''' ||  simb || ''''|| ') ' 
          || substr(STR_FROM, (values(locate('from',STR_FROM))), (length(STR_FROM) - (values(locate('from',STR_FROM)))) ); 
           
          set STR_RESULT =  cast((values(TEMP.t_j_b_s(STR_EXEC))) as varchar(32000));           
           RETURN 
                    --select cast(STR_RESULT as varchar(32000)) from SYSIBM.SYSDUMMY1; 
                    values(cast(STR_RESULT as varchar(32000))) ;
      END;

CREATE OR REPLACE  FUNCTION TEMP.tools_join_base_str
      ( STR_FROM	VARCHAR(2000), SIMB VARCHAR(10))
        RETURNS VARCHAR(32000)
      BEGIN 
           DECLARE STR_EXEC   VARCHAR(4000); 
           DECLARE STR_RESULT VARCHAR(32000);
           DECLARE c1 CURSOR FOR s1;
          set STR_EXEC = 'select ' || 'listagg(' ||'cast( '
          || substr (STR_FROM, (values(locate('select',STR_FROM)) + 6), (values(locate('from',STR_FROM)) -   (values(locate('select',STR_FROM)) + 6)) )  
          ||' as varchar(32000)) '
          || ',' || '''' ||  SIMB || ''''|| ') ' 
          || substr(STR_FROM, (values(locate('from',STR_FROM))), (length(STR_FROM) - (values(locate('from',STR_FROM)))) ); 
           PREPARE s1 FROM  STR_EXEC ; 
           OPEN c1;
                    FETCH c1 INTO STR_RESULT;
           CLOSE c1;
           RETURN cast(STR_RESULT as VARCHAR(32000));
      END;

select * from table(values(TEMP.tools_join_base_str  ('select id_person from form.ISUER where ID_PERSON < 2000', ',')))

CREATE OR REPLACE FUNCTION TEMP.TESTSC ()
  RETURNS INTEGER
BEGIN ATOMIC 
DECLARE STR_PER INTEGER;
SET STR_PER = (select MAX(ID_PERSON) from FORM.FUN where ID_PERSON_FUND_PARENT  <50);
RETURN STR_PER;
END;

select TEMP.TESTSC() from SYSIBM.SYSDUMMY1; -- возвращаются правильные данные


CREATE OR REPLACE FUNCTION TEMP.TESTSB ()
  RETURNS INTEGER
BEGIN  
DECLARE STR_PER INTEGER;
SET STR_PER = (select MAX(ID_PERSON) from FORM.FUN where ID_PERSON_FUND_PARENT  <50);
RETURN STR_PER;
END;

select TEMP.TESTSB() from SYSIBM.SYSDUMMY1; -- Здесь выходит ошибка

CREATE OR REPLACE FUNCTION TEMP.TESTSC ()
  RETURNS INTEGER
BEGIN ATOMIC 
DECLARE STR_PER INTEGER;
DECLARE STR_EXEC VARCHAR(4000);
--SET STR_PER = (select MAX(ID_PERSON) from FORM.FUN where ID_PERSON_FUND_PARENT  <50);
SET STR_EXEC = (select MAX(ID_PERSON) from FORM.FUN where ID_PERSON_FUND_PARENT  <50);

PREPARE s1 FROM  STR_EXEC ; 
           OPEN c1;
           FETCH c1 INTO STR_PER;
           CLOSE c1;
RETURN STR_PER;
END;

CREATE OR REPLACE FUNCTION TEMP.TESTSG ()
  RETURNS VARCHAR(32000)
BEGIN ATOMIC 
DECLARE STR_RESULT VARCHAR(32000);
DECLARE STR_EXEC   VARCHAR(32000);
-- FORM.FUN - Никнэйм
 SET STR_RESULT = cast((select MAX(ID_PERSON)  from FORM.FUN where ID_PERSON_FUND_PARENT  <50) as varchar(100)) ;
RETURN cast(STR_RESULT as VARCHAR(32000));
END;
select TEMP.TESTSС() from SYSIBM.SYSDUMMY1; -- работает

CREATE OR REPLACE PROCEDURE TEMP.RETURN_STR
(IN IN_STR   VARCHAR(32000), 
 OUT OUT_STR VARCHAR(32000)
) 
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
NO EXTERNAL ACTION
OLD SAVEPOINT LEVEL
READS SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE c1 CURSOR FOR s1;
PREPARE s1 FROM IN_STR;
OPEN c1;
FETCH c1 INTO OUT_STR;
CLOSE c1;
RETURN;
END;