9 Content Management API Introduction

This chapter provides an overview of a selection of the APIs provided with Oracle Portal. You can use these particular APIs to write code for performing content management tasks. It contains the following sections:

9.1 Overview

Oracle Portal uses a schema within the Oracle Metadata Repository, shown in Figure 9-1, to store the content and metadata associated with the portal instance. This schema is sometimes referred to as the content repository. For example, when a contributor adds a file item to a portal page, the file is uploaded to a table in the portal schema of the MDS Repository along with the metadata supplied.

Figure 9-1 The MDS Repository

The OracleAS Metadata Repository.
Description of "Figure 9-1 The MDS Repository"

For more information about the MDS Repository and Oracle Portal architecture in general, refer to the Oracle Fusion Middleware Administrator's Guide for Oracle Portal.

Oracle Portal provides many built-in tools to help you manage the content in the portal schema of the MDS Repository right from your Web browser. However, sometimes you may find that you need to work with your content in an environment outside of the Oracle Portal browser-based user interface.

The following are some examples of when you might want to do this:

  • Building an alternative user interface when the product user interface does not quite meet your requirements, for instance:

    • to change the look and feel of the wizards to meet your own corporate design

    • to add custom validations

    • to provide a custom search form or search results page

  • Bulk loading content with metadata

  • Integrating with external applications and content management systems

  • Archiving items (for example, moving them to an archive page or offline storage)

  • Managing versions (for example, deleting or purging noncurrent versions, limiting the number of versions, and so on)

  • Integrating with external workflow systems

The content management APIs enable you to interact with the portal schema of the MDS Repository programmatically, rather than by using the Oracle Portal user interface. For more information, refer to Section 9.2, "Content Management APIs".

You can also query the data in the content repository using a set of secure views. For more information, refer to Section 9.2.1, "Secure Content Repository Views".

9.2 Content Management APIs

There are multiple public APIs that enable you to programmatically perform many content management tasks, such as adding items and creating pages.

The majority of the APIs for content management are contained within the WWSBR_API package. You may also find the following API packages useful when writing code to perform content management tasks:

  • The WWSRC_API package contains APIs for performing searches on content in the portal schema of the MDS Repository.

  • The WWSEC_API package contains APIs for controlling access to content in the portal schema of the MDS Repository.

  • The WWCTX_API package contains APIs for managing a session context for a specific user.

  • The WWPRO_API_INVALIDATION package contains APIs for invalidating content in Oracle Web Cache.


Only use public APIs in your code. The use of non-public APIs is not supported and may cause your code to break when upgrading to new releases.

More on OTN

For more information about these packages, refer to Section F.1, "Supported APIs". For a full list of the supported PL/SQL APIs, refer to the Oracle Portal PL/SQL API Reference on Portal Center:


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

9.2.1 Secure Content Repository Views

Held within the portal schema are a number of content repository views. You can use these views to query back data on the documents and items stored in the content repository. For a list of the secure views, refer to Section F.2, "Secure Views".

Many of the content management APIs that are discussed in this manual require you to pass object IDs as parameters. To do this, you need to know the IDs of the objects with which you want to work. You can use the secure content repository views to find the IDs of portal objects. For more information, refer to Section 10.3, "Finding an Object ID".


Direct access to other tables and views in the portal schema of the MDS Repository is not supported, as the definition of those tables and views may change between releases.

9.2.2 Terminology

To maintain backward compatibility with the Oracle9iAS Portal Release 1 (3.0.9) content area APIs and views, many of the API procedure and parameter names and the view and column names continue to use Release 1 terminology. You may find Table 9-1 useful to map the Release 1 terminology to the current terminology.

Table 9-1 Mapping of Release 1 Terminology to Current Terminology

Release 1 Terminology Current Terminology

content area

page group



navigation bar

navigation page

9.3 Providing Access to the APIs and Secure Views

The portal schema automatically has the appropriate access to the public APIs and secure views. To enable another schema to access the public APIs and secure views, use the following script:


To provide access to the APIs, perform the following steps:

  1. Change to the directory containing the provsyns.sql script:

    Windows: cd <ORACLE_HOME>\portal\admin\plsql\wwc
    Linux/Unix: cd <ORACLE_HOME>/portal/admin/plsql/wwc
  2. Log in to SQL*Plus as the portal schema owner. For example:

    sqlplus portal/oracle1

    You must run provsyns.sql as the portal schema owner. By default the portal schema is called PORTAL. An administrator can use Oracle Internet Directory to obtain the portal schema password as follows:

    1. Navigate to:

      • Entry Management

      • cn=OracleContext

      • cn=Products

      • cn=IAS

      • cn=Infrastructure Databases

      • OrclReferenceName=Infrastructure Database (for example, iasdb.server.domain.com)

      • OrclResourceName=Schema Name (for example, PORTAL)

    2. Click this entry.

    3. Look for the orclpasswordattribute value in the right panel. This is the schema password.

  3. Run the provsyns.sql script:

    SQL>@provsyns.sql <schema>

    Where schema is the name of the schema to which you want to grant access.


Ignore any messages related to a missing file when running provsyns.sql.

9.4 Guidelines for Using the APIs

When using the APIs described in this manual, you should follow the best practice guidelines described in the following sections.

9.4.1 Using a Separate Schema

When creating procedures and packages that use the content management APIs, create them in a separate schema of the database in which Oracle Portal is installed. Do not create them in the portal schema. Creating additional procedures and packages in the portal schema is not supported and they may be lost when upgrading to a new release.

Once you have created the schema for your procedures and packages, you must grant it access to the APIs and secure content repository views. For information about how to do this, refer to Section 9.3, "Providing Access to the APIs and Secure Views".

9.4.2 Using Constants

All the API packages include predefined constants that you can use to easily refer to Oracle Portal objects and metadata. For example, the WWSBR_API package includes constants for base attributes (such as ATTRIBUTE_AUTHOR and ATTRIBUTE_TEXT), seeded item types (such as ITEM_TYPE_FILE and ITEM_TYPE_URL), and image alignment options (such as ALIGN_BOTTOM and ALIGN RIGHT). To make your code robust, you should use these constants wherever possible for object IDs and attribute values. That way, if the actual ID or values change, your code will still work because the name of the constant will stay the same.

More on OTN

For a full list of the constants available in each API package, refer to the Oracle Portal PL/SQL API Reference on Portal Center:


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

9.4.3 Invalidating the Cache

Many of the APIs automatically generate cache invalidation messages for the pages that are affected by the changes. Therefore, you should always call wwpro_api_invalidation.execute_cache_invalidation at the end of your routine to process these messages (see Example 11-3 for an example of how to do this). If you do not call this procedure, your changes may not be visible until the affected pages are invalidated through other means.

There is no need to call execute_cache_invalidation more than once. For example, if you are adding or updating multiple items in a loop, call execute_cache_invalidation when the loop is complete.

9.4.4 Issuing Commits

The APIs do not issue commits. However, if you are calling the APIs from a browser session through Portal Services, Portal Services will perform an automatic commit before returning control to the browser.

The wwpro_api_invalidation.execute_cache_invalidation API also issues a commit. If you are calling the APIs from an external environment (for example, SQL*Plus or a Web provider), and you need to commit before processing the cache invalidations, you must explicitly issue a commit statement in your code. Similarly, if you need to rollback, do so before calling execute_cache_invalidation.

9.4.5 Resetting CMEF Global Variables

If you are using the content management APIs in conjunction with the Content Management Event Framework (CMEF) you need to make sure that you reset the CMEF global variables before or after each API call.

When a user performs an action using the user interface, Oracle Portal uses various global variables to determine which CMEF messages get logged. After each action, these global variables are automatically reset ready for the next action. When you use the APIs to perform portal actions, these global variables are not automatically reset. Therefore, to ensure that CMEF messages are logged correctly, you must reset the CMEF variables explicitly by calling the wwsbr_api.clear_cmef_context API before or after each API call.

For example, if you use the APIs to create several items on a page, you need to call the clear_cmef_context API between each of the add_item calls (see Example 9-1).

Example 9-1 Calling the clear_cmef_context API

l_new_item_master_id1 := wwsbr_api.add_item(
  p_caid              => l_caid,
  p_folder_id         => l_folder_id,
l_new_item_master_id2 := wwsbr_api.add_item(
  p_caid              => l_caid,
  p_folder_id         => l_folder_id,

For more information about CMEF, refer to Chapter 16, "Using the Content Management Event Framework".

9.4.6 Using Predefined Exceptions

The API packages contain many predefined exceptions. When coding with the content management APIs, it is good practice to include the appropriate predefined exceptions rather than relying on the WHEN OTHERS exception to pick up all errors. This also has the advantage that any error messages generated by the code can be more specific to the actual problem. Example 9-2 shows some of the exceptions you might include when calling the wwsbr_api.set_attribute API.

Example 9-2 Using Predefined Exceptions

   p_site_id           => 37,
   p_thing_id          => 8056,
   p_attribute_site_id => wwsbr_api.SHARED_OBJECTS,
   p_attribute_id      => wwsbr_api.ATTRIBUTE_TITLE,
   p_attribute_value   => 'New Display Name'
 -- Process cache invalidation messages.
 when wwsbr_api.ITEM_NOT_FOUND_ERROR then
   dbms_output.put_line('Item does not exist');
 when wwsbr_api.ATTRIBUTE_NOT_FOUND then
   dbms_output.put_line('Attribute does not exist');
 when wwsbr_api.ITEM_NOT_FOR_UPDATE then
   dbms_output.put_line('Cannot update an item with a status of Rejected, Deleted or Marked for Delete');
 when wwsbr_api.NOT_AUTHORIZED_USER then
   dbms_output.put_line('User trying to update the item is not the current user or the user who checked the item out');
 when wwsbr_api.EDIT_CUSTOM_ATTR then
   dbms_output.put_line('Error while trying to update a custom attribute');
 when OTHERS then
   dbms_output.put_line('Error '||to_char(sqlcode)||': '||sqlerrm);


When using predefined exceptions, remember to include the name of the package that owns it, for example wwsbr_api.PAGE_NOT_FOUND.

More on OTN

For a full list of the exceptions available in each API package, refer to the Oracle Portal PL/SQL API Reference on Portal Center:


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

9.4.7 Naming Objects

When creating page groups and page group objects, you specify a unique internal name for the object (p_name). Internal names must:

  • be no more than 60 characters in length.

  • not contain spaces or special characters other than the underscore character (_).

9.5 Guidelines for Using the Secure Views

When using the secure views described in this manual, you should follow the best practice guidelines described in the following sections.


In this release, there is no supported view of the document table in the repository. A secure view of the document table is planned for a future release.

9.5.1 Identifying Primary Keys

The primary key for most objects contains the object id, the page group id (caid), and the language. When joining between views, always use these columns in the JOIN clause. Example 9-3 shows the JOIN when joining an item to its page.

Example 9-3 Joining an Item to Its Page

select ...
from wwsbr_all_items i,
     wwsbr_all_folders p
where i.folder_id = p.id
  and i.caid = p.caid
  and i.language = p.language

9.5.2 Querying Translatable Objects

If an object is translatable (that is, it resides in a page group for which translations are enabled and it includes languages in its key), you must observe the following rules:

  • If the object (or its current version) is translated, a row will exist for the translation. To select the row for the current session language, compare the value of the language column to the function wwctx_api.get_nls_language().

  • If the object is not translated, select the row for the page group's default language.

Example 9-4 selects the translated page display name for all pages in a given page group.

Example 9-4 Selecting a Translated Page Display Name

select p.display_name title
from wwsbr_all_folders p
where p.caid = 53
and (p.language = wwctx_api.get_nls_language -- The current language.
  or (exists -- A row for the page in the page group default language.
      (select pg.id
       from wwsbr_all_content_areas pg
       where pg.id = p.caid
         and pg.default_language = p.language
      and not exists -- A row for the page in the current language.
       (select p2.id
        from wwsbr_all_folders p2
        where p2.id = p.id
          and p2.language = wwctx_api.get_nls_language


When writing PL/SQL routines that refer to the current session language, call wwctx_api.get_nls_language once and store it in a variable. Referring to the variable in any following SQL statements, rather than calling the API multiple times, results in better performing code.

9.5.3 Selecting Data for the Current User

To select data for the current user, use the function wwctx_api.get_user. Example 9-5 selects items created by the current user.

Example 9-5 Selecting Items Created by the Current User

  l_user varchar2(60);
  l_user := wwctx_api.get_user;
  select ...
  from wwsbr_all_items
  where creator = l_user

9.6 Code Samples

The code samples provided in the next few chapters are intended to provide some examples of how you can use the content management and associated APIs. Not all of the APIs are used in these samples, and the samples provided do not utilize all the parameters, constants, exceptions, and so on available to the APIs.

More on OTN

For a full list of the public APIs available with Oracle Portal and their parameters, constants, exceptions and so on, refer to the Oracle Portal PL/SQL API Reference on Portal Center:


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

The examples are written to be run from SQL*Plus. If running from other environments, for example Portal Services, you may need to make changes to the code given the context in which it is executing.

The examples, with the exception of Chapter 14, "Creating Multi-Lingual Content", which deals with translations, assume that the language context is set to the default language of the page group being manipulated. This simplifies the WHERE clause on any SQL statements. Objects always have a translation in the default language therefore the queries will return a single row as appropriate.

If this assumption on the language code does not hold true for your code, refer back to Section 9.5.2, "Querying Translatable Objects" for an example of how to extend the WHERE clause to deal with multiple translations or the non-existence of a translation in the given language context.