A generic search-functionality

APEX has a nice search-functionality to search all objects in the application by just entering one search-condition. From the result-page of the search you can then directly navigate to the pages where you can edit the found objects. My idea was to create something similar for a self-build APEX-application.

The approach

Not all fields in an application are worth to be searched on. So my approach is to create a table with all "searchable column" in the application along with some additional information.

• Tablename - The name of the table/view to be searched

• Columnname - The name of the column to be searched

• ID-Column1 - The first column of the primary key (used later in the page-url to set Page-Item-Values)

• ID-Column2 - The first column of the primary key (used later in the page-url to set Page-Item-Values)

• Object-description - a speaking name for the object represented by the table

• Column-description - a speaking name of whats inside the column

• Ident-Column - a column to select the rows identifying column (this is in general a unique key to be shown to the enduser)

• Page-URL - URL to link to the associated APEX-Page. This must be of the format f?p=&APP_ID.:9001:&SESSION_ID.::NO::IDCOLUMN1,IDCOLUMN2:&IDVALUE1.,&IDVALUE2. The variable in that url (&APP_ID. , &SESSION_ID. , &IDVALUE1. , &IDVALUE2. ) are replaced at runtime with the appropaite value

The table-definition looks like this

CREATE TABLE SEARCHABLE_FIELDS (  
ID                  NUMBER(38)    NOT NULL,  
TABLE_NAME          VARCHAR2(30)  NOT NULL,  
COLUMN_NAME         VARCHAR2(30)  NOT NULL,   
IDCOLUMN1           VARCHAR2(30)  NOT NULL,  
IDCOLUMN2           VARCHAR2(30)          ,  
OBJECT_DESCRIPTION  VARCHAR2(255) NOT NULL,  
COLUMN_DESCRIPTION  VARCHAR2(255) NOT NULL,  
IDENT_COLUMN        VARCHAR2(30)  NOT NULL,  
PAGE_URL            VARCHAR2(255) NOT NULL);

The implementation - db-side

The search-functionality is implemented as a pipelined function in the db, so i need an object type and a collection.

The columns contain the ones from the search-definition-table along with the "found" data for one record.

CREATE OR REPLACE TYPE CO_APX_SEARCH_RESULT AS OBJECT (
  TABLE_NAME         VARCHAR2(30),
  COLUMN_NAME        VARCHAR2(30),
  OBJECT_DESCRIPTION VARCHAR2(4000),
  COLUMN_DESCRIPTION VARCHAR2(4000),
  IDCOLUMN1          VARCHAR2(30),
  IDCOLUMN2          VARCHAR2(30),
  IDVALUE1           VARCHAR2(4000),
  IDVALUE2           VARCHAR2(4000),
  OBJECT_IDENTIFIER  VARCHAR2(4000),
  SEARCH_IDENTIFIER  VARCHAR2(4000),
  PAGE_URL           VARCHAR2(4000)
);

And, as needed for the pipelined function an additional collection on that object.type

CREATE OR REPLACE TYPE COT_APX_SEARCH_RESULT AS TABLE OF CO_APX_SEARCH_RESULT;

At last, we need a function which does the real searching. The function accepts the search-strings as parameter and returns a collection of search-results to the caller. The searching itself is done by dynamic sql based on the search-definitions in the table SEARCHABLE_FIELDS. Here's the function:

CREATE OR REPLACE FUNCTION FK_SEARCH_APEX(i_vcSearchString IN VARCHAR2)
RETURN COT_APX_SEARCH_RESULT PIPELINED
AUTHID CURRENT_USER IS
  CURSOR crSearch IS
    SELECT TABLE_NAME,
           COLUMN_NAME,
           IDCOLUMN1,
           IDCOLUMN2,
           OBJECT_DESCRIPTION,
           COLUMN_DESCRIPTION,
           IDENT_COLUMN,
           REPLACE(REPLACE(PAGE_URL, '&APP_ID.', V('APP_ID')), '&SESSION_ID.', V('SESSION')) PAGE_URL
      FROM SEARCHABLE_FIELDS;
  TYPE tSearch IS TABLE OF crSearch%ROWTYPE;
 
  lSearch         tSearch;
  lResult         COT_APX_SEARCH_RESULT;
  vcQuery         VARCHAR2(32000);
  vcSearchString  VARCHAR2(32000);
  exAsserted      EXCEPTION;
  PRAGMA EXCEPTION_INIT(exAsserted, -06502);
BEGIN
  IF i_vcSearchString IS NOT NULL THEN
    -- Searchstring is defined, Assert for SQL-injection
    BEGIN
      vcSearchString:=DBMS_ASSERT.ENQUOTE_LITERAL('%' || UPPER(i_vcSearchString) || '%');
    EXCEPTION
      WHEN exAsserted THEN
        vcSearchString:=NULL;
    END;
  END IF;
  IF vcSearchString IS NOT NULL THEN
    -- Searchstring is defined, load all searches
    OPEN crSearch;
    FETCH crSearch
    BULK COLLECT INTO lSearch;
    CLOSE crSearch;
    -- check for results
    IF lSearch.COUNT>0 THEN
      -- now do each search via dynamic sql
      FOR i IN 1..lSearch.COUNT LOOP
        vcQuery:='select CO_APX_SEARCH_RESULT('''  || lSearch(i).TABLE_NAME                || ''',''' ||
                                                      lSearch(i).COLUMN_NAME               || ''',''' ||
                                                      lSearch(i).OBJECT_DESCRIPTION        || ''',''' ||
                                                      lSearch(i).COLUMN_DESCRIPTION        || ''',''' ||
                                                      lSearch(i).IDCOLUMN1                 || ''','''  ||
                                                      lSearch(i).IDCOLUMN2                 || ''','  ||
                                                      lSearch(i).IDCOLUMN1                 || ',' ||
                                                      NVL(lSearch(i).IDCOLUMN2, 'NULL')    || ',' ||
                                                      lSearch(i).IDENT_COLUMN              || ',' ||
                                                      lSearch(i).COLUMN_NAME               || ',''' ||
                                                      lSearch(i).PAGE_URL                  || ''')' ||
                  '  FROM ' || lSearch(i).table_name         ||
                  ' WHERE UPPER(' || lSearch(i).column_name || ') LIKE ' || vcSearchString;
        EXECUTE IMMEDIATE vcQuery
        BULK COLLECT INTO lResult;
        FOR j IN 1..lResult.COUNT LOOP
          -- Replace ID-Values in url
          lResult(j).PAGE_URL:=REPLACE(lResult(j).PAGE_URL, '&IDVALUE1.', lResult(j).IDVALUE1);
          lResult(j).PAGE_URL:=REPLACE(lResult(j).PAGE_URL, '&IDVALUE2.', lResult(j).IDVALUE2);
          -- Pipe the result
          PIPE ROW (lResult(j));
        END LOOP;
      END LOOP;
    END IF;
  END IF;
  RETURN;
END;

If you now want to extend your application with search-cababilities to new objects, you only have to create addtional records in the SEARCHABLE_FIELDS-table.

That's it for the db-side, now let's integrate it in the APEX-application.

The implementation - APEX-side - The search page

The first thing we have to do is create a search-page. To do so, we create a new report-page with a report-region based on the following query

SELECT OBJECT_DESCRIPTION,
       OBJECT_IDENTIFIER,
       COLUMN_DESCRIPTION,
       SEARCH_IDENTIFIER,
       PAGE_URL
  FROM TABLE(CAST (FK_SEARCH_APEX(:P9300_SEARCH_FIELD) AS COT_APX_SEARCH_RESULT))

The passing of the search-string is done in the last line of the query as argument to the call of the function, name that item according to your requirements. In my case, i added an additional region as a "Report Filter-Single Row"  region with just that item in it.

Also make sure that the report-column for PAGE_URL correctly

• Diplay As - "Standard Report column"

• Link Text - "Show"

• Target - "URL"

• URL - "#PAGE_URL#"

  

Now we have a search-page which is capable of searching for everything defined in the SEARCHABLE_FIELDS-table and branching to the appropiate page for each result.

You can include that page in your menu-structure or tab-bar.

The implementation - APEX-side- Page 1

But i wanted also to have the functionality of the little searchfield in upper right of the tab-bar, as you have it in the application-builder. For this i had to trick a little and tweak in the HTML of the application-builder main-page. From there i took the html-fragments, and the css.

First, i created my own template for the page and extended the tab-region for the search-field. In the body of the template, i added the following HTML direct behind the #TAB_CELLS#-tag

<div id="appl-search"><table border="0">
<tr>
  <td nowrap="nowrap" align="right"></td>
  <td  colspan="1" rowspan="1" align="left" valign="middle">
    <div class="searchbox-plugin">
      <span class="left"></span>
      <input type="text"  id="P9300_SEARCH_FIELD" value="Anwendung durchsuchen" />
      <span class="right"></span>
    </div>
  </td>
</tr>
</table>
</div>

Its important that the id-attribute of the input-item matches the itemname of the searchitem on my search-page.

To make this look somehow nice i took the images

• apex-search-left.gif

• apex-search-center.gif

• apex-search-right.gif

from the apex-application-builder installfiles and uploaded them into my application.

Additionally the following styles has to be included either in the pages html-header-attribut or in the application's css-file.

<style>
#appl-search {
    width: 165px;
    margin: 9px 20px 0 0;
    float: right;
    position: relative;
}

#appl-search .left {
    display: block;
    height: 19px;
    left: 6px;
    top: 0;
    width: 10px;
    position: absolute;
    background: transparent url(#APP_IMAGES#apex-search-left.gif) no-repeat scroll 0 0;
}

#appl-search input {
    background: transparent url(#APP_IMAGES#apex-search-center.gif) repeat-x scroll center top;
    border: 0 none;
    font-size: 12px;
    color: #404040;
    margin: 0;
    padding: 2px;
    right: 18px;
    top: 0;
    width: 127px;
    outline: none;
    position: absolute;

}

#appl-search .right {
    display: block;
    height: 19px;
    right: 0;
    top: 0;
    width: 19px;
    cursor: pointer;
    position: absolute;
    background: transparent url(#APP_IMAGES#apex-search-right.gif) no-repeat scroll 0 0;
}
</style>

Here's the result

Obviously, this only looks nice with theme 2 "Builder Blue", for other themes you will have to create your own images and style.

The next step is to implement two dynamic actions to make up the functionality of the searchfield. The first action is to remove the text shown inside the search field and make it empty. So we have a dynamic action with the following properties:

• Event - get Focus

• Selection Type - Item

• Item - P9300_SEARCH_FIELD (or the name of what you defined in the template)

• Action - Execute Javascript

• Code - $s("P9300_SEARCH_FIELD", "");

The second dynamic action is to submit the page and redirect to the search-page. This dynamic action has actually two actions, first it submits the item-content of P9300_SEARCH_FIELD to session-state, then it redirects to the search-page. The first step is necessary, because the search-item isn't a "real" page-item defined declaratively in the page-definition.

Here are the settings

• Event - Change

• Selection Type - Item

• Item - P9300_SEARCH_FIELD (or the name of what you defined in the template)

• Condition - Is not null

First Action

• Action - Execute PL/SQL

• PL/SQL-Code - NULL;

• Items to submit - P9300_SEARCH_FIELD

Second action

• Action - Submit page 

• Request - SEARCH

And lastly you need a branch in the page processing going to your search-page

• branch type - branch to page

• Branch point - On submit .- Before computation

• Target type - Page in the application

• Page - 9300 (or the number of the page you use)

That's it. Now upon entering a search-text in the search-field and leaving the field with TAB or ENTER will redirect to the search-page, passing the search-text. The search-page will execute the report-query, which in turn executes our search-function. The results are shown and you have a link to navigate to the appropiate page.

You can test out your own on apex.oracle.com