Listing #1:
I created a table as follows:
CREATE TABLE LU_XML_MAPPING
(
  TABLE_FIELD_NAME  VARCHAR2(60 BYTE),
  XML_MAPPING       VARCHAR2(30 BYTE)
);

ALTER TABLE LU_XML_MAPPING ADD (
  UNIQUE (XML_MAPPING));

Listing #2:
CREATE TABLE SEARCH_TABLE
(
  DEP_ID       NUMBER(12)                       NOT NULL,
  XML_CLOB     CLOB                             NOT NULL,
  UPDATE_DATE  DATE                             DEFAULT SYSDATE
);

CREATE UNIQUE INDEX SEARCH_TABLE_PK ON SEARCH_TABLE
(DEP_ID);

ALTER TABLE SEARCH_TABLE ADD (
  CONSTRAINT SEARCH_TABLE_PK
 PRIMARY KEY
 (DEP_ID));

ALTER TABLE SEARCH_TABLE ADD (
  CONSTRAINT SEARCH_TABLE_FK 
 FOREIGN KEY (DEP_ID) 
 REFERENCES DEPOSITS_BASE (DEP_ID)
    ON DELETE CASCADE);

Listing #3:
CREATE OR REPLACE PACKAGE Create_Search_Field AS

PROCEDURE deposit_info(dep_id_in NUMBER, mode_in VARCHAR2);

PROCEDURE land_st_info(dep_id_in IN NUMBER);

PROCEDURE print_doc(dep_id_in NUMBER, mode_in VARCHAR2);

END Create_Search_Field;

The associated package body for the above procedure is:

CREATE OR REPLACE PACKAGE BODY Create_Search_Field
AS
-----------------------------------------------------------------------
   PROCEDURE deposit_info (dep_id_in NUMBER, mode_in VARCHAR2)
   IS
      TYPE depc IS REF CURSOR;

      v_cursor   depc;
      v_rec      deposits%ROWTYPE;
      v_sql      VARCHAR2 (1000);
      v_count    NUMBER             := 0;
      v_clob     CLOB               := NULL;
      v_mode     VARCHAR2 (6)       := mode_in;
      v_errorcode VARCHAR2(90)      := NULL;
      v_errortext VARCHAR2(4000)    := NULL;
   BEGIN

      SELECT COUNT (*)
      INTO   v_count
      FROM   deposits
      WHERE  dep_id = dep_id_in;

      IF v_count > 0
      THEN
         v_sql := 'SELECT * ' || 'FROM deposits WHERE 1=1';

         IF dep_id_in IS NOT NULL
         THEN
            DBMS_SESSION.set_context ('SEARCH_CTX', 'DEP_ID', dep_id_in); 
            v_sql :=
                  v_sql
               || ' and dep_id = sys_context(''SEARCH_CTX'', ''DEP_ID'')';
         END IF;

         OPEN v_cursor FOR v_sql;

         v_clob := v_clob || '  <DEPOSITS_DATA> ' || CHR (13);

         LOOP
            FETCH v_cursor
            INTO  v_rec;

            EXIT WHEN v_cursor%NOTFOUND;
            v_clob :=
                  v_clob
               || '    <DEPOSIT_ID>'
               || v_rec.dep_id
               || '</DEPOSIT_ID> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <RECORD_TYPE>'
               || v_rec.rec_tp
               || '</RECORD_TYPE> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <DEVELOPMENT_STATUS>'
               || v_rec.dev_st
               || '</DEVELOPMENT_STATUS> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <OPERATION_TYPE>'
               || v_rec.oper_tp
               || '</OPERATION_TYPE> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <SITE_COMMODITY_TYPES>'
               || v_rec.site_commod_type
               || '<SITE_COMMODITY_TYPES> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <SIGNIFICANT>'
               || v_rec.sig
               || '</SIGNIFICANT> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <MINERALIZED_AREA_NAME>'
               || v_rec.min_area_name
               || '</MINERALIZED_AREA_NAME> '
               || CHR (13);
         END LOOP;

         v_clob := v_clob || '  </DEPOSITS_DATA>     ' || CHR (13);
      END IF;

      IF UPPER (v_mode) = 'INSERT'
      THEN
         INSERT INTO SEARCH_TABLE
              VALUES (dep_id_in, v_clob, SYSDATE);
      ELSIF UPPER (v_mode) = 'UPDATE'
      THEN
         UPDATE SEARCH_TABLE
            SET xml_clob = v_clob,
			    update_date = SYSDATE
          WHERE dep_id = dep_id_in;
      END IF;

   EXCEPTION
      WHEN OTHERS
   THEN
-- enter a call to your logging routine, optional
      NULL;

   END deposit_info;

-----------------------------------------------------------------------
   PROCEDURE land_st_info (dep_id_in IN NUMBER)
   IS
      TYPE land_stc IS REF CURSOR;

      v_cursor   land_stc;
      v_rec      land_status%ROWTYPE;
      v_sql      VARCHAR2 (1000);
      v_count    NUMBER                := NULL;
      v_clob     CLOB                  := NULL;
   BEGIN
      SELECT COUNT (*)
      INTO   v_count
      FROM   LAND_ST_BASE
      WHERE  dep_id = dep_id_in;

      IF v_count > 0
      THEN
         v_sql := 'SELECT * ' || 'FROM land_status WHERE 1=1';

         IF dep_id_in IS NOT NULL
         THEN
            v_sql :=
                  v_sql
               || ' and dep_id = sys_context(''SEARCH_CTX'', ''DEP_ID'')';
         END IF;

         v_sql := v_sql || ' ORDER BY LINE';

         OPEN v_cursor FOR v_sql;

         v_clob := v_clob || '  <LAND_STATUS_DATA>' || CHR (13);

         LOOP
            FETCH v_cursor
            INTO  v_rec;

            EXIT WHEN v_cursor%NOTFOUND;
            v_clob :=
                  v_clob
               || '    <LAND_STATUS>'
               || v_rec.land_st
               || '</LAND_STATUS> '
               || CHR (13);
            v_clob :=
                  v_clob
               || '    <ADMINISTRATIVE_AREA>'
               || v_rec.admin_area
               || '</ADMINISTRATIVE_AREA> '
               || CHR (13);
         END LOOP;

         v_clob := v_clob || '  </LAND_STATUS_DATA>' || CHR (13);
      END IF;

      UPDATE SEARCH_TABLE
         SET xml_clob = xml_clob || v_clob
       WHERE dep_id = dep_id_in;
   END land_st_info;

-----------------------------------------------------------------------
PROCEDURE print_doc (dep_id_in NUMBER, mode_in VARCHAR2)
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      v_mode   VARCHAR2 (6) := mode_in;
   BEGIN

      deposit_info (dep_id_in, v_mode);
      land_st_info (dep_id_in);
-- add in calls to execute other procedures for additional tables here...
      COMMIT;
   END print_doc;
END Create_Search_Field;
/

Listing #4:
CREATE OR REPLACE PROCEDURE Fill_Clobs  IS
      v_errorcode VARCHAR2(90)      := NULL;
      v_errortext VARCHAR2(4000)    := NULL;

CURSOR dep_cur IS
SELECT dep_id 
FROM DEPOSITS_BASE
WHERE dep_id  IN 
  (SELECT dep_id FROM SEARCH_TABLE WHERE dbms_lob.getlength(xml_clob) < 1)
ORDER BY dep_id;

dep_rec dep_cur%ROWTYPE;
v_id   NUMBER  := NULL;

BEGIN

FOR dep_rec IN dep_cur
LOOP
   v_id := dep_rec.dep_id;
   Create_Search_Field.print_doc(v_id,'INSERT');
END LOOP;

   EXCEPTION
      WHEN OTHERS
   THEN
-- enter a call to your logging routine, optional
NULL;

END;


Listing #5:
DROP INDEX search_index; -- search_index is the index name, again same caveats as above.

ctx_ddl.create_preference('clob_idx_storage', 'BASIC_STORAGE');

ctx_ddl.set_attribute('clob_idx_storage', 'I_TABLE_CLAUSE', 'tablespace usgs_lob_idx storage (initial 10M next 10M)');

ctx_ddl.set_attribute('clob_idx_storage', 'R_TABLE_CLAUSE', 'tablespace usgs_lob_idx storage (initial 1M) lob (data) store as (cache)');

ctx_ddl.set_attribute('clob_idx_storage', 'N_TABLE_CLAUSE', 'tablespace usgs_lob_idx storage (initial 1M)');

ctx_ddl.set_attribute('clob_idx_storage', 'I_INDEX_CLAUSE', 'tablespace usgs_lob_idx storage (initial 1M) compress 2');

ctx_ddl.set_attribute('clob_idx_storage', 'P_TABLE_CLAUSE', 'tablespace usgs_lob_idx storage (initial 1M)');

CREATE INDEX search_index ON search_table(xml_clob) 
indextype IS ctxsys.context 
parameters('datastore ctxsys.direct_datastore 
storage clob_idx_storage
filter ctxsys.null_filter  
section group ctxsys.auto_section_group
sync (ON COMMIT)');

Modify the above accordingly, if you need/want a different tablespace or will have multiple text indexes, hence different “preferences” and/or index names.

Listing #6:
SELECT   dep_id, dev_st, oper_tp, sig, NAME, 
  MIN (commod) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) commod,
  MIN (commod_group) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) commod_group,
  MIN (import) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) import,
  MIN (usgs_num) KEEP (DENSE_RANK FIRST ORDER  BY mt_rec ASC) usgs_num,
  MIN (model_name) KEEP (DENSE_RANK FIRST ORDER  BY mt_rec ASC) model_name,
  MIN (country) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) country,
  MIN (state_prov) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) state_prov,
  MIN (county) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) county
FROM     NEW_MASTER_QUERY
WHERE    dep_id IN
    (SELECT dep_id
     FROM search_table where CONTAINS( xml_clob, '&F130_WHERE_CLAUSE.') > 0 )
GROUP BY dep_id, dev_st, oper_tp, sig, NAME

Listing #7:
<script language="JavaScript" type="text/javascript">
<!-- Hide
//check if the next sibling node is an element node
function get_nextsibling(n)
  {
  var x=n.nextSibling;
  while (x.nodeType!=1)
   {
   x=x.nextSibling;
   }
  return x;
  }

function va_AddTableRow(pThis, pThat, pNum) {
    
    if ($x(pThis).parentNode.parentNode.nextSibling) {return};    

    var tt = html_GetElement(pThat);
    for (i = 0; i < pNum; i++) {
        var ogTR = tt.rows[1];
        var trClone = ogTR.cloneNode(true);
   trClone.firstChild.nextSibling.nextSibling.firstChild.value = '';
        var last_child = trClone.lastChild.getElementsByTagName("input");
        for (var j = 0; j < last_child.length; j++) {
            if (last_child[j].type == "hidden" &&
                last_child[j].name == "f01") {
                last_child[j].value = "";
            }
        }
        if (document.all) {
            myNewRow = tt.insertRow(tt.rows.length);
            oReplace = myNewRow.replaceNode(trClone);
        } else {
            tt.appendChild(trClone);
        }
    }
}
// End Hide -->
</script>

Listing #8:
declare
v_sql  varchar2(4000);
v_count PLS_INTEGER;

begin

IF apex_collection.collection_exists( p_collection_name => 'SEARCH_COLLECTION' ) THEN
   v_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT (p_collection_name => 'SEARCH_COLLECTION' );
   IF v_count > 0 THEN
v_sql := 'select apex_item.select_list_from_lov_xl(1,c001,''LOV_TABLE_NAMES'',null,''NO'') table_field_name,
       apex_item.select_list_from_lov(3,c002,''LOV_COMPARISON'',null,''NO'') comp_oper,
       apex_item.text(4,c003,45) search_text,
       apex_item.select_list_from_lov(5,c004,''LOV_AND_OR'',''onChange="javascript:va_AddTableRow(this,''''table_grab'''',1);"'',''NO'') and_or
       FROM apex_collections
       WHERE collection_name = ''SEARCH_COLLECTION''
       ORDER BY seq_id
';
   ELSE
v_sql:= 'select apex_item.select_list_from_lov_xl(1,table_field_name,''LOV_TABLE_NAMES'',null,''NO'') table_field_name,
       apex_item.select_list_from_lov(3,comp_oper,''LOV_COMPARISON'',null,''NO'') comp_oper,
       apex_item.text(4,search_text,45) search_text,
       apex_item.select_list_from_lov(5,and_or,''LOV_AND_OR'',''onChange="javascript:va_AddTableRow(this,''''table_grab'''',1);"'',''NO'') and_or
from lu_xml_search;';
END IF;

ELSE
v_sql:= 'select apex_item.select_list_from_lov_xl(1,table_field_name,''LOV_TABLE_NAMES'',null,''NO'') table_field_name,
       apex_item.select_list_from_lov(3,comp_oper,''LOV_COMPARISON'',null,''NO'') comp_oper,
       apex_item.text(4,search_text,45) search_text,
       apex_item.select_list_from_lov(5,and_or,''LOV_AND_OR'',''onChange="javascript:va_AddTableRow(this,''''table_grab'''',1);"'',''NO'') and_or
from lu_xml_search;';
END IF;
return v_sql;

end;

Listing #9:
DECLARE 
clause VARCHAR2(900);
v_tag VARCHAR2(60) := NULL;
v_tab VARCHAR2(60) := NULL;
v_search VARCHAR2(60) := NULL;
v_cond VARCHAR2(5) := NULL;
i pls_integer := 1;
v_errorcode VARCHAR2(90) := NULL;
v_errortext VARCHAR2(4000) := NULL;

BEGIN
   clause := ' AND 1=1';
   FOR i IN 1 .. apex_application.g_f01.COUNT
   LOOP

      IF apex_application.g_f01(i) IS
      NOT NULL THEN
         IF apex_application.g_f01(i) LIKE '%ENTIRE TABLE%' THEN
            IF apex_application.g_f03(i) = 'EQUALS' THEN
               SELECT xml_table_mapping
               INTO v_tag
               FROM lu_xml_mapping
               WHERE TABLE_NAME = apex_application.g_f01(i);
            ELSE -- g_fo3(i) = 'NOT EQUALS'
               IF apex_application.g_f03(i) = 'NOT EQUALS' THEN
                  SELECT xml_table_mapping
                  INTO v_tag
                  FROM lu_xml_mapping
                  WHERE TABLE_NAME = apex_application.g_f01(i);
               END IF;
            END IF;
         ELSE
            IF apex_application.g_f03(i) = 'EQUALS' THEN
               SELECT xml_field_mapping
               INTO v_tag
               FROM lu_xml_mapping
               WHERE TABLE_NAME = apex_application.g_f01(i);
            ELSE -- g_fo3(i) = 'NOT EQUALS'
               IF apex_application.g_f03(i) = 'NOT EQUALS' THEN
                  SELECT xml_field_mapping
                  INTO v_tag
                  FROM lu_xml_mapping
                  WHERE TABLE_NAME = apex_application.g_f01(i);
               END IF;
            END IF;
         END IF;
      END IF;
   v_search := apex_application.g_f04(i);
   clause := clause || v_cond || ' contains(xml_clob, ''' || v_search || ' within ' || v_tag || ''') > 0';
   v_cond := ' ' || apex_application.g_f05(i);

   END LOOP;
IF clause = ' AND 1=1' THEN
   :P3_WHERE_CLAUSE := '1=1';
ELSE
   :P3_WHERE_CLAUSE := SUBSTR(clause,9);
END IF;

EXCEPTION
WHEN no_data_found THEN
-- enter a call to your logging routine, optional
NULL;
WHEN others THEN
   -- enter a call to your logging routine, optional
   NULL;
END;

Listing #10:
SELECT   dep_id, dev_st, oper_tp, sig, NAME, 
         MIN (commod) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) commod,
         MIN (commod_group) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) commod_group,
         MIN (import) KEEP (DENSE_RANK FIRST ORDER  BY c_line ASC) import,
         MIN (usgs_num) KEEP (DENSE_RANK FIRST ORDER  BY mt_rec ASC) usgs_num,
         MIN (model_name) KEEP (DENSE_RANK FIRST ORDER  BY mt_rec ASC) model_name,
         MIN (country) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) country,
         MIN (state_prov) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) state_prov,
         MIN (county) KEEP (DENSE_RANK FIRST ORDER  BY l_line ASC) county
FROM     NEW_MASTER_QUERY
WHERE    dep_id IN
    (SELECT dep_id
     FROM search_table where &P3_WHERE_CLAUSE. )
GROUP BY dep_id, dev_st, oper_tp, sig, NAME

Listing #11:
DECLARE
   search_clause   VARCHAR2 (1900)  := NULL;
   v_tag           VARCHAR2 (60)   := NULL;
   v_tab           VARCHAR2 (60)   := NULL;
   v_not           VARCHAR2 (4)    := NULL;
   v_search        VARCHAR2 (60)   := NULL;
   v_cond          VARCHAR2 (5)    := NULL;
   i               PLS_INTEGER     := 1;
BEGIN
   search_clause := ' AND 1=1';
   apex_collection.create_or_truncate_collection
                      (p_collection_name      => 'SEARCH_COLLECTION');

   IF apex_application.g_f01 (i) IS NOT NULL
   THEN
      FOR i IN 1 .. apex_application.g_f01.COUNT
      LOOP
         IF apex_application.g_f03 (i) = 'EQUALS'
         THEN
            v_not := NULL;
         ELSE
            -- g_fo3(i) = 'NOT EQUALS'
            v_not := ' NOT';
         END IF;

         SELECT xml_mapping
         INTO   v_tag
         FROM   LU_XML_MAPPING
         WHERE  table_field_name = apex_application.g_f01 (i);

      apex_collection.add_member (p_collection_name  => 'SEARCH_COLLECTION',
                                  p_c001             => apex_application.g_f01(i),
                                  p_c002             => apex_application.g_f03(i),
                                  p_c003             => apex_application.g_f04(i),
                                  p_c004             => apex_application.g_f05(i)
                                 );
      v_search := apex_application.g_f04 (i);
      search_clause :=
            search_clause
         || v_cond
         || v_not
         || ' contains(xml_clob, '''
         || v_search
         || ' within '
         || v_tag
         || ''') > 0';
      v_cond := ' ' || apex_application.g_f05 (i);
      v_not := NULL;
      END LOOP;
   END IF;

   IF search_clause = ' AND 1=1'
   THEN
      :p3_where_clause := '1=1';
   ELSE
      :p3_where_clause := SUBSTR (search_clause, 9);
   END IF;

   -- Now need to clear the last and/or condition in the collection
   i := apex_application.g_f01.COUNT;
   IF apex_application.g_f01 (i) IS NOT NULL
   THEN
   apex_collection.update_member_attribute
                                    (p_collection_name      => 'SEARCH_COLLECTION',
                                     p_seq                  => i,
                                     p_attr_number          => 4,
                                     p_attr_value           => NULL
                                    );
   END IF;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- enter a call to your logging routine, optional
      NULL;
   WHEN OTHERS
   THEN
      -- enter a call to your logging routine, optional
      NULL;

      :p3_where_clause := '1=1';
END;