CREATE PROCEDURE "VYGRUZKA" (out par varchar( 20 ))
DYNAMIC RESULT SETS  10 
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
BEGIN
    -- Declare variables
    DECLARE C_OBL    VARCHAR( 20 );
    DECLARE STMT_OBL VARCHAR( 7000 );
    DECLARE SQLCODE  INTEGER DEFAULT  0 ;

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH HOLD FOR
    SELECT trim(FCODE_KATO)
    FROM   metaadm.spr_soato_obl   
    WITH UR;

    -- Cursor left open for client application
    OPEN cursor1;

    FETCH cursor1 INTO C_OBL;    

    WHILE SQLCODE!= 100  DO        
     
    -- устанавливаем текст запроса для выборки из юрлиц по авто
        SET STMT_OBL =
        'EXPORT TO /tmp/stmt_'||C_OBL||'.txt OF DEL '||
                'MODIFIED BY COLDEL| CODEPAGE=1208 '||
                'MESSAGES ON SERVER '||
        'select a.frnn as rnn, '||
        'case a.FSUB '||
        '    when ''1'' then coalesce(a.fnaim,'''') '||
        '    when ''2'' then '||
        '    con_fio(coalesce(a.ffam,''''), coalesce(a.fim,''''), coalesce(a.FOTCH,'''')) '||
        '    end as naimenovanie, '||
        '    coalesce(spr1.fname,'''') as subject, '||
        '    coalesce(a.fmarka,'''') as marka, '||
        '    coalesce(spr2.fname,'''') as tip_ts, '||
        '    coalesce(spr3.fkat_ts,'''') as kat_ts, '||
        '    coalesce(a.fgod,'''') as god , '||
        '    a.fgos, '||
        '    case when a.FDATE_PRAVO is null then '' '' else char(a.fdate_pravo,eur) end as date_pravo, '||
        '    coalesce(spr4.fpoiz,'''') as str, '||
        '    coalesce(a.fmosh_ls,0) as mosh_ls, '||
        '    coalesce(a.fmosh_kvt,0) as mosh_kvt, '||
        '    coalesce(a.fobiem,0) as obiem, ' ||
        '    coalesce(a.fmest,0) as mest, '||
        '    coalesce(a.fkuz,'''') as kuz, ' ||
        '    coalesce(a.FDVIG,'''') as dvig, '||
        '    con_addr(coalesce(rtrim(spr5.fname),''''), '||
        '             coalesce(rtrim(spr6.fname),''''), '||
        '             coalesce(a.fnas_p,''''), '||
        '             coalesce(a.fulitsa,''''), '||
        '             coalesce(a.fdom,''''), '||
        '             coalesce(a.fkv,'''')) as addres '||
        '    from metaadm.cl_119 a '||        
        '    left join metaadm.SPR_U_F spr1 on a.fsub=spr1.fcode '||
        '    left join metaadm.spr_tip_ts spr2 on a.ftip_ts=spr2.FCODE '||
        '    left join metaadm.spr_kat_ts spr3 on a.fkat_ts=spr3.FCODE '||
        '    left join metaadm.SPR_POIZ spr4 on a.fstr_pr=spr4.fcode '||
        '    left join metaadm.SPR_SOATO_OBL spr5 on a.FOBL=spr5.fcode '||
        '    left join metaadm.SPR_SOATO_raion spr6 on a.Fraion=spr6.fcode '||
        '    where fsrts not in (select fsrts from metaadm.cl_108) '||
        '      and spr5.fcode_kato like '''||C_OBL||'%'' '||
        '    fetch first 10 rows only';    
      
   CALL SYSPROC.ADMIN_CMD(TRIM(STMT_OBL));      
   
   FETCH cursor1 INTO C_OBL;    

   set par=C_OBL;
   END WHILE;
   
   CLOSE cursor1;
END