Skip Headers

Oracle® Application Server Portal Developer's Guide 10g Release 3 (10.1.3) B25601-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# 13 Searching Portal Content

This chapter describes how to use the public search APIs provided with OracleAS Portal. The search APIs are available in the WWSRC_API package.

The public search APIs enable you to search your portal programmatically and return search results as content records or an XML document. Using these APIs, you can design completely custom search forms that seamlessly integrate with OracleAS Portal pages. You can also customize how portal search results get displayed on a page; you are no longer limited to the search results portlet provided with OracleAS Portal. As the public search APIs can return the results in XML, you may process or format the search results however you require.

You can also use the search APIs to display portal content in any custom application. For example, if you have a Web-based application you could easily provide a search form in the application and display the search results from OracleAS Portal there as well.

The WWSRC_API package provides two different types of API:

- Search submission/execution APIs

  - Item search

  - Page search

  - Category search

  - Perspective search

- Search results APIs

  - Get item results as XML document

  - Get page results as XML document

To determine the structure of the search results returned by the search APIs, refer to the appropriate secure content repository view, as shown in Table 13-1.

Table 13-1 Search Results to Secure View Mapping

Search API | Secure Content Repository View
--- | ---
wwsrc_api.item_search | WWSBR_ALL_ITEMS
wwsrc_api.page_search | WWSBR_ALL_FOLDERS
wwsrc_api.category_search | WWSBR_ALL_CATEGORIES
wwsrc_api.perspective_search | WWSBR_ALL_PERSPECTIVES

For example, the results returned by the `item_search` API have the same structure as the WWSBR_ALL_ITEMS view. For information about the structure of the secure views, refer to Chapter F, "Content Management APIs and Views".

This chapter contains the following sections:

- Searching For Items Across All Page Groups

- Searching For Pages in Specific Page Groups

- Searching For Items By Attribute

- Transforming Search Results into XML

- Displaying Search Results

For the examples in the following sections, you need to enable output in SQL*Plus. To do this log on to SQL*Plus as the portal schema owner and enter the following command:

    SQL> SET SERVEROUTPUT ON

For more information about the public search APIs, refer to the Oracle Application Server Portal PL/SQL API Reference on Portal Center:

    http://portalcenter.oracle.com

In the Portal Focus Areas section, click Portlet Development, and then click PL/SQL API Reference (APIs and References section).

Tip: Remember, if you are calling the APIs from a Web provider or external application, you need to set the session context first. For more information, refer to Section 10.1, "Setting the Session Context".

## 13.1 Searching For Items Across All Page Groups

Example 13-1 uses the `item_search` API to search all the page groups in your portal for items that contain the term 'portal'.

Example 13-1 Searching Across All Page Groups (item_search API)

    declare
      l_results    wwsrc_api.items_result_array_type;
      l_count      number;
      l_scores     wwsrc_api.number_list_type;
    begin
      l_results := wwsrc_api.item_search(
        p_mainsearch => 'portal',
        p_out_count  => l_count,
        p_out_scores => l_scores
      );
      dbms_output.put_line('Number of results: ' || l_count);
    exception
      ...
    end;
    /

- p_mainsearch is the keyword or term for which you want to search. This may be any value, including an Oracle Text query expression.

- p_out_count is the number of search hits.

- p_out_scores is an array of search results scores. This is the Oracle Text relevancy score, rating how well each result matches the search term (or any other textual search criteria). The index of the array matches the index of the results array returned by the function.

In Example 13-1, the maximum number of search results that may be returned is determined by the `wwsrc_api` constant MAX_ROWS (default is 1000). This avoids the possibility of a search query with many hits taking too long to run if a specific number of rows is not specified. For an example of how to specify the number of rows to return see Example 13-2.

## 13.2 Searching For Pages in Specific Page Groups

Example 13-2 uses the `page_search` API to search in two specific page groups (MyPageGroup and Shared) for pages that contain the term 'Oracle'. In this example, only the first ten results are returned.

Example 13-2 Searching in Specific Page Groups (page_search API)

    declare
      l_results   wwsrc_api.pages_result_array_type;
      l_count     number;
      l_scores    wwsrc_api.number_list_type;
      l_pggroups  wwsrc_api.number_list_type;
    begin
      l_pggroups(1) := 0;  -- Page group  0 (shared).
      l_pggroups(2) := 33; -- Page group 33 (mypagegroup).
      l_results := wwsrc_api.page_search(
        p_mainsearch  => 'Oracle',
        p_page_groups => l_pggroups,
        p_rows        => 10,
        p_out_count   => l_count,
        p_out_scores  => l_scores
      );
      dbms_output.put_line('Number of results:' || l_count);
    exception
      ...
    end;
    /

- p_mainsearch is the keyword or term for which you want to search. This may be any value, including an Oracle Text query expression.

- p_page_groups is an array of the IDs of the page groups that you want to search. If you do not include this parameter, the value defaults to `wwsrc_api.EMPTY_NUMBER_LIST` (that is, all page groups).

- p_rows is the maximum number of search results to return. This defaults to the `wwsrc_api` constant value `MAX_ROWS`.

- p_out_count is the number of search hits.

- p_out_scores is an array of search result scores. This is the Oracle Text relevancy score, rating how well each result matches the search term (or any other textual search criteria). The index of the array matches the index of the results array returned by the function.

## 13.3 Searching For Items By Attribute

Example 13-3 uses the `specify_attributes` and `item_search` APIs to search for all file items created by Joe Bloggs since 01-Jan-2004 that contain the term 'Oracle'.

Example 13-3 Searching Specific Attributes (specify_attributes API)

    declare
      l_results           wwsrc_api.items_result_array_type;
      l_count             number;
      l_scores            wwsrc_api.number_list_type;
      l_attributes        wwsrc_runtime_attr_varray;
      l_createdate_attrid wwsbr_attributes.id%type;
      l_createdate_caid   wwsbr_attributes.caid%type;
      l_createdate_type   wwsbr_attributes.data_type%type;
    begin
      -- Build up attribute object with author criteria.
      wwsrc_api.specify_attributes(
        p_id                 => wwsbr_api.ATTRIBUTE_AUTHOR,
        p_siteid             => wwsbr_api.SHARED_OBJECTS,
        p_value              => 'Joe Bloggs',
        p_operator           => wwsrc_api.CONTAINS_ALL,
        p_datatype           => wwsrc_api.DATA_TYPE_TEXT,
        p_in_out_attr_varray => l_attributes
      );
      -- Build up attribute object with create date criteria
      -- using wwsbr_attributes view.
      select id, caid, data_type
      into l_createdate_attrid, l_createdate_caid, l_createdate_type
      from wwsbr_attributes
      where name = 'createdate'
      and rownum = 1; -- Ignore translations.
      wwsrc_api.specify_attributes(
        p_id                 => l_createdate_attrid,
        p_siteid             => l_createdate_caid,
        p_value              => '01-JAN-2004',
        p_operator           => wwsrc_api.GREATER_THAN,
        p_datatype           => l_createdate_type,
        p_in_out_attr_varray => l_attributes
      );
      -- Perform the search.
      l_results := wwsrc_api.item_search(
        p_mainsearch     => 'Oracle',
        p_itemtypeid     => wwsbr_api.ITEM_TYPE_FILE,
        p_itemtypesiteid => wwsbr_api.SHARED_OBJECTS,
        p_attributes     => l_attributes,
        p_out_count      => l_count,
        p_out_scores     => l_scores
      );
      dbms_output.put_line('Number of results: ' || l_count);
    exception
      ...
    end;
    /

Parameters for `specify_attributes`:

- p_id is the ID of the attribute. Use the `wwsbr_api` constants or query the WWSBR_ATTRIBUTES view to find this ID.

- p_siteid is the ID of the page group to which the attribute belongs.

- p_value is the attribute value to include as search criteria. This must be a text value.

- p_operator is the search operator that you want to use.

- p_datatype is the datatype of the attribute value.

- p_in_out_attr_varray is the existing varray of attributes (if any).

Parameters for `item_search`:

- p_mainsearch is the keyword or term for which you want to search. This may be any value, including an Oracle Text query expression.

- p_itemtypeid is the ID of the type of item for which you want to search. Use the `wwsbr_api` constants or query the WWSBR_ITEM_TYPES view to find this ID. Note that this is the actual subtype of the item, not the base type.

- p_itemtypesiteid is the ID of the page group to which the item type belongs.

- p_attributes is an array of attributes to search together with their operators and values. You can build up the values for this parameter by calling the `specify_attributes` API.

- p_out_count is the number of search hits.

- p_out_scores is an array of search result scores. This is the Oracle Text relevancy score, rating how well each result matches the search term (or any other textual search criteria). The index of this array matches the index of the results array returned by the function.

## 13.4 Transforming Search Results into XML

The APIs mentioned so far return search results in an array. To provide you with more flexibility about how to display your search results, OracleAS Portal also provides several APIs that return search results as XML. These APIs return the XML results as a CLOB.

The following sections describe how you can generate a physical XML file from a CLOB produced by the search APIs and store the XML file in a directory on the OracleAS Portal middle tier:

1. Creating a Directory for the XML File.

2. Creating an XML File from a CLOB

3. Generating Search Results in XML

These examples assume that the database is on the same machine as the OracleAS Portal middle tier.

### 13.4.1 Creating a Directory for the XML File

If you decide to transform your search results into an XML file, you must first perform some set up tasks to define the physical directory in which to write the file.

To create a directory on the OracleAS Portal middle tier:

1. In SQL*Plus, connect as SYSTEM or SYS and enter the following command:

       create directory <dirname> as '<physical_location>';

   For example:

       create directory RESULTDIR as '/u02/ora/OraHome_101202_PortalMF/Apache/Apache/htdocs/searchapi';

   In this example, we use the `htdocs` directory of the OracleAS Portal middle tier. This is a good location to use as you can then access the XML results file through HTTP, in the format:

       http://<Portal_Mid_Tier>:<Port>/<directory>/<XML_Output_File>

   For example:

       http://my.portal.com:7778/searchapi/results.xml

   You can then use this to specify the XML file as the data source for OmniPortlet. For an example of how to do this, refer to Section 13.5.1, "Displaying XML Search Results in OmniPortlet".

2. Still in SQL*Plus, grant write privileges to the directory from PL/SQL programs. In our example, we would do this by entering the following command:

       grant write on directory RESULTDIR to public;

3. Check that you can write files to the new directory by running the following procedure:

       declare
        2 v_output_file1 utl_file.file_type;
        3 begin
        4 v_output_file1 := utl_file.fopen('RESULTDIR', 'NEW.txt', 'a');
        5 utl_file.put_line(v_output_file1, 'NATURE and Beauty');
        6 utl_file.fclose_all;
        7 end;
        8 /

       PL/SQL procedure successfully completed.

   Now, when you go to the directory on the file system, you should see the NEW.txt file.

### 13.4.2 Creating an XML File from a CLOB

When you have specified the location for the XML output, you can write the procedure that creates it. Example 13-4 shows a generic procedure that takes the CLOB produced by the `get_all_items_xml` API and writes an XML file to a specific location. It takes two input parameters: the file name and the CLOB.

Example 13-4 Writing an XML File to a Specific File System Location

    create or replace procedure results_xml_to_file(xml_filename varchar2, result_xml clob) as
      l_amount binary_integer;
      l_offset number := 1;
      l_text   varchar2(32767);
      l_file   utl_file.file_type;
      l_clob   clob;
    begin
      l_clob := result_xml;
      l_file := utl_file.fopen(
        location  => 'RESULTDIR', -- Directory name is case-sensitive.
        filename  => xml_filename,
        open_mode => 'w'
      );
      l_amount := 32767;
      l_offset := 1;
      begin
        dbms_lob.open(l_clob, dbms_lob.file_readonly);
        loop
          dbms_lob.read(l_clob, l_amount, l_offset, l_text);
          utl_file.put(
            file   => l_file,
            buffer => l_text
          );
          l_offset := l_offset + l_amount;
          exit when l_amount < 32767;
        end loop;
        utl_file.new_line(file => l_file);
        dbms_lob.close(l_clob);
      end;
      utl_file.fclose(file => l_file);
    exception
      ...
    end;
    /

### 13.4.3 Generating Search Results in XML

Now, when you use the APIs to produce search results as XML, you can call the procedure in Example 13-4 to write the resulting CLOB to an XML file on the file system. Example 13-5 shows how you might do this.

Example 13-5 Generating Search Results in XML (get_all_items_xml API)

    declare
      l_results   wwsrc_api.items_result_array_type;
      l_scores    wwsrc_api.number_list_type;
      l_count     number;
      l_clob      clob;
    begin
      l_results := wwsrc_api.item_search(
        p_mainsearch => 'portal',
        p_out_count  => l_count,
        p_out_scores => l_scores
      );
      l_clob := wwsrc_api.get_all_items_xml(l_results);
      results_xml_to_file('results12.xml', l_clob);
    exception
      ...
    end;
    /

For information about the `item_search` API used inExample 13-5, refer to Section 13.1, "Searching For Items Across All Page Groups".

You can use the XML file generated by this API as a data source for OmniPortlet. For an example of how you might do this, refer to Section 13.5.1, "Displaying XML Search Results in OmniPortlet".

### 13.4.4 Workaround for get_item_xml

When you use `get_item_xml` to transform search results to XML, the API produces a `<?xml version="1.0" ?>` element for each search result. This produces invalid XML. Example 13-6 shows a workaround that removes these tags and also adds opening and closing `<ResultSet>` tags to produce an XML file with the ROWSET/ROW structure. If you want to use the XML file as a data source for OmniPortlet, you should do something similar.

Note: Example 13-6 loops through the search results and writes them all to XML (producing the same results as using `get_all_items_xml`). Typically, you would use `get_item_xml` to filter the search results somehow.

Example 13-6 Using get_item_xml to Write Search Results to a File

    declare
      l_amount    binary_integer;
      l_offset    number       := 1;
      l_text      varchar2(32767);
      l_file      utl_file.file_type;
      l_clob      clob;
      l_results   wwsrc_api.items_result_array_type;
      l_scores    wwsrc_api.number_list_type;
      l_count     number;
    begin
      l_results := wwsrc_api.item_search(
        p_mainsearch => 'portal',
        p_out_count  => l_count,
        p_out_scores => l_scores
      );
      l_file := utl_file.fopen(
        location  => 'RESULTDIR', -- Directory name is case-sensitive.
        filename  => 'results14.xml',
        open_mode => 'w'
      );
      utl_file.put(
        file   => l_file,
        buffer => '<ResultSet>'
      );
        for i in 1..l_results.count loop
          l_amount := 32767;
          l_offset := 1;
        begin
          l_clob := wwsrc_api.get_item_xml(l_results(i));
          dbms_lob.open(l_clob, dbms_lob.file_readonly);
        loop
          dbms_lob.read(l_clob, l_amount, l_offset, l_text);
          -- Workaround for XML generated with get_item_xml.
          if instr(l_text, '?>') != 0 then
            l_text := substr(l_text, instr(l_text, '?>') + 2);
          end if;
          -- End of workaround.
          utl_file.put(
            file   => l_file,
            buffer => l_text
          );
          l_offset := l_offset + l_amount;
          exit when l_amount < 32767;
        end loop;
        utl_file.new_line(file => l_file);
        dbms_lob.close(l_clob);
        end;
        end loop;
        utl_file.put(
          file   => l_file,
          buffer => '</ResultSet>'
        );
        utl_file.fclose(file => l_file);
    exception
      ...
    end;
    /

## 13.5 Displaying Search Results

For your search query to be useful, you need to display the results somewhere. You can choose to display the results of the search directly from the array, for example, in a dynamic page. For more flexibility, you can transform the search results into XML first and then display the XML, for example in OmniPortlet.

### 13.5.1 Displaying XML Search Results in OmniPortlet

The following steps provide an example of how to use the XML generated by the search APIs as the data source for OmniPortlet. For example, you could schedule a job to regularly execute an API to produce search results as XML and then automatically display those results in an OmniPortlet.

For more information about OmniPortlet, refer to the Oracle Application Server Portal Developer's Guide.

Note: The XML produced by the search APIs is returned in a CLOB. Therefore, if you want to use the XML as a data source for OmniPortlet, you must first write the CLOB to a file. For an example of how to do this, refer to Section 13.4, "Transforming Search Results into XML".

1. First you need to add a new OmniPortlet instance to a page.

   Tip: OmniPortlet is usually available in the Portlet Builders area of the Portlet Repository.

2. Click the Define link to launch the OmniPortlet Wizard.

3. On the Type page of the wizard, select XML, then click Next.

4. On the Source page, in the XML URL field, enter the URL of your XML file, for example:

       http://my.portal.com:7778/searchapi/results.xml

   Because the XML data produced by the search APIs uses a ROWSET/ROW structure, you do not need to specify an XSL filter.

5. Click Next.

6. On the View page, select Tabular for the Layout Style, then click Next.

7. On the Layout page, choose the columns you want to display in the portlet and how you want them to appear.

8. Click Finish to create the OmniPortlet instance.

### 13.5.2 Displaying Search Results in a Dynamic Page

The following two examples show how to display search results in a dynamic page portlet. Users can then add this portlet to any page within your portal.

1. Create a procedure that the dynamic page can call to perform the search.

   The procedure in Example 13-7 uses the `item_search` API to search for a specified term (`searchterm`) and displays only those results with a score higher than a specified value (`score`).

   Note: You must create the procedure in the database in which your portal resides.

   Example 13-7 Procedure to Perform the Search

       create or replace procedure search_results(searchterm varchar2, score number) as
         x varchar2(100);
         y number;
         l_results   wwsrc_api.items_result_array_type;
         l_count     number;
         l_scores    wwsrc_api.number_list_type;
       begin
         x := searchterm;
         y := score;
         l_results := wwsrc_api.item_search(
           p_mainsearch => x,
           p_out_count  => l_count,
           p_out_scores => l_scores
         );
         htp.p('Number of total hits: ' || l_count);
         htp.p('<br>');
         htp.p('<br>');
         for i in 1..l_results.count loop
           if (l_scores(i) > y) then
             htp.p('<b>' || i || '</b> - <a href="' || l_results(i).url ||
               '">' || l_results(i).display_name || '</a>');
             htp.p('<br>');
             htp.p('score = ' || l_scores(i));
             htp.p('<br>');
           end if;
         end loop;
       exception
         ...
       end;
       /
       grant execute on search_results to public;

   For information about the `item_search` API used inExample 13-7, refer to Section 13.1, "Searching For Items Across All Page Groups".

2. Create the dynamic page.

   A dynamic page is one of the portlets you can build using the OracleAS Portal Portlet Builder. For more information about Portlet Builder, refer to the Oracle Application Server Portal Developer's Guide. For more information about building dynamic pages, refer to the OracleAS Portal online Help.

   Example 13-8 shows the code to use for the dynamic page.

   Tip: Replace `<schema>` with the name of the schema in which you created your procedure.

   Example 13-8 Code for Dynamic Page to Display Results

       <html>
         <head>
           <title>Example for Search API UI</title>
         </head>
         <body>
           <h2>My Search Form</h2>
           <oracle>
             declare
               x varchar2(100);
               y number;
             begin
               x := :searchterm;
               y := :score;
               <schema>.search_results(x,y);
             end;
           </oracle>
         </body>
       </html>

3. Provide default values for the two bind variables defined in the HTML code so that the dynamic page displays some results (Figure 13-1).

   Figure 13-1 Providing Default Values for Bind Variables

   Description of "Figure 13-1 Providing Default Values for Bind Variables"

4. Make sure the dynamic page is available as a portlet.

5. You can now add this dynamic page portlet to any page in your portal.

   Tip: You should find the portlet in the Staging Area of the Portlet Repository under the name of the provider in which you created it.

   Figure 13-2 Default Search Results Displayed in the Dynamic Page Portlet

   Description of "Figure 13-2 Default Search Results Displayed in the Dynamic Page Portlet"

   The dynamic page portlet displays search results based on the default values provided when you created the dynamic page.

6. Users can personalize this portlet to provide their own search term and minimum score.

   Figure 13-3 Personalizing Search Criteria

   Description of "Figure 13-3 Personalizing Search Criteria"

   In Figure 13-4 you can see the changes in the search results when a user personalizes the portlet and changes the minimum score to 70.

   Figure 13-4 Personalized Search Results Displayed in the Dynamic Page Portlet

   Description of "Figure 13-4 Personalized Search Results Displayed in the Dynamic Page Portlet"