html>

Overview | Package | Object Type | Deprecated | Index | Help
| OracleAS Portal PL/SQL API Reference - 9.0.4
PREV PACKAGE NEXT PACKAGE | FRAMES NO FRAMES
VIEW SUMMARY | VIEW DETAIL

## Secure Content Repository Views

This document describes the supported database views in the OracleAS Portal content repository.

These views allow developers to build custom queries against the content repository. Direct access to other tables and views in the repository is not supported, as the definition of those tables and views may change between releases.

These views are backward compatible with the Oracle9iAS Portal Release 1 (3.0.9) content area views, except as noted. To maintain compatibility, many of the view and column names continue to use Release 1 terminology, i.e. Page Groups are referred to as Content Areas, etc.

Release 2 Terminology | Release 1 Terminology
Page Groups | Content Areas
Pages | Folders
Navigation Pages | Navigation Bars

Other than view and column names, this document employs OracleAS Portal Release 2 terminology.

### Differences Between 3.0.9 and 9.0.4 Views

In this release, OracleAS Portal 9.0.4 views are backwards compatible with 3.0.9 views, with the following exceptions:

- Columns NAVIGATION_BAR_ID, NAVIGATION_BAR_CAID, DISPLAY_FULL_SCREEN, IS_CACHING_ON, and SEARCH_VALUE in views WWSBR_ALL_FOLDERS and WWSBR_USER_FOLDERS have been deprecated. The columns are retained for backward compatibility, but their values are always null.
- Columns HIDEINBROWSE, SEQ, AUTHOR_SEQ, CREATEDATE_SEQ, ITEMTYPE_SEQ have been removed from the view WWSBR_ALL_ITEMS.
- Column TYPE in view WWSBR_ALL_ITEMS has been deprecated.
- Column BASE_ITEM_TYPE has been removed from the view WWSBR_ITEM_TYPES.
- Column ALIGNMENT in view WWSBR_ALL_NAVIGATION_BARS has been deprecated.

New views and columns to support the new Release 2 functionality have been added to the API set, as noted in this documentation. For a full description of the new OracleAS Portal Release 2 functionality and how it compares to the Oracle Portal Release 1 (3.0.9) release, refer to the OracleAS Portal Release 2 Upgrade Guide or the Technical Note entitled What Happened to Content Areas? Both these documents are available on Portal Center at http://portalcenter.oracle.com.

### General Rules for All Views

- 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. For example, when joining an item to it's page, the join is:

>     select ...
>     from WWSBR_ALL_ITEMS items, WWSBR_ALL_FOLDERS pages
>     where items.folder_id = pages.id
>     and   items.caid = pages.caid
>     and   items.language = pages.language

- If an object is translatable (i.e., it resides in a page group for which translations are enabled and it includes language 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 that row for the current browser session, 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 .

> For example, to select the translations in the current browser session for all pages in a given page group:

>     select  p.display_name title
>       from  wwsbr_all_folders p
>       where p.caid = 1
>       and   (p.language = wwctx_api.get_nls_language() -- get the current language for the browser session
>             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()
>                   )
>                )
>             )

- To select data for the current user, use the function WWCTX_API.GET_USER.

> For example, to select items created by the current user:

>     select ...
>     from WWSBR_ALL_ITEMS
>     where creator = WWCTX_API.GET_USER

- Privileges affecting these views may be granted directly to the user, to a group the user belongs to, or derived from page group or global privileges.
- 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.
- The views implement the underlying user and group privileges, and therefore require that the session context is set to execute as a specific OracleAS Portal user. If the views are being accessed from an OracleAS Portal browser session (a database provider, PL/SQL item, or stored procedure called from the OracleAS Portal Navigator), context is set automatically to the user who is currently logged into the portal and will also use the user's current language setting. If the view is accessed from a Web portlet or an external application (e.g. SQL*Plus), you must set the context to a specific user using the procedure WWCTX_API.SET_CONTEXT.

> For example, to set context to the user "GEORGE" with the password "welcome":

>     DECLARE
>       p_user_name VARCHAR2(60) := 'GEORGE';
>       p_password VARCHAR2(60) := 'welcome';
>       p_company VARCHAR2(60) := NULL;
>     BEGIN
>       wwctx_api.set_context(p_user_name,p_password,p_company);
>     EXCEPTION
>        WHEN OTHERS THEN
>           dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
>           RAISE;
>     END;

> It is currently not possible to change the language context in an external environment. All programs execute with the default language of the portal.

### Using sbrapi.sql to Grant Access to Views

Use the script sbrapi.sql, located in the directory ORACLE_HOME/portal/admin/plsql/wws, to grant access to these views. This script:

- Takes the name of a database schema as a parameter.
- Grants SELECT on all the secured content repository views.
- Grants EXECUTE on the WWSBR_API package to the passed SCHEMA.

You must run sbrapi.sql as the Portal schema owner (SCHEMA_OWNER).

>

View Summary
`WWSBR_ALL_FOLDERS` | Describes pages on which the current user has view privilege or higher.
`WWSBR_USER_FOLDERS` | Describes pages on which the current user has manage privilege or higher.
`WWSBR_FOLDER_PERSPECTIVES` | Describes the perspectives associated with pages viewable by the current user.
`WWSBR_FOLDER_ATTRIBUTES` | Describes the attributes associated with pages viewable by the current user.
`WWSBR_ALL_ITEMS` | Describes the items viewable by the current user.
`WWSBR_ITEM_PERSPECTIVES` | Describes the perspectives associated with items viewable by the current user.
`WWSBR_ITEM_ATTRIBUTES` | Describes the attributes and attribute values associated with items viewable by the current user.
`WWSBR_ALL_CATEGORIES` | Describes all categories.
`WWSBR_ALL_PERSPECTIVES` | Describes all perspectives.
`WWSBR_ALL_CONTENT_AREAS` | Describes all page groups.
`WWSBR_ATTRIBUTES` | Describes all attributes.
`WWSBR_ITEM_TYPES` | Describes all item types.
`WWSBR_CONTENT_AREA_ITEM_TYPES` | Describes which item types are available to which page groups.
`WWSBR_ITEM_TYPE_ATTRIBUTES` | Describes the attributes that are available within each item type.
`WWSBR_FOLDER_TYPES` | Describes all page types.
`WWSBR_FOLDER_TYPE_ATTRIBUTES` | Describes the attributes that are available within each page type.
`WWSBR_ALL_NAVIGATION_BARS` | Describes all navigation pages viewable by the current user.
`WWSBR_ALL_STYLES` | Describes all styles.
`WWSBR_ALL_FOLDER_REGIONS` | Describes all page regions on pages viewable by the current user.

View Detail

l

### WWSBR_ALL_FOLDERS

Describes pages on which the current user has view privilege or higher.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     PARENT_ID                                          NUMBER
     NAME                                      NOT NULL VARCHAR2(60)
     DISPLAY_NAME                              NOT NULL VARCHAR2(256)
     CATEGORY_ID                                        NUMBER
     CATEGORY_SITEID                                    NUMBER
     DESCRIPTION                                        VARCHAR2(2000)
     TYPE_ID                                   NOT NULL NUMBER
     TYPE_SITEID                               NOT NULL NUMBER
     BASE_TYPE_ID                              NOT NULL NUMBER
     IS_PORTLET                                NOT NULL NUMBER(1)
     IS_CACHING_ON                                      VARCHAR2
     CACHE_MODE                                         NUMBER(1)
     SUB_FOLDER_SEQUENCE                                NUMBER
     DISPLAY_IN_PARENT_FOLDER                  NOT NULL NUMBER(1)
     ITEMVERSIONING                                     VARCHAR2(30)
     STYLE_ID                                  NOT NULL NUMBER
     STYLE_CAID                                NOT NULL NUMBER
     URL_VALUE                                          VARCHAR2(4000)
     SEARCH_VALUE                                       VARCHAR2(2000)
     PLSQL_VALUE                                        VARCHAR2(2000)
     IMAGE                                              VARCHAR2(350)
     TITLE_IMAGE_NAME                                   VARCHAR2
     ROLLOVER_IMAGE_NAME                                VARCHAR2(350)
     BANNER_IMAGE_NAME                                  VARCHAR2
     NAVIGATION_BAR_ID                                  VARCHAR2
     NAVIGATION_BAR_CAID                                VARCHAR2
     IS_PUBLIC                                 NOT NULL NUMBER(1)
     ITEM_LEVEL_SECURITY                       NOT NULL NUMBER(1)
     DISPLAY_FULL_SCREEN                                VARCHAR2
     PLSQL_FOLDER_EXECUTOR                              VARCHAR2(30)
     CREATEDATE                                         DATE
     CREATOR                                            VARCHAR2(256)
     UPDATEDATE                                         DATE
     UPDATOR                                            VARCHAR2(256)

Primary Key:
  `id`
  `caid`
  `language`

Columns:
  `id` - unique identifier of the page within the page group
  `caid` - id of the page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `parent_id` - id of the parent page. "0" (zero) if root page of page group
  `name` - internal name for the page. Must be unique within parent page.
  `display_name` - page display name, or Title
  `category_id` - id for the category assigned to the page. FK to WWSBR_ALL_CATEGORIES.ID.
  `category_siteid` - page group id for the category assigned to the page. FK to WWSBR_ALL_CATEGORIES.CAID.
  `description` - description of the page
  `type_id` - id of the page type. FK to WWSBR_FOLDER_TYPES.ID.
  `type_siteid` - page group id for the page type. FK to WWSBR_FOLDER_TYPES.CAID.
  `base_type_id` - the id of the page base type. FK to WWSBR_FOLDER_TYPES.ID where WWSBR_FOLDER_TYPES.IS_BASE_FOLDER_TYPE = 1.
  `is_portlet` - indicates if the page is published as a portlet. Valid values: 0 - not a portlet, 1 - is a portlet.
  `is_caching_on` - DEPRECATED. This column is retained for backwards compatibility only. Its value is always null.
  `cache_mode` - the caching mode for the page. Valid values: 2 - no caching, 1 - Cache Page Definition Only, 0 - Cache Page Definition And Content For x Minutes, 4 - Cache Page Definition Only at System Level, 3 - Cache Page Definition and Content at System Level for x Minutes.
  `sub_folder_sequence` - the sequence (order) of the page on its parent page's sub page region
  `display_in_parent_folder` - indicates if the page is displayed on its parent page's sub page region
`itemversioning`- Determines the level of item versioning for the page. Valid values are: versionnone - No Versioning, versionsimple - Simple Versioning, versionaudit - Audit Versioning. Note that the page property overrides the page group setting for `itemversioning`, but only if the page is set to a higher value. Thus, if the page group is set to `versionnone`, the page can specify all three levels. If the page group specifies `versionsimple`, the page can implement either `versionsimple`or `versionaudit`- a setting of `versionnone`is ignored. If the page group specifies `versionaudit`, the page can only implement `versionaudit`- the other settings will be ignored.
  `style_id` - id for the style used by the page. FK to WWSBR_ALL_STYLES.ID.
  `style_caid` - page group id for the style used by the page. FK to WWSBR_ALL_STYLES.CAID.
  `url_value` - url value for a URL type page
`search_value`- DEPRECATED. Search pages are no longer supported in Release 2. This column is retained for backwards compatibility only.
  `plsql_value` - plsql value for a PL/SQL type page
  `image` - unique name of the image associated with the page. Matches the NAME column in the document table.
  `title_image_name` - DEPRECATED. This column is retained for backward compatibility only.
  `rollover_image_name` - the unique name of the rollover image associated with the page. Matches the NAME column in the document table.
  `banner_image_name` - DEPRECATED. Banner images are no longer supported in Release 2. This column is retained for backward compatibility only.
  `navigation_bar_id` - DEPRECATED. This column is retained for backward compatibility only.
  `navigation_bar_caid` - DEPRECATED. This column is retained for backward compatibility only.
  `is_public` - indicates that the page is viewable by PUBLIC. Valid values are 0 - not public, 1 - public.
  `item_level_security` - indicates that item level security is enabled for the page. Valid values are 0 - ILS disabled, 1 - ILS enabled.
  `plsql_folder_executor` - for PL/SQL type pages, the database schema used to execute the PL/SQL code
  `createdate` - date the page was created
  `creator` - username of the person who created the page
  `updatedate` - date the page was last updated
  `updator` - username of the person who last updated the page

### WWSBR_USER_FOLDERS

Describes pages on which the current user has manage privilege or higher.

     Name                                      Null?    Type
     ----------------------------------------- -------- ------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     PARENT_ID                                          NUMBER
     NAME                                      NOT NULL VARCHAR2(60)
     DISPLAY_NAME                              NOT NULL VARCHAR2(256)
     CATEGORY_ID                                        NUMBER
     CATEGORY_CAID                                      NUMBER
     DESCRIPTION                                        VARCHAR2(2000)
     TYPE_ID                                   NOT NULL NUMBER
     TYPE_CAID                                 NOT NULL NUMBER
     BASE_TYPE_ID                              NOT NULL NUMBER
     IS_PORTLET                                NOT NULL NUMBER(1)
     IS_CACHING_ON                                      VARCHAR2(0)
     CACHE_MODE                                         NUMBER(1)
     SUB_FOLDER_SEQUENCE                                NUMBER
     DISPLAY_IN_PARENT_FOLDER                  NOT NULL NUMBER(1)
     ITEMVERSIONING                                     VARCHAR2(30)
     STYLE_ID                                  NOT NULL NUMBER
     STYLE_CAID                                NOT NULL NUMBER
     URL_VALUE                                          VARCHAR2(4000)
     SEARCH_VALUE                                       VARCHAR2(2000)
     PLSQL_VALUE                                        VARCHAR2(2000)
     IMAGE                                              VARCHAR2(350)
     TITLE_IMAGE_NAME                                   VARCHAR2(0)
     ROLLOVER_IMAGE_NAME                                VARCHAR2(350)
     BANNER_IMAGE_NAME                                  VARCHAR2(0)
     NAVIGATION_BAR_ID                                  VARCHAR2(0)
     NAVIGATION_BAR_CAID                                VARCHAR2(0)
     IS_PUBLIC                                 NOT NULL NUMBER(1)
     ITEM_LEVEL_SECURITY                       NOT NULL NUMBER(1)
     DISPLAY_FULL_SCREEN                                VARCHAR2(0)
     PLSQL_FOLDER_EXECUTOR                              VARCHAR2(30)
     CREATEDATE                                         DATE
     CREATOR                                            VARCHAR2(256)
     UPDATEDATE                                         DATE
     UPDATOR                                            VARCHAR2(256)

Primary Key:
  `id`
  `caid`
  `language`

Columns:
  `id` - unique identifier of the page within the page group
  `caid` - id of the page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `parent_id` - id of the parent page. "0" (zero) if the root page of page group.
  `name` - internal name for the page. Must be unique within parent page.
  `display_name` - page's display name (Title)
  `category_id` - id for the category assigned to the page. FK to WWSBR_ALL_CATEGORIES.ID.
  `category_siteid` - page group id for the category assigned to the page. FK to WWSBR_ALL_CATEGORIES.CAID.
  `description` - description of the page
  `type_id` - id of the page type. FK to WWSBR_FOLDER_TYPES.ID.
  `type_siteid` - page group id for the page type. FK to WWSBR_FOLDER_TYPES.CAID.
  `base_type_id` - id of the page base type. FK to WWSBR_FOLDER_TYPES.ID where WWSBR_FOLDER_TYPES.IS_BASE_FOLDER_TYPE = 1.
  `is_portlet` - indicates if the page is published as a portlet. Valid values: 0 - not a portlet, 1 - is a portlet.
  `is_caching_on` - DEPRECATED. This column is retained for backwards compatibility only. Its value is always null.
  `cache_mode` - caching mode for the page. Valid values: 2 - no caching, 1 - Cache Page Definition Only, 0 - Cache Page Definition And Content For x Minutes, 4 - Cache Page Definition Only at System Level, 3 - Cache Page Definition and Content at System Level for x Minutes.
  `sub_folder_sequence` - the sequence (order) of the page on its parent page's sub page region
  `display_in_parent_folder` - indicates if the page is displayed on its parent page's sub page region
  `itemversioning` - determines the level of item versioning for the page. Valid values are: versionnone - No Versioning, versionsimple - Simple Versioning, versionaudit - Audit Versioning. Note that the page property overrides the page group setting for `itemversioning`, but only if the page is set to a higher value. Thus, if the page group is set to `versionnone`, the page can specify all three levels. If the page group specifies `versionsimple`, the page can implement either `versionsimple` or `versionaudit` - a setting of `versionnone` is ignored. If the page group specifies `versionaudit`, the page can only implement `versionaudit` - the other settings will be ignored.
  `style_id` - id for the style used by the page. FK to WWSBR_ALL_STYLES.ID.
  `style_caid` - page group id for the style used by the page. FK to WWSBR_ALL_STYLES.CAID.
  `url_value` - url value for a URL type page
  `search_value` - DEPRECATED. Search pages are no longer supported in Release 2. This column is retained for backwards compatibility only.
  `plsql_value` - plsql value for a PL/SQL type page
  `image` - unique name of the image associated with the page. Matches the NAME column in the document table.
  `title_image_name` - DEPRECATED. This column is retained for backward compatibility only.
  `rollover_image_name`- unique name of the rollover image associated with the page. Matches the NAME column in the document table .
  `banner_image_name`- DEPRECATED. Banner images are no longer supported in Release 2. This column is retained for backward compatibility only.
  `navigation_bar_id` - DEPRECATED. This column is retained for backward compatibility only.
  `navigation_bar_caid` - DEPRECATED. This column is retained for backward compatibility only.
  `is_public` - indicates that page is viewable by PUBLIC. Valid values are 0 - not public, 1 - public.
  `item_level_security` - indicates that item level security is enabled for the page. Valid values are 0 - ILS disabled, 1 - ILS enabled.
  `plsql_folder_executor` - for PL/SQL type pages, the database schema used to execute the PL/SQL code.
  `createdate` - date the page was created.
  `creator` - username of the person who created the page
  `updatedate` - date the page was last updated
  `updator` - username of the person who last updated the page

### WWSBR_FOLDER_PERSPECTIVES

Describes the perspectives associated with pages viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     FOLDER_CAID                               NOT NULL NUMBER
     FOLDER_ID                                 NOT NULL NUMBER
     PERSPECTIVE_ID                            NOT NULL NUMBER
     PERSPECTIVE_CAID                          NOT NULL NUMBER
     PERSPECTIVE_NAME                                   VARCHAR2(60)

Primary Key:
  `folder_caid`
  `folder_id`
  `perspective_id`
  `perspective_caid`

Columns:
  `folder_caid` - id of the page's page group. FK to WWSBR_ALL|USER_FOLDERS.CAID.
`folder_id` - id of the page. FK to WWSBR_ALL|USER_FOLDERS.ID.
  `perspective_id` - id of the perspective. FK to WWSBR_ALL_PERSPECTIVES.ID.
  `perspective_caid` - id of the perspective's page group. FK to WWSBR_ALL_PERSPECTIVES.CAID.
  `perspective_name` - internal name of the perspective. FK to WWSBR_ALL_PERSPECTIVES.NAME.

### WWSBR_FOLDER_ATTRIBUTES

Describes the attributes associated with pages viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     FOLDER_CAID                               NOT NULL NUMBER
     FOLDER_ID                                 NOT NULL NUMBER
     ATTRIBUTE_ID                                       NUMBER
     ATTRIBUTE_CAID                                     NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     VALUETYPE                                 NOT NULL VARCHAR2(30)
     VALUE                                              VARCHAR2(2000)

Primary Key:
  `folder_caid`
  `folder_id`
  `attribute_id`
  `attribute_caid`
  `language`

Columns:
  `folder_caid` - id of the page's page group. FK to WWSBR_ALL|USER_FOLDERS.CAID.
  `folder_id` - id of the page. FK to WWSBR_ALL|USER_FOLDERS.ID.
  `attribute_id` - id of the attribute. FK to WWSBR_ATTRIBUTES.ID.
  `attribute_caid` - id of the the attribute's page group. FK to WWSBR_ATTRIBUTES.CAID.
  `language` - language code. FK to WWSBR_ALL|USER_FOLDERS.LANGUAGE and WWSBR_ATTRIBUTES.LANGUAGE.
  `valuetype` - data type of the attribute
  `value` - value of the attribute

### WWSBR_ALL_ITEMS

Describes the items viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     MASTERID                                  NOT NULL NUMBER
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     IS_CURRENT_VERSION                        NOT NULL NUMBER(1)
     FOLDER_ID                                 NOT NULL NUMBER
     FOLDER_REGION_ID                          NOT NULL NUMBER
     NAME                                      NOT NULL VARCHAR2(256)
     DISPLAY_NAME                                       VARCHAR2(256)
     ITEMTYPE                                  NOT NULL VARCHAR2(30)
     TYPE                                      NOT NULL VARCHAR2(30)
     SUBTYPE                                            VARCHAR2(40)
     SUBTYPE_CAID                                       NUMBER
     PARENT_ITEM_ID                                     NUMBER
     CATEGORY_ID                                        NUMBER
     CATEGORY_CAID                                      NUMBER
     AUTHOR                                             VARCHAR2(50)
     DESCRIPTION                                        VARCHAR2(2000)
     PUBLISH_DATE                              NOT NULL DATE
     EXPIREMODE                                         VARCHAR2(90)
     EXPIRENUMBER                                       NUMBER
     EXPIREDATE                                         DATE
     IMAGE                                              VARCHAR2(350)
     KEYWORDS                                           VARCHAR2(2000)
     URL                                                VARCHAR2(4000)
     FILENAME                                           VARCHAR2(350)
     TEXT                                               CLOB
     FOLDER_LINK_ID                                     NUMBER
     FOLDER_LINK_CAID                                   NUMBER
     ACTIVE                                    NOT NULL NUMBER(1)
     CAN_BE_CHECKEDOUT                                  NUMBER(1)
     IS_ITEM_CHECKEDOUT                                 NUMBER(1)
     CHECKER_USERNAME                                   VARCHAR2(30)
     CHECKOUT_DATE                                      DATE
     FULLSCREEN                                NOT NULL NUMBER(1)
     INPLACE                                   NOT NULL NUMBER(1)
     CREATEDATE                                NOT NULL DATE
     CREATOR                                   NOT NULL VARCHAR2(256)
     UPDATEDATE                                         DATE
     UPDATOR                                            VARCHAR2(256)

To avoid duplicate rows when querying for currently published items, always include the columns language, active (active=1) and is_current_version (is_current_version=1). For example, to select all items on a given page (folder_id =1) and a given page group (caid = 75):

>     select i.display_name title, i.id latestversion
>       from  wwsbr_all_items i
>       where i.folder_id = 1
>       and   i.caid = 75
>       and   i.active = 1
>       and   i.is_current_version = 1
>       and   (i.language = wwctx_api.get_nls_language -- the current session language
>             or (    exists -- a row for the item in the page group default language
>                     (select pg.id
>                      from  wwsbr_all_content_areas pg
>                      where pg.id = i.caid
>                      and   pg.default_language = i.language
>                      )
>                  and not exists -- a row for the item in the current language
>                     (select i2.id
>                      from  wwsbr_all_items i2
>                      where i2.id = i.id
>                      and i2.language = wwctx_api.get_nls_language
>                      and i2.active = i.active
>                      and i2.is_current_version = i.is_current_version
>                      )
>                )
>             )
>      order by i.id

Primary Key:

  `id`
  `caid`
  `language`
  `active = 1`
  `is_current_version = 1`

Columns:
  `masterid` - master id for the item. This value is used to relate multiple versions and/or translations of a item. All versions and translations of an item will have the same master id.
  `id` - id of the item. Each version of an item receives a new id. If translations and approvals are enabled, multiple rows may exist for the same id.
  `caid` - id of the item's page group. FK to WWSBR_ALL|USER_FOLDERS.CAID and WWSBR_ALL_CONTENT_AREAS.ID.
  `language -` language code
  `is_current_version` - indicates that the item is the current version. Valid values: 0 - not current version, 1 - current version.
  `folder_id` - id of the page containing the item. FK to WWSBR_ALL|USER_FOLDERS.ID and WWSBR_ALL_FOLDER_REGIONS.FOLDER_ID.
  `folder_region_id` - id of the region containing the item. FK to WWSBR_ALL_FOLDER_REGIONS.ID.
  `name` - internal name of the item. The name is a system generated GUID (Global Unique Identifier) value that can be used as an alternate id. The name is also used in the direct access URL format "/<DAD>/url/item/<NAME>" (e.g. http://mysite.mydomain:7780/pls/portal/url/item/979DAAC2FA541226E034080020F7DA4B). Use this format when building durable links to items, as the name of the item will not change even if the item is moved to another page.
  `display_name -` display name or title of the item
  `itemtype` - the base item type; e.g. "basefile", "baseimage", "basetext", "baseurl", etc.
  `type` - DEPRECATED. This column is retained for backward compatibility only.
  subtype - id of the item type. FK to WWSBR_ITEM_TYPES.ID.
  `subtype_caid`- id of the item type. FK to WWSBR_ITEM_TYPES.ID
  `parent_item_id` - id of the parent item, if the item is a sub item. Value is "0" (zero) if item is not a sub item.
  `category_id` - id for the category assigned to the item. FK to WWSBR_ALL_CATEGORIES.ID.
  `category_caid` - page group id for the category assigned to the item. FK to WWSBR_ALL_CATEGORIES.CAID.
  `author` - author of the item
  `description` - description of the item
  `publish_date` - date the item will be published
  `expiremode` - expiration mode for the item. Valid values: 'PERMANENT' (item never expires), 'NUMBER' (item expires in EXPIRENUMBER days from CREATEDATE), or 'DATE' (item expires on EXPIREDATE)..
  `expirenumber` - number of days after CREATEDATE when item will expire. Only valid when EXPIREMODE = 'NUMBER'.
  `expiredate` - date when item will expire. Only valid when EXPIREMODE = 'DATE'.
  `image` - image associated with the item. This value may be a reference (e.g. a path to an image stored on the file system or in the content repository) or a unique document name that matches the NAME column in the document table.
  ``keywords - keywords for the item
  `url` - URL for a url type item
  `filename` - unique name of the file associated with a file type item. Matches the NAME column in the document table.
  `text` - text for a text type item.
  `folder_link_id` - page id for a page link type item. FK to WWSBR_ALL|USER_FOLDERS.ID.
  `folder_link_caid` - page group id for a page link type item. FK to WWSBR_ALL|USER_FOLDERS.CAID.
  `active` - indicates the active status of the item. Valid values: 1 - active, 2 - pending approval as new and current version, 3 - pending approval as new and not current version, 0 - pending approval, -1 - deleted, -7 - rejected, -9 - reject deleted.
  `can_be_checkedout` - indicates if the item can be checked out. Valid values: 0 - cannot be checked out, 1 - can be checked out.
  `is_item_checkedout` - indicates if the item is checked out. Valid values: 0 - item is not checked out, 1- item is checked out
  `checker_username` - username of the user who checked out the item
  `checkout_date` - date the item was checked out
  `fullscreen` - indicates the rendering behavior for an item shown as a link. Valid values: 0 - clicking the link displays the item in a new browser window, 1 - clicking the link displays the item in the same browser window.
  `inplace` - indicates the rendering behavior for a text or PL/SQL item. Valid values: 0 - a link to the item is displayed; the rendering behavior of the link depends on the setting of the FULLSCREEN column, 1 - the item is rendered in-place in the region.
  `createdate` - date the item was created.
  `creator` - username of the person who created the item.
  `updatedate` - date the item was last updated.
  `updator` - username of the person who last updated the item.

### WWSBR_ITEM_PERSPECTIVES

Describes the perspectives associated with items viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ITEM_CAID                                 NOT NULL NUMBER
     ITEM_ID                                   NOT NULL NUMBER
     ITEM_MASTERID                                      NUMBER
     PERSPECTIVE_ID                            NOT NULL NUMBER
     PERSPECTIVE_CAID                          NOT NULL NUMBER
     PERSPECTIVE_NAME                                   VARCHAR2(60)

Primary Key:
  `item_caid`
  `item_id`
  `item_masterid`
  `perspective_id`
  `perspective_caid`

Columns:
  `item_caid` - id of the item's page group. FK to WWSBR_ALL_ITEMS.CAID.
  `item_id` - id of the item. FK to WWSBR_ALL_ITEMS.ID.
  `item_masterid` - master id of the item. FK to WWSBR_ALL_ITEMS.MASTERID.
  `perspective_id` - id of the perspective. FK to WWSBR_ALL_PERSPECTIVES.ID.
  `perspective_caid` - id of the perspective's page group. FK to WWSBR_ALL_PERSPECTIVES.CAID.
  `perspective_name` - internal name of the perspective. FK to WWSBR_ALL_PERSPECTIVES.NAME.

### WWSBR_ITEM_ATTRIBUTES

Describes the attributes and attribute values associated with items viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ITEM_CAID                                 NOT NULL NUMBER
     ITEM_MASTERID                             NOT NULL NUMBER
     ATTRIBUTE_ID                                       NUMBER
     ATTRIBUTE_CAID                                     NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     VALUETYPE                                 NOT NULL VARCHAR2(30)
     VALUE                                              VARCHAR2(2000)

Primary Key:
  `item_caid`
  `item_masterid`
  `attribute_id`
  `attribute_caid`
  `language`

Columns:
  `item_caid` - id of the items's page group. FK to WWSBR_ALL_ITEMS.CAID.
  `item_masterid` - master id of the item. FK to WWSBR_ALL_ITEMS.MASTERID.
  `attribute_id` - id of the the attribute. FK to WWSBR_ATTRIBUTES.ID.
  `attribute_caid` - id of the the attribute's page group. FK to WWSBR_ATTRIBUTES.CAID.
  `language` - language code. FK to WWSBR_ALL_ITEMS.LANGUAGE and WWSBR_ATTRIBUTES.LANGUAGE.
  `valuetype` - data type of the attribute
  `value` - value of the attribute

### WWSBR_ALL_CATEGORIES

Describes all categories.

    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    ID                                        NOT NULL NUMBER
    CAID                                      NOT NULL NUMBER
    PARENT_ID                                 NOT NULL NUMBER
    LANGUAGE                                  NOT NULL VARCHAR2(30)
    NAME                                               VARCHAR2(60)
    DISPLAY_NAME                              NOT NULL VARCHAR2(240)

Primary Key:
  `id`
  `caid`
  `language`

Columns:
  `id` - id of the category
  `caid` - id of the category's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID
  `parent_id` - id of the the category's parent category. "0" (zero) if the category has no parent.
  `language` - language code
  `name` - internal name of the category. Unique within parent category.
  `display_name` - display name (title) of the category

### WWSBR_ALL_PERSPECTIVES

Describes all perspectives.

    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    ID                                        NOT NULL NUMBER
    CAID                                      NOT NULL NUMBER
    PARENT_ID                                 NOT NULL NUMBER
    LANGUAGE                                  NOT NULL VARCHAR2(30)
    NAME                                               VARCHAR2(60)
    DISPLAY_NAME                              NOT NULL VARCHAR2(240)

Primary Key:
  `id`
  `caid`
  `language`

Columns:
  `id` - id of the perspective.
  `caid` - id of the perspective's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `parent_id` - id of the the perspective's parent perspective. "0" (zero) if the perspective has no parent
  `language` - language code
  `name` - internal name of the perspective. Unique within parent perspective.
  `display_name` - display name (title) of the perspective

### WWSBR_ALL_CONTENT_AREAS

Describes all page groups. This view shows all page groups in the Repository, regardless of the current user's privileges.

    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    ID                                        NOT NULL NUMBER
    LANGUAGE                                  NOT NULL VARCHAR2(30)
    NAME                                      NOT NULL VARCHAR2(60)
    DISPLAY_NAME                              NOT NULL VARCHAR2(240)
    DEFAULT_LANGUAGE                          NOT NULL VARCHAR2(30)

Primary Key:
  `id
  language`

Alternate Key:
  `name
  language`

Columns:
  `id` - id of the page group
  `language` - language code
  `name` - internal name of the page group
  `display_name` - display name (title) of the page group
  `default_language` - code for the default language of the page group

### WWSBR_ATTRIBUTES

Describes all attributes.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     NAME                                      NOT NULL VARCHAR2(256)
     DISPLAY_NAME                                       VARCHAR2(256)
     IS_BASE_ATTRIBUTE                         NOT NULL NUMBER(1)
     DATA_TYPE                                 NOT NULL VARCHAR2(30)
     LENGTH                                             NUMBER(5)
     IS_TRANSLATABLE                           NOT NULL NUMBER(1)

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `name
  caid
  language`

Columns:
  `id` - id of the attribute
  `caid` - id of the attribute's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `name` - internal name of the attribute. Functions as alternate key (unique within page group).
  `display_name` - display name (title) of the attribute
  `is_base_attribute` - indicates that the attribute is a base attribute. Valid values: 0 - not a base attribute, 1 - is a base attribute
  `data_type` - data type of the attribute
  `length` - length of the attribute
  `is_translatable` - indicates that the attribute is translatable, meaning that a different value can be stored for each translation. If the attribute is not translatable, the same value is stored for all translations. Valid values: 0 - not translatable, 1 - is translatable.

### WWSBR_ITEM_TYPES

Describes all item types.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     NAME                                               VARCHAR2(30)
     DISPLAY_NAME                                       VARCHAR2(256)
     DESCRIPTION                                        VARCHAR2(2000)
     IS_BASE_ITEM_TYPE                         NOT NULL NUMBER
     BASE_ITEM_TYPE_ID                                  NUMBER

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `name
  caid
  language`

Columns:
  `id` - id of the item type
  `caid` - id of the item types's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `name` - internal name of the item type. Functions as alternate key (unique within page group).
  `display_name` - display name (title) of the item type
  `description` - description of the item type
  `is_base_item_type` - indicates that the item type is a base item type. Valid values: 0 - item type is not a base item type, 1 - item type is a base item type. Note that only non-base item types can be assigned to items.
  `base_item_type_id` - id of the base item type on which this item type is based

### WWSBR_CONTENT_AREA_ITEM_TYPES

Describes which item types are available to which page groups. An item type cannot be assigned to an item unless the item type is available within the item's page group.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     CAID                                      NOT NULL NUMBER
     ITEM_TYPE_ID                              NOT NULL NUMBER
     ITEM_TYPE_CAID                            NOT NULL NUMBER

Primary Key:
  `caid
  item_type_id
  item_type_caid`

Columns:
  `caid` - id of the page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `item_type_id` - id of the item type. FK to WWSBR_ITEM_TYPES.ID.
  `item_type_caid -` id of the item type's page group. FK to WWSBR_ITEM_TYPES.CAID.

### WWSBR_ITEM_TYPE_ATTRIBUTES

Describes the attributes that are available within each item type.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     ITEM_TYPE_ID                              NOT NULL NUMBER
     ITEM_TYPE_CAID                            NOT NULL NUMBER
     ATTRIBUTE_ID                              NOT NULL NUMBER
     ATTRIBUTE_CAID                            NOT NULL NUMBER
     HIDDEN_ATTRIBUTE                          NOT NULL NUMBER(1)
     DEFAULT_VALUE                                      VARCHAR2(2000)

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `item_type_id`
  item_type_caid
  attribute_id
  attribute_caid

Columns:
  `id` - id of the item type attribute row
  `caid` - id of the page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `item_type_id` - id of the item type. FK to WWSBR_ITEM_TYPES.ID.
  `item_type_caid` - id of the item type's page group. FK to WWSBR_ITEM_TYPES.CAID.
  `attribute_id` - id of the attribute. FK to WWSBR_ATTRIBUTES.ID.
  `attribute_caid` - id of the attribute's page group. FK to WWSBR_ATTRIBUTES.CAID.
  `hidden_attribute` - indicates if the attribute should be hidden. Valid values: 0 - attribute is hidden, 1 - attribute is not hidden. This column is used for certain built-in item types and is not intended for customer use.
  `default_value` - default value of the attribute. The default value is always stored as VARCHAR2, regardless of the attribute's base type

### WWSBR_FOLDER_TYPES

Describes all page types.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     NAME                                               VARCHAR2(30)
     DISPLAY_NAME                                       VARCHAR2(256)
     DESCRIPTION                                        VARCHAR2(2000)
     IS_BASE_FOLDER_TYPE                       NOT NULL NUMBER
     BASE_FOLDER_TYPE_ID                                NUMBER

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `name
  caid
  language`

Columns:
  `id` - id of the page type
  `caid` - id of the page types's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `name` - internal name of the page type. Functions as alternate key (unique within page group).
  `display_name` - display name (title) of the page type
  `description` - description of the page type
  `is_base_folder_type` - indicates that the page type is a base page type. Valid values: 0 - is not a base page type, 1 - is a base page type
  `base_folder_type_id` - id of the base page type on which this page type is based

### WWSBR_FOLDER_TYPE_ATTRIBUTES

Describes the attributes that are available within each page type.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     FOLDER_TYPE_ID                            NOT NULL NUMBER
     FOLDER_TYPE_CAID                          NOT NULL NUMBER
     ATTRIBUTE_ID                              NOT NULL NUMBER
     ATTRIBUTE_CAID                            NOT NULL NUMBER
     DEFAULT_VALUE                                      VARCHAR2(2000)

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `folder_type_id`
  folder_type_caid
  attribute_id
  attribute_caid

Columns:
  `id` - id of the page type attribute row
  `caid` - id of the page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `folder_type_id` - id of the page type. FK to WWSBR_FOLDER_TYPES.ID.
  `folder_type_caid` - id of the page type's page group. FK to WWSBR_FOLDER_TYPES.CAID.
  `attribute_id` - id of the attribute. FK to WWSBR_ATTRIBUTES.ID.
  `attribute_caid` - id of the attribute's page group. FK to WWSBR_ATTRIBUTES.CAID.
  `default_value` - default value of the attribute. The default value is always stored as VARCHAR2, regardless of the attribute's base type.

### WWSBR_ALL_STYLES

Describes all styles.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     NAME                                      NOT NULL VARCHAR2(65)
     DISPLAY_NAME                                       VARCHAR2(350)

Primary Key:
  `id
  caid
  language`

Alternate Key:
  `name
  caid
  language`

Columns:
  `id` - id of the style
  `caid` - id of the style's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `name` - internal name of the style. Unique within page group.
  `display_name` - display name (title) of the style

### WWSBR_ALL_NAVIGATION_BARS

Describes all navigation pages viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                        NOT NULL NUMBER
     CAID                                      NOT NULL NUMBER
     LANGUAGE                                  NOT NULL VARCHAR2(30)
     NAME                                      NOT NULL VARCHAR2(60)
     DISPLAY_NAME                              NOT NULL VARCHAR2(256)
     STYLE_ID                                  NOT NULL NUMBER
     STYLE_CAID                                NOT NULL NUMBER
     IS_PORTLET                                NOT NULL NUMBER(1)
     ALIGNMENT                                          VARCHAR2

Primary Key:

  `id
  caid
  language`

Alternate Key:
  `name
  caid
  language`

Columns:
  `id` - id of the navigation page
  `caid` - id of the navigation page's page group. FK to WWSBR_ALL_CONTENT_AREAS.ID.
  `language` - language code
  `name` - internal name of the navigation page. Unique within page group.
  `display_name` - display name (title) of the navigation page
  `style_id`- id for the style used by the page. FK to WWSBR_ALL_STYLES.ID.
  `style_caid` - page group id for the style used by the navigation page. FK to WWSBR_ALL_STYLES.CAID.
  `is_portlet` - indicates if the page is published as a portlet. Valid values: 0 - not a portlet, 1 - is a portlet
  `alignment` - DEPRECATED as of version 9.0.2.6

### WWSBR_ALL_FOLDER_REGIONS

Describes all page regions on pages viewable by the current user.

     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     ID                                                 NUMBER(38)
     DISPLAY_NAME                                       VARCHAR2(4000)
     LANGUAGE                                           VARCHAR2(7)
     FOLDER_ID                                          NUMBER(38)
     FOLDER_CAID                                        NUMBER(38)
     PARENT_ID                                          NUMBER(38)
     TYPE                                               VARCHAR2(30)
     SEQ                                                NUMBER
     ALLOW_CONTENT                                      NUMBER

Primary Key:
  `id
  language`

Columns:
  `id` - id of the region
  `display_name` - display name (title) of the region. May be null.
  `language` - language code
  `folder_id`- id of the page containing the region. FK to WWSBR_ALL|USER_FOLDERS.ID.
  `folder_caid` - page group id for the page containing the region. FK to WWSBR_ALL|USER_FOLDERS.CAID.
  `parent_id` - id of the parent region, if region is nested
  `type -` the region type. Valid values: NONE - undefined, TAB - a tab region, SUBPAGE - a sub page region, ITEM - an item region, PORTLET - a portlet region. Items can be only be added to ITEM regions.
  `seq` - sequence of a region within the parent region
  `allow_content -` indicates if the region allows content to be added. Valid values: 0 - does not allow content to be added, 1 - allows content to be added.

Overview | Package | Object Type | Deprecated | Index | Help
| OracleAS Portal PL/SQL API Reference - 9.0.4
PREV PACKAGE NEXT PACKAGE | FRAMES NO FRAMES
VIEW SUMMARY | VIEW DETAIL