13 Searching Portal Content

This chapter describes how to use the public search APIs provided with Oracle 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 Oracle 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 Oracle 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 Oracle Portal there as well.

The WWSRC_API package provides the following two types of API:

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:

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
More on OTN

For more information about the public search APIs, refer to the Oracle 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;
/

The following are 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).

The following are 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, Oracle 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 Oracle Portal middle tier:

  1. Section 13.4.1, "Creating a Directory for the XML File".

  2. Section 13.4.2, "Creating an XML File from a CLOB"

  3. Section 13.4.3, "Generating Search Results in XML"

These examples assume that the database is on the same machine as the Oracle 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 Oracle Portal middle tier, perform the following steps:

  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 Oracle 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 steps in this section 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 Chapter 3, "Creating Portlets with OmniPortlet".

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".

To display XML search results in OmniPortlet, perform the following steps:

  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. To do this, perform the following steps:

  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 Oracle Portal Portlet Builder. For more information about Portlet Builder, refer to the Oracle Fusion Middleware Developer's Guide for Oracle Portal. For more information about building dynamic pages, refer to the Oracle 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;
            <schemaname>.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

    Providing information about 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.

    The dynamic page portlet displays search results based on the default values provided when you created the dynamic page, as shown in Figure 13-2.

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

    Search results displayed in a dynamic page portlet.
    Description of "Figure 13-2 Default Search Results Displayed in the Dynamic Page Portlet"

  6. Users can personalize this portlet to provide their own search term and minimum score, as shown in Figure 13-3.

    Figure 13-3 Personalizing Search Criteria

    The Personalize Parameters page.
    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

    Dynamic page portlet with personalized search criteria.
    Description of "Figure 13-4 Personalized Search Results Displayed in the Dynamic Page Portlet"