CREATE OR REPLACE PACKAGE BODY raws_owner.oracle_search
AS
   PROCEDURE simple_search (
      searchterm          VARCHAR2,
      searchergpn         NUMBER,
      p_res         OUT   t_res
   )
   AS
      v_temp_table   search_result_type_tbl;
      v_res_table   search_result_type_tbl;
   BEGIN
      SELECT search_result_type (a.ID, 'Attachment', a.NAME)
      BULK COLLECT INTO v_temp_table
        FROM attachments a
       WHERE contains (a.NAME, searchterm) >  0 
          OR contains (a.DATA, searchterm) >  0 ;
      v_res_table := v_temp_table;

      SELECT search_result_type (obi.obi_itsrm_id, 'Obi', obi.title)
      BULK COLLECT INTO v_temp_table
        FROM t_obi_itsrm obi
       WHERE contains (obi.title, searchterm) >  0 ;
      v_res_table := v_res_table MULTISET UNION ALL v_temp_table;

-- возвращается только результат этого запроса. остальные удаляются
      SELECT search_result_type ('id', 'test', 'hello world')
      BULK COLLECT INTO v_temp_table
        FROM DUAL;
      v_res_table := v_res_table MULTISET UNION ALL v_temp_table;
        
      OPEN p_res FOR
         SELECT *
           FROM TABLE (v_res_table);
   END;
END oracle_search;

CREATE OR REPLACE PACKAGE BODY raws_owner.oracle_search
AS
   PROCEDURE simple_search (
      searchterm          VARCHAR2,
      searchergpn         NUMBER,
      p_res         OUT   t_res
   )
   AS
   BEGIN
      OPEN p_res FOR
      SELECT search_result_type (a.ID, 'Attachment', a.NAME)
        FROM attachments a
       WHERE contains (a.NAME, searchterm) >  0 
          OR contains (a.DATA, searchterm) >  0 
       union all
      SELECT search_result_type (obi.obi_itsrm_id, 'Obi', obi.title)
        FROM t_obi_itsrm obi
       WHERE contains (obi.title, searchterm) >  0 
       union all
      SELECT search_result_type ('id', 'test', 'hello world')
        FROM DUAL;
   END;
END oracle_search;