CREATE OR REPLACE FUNCTION GET_BLOB_AND_OTHER (in_num in number) return number is
       is_ok number(1,0);
   PRAGMA AUTONOMOUS_TRANSACTION;    
   
   from_date date;
   till_date date;
   num varchar2(20);
   num_a varchar2(20); 
   i number(7,0);   

   CURSOR st_Cur
   IS
   select TO_CHAR(NUM),
          NUM_A,
          FROM_DATE,
          TILL_DATE
   from LIST@DBLINK
   where NUM_ID=in_num;
      
begin
   i:=1;
   OPEN st_Cur;
   LOOP  
     FETCH st_Cur INTO num, num_a, from_date,till_date;
       EXIT WHEN st_Cur%NOTFOUND;
       
       INSERT INTO OUT_RESULT@DBLINK (NUM_ID,LINE_NUMBER,RESULT)
       VALUES (in_num,i,
       (select xmlroot(XMLElement("gt-response",xmlattributes('http://gt-response.java-test-site.com' as "xmlns"),
               XMLElement("data",XMLElement("Field",xmlattributes('NAME_1' as "name"),'name 1'),
                                 XMLElement("Field",xmlattributes('NAME_2' as "name"),'name 2'),
                                 XMLElement("Field",xmlattributes('DATE_FROM' as "name"),from_date),
                                 XMLElement("Field",xmlattributes('DATE_TILL' as "name"),till_date)), 
               XMLAGG(XMLElement("data",
                  XMLElement("Field",xmlattributes('ID' as "name"),t.id),
                  XMLElement("Field",xmlattributes('NUMBER' as "name"),t0.num),
                  XMLElement("Field",xmlattributes('DATE1' as "name"),t0.date1),
                  XMLElement("Field",xmlattributes('DATE2' as "name"),t0.date2),
                  XMLElement("Field",xmlattributes('SUMM' as "name"),t0.summ),
                  XMLElement("Field",xmlattributes('CUR' as "name"),t0.cur),
                  XMLElement("Field",xmlattributes('DET' as "name"),t0.det),
                  XMLElement("Field",xmlattributes('LOC' as "name"),case length(tca.country || ', ' || tca.city) 
                                                                                 when 2 then ''
                                                                                 else tca.country || ', ' || tca.city
                                                                               end)))) , version '1.0').getBlobVal(171)
       from table (get_data(from_date,till_date,card_num)) t0
       left join transaction_table t on t.id= t0.id
       left join transaction_table_2 tca on tca.id=t.id) ) ;
       commit;
       i:=i+1;
 	END LOOP;
  CLOSE st_Cur;
  
  
  return 0;
end GET_BLOB_AND_OTHER;

execute immediate 'INSERT INTO OUT_RESULTS@DBLINK SELECT * FROM TEMP_TABLE' ;