3 APIs – XBRL Extension to Oracle XML DB

This chapter describes the application programming interfaces (APIs) provided by XBRL Extension to Oracle XML DB. It covers these topics:

Several of the examples in this chapter refer to the downloadable demonstration examples. See "Installing the Sample XBRL Repository and GAAP Demo".

Note:

  • There are two versions of some of the functions described here. The versions have the same name, except that one name has "2" appended to it. The version whose name ends in "2" returns an instance of data type ORAXBRL_CONCEPTLIST (or ORAXBRL_ITEMLIST, in the case of multiple_instance_network2). The version whose name has no "2" appended to it returns an XMLType instance.

  • When, for a given parameter, no default value is declared in the signature of a procedure or function, and no default value for that parameter is mentioned in the text, it means that an error is raised if the argument is NULL.

XBRL Repository Storage API: DBMS_ORAXBRL

The XBRL repository storage APIs are in PL/SQL package DBMS_ORAXBRL. An application can use this package to load, delete, or retrieve XBRL content from an XBRL repository. (Alternatively, you can manipulate XBRL content using WebDAV files and folders.) An application can also use this package to register a taxonomy schema or investigate discoverable taxonomy set (DTS) information.

Note:

The procedures in package DBMS_ORAXBRL that load a single document into the XBRL repository have a parameter whose value is an absolute URI that is used as the base location for any relative URIs that are found in the document to be loaded (and in any documents referenced from that document). More precisely, this base location is used to interpret relative URIs that are the values of attributes xlink:href and schemaLocation.

It is your responsibility to ensure that the location parameter you use specifies the proper base location for any such relative URIs.

Table 3-1 DBMS_ORAXBRL Repository Storage APIs

Name Description

bulkLoadXBRLFiles

Load a set of XBRL files.

createTupleDataTable

Create an object-relational XMLType storage table for a tuple element.

deleteAuxDocument

Delete an auxiliary document from the XBRL repository.

deleteFolder

Forcefully (without DTS integrity check) delete all of the taxonomies under the given XBRL repository folder.

deleteInstance

Delete an instance document from the XBRL repository.

deleteLinkbase

Delete a linkbase document from the XBRL repository. Raise an error if the linkbase is referenced by other taxonomies or instance documents in the XBRL repository, unless the force argument is 1 or greater.

deleteTaxonomy

Delete a taxonomy, including its schema and linkbases, given the location of the taxonomy schema. Raise an error if the taxonomy is referenced by other taxonomies or instance documents in the XBRL repository, unless the force argument is 1 or greater.

dropTupleDataTable

Drop the XMLType storage table for a tuple element.

DTS_files

Return a discoverable taxonomy set (DTS), given a starting document.

DTS_filelist

Return the discoverable taxonomy set (DTS) for a given URI.

getAuxDocForRepoPath

Return an auxiliary document that is associated with a document in the XBRL repository.

getDocument

Return a document that is in the XBRL repository.

isDocPathValid

Return the XBRL validity of a document in the XBRL repository.

loadAuxDocument

Load an auxiliary document into the XBRL repository.

loadInstance

Load one instance document into the XBRL repository. If the document is present in the repository, replace it.

loadLinkbase

Load one linkbase into an XBRL repository. If the document is present in the repository, replace it. DTS integrity is not checked after the call.

loadSchema

Load one taxonomy schema into an XBRL repository. If the document is present in the repository, replace it. Discoverable taxonomy set (DTS) integrity is not checked after the call.

mapPublishedLocation

Apply a published location (a URL) recursively to all files and folders under the specified Oracle XML DB Repository folder. Invoke this procedure after uploading XBRL documents if you use protocols to upload.

registerTaxonomySchema

Register one taxonomy schema with the XBRL repository. Invoke this after loadSchema. This is needed for any schema that has tuple elements.

updateDocValidity

Update the validity status of a document and associate an auxiliary document with it.

validateDTSIntegrity, validateDTSIntegrity2

Check whether all referenced taxonomy schemas and linkbases exist in the XBRL repository. Return the list of taxonomies as an XML document, indicating which are missing. The return value reflects the state of the repository at the time the procedure is invoked.

The detailed API information is given the following sections.

bulkLoadXBRLFiles

Upload a set of files. Before invoking this procedure, create a database directory.

PROCEDURE bulkLoadXBRLFiles(operation NUMBER,
                            directory VARCHAR2,
                            filelist  VARCHAR2,
                            target    VARCHAR2);

Table 3-2 DBMS_ORAXBRL.BULKLOADXBRLFILES Parameters

Parameter Description

operation

Type of operation to be carried out:

  • 1: Load a taxonomy schema.

  • 2: Load a linkbase.

  • 3: Load an XBRL instance.

  • 4: Load an auxiliary document.

  • 5: Register a taxonomy schema.

  • 6: Insert instance tuple data into a tuple data table. Load the corresponding instance file first, if not already loaded.

Raise an error if NULL.

directory

Database directory where the files reside. Raise an error if NULL.

filelist

XML document listing the file names to be loaded. Raise an error if NULL.

target

Folder location in Oracle XML DB Repository where documents will be uploaded. If NULL, then the upload folder is taken from the locations in filelist.

Note:

If many documents are to be bulk-loaded, consider using multiple sessions so they are loaded in parallel.

createTupleDataTable

Create a tuple data table. The table has these columns:

  • docid (RAW16) – An XBRL instance document identifier.

  • tupledata (XMLType stored object-relationally) – Instance data for a tuple element of an XBRL schema.

PROCEDURE createTupleDataTable(schemaLoc  VARCHAR2,
                               table_name VARCHAR2,
                               elem_name  VARCHAR2);

Does nothing if any of the arguments is NULL.

Table 3-3 DBMS_ORAXBRL.CREATETUPLEDATA Parameters

Parameter Description

schemaLoc

Location in the XBRL repository of the XBRL schema on which the tuple data table will be based. This must be an absolute URI, by which other documents can refer to this taxonomy schema. Do nothing if NULL.

table_name

Name of the XMLType table to be created. Do nothing if NULL.

elem_name

Name of the tuple element. Do nothing if NULL.

deleteAuxDocument

Delete an auxiliary document from the XBRL repository.

PROCEDURE deleteAuxDocument(auxDocPath VARCHAR2);

Table 3-4 DBMS_ORXBRL.DELETEAUXDOCUMENT Parameters

Parameter Description

auxDocPath

Location in the XBRL repository of the auxiliary document to be deleted.

deleteFolder

Forcefully (without DTS integrity check) delete all of the taxonomies under the given XBRL repository folder.

PROCEDURE deleteFolder(folder VARCHAR2);

Table 3-5 DBMS_ORXBRL.DELETEFOLDER Parameters

Parameter Description

folder

XBRL repository folder that contains the taxonomies to be deleted. Raise an error if NULL. If you use Oracle XML DB Repository to provide protocol access, then folder can alternatively name a folder in Oracle XML DB Repository.

To delete the root (top-level) folder, use deleteFolder('/').

deleteInstance

Delete an XBRL instance document from the XBRL repository. Delete the tuple instance data from the tuple data table, if present.

PROCEDURE deleteInstance(instanceLoc VARCHAR2);

Table 3-6 DBMS_ORXBRL.DELETEINSTANCE Parameters

Parameter Description

instanceLoc

The instanceLoc value that was specified for loadInstance when this instance document was loaded. Raise an error if NULL.

deleteLinkbase

Delete a linkbase document from the XBRL repository. Raise an error if the linkbase is referenced by other taxonomies or instance documents in the XBRL repository, unless the force argument is 1 or greater.

PROCEDURE deleteLinkbase(linkbaseLoc VARCHAR2, force PLS_INTEGER DEFAULT 0);

Table 3-7 DBMS_ORXBRL.DELETELINKBASE Parameters

Parameter Description

linkbaseLoc

The linkbaseLoc value that was specified for loadLinkbase when this linkbase was loaded. Raise an error if NULL.

force

1 or greater: Delete without first performing a DTS integrity check.

0 or less: Raise an error if the check fails.

Raise an error if NULL.

deleteTaxonomy

Delete a taxonomy, including the taxonomy schema and linkbases, given the target namespace of the taxonomy schema. Raise an error if the taxonomy is referenced by other taxonomies or instance documents in the XBRL repository, unless argument force is 1 or greater. If the taxonomy schema was registered, then delete the schema.

PROCEDURE deleteTaxonomy(repopath VARCHAR2, force PLS_INTEGER DEFAULT 0);

Table 3-8 DBMS_ORXBRL.DELETETAXONOMY Parameters

Parameter Description

repopath

Location in the XBRL repository of an XBRL taxonomy schema. This must be an absolute URI, by which other documents can refer to this taxonomy schema. Raise an error if NULL.

force

1 or greater: Delete without first performing a DTS integrity check.

0 or less: Delete if a DTS integrity check succeeds. Raise an error if the check fails.

Raise an error if NULL.

dropTupleDataTable

Drop the tuple data for a tuple element.

PROCEDURE dropTupleDataTable(repopath   VARCHAR2,
                             table_name VARCHAR2,
                             elem_name  VARCHAR2,
                             force      IN PLS_INTEGER DEFAULT 0);

Table 3-9 DBMS_ORXBRL.DROPTUPLETABLE Parameters

Parameter Description

repopath

Location in the XBRL repository of an XBRL schema. This must be an absolute URI, by which other documents can refer to this taxonomy schema. Do nothing if NULL.

table_name

Name of the tuple data table to be dropped. Do nothing if NULL.

elem_name

Name of the tuple element. Do nothing if NULL.

force

If 1 or greater then drop the table.

If 0 or less then:

  • If the table is empty then drop it.

  • Otherwise, raise an error.

DTS_files

Return the discoverable taxonomy set (DTS) for a given URI. The first time you invoke this procedure, it builds a cache for the DTS. Subsequent calls simply return the cached result. Also, calculations of the DTS that are implicit in other procedures and queries use the cached result instead of recomputing the DTS. Use of the cache can make network queries run faster.

If a DTS is expected to be large for a given entry URI, then invoke this procedure after loading all documents and before network generation. As a guideline, if a DTS lists more than 100 documents, then it will take at least a second to compute the DTS. For example, for entry URI 'http://xbrl.us/us-gaap-entryPoint-std/2008-03-31', the DTS contains 600 entries, so it takes several seconds to compute the list.

PROCEDURE DTS_files(entryURI IN VARCHAR2);

Table 3-10 DBMS_ORXBRL.DTS_FILES Parameters

Parameter Description

entryURI

Entry URI for the required DTS. Raise an error if NULL.

DTS_filelist

Return the discoverable taxonomy set (DTS) for a given URI. This function behaves similarly to procedure DTS_files, but:

  • It always returns the cached result, if found. If not found, it builds (and caches) the DTS.

  • It returns NULL instead of raising an error if the argument is NULL.

FUNCTION  DTS_filelist(entryURI IN VARCHAR2) RETURN ORAXBRL_DTSURLLIST;

Table 3-11 DBMS_ORXBRL.DTS_FILELIST Parameters

Parameter Description

entryURI

Entry URI for the required DTS. Return NULL if NULL.

getAuxDocForRepoPath

Return the auxiliary document that is associated with the document that is at a specified XBRL repository location.

FUNCTION getAuxDocForRepopath(repoPath VARCHAR2) RETURN XMLType;

Table 3-12 DBMS_ORAXBRL.GETAUXDOCFORREPOPATH Parameters

Parameter Description

repoPath

Location in the XBRL repository of the document that is associated with the auxiliary document to retrieve.

getDocument

Return the document that is at a specified XBRL repository location.

FUNCTION getDocument(repoPath VARCHAR2) RETURN XMLType;

Table 3-13 DBMS_ORAXBRL.GETDOCUMENT Parameters

Parameter Description

repoPath

Location in the XBRL repository of the document to retrieve.

isDocPathValid

Return the XBRL validity of a document in the XBRL repository. 1 means the document is valid; 0 means it is invalid.

FUNCTION isDocPathValid(repoPath VARCHAR2) RETURN PLS_INTEGER;

Table 3-14 DBMS_ORAXBRL.ISDOCPATHVALID Parameters

Parameter Description

repoPath

Location in the XBRL repository of the document.

loadAuxDocument

Load an auxiliary document into the XBRL repository at a specified location.

PROCEDURE loadAuxDocument(auxDocPath VARCHAR2, auxiliaryDoc XMLType);

Table 3-15 DBMS_ORAXBRL.LOADAUXDOCUMENT Parameters

Parameter Description

auxDocPath

Location in the XBRL repository to load the auxiliary document auxiliaryDoc.

auxiliaryDoc

Auxiliary document to load into repository location auxDocPath.

loadInstance

Load one instance document into the XBRL repository. For a top-level tuple element whose taxonomy schema is registered and whose tuple data table has been created, insert the instance tuple data into the tuple data table.

PROCEDURE loadInstance(instanceLoc VARCHAR2,
                       instanceDoc XMLType,
                       valid       IN PLS_INTEGER DEFAULT NULL,
                       auxLoc      VARCHAR2 DEFAULT NULL);

Table 3-16 DBMS_ORXBRL.LOADINSTANCE Parameters

Parameter Description

instanceLoc

Location in the XBRL repository of instance document instanceDoc. This must be an absolute URI. Raise an error if NULL.

Any relative URIs used as the value of attribute xlink:href in document instanceDoc are interpreted relative to location instanceLoc. You must ensure that instanceLoc is the proper base location for any such relative URIs.

instanceDoc

XBRL instance document. Raise an error if NULL.

valid

Validity of document instanceDoc. 1: valid, 0: invalid.

auxLoc

XBRL repository location of an XBRL auxiliary document that is mapped to instanceDoc.

loadLinkbase

Load one XBRL linkbase into the XBRL repository. DTS integrity is not checked after the call.

PROCEDURE loadLinkbase(linkbaseLoc VARCHAR2,
                       linkbaseDoc XMLType,
                       valid       IN PLS_INTEGER DEFAULT NULL,
                       auxLoc      VARCHAR2 DEFAULT NULL);

Table 3-17 DBMS_ORXBRL.LOADLINKBASE Parameters

Parameter Description

linkbaseLoc

Location in the XBRL repository of linkbase document doc. This must be an absolute URI, by which other documents can refer to this linkbase. Raise an error if NULL.

Any relative URIs used as the value of attribute xlink:href in document linkbaseDoc are interpreted relative to location linkbaseLoc. You must ensure that linkbaseLoc is the proper base location for any such relative URIs.

linkbaseDoc

XBRL linkbase document. Raise an error if NULL.

valid

Validity of document linkbaseDoc. 1: valid, 0: invalid.

auxLoc

XBRL repository location of an XBRL auxiliary document that is mapped to linkbaseDoc.

loadSchema

Load one taxonomy schema into an XBRL repository. DTS integrity is not checked after the call.

PROCEDURE loadSchema(schemaLoc VARCHAR2,
                     schemaDoc XMLType,
                     valid     IN PLS_INTEGER DEFAULT NULL,
                     auxLoc    VARCHAR2 DEFAULT NULL);

Table 3-18 DBMS_ORAXBRL.LOADSCHEMA Parameters

Parameter Description

schemaLoc

Location in the XBRL repository of schema document schemaDoc. This must be an absolute URI, by which other documents can refer to this taxonomy schema (using element schemaRef). Raise an error if NULL.

Any relative URIs used as the values of attributes xlink:href and schemaLocation in document schemaDoc are interpreted relative to location schemaLoc. You must ensure that schemaLoc is the proper base location for any such relative URIs.

schemaDoc

XBRL schema document. Raise an error if NULL.

valid

Validity of document schemaDoc. 1: valid, 0: invalid.

auxLoc

XBRL repository location of an XBRL auxiliary document that is mapped to schemaDoc.

mapPublishedLocation

Map an Oracle XML DB Repository path to an HTTP URL. This applies a published location (a URL) recursively to all files and folders under the specified Oracle XML DB Repository folder.

Each repository path starts with /XBRL/ followed by a user name. These top two levels of the path are, in effect, replaced by the URL that you provide as the published location.

For example, if a document is loaded into the XBRL repository at path /XBRL/some-user/us-gaap/1.0/elts/us-gaap-std-2008-03-31.xsd, and you invoke mapPublishedLocation('/XBRL/some-user/us-gaap', 'http://xbrl.us') , then the file is published at http://xbrl.us/ us-gaap/1.0/elts/us-gaap-std-2008-03-31.xsd.

You can optionally exclude all repository documents below a given level from being published. They are then ignored for XBRL purposes; in particular, they are not available for discovery. You do this be specifying the optional parameter levels. Documents at a depth greater than levels below folderpath are ignored.

Invoke this procedure after you use protocols to upload XBRL documents.

PROCEDURE mapPublishedLocation (folderpath VARCHAR2,
                                publishedLocation VARCHAR2,
                                levels PLS_INTEGER);

Table 3-19 DBMS_ORAXBRL.MAPPUBLISHEDLOCATION Parameters

Parameter Description

folderpath

XBRL repository folder to be mapped to publishedLocation.

publishedLocation

URL to be mapped to folderpath.

levels

Number of levels below folderpath for which to make documents available for discovery. By default there is no limit: all documents below folderpath are made available for discovery.

registerTaxonomySchema

Register one taxonomy schema with the XBRL repository. Invoke this after calling loadSchema. Needed for any schema that has tuple elements.

PROCEDURE registerTaxonomySchema(schemaLoc  VARCHAR2,
                                 annotation XMLType DEFAULT NULL,
                                 force      IN PLS_INTEGER DEFAULT 0);

Note:

Before invoking registerTaxonomySchema for the first time, you must run the script xbrlregschema.sql to register the standard XBRL schemas. See "XBRLScripts".

Table 3-20 DBMS_ORXBRL.REGISTERTAXONOMYSCHEMA Parameters

Parameter Description

schemaLoc

Location in the XBRL repository of the XBRL schema to be registered. This must be an absolute URI, by which other documents can refer to this taxonomy schema. Raise an error if NULL.

annotation

List of XBRL tuple elements, with their names and default table names. During registration, create the table with the specified default name for storing the XBRL tuple data. Do not create the table if NULL (the default value).

force

1 or greater: Do not raise an error if the schema is invalid. You can use 1 when registering a set of schemas with circular dependencies, to avoid raising errors due to those dependencies.

0 or less: Raise an error if the schema to be registered is invalid.

Example 3-1 shows the XML format for parameter annotation.

Example 3-1 Format of Parameter annotation

<schemaAnnotation>
  <element>
    <elementName>. . .</elementName>
    <defaultTableName>. . .</defaultTableName>
  </element> 
</schemaAnnotation>

updateDocValidity

Update the validity status of a document and associate an auxiliary document with it. Use this procedure after validation. (To update the auxiliary document, use procedure loadAuxDocument.)

PROCEDURE updateDocValidity(xbrlLoc    VARCHAR2,
                            valid      PLS_INTEGER DEFAULT NULL,
                            auxDocPath VARCHAR2 DEFAULT NULL);

Table 3-21 DBMS_ORAXBRL.UPDATEDOCVALIDITY Parameters

Parameter Description

xbrlLoc

Location in the XBRL repository of the document whose validity is being updated.

valid

New validation status of the document at location xbrlLoc. 1 means valid; 0 means invalid.

auxDocPath

Location in the XBRL repository of the auxiliary document to associate with the document at location xbrlLoc.

validateDTSIntegrity, validateDTSIntegrity2

Check whether all referenced taxonomy schemas and linkbases exist in the XBRL repository. Return the list of taxonomies as an XML document, indicating which taxonomies are missing. Reflects the state the XBRL repository at the time the procedure is invoked.

FUNCTION validateDTSIntegrity(entryURI VARCHAR2) RETURN XMLType;
FUNCTION validateDTSIntegrity2(entryURI VARCHAR2) RETURN ORAXBRL_CONCEPTLIST;

Table 3-22 DBMS_ORXBRL.VALIDATEDTSINTEGRITY(2) Parameters

Parameter Description

entryURI

The entry URI into a taxonomy. Raise an error if NULL.

Return an XML document that contains the locations of the documents in the discoverable taxonomy set (DTS) and an indication of whether that XML document is in XBRL repository. Example 3-2 illustrates this.

Example 3-2 validateDTSIntegrity

SELECT DBMS_ORAXBRL.validateDTSIntegrity(' http://xbrl.boa.com/2006-12-31')
  FROM DUAL;

<DiscoverSet>
  <DocumentPath InRepository="True">boa-20061231.xsd< /DocumentPath>
  <DocumentPath InRepository="True">
http://xbrl.us/us-gaap/1.0/elts/us-gaap-std-2008-03-31.xsd</DocumentPath>
  <DocumentPath InRepository="True" >
http://xbrl.us/us-gaap/1.0/elts/us-types-2008-03-31.xsd</DocumentPath>
  <DocumentPath InRepository="True" >
http://xbrl.us/us-gaap/1.0/elts/us-types-2008-03-31.xsd</DocumentPath>
  <DocumentPath InRepository="False" > boa-20061231_pre.xml</DocumentPath>
  <DocumentPath InRepository="True" > boa-20061231_cal.xml</DocumentPath>
. . .
</DiscoverSet>

XBRL Repository Query

You can query the XBRL content in an XBRL repository directly, using the XQuery language. XBRL Extension to Oracle XML DB also provides the following:

XBRL Relational Representation

XBRL Extension to Oracle XML DB provides relational views of your XBRL content. This XBRL relational representation is a third normal form data model. It gives you simple access to attributes of schemas, linkbases, targets of linkbases, and items in an instance document. You can query these views directly, or you can create derived views over them to extract a particular representation (see steps 3 and 5, "Building and Using a Sample XBRL Application: USGAAP 2008").

Table 3-23 Relational Views of XBRL Content

Type Name Description

Schema

ORAXBRL_XS_TARGETNSV

Target namespace used in an XBRL schema document.
 

ORAXBRL_XS_NSV

Namespaces referenced by an XBRL schema document.
 

ORAXBRL_XS_IMPORTNSV

Schemas imported by an XBRL schema document.
 

ORAXBRL_XS_LINKBASEREFV

Linkbases referenced by an XBRL schema document
 

ORAXBRL_XS_ROLETYPEV

roletype elements used in an XBRL taxonomy.
 

ORAXBRL_XS_ARCROLETYPEV

arcroletype elements used in an XBRL taxonomy.
 

ORAXBRL_XS_ELEMENT

Elements used in an XBRL taxonomy.
 

ORAXBRL_XS_GROUPV

Element groups used in and XBRL taxonomy.
 

ORAXBRL_XS_COMPLEXTYPESC

Complex types defined in an XBRL taxonomy.

Linkbase

ORAXBRL_PRES_LINKBASE

Presentation arcs defined in an XBRL taxonomy.
 

ORAXBRL_CALCULATION_LINKBASE

Calculation arcs defined in an XBRL taxonomy.
 

ORAXBRL_DEFINITION_LINKBASE

Definition arcs defined in an XBRL taxonomy.
 

ORAXBRL_LABEL_LINKBASE

Label arcs defined in an XBRL taxonomy.
 

ORAXBRL_REFERENCE_LINKBASE

Reference arcs defined in an XBRL taxonomy.

Instance

ORAXBRL_INST_SCHEMAREFV

XBRL schemas referenced by an XBRL instance document.
 

ORAXBRL_INST_LINKBASEREFV

Linkbases referenced by anXBRL instance document.
 

ORAXBRL_INST_ROLEREFV

Role references from an XBRL instance document.
 

ORAXBRL_INST_ARCROLEREFV

arcroleref elements from an XBRL instance document.
 

ORAXBRL_INST_NSV

Namespaces used in an XBRL instance document.
 

ORAXBRL_INST_UNITV

Unit definitions in an XBRL instance document.
 

ORAXBRL_INST_CONTEXTV

Context definitions in an XBRL instance document.
 

ORAXBRL_FOOTNOTES

Footnotes defined in an XBRL instance document.
 

ORAXBRL_SEGMENT_EXPLICITV

Explicit dimensional attributes defined in the segment part of an XBRL instance document.
 

ORAXBRL_SCENARIO_EXPLICITV

Explicit dimensional attributes defined in the scenario part of an XBRL instance document.
 

ORAXBRL_SEGMENT_TYPEDV

Typed dimensional attributes defined in the segment part of an XBRL instance document.
 

ORAXBRL_SCENARIO_TYPEDV

Typed dimensional attributes defined in the scenario part of an XBRL instance document.
 

ORAXBRL_INST_ITEMV

Fact values reported in an XBRL instance document.

Instance Network Functions: DBMS_ORAXBRLI

The instance network functions are part of PL/SQL package DBMS_ORAXBRLI. You can use these functions to generate XBRL reports that combine taxonomy and instance data.

There are essentially two versions of each function. The version whose name ends in "2" returns an instance of data type ORAXBRL_CONCEPTLIST (or ORAXBRL_ITEMLIST, in the case of multiple_instance_network2). The version whose name has no "2" appended to it returns an XMLType instance.

Table 3-24 DBMS_ORAXBRI Repository Query APIs

Function Description

instance_network, instance_network2

Return reported data, organized by a base set of concept-concept relationships, such as a presentation tree.

multiple_instance_network, multiple_instance_network2

Return reported data across multiple instance documents, organized by a base set of concept-concept relationships, such as a presentation tree.

instance_network, instance_network2

Return reported data, organized by a base set of concept-concept relationships, such as a presentation tree.

FUNCTION instance_network (entryURI             VARCHAR2,
                           entity               VARCHAR2,
                           periodstart          DATE,
                           periodend            DATE,
                           network_eLinkRoleURI VARCHAR2,
                           network_arcNSURI     VARCHAR2,
                           network_arcLocalName VARCHAR2,
                           network_arcRoleURI   VARCHAR2,
                           label_eLinkRoleURI   VARCHAR2,
                           label_arcRoleURI     VARCHAR2,
                           label_roleURI        VARCHAR2,
                           lang                 VARCHAR2,
                           includeReference     PLS_INTEGER)
   RETURN XMLType;Foot 1

Table 3-25 DBMS_ORAXBRI.INSTANCE_NETWORK(2) Parameters

Parameter Description

entryURI

Entry URI into the taxonomy. Raise an error if NULL.

entity

Entity identifier. Raise an error if NULL.

periodStart

Starting period. Raise an error if NULL.

periodEnd

Ending period. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

includeReference

Ignored.

multiple_instance_network, multiple_instance_network2

Return reported data across multiple instance documents, organized by a base set of concept-concept relationships, such as a presentation tree.

FUNCTION multiple_instance_network(entryURI             VARCHAR2,
                                   entityList           VARCHAR2,
                                   periodstart          DATE,
                                   periodend            DATE,
                                   network_eLinkRoleURI VARCHAR2,
                                   network_arcNSURI     VARCHAR2, 
                                   network_arcLocalName VARCHAR2, 
                                   network_arcRoleURI   VARCHAR2,
                                   label_eLinkRoleURI   VARCHAR2,
                                   label_arcRoleURI     VARCHAR2, 
                                   label_roleURI        VARCHAR2,
                                   lang                 VARCHAR2,
                                   includeReference     PLS_INTEGER) 
  RETURN XMLType;Foot 2

Table 3-26 DBMS_ORAXBRI.MULTIPLE_INSTANCE_NETWORK(2) Parameters

Parameter Description

entryURI

Entry URI into the taxonomy. Raise an error if NULL.

entityList

A comma-delimited list of entities: entity1,entity2,… entityN. Raise an error if NULL.

periodStart

The starting period. Raise an error if NULL.

periodEnd

The ending period. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

includeReference

Ignored.

Concept Network Functions: DBMS_ORAXBRLT

The concept network functions are part of PL/SQL package DBMS_ORAXBRLT. They generate XBRL taxonomy hierarchies. There are essentially two versions of each function. The version whose name ends in "2" returns an instance of data type ORAXBRL_CONCEPTLIST. The version whose name has no "2" appended to it returns an XMLType instance.

Table 3-27 DBMS_ORAXBRT Concept Network Function APIs

Function Description

concept_roots, concept_roots2

Return the root nodes that correspond to a given DTS, entry URI, and XLink role.

concepts_in_tree, concepts_in_tree2

Return the concepts that are the descendents of a particular node in a base set tree, with labels. If no entry URI is specified, then return all concepts with the specified XLink role.

concepts_network, concepts_network2

Return a view of a base set of concept-concept relationships, such as a presentation tree.

concepts_network, concepts_network2

Return a view of a base set of concept-concept relationships, such as a presentation tree.

FUNCTION concepts_network(entryURI             VARCHAR2,
                          network_linkRoleURI  VARCHAR2,
                          network_arcNSURI     VARCHAR2,
                          network_arcLocalName VARCHAR2,
                          network_arcRoleURI   VARCHAR2,
                          labeleLinkRoleURI    VARCHAR2,
                          labelarcRoleURI      VARCHAR2,
                          label_roleURI        VARCHAR2,
                          lang                 VARCHAR2,
                          includeReference     PLS_INTEGER) 
  RETURN XMLType;Foot 3

Table 3-28 DBMS_ORAXBRT.CONCEPTS_NETWORK(2) Parameters

Parameter Description

entryURI

Entry URI into a taxonomy. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

includeReference

Ignored.

concept_roots, concept_roots2

Return the root nodes that correspond to a given DTS, entry URI, and XLink role.

FUNCTION concept_roots(entryURI             VARCHAR2,
                       network_eLinkRoleURI VARCHAR2(200),
                       network_arcNSURI     VARCHAR2(200), 
                       network_arcLocalName VARCHAR2(200),
                       network_arcRoleURI   VARCHAR2(200),
                       label_eLinkRoleURI   VARCHAR2(200),
                       label_arcRoleURI     VARCHAR2(200),
                       label_roleURI        VARCHAR2(200),
                       lang                 VARCHAR2(200),
                       includeReference     PLS_INTEGER)
  RETURN XMLType;Foot 4

Table 3-29 DBMS_ORAXBRT.CONCEPT_ROOTS(2) Parameters

Parameter Description

entryURI

Entry URI for a taxonomy. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

includeReference

Ignored.

concepts_in_tree, concepts_in_tree2

Return the concepts that are descendents of a particular node in a base set tree, with labels. If no entry URI is specified, then return all concepts.

FUNCTION concepts_in_tree(entry                VARCHAR2, 
                          concept_namespaceURI VARCHAR2,
                          concept_name         VARCHAR2,
                          network_eLinkRoleURI VARCHAR2,
                          network_arcNSURI     VARCHAR2,
                          network_arcLocalName VARCHAR2,
                          network_arcRoleURI   VARCHAR2,
                          label_eLinkRoleURI   VARCHAR2, 
                          label_arcRoleURI     VARCHAR2,
                          label_roleURI        VARCHAR2,
                          lang                 VARCHAR2,
                          includeReference     PLS_INTEGER)
  RETURN XMLTYPE;Foot 5

Table 3-30 DBMS_ORAXBRT.CONCEPTS_IN_TREE(2) Parameters

Parameter Description

entry

Entry into a taxonomy. Raise an error if NULL.

concept_namespaceURI

Concept namespace URI. Raise an error if NULL.

concept_name

Concept name. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

includeReference

Ignored.

Transforming Procedures: DBMS_ORAXBRLV

Transforming procedures are used to generate derived views that are based on one of the following:

  • The XBRL relational representation

  • The network generation APIs

  • Dimensional information

The transforming procedures are in PL/SQL package DBMS_ORAXBRLV.

Table 3-31 DBMS_ORAXBRV Transforming Procedure APIs

Procedure Description

createFactTable (Deprecated)

Create a fact table as a view.

createHyperCubeFactTable

Search the hypercube network of the given primary item to find valid dimensions. Create a star schema, which is a fact table plus dimension tables. Optionally create a join view between the fact table and the dimension tables.

createHyperCubeSuperFactTable

Search for primary items that include the given hypercube. Create a super fact table and dimension tables. Optionally create a join view between the super fact table and the dimension tables.

createStarSchemaFromFact

Search the hypercube network of the given primary item to find valid dimensions. Create a star schema, which is a fact table plus dimension tables. Optionally create a join view between the fact table and the dimension tables. Optionally cache the names of the created tables.

createStarSchemaFromHC

Search for primary items that include the given hypercube. Create a super fact table and dimension tables. Optionally create a join view between the super fact table and the dimension tables. Optionally cache the names of the created tables.

createSuperFactTable (Deprecated)

Create a super fact table as a view.

createViewForConceptRoots

Create a relational view for PL/SQL function concepts_roots.

createViewForConceptTree

Create a relational view for PL/SQL functions concepts_network and concepts_in_tree.

createViewForInstanceNetwork

Create a relational view for PL/SQL functions instance_network and multiple_instance_network.

dropStarSchema

Drop a star schema: the fact table or super fact table, dimension tables, and join view (if any) that are identified by a given tableName, provided such information is cached in a system table.

createFactTable (Deprecated)

Create a fact table as a view.

Note:

Procedure createFactTable is deprecated as of XBRL Extension to Oracle XML DB 11g Release 2 (11.2.0.2.1).
PROCEDURE createFactTable(tableName           VARCHAR2,
                          entity              VARCHAR2,
                          entryURI            VARCHAR2,
                          conceptNamespaceURI VARCHAR2,
                          conceptLocalName    VARCHAR2);

Table 3-32 DBMS_ORAXBRV.CREATEFACTTABLE Parameters (Deprecated)

Parameter Description

tableName

Name of the fact-table view. Raise an error if NULL.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into the taxonomy. Raise an error if NULL.

It must be the target namespace of the schema specified in element schemaRef of the instance document. (entity, entryURI) uniquely identifies an XBRL instance document.

conceptNamespaceURI

URI for the concept namespace. Raise an error if NULL.

conceptLocalName

Local name of the concept. Raise an error if NULL.

Example 3-3 createFactTable

createFactTable('SALES', 'SAMP', 'http://www.SampleCompany.com/Company',
                'http://www.example.com/Patterns/Sales', 'Sales');

This creates the fact-table view SALES. See Example 3-5.

createHyperCubeFactTable

Search the hypercube network of a given primary item to find valid dimensions. Create a star schema, which is a fact table plus dimension tables. If argument tableName is not NULL then also create a join view between the fact table and the dimension tables. The pair (entity, entryURI) uniquely identifies an XBRL instance document.

PROCEDURE createHyperCubeFactTable(tableName           VARCHAR2, 
                                   entity              VARCHAR2, 
                                   entryURI            VARCHAR2, 
                                   conceptNamespaceURI VARCHAR2, 
                                   conceptLocalName    VARCHAR2,
                                   xlinkRole           VARCHAR2);

For a factFoot 6  name to be usable as parameter conceptLocalName, the fact should have associated dimension information. Search instance documents for occurrences of a fact you are interested in. If you find that the fact is associated with dimension information then it is a candidate for use with procedure createHyperCubeFactTable.

Table 3-33 DBMS_ORAXBRV.CREATEHYPERCUBEFACTTABLE Parameters

Parameter Description

tableName

If NULL, create a fact table and dimension tables (only).

Otherwise, create a fact table, dimension tables, and a join (as a view named tableName) between the fact table and the dimension tables.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into a taxonomy. The target namespace of the schema that is specified in element schemaRef of the instance document. Raise an error if NULL.

conceptNamespaceURI

Concept namespace URI. Raise an error if NULL.

conceptLocalName

Concept local name. Raise an error if NULL.

xLinkRole

Extended link role (xlink:role) of the base sets that contain the has-hypercube arcs, that is, the arcs that have arc role http://xbrl.org/int/dim/arcrole/all or http://xbrl.org/int/dim/arcrole/notAll. Raise an error if NULL.

createHyperCubeFactTable(t, e, u, cnu, cln, xr) is equivalent to createStarSchemaFromFact(t, e, u, cnu, cln, xr, 1, 0), if t is not NULL.

createHyperCubeFactTable(NULL, e, u, cnu, cln, xr) is equivalent to createStarSchemaFromFact('DUMMY', e, u, cnu, cln, xr, 0, 0).

Example 3-4 createHyperCubeFactTable

createHyperCubeFactTable('SALES_DIM',
                         'SAMP',
                         'http://www.SampleCompany.com/Company',
                         'http://www.example.com/Patterns/Sales',
                         'Sales',
                         'http://www.SampleCompany.com/PrimaryConcepts');

This creates fact table user_SALES, dimension tables user_BYPRODUCTPLACEHOLDER and user_BYREGIONPLACEHOLDER, and view SALES_DIM, where user is the database user logged in when createHyperCubeFactTable is invoked. These tables are shown in Example 3-5, Example 3-6, and Example 3-7.

Example 3-5 Fact Table user_SALES

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
CONTEXT_ID                                         VARCHAR2(2048)
COMPANY_NAME                                       VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
INSTANT_DATE                                       DATE
VALUE                                              CLOB

Example 3-6 Dimension Tables user_BYPRODUCTPLACEHOLDER and user_BYREGIONPLACEHOLDER

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
CONTEXT_ID                                         VARCHAR2(4000)
DOMAIN_VALUE                                       VARCHAR2(4000)

Column CONTEXT_ID is the primary key for the dimension table, and column DOMAIN_VALUE contains the value of the dimension domain members.

Example 3-7 Join View SALES_DIM

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
COMPANY_NAME                                       VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
INSTANT_DATE                                       DATE
BYREGIONPLACEHOLDER                                VARCHAR2(4000)
BYPRODUCTPLACEHOLDER                               VARCHAR2(4000)
VALUE                                              CLOB

createHyperCubeSuperFactTable

Search for primary items that include the given hypercube. Create a super fact table and dimension tables. If argument tableName is not NULL then also create a join view between the super fact table and the dimension tables.

A super fact table can contain more than one kind of fact. It acts like a collection of fact tables that each contain one kind of fact. It contains all of the primary items associated with a given hyper cube.

PROCEDURE createHyperCubeSuperFactTable(tableName         VARCHAR2, 
                                        entity            VARCHAR2, 
                                        entryURI          VARCHAR2,
                                        HCNamespaceURI    VARCHAR2,
                                        HCLocalName       VARCHAR2, 
                                        HCXlinkRole       VARCHAR2,
                                        HCContextElement  VARCHAR2,
                                        HCTargetRole      VARCHAR2);

Table 3-34 DBMS_ORAXBRV.CREATEHYPERCUBESUPERFACTTABLE Parameters

Parameter Description

tableName

If NULL, create a fact table and dimension tables (only).

Otherwise, create a fact table, dimension tables, and a join (as a view named tableName) between the fact table and the dimension tables.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into the taxonomy. Raise an error if NULL.

It must be the target namespace of the schema specified in element schemaRef of the instance document. (entity, entryURI) uniquely identifies an XBRL instance document.

HCNamespaceURI

Namespace URI of the hypercube. Raise an error if NULL.

HCLocalName

Local name of the hypercube. Raise an error if NULL.

HCXLinkRole

XLink role (xlink:role) of the base sets that contain arc has-hypercube. Raise an error if NULL.

HCContextElement

Value of attribute contextElement specified in arc has-hypercube. Raise an error if NULL.

HCTargetRole

Value of attribute targetRole specified in arc has-hypercube. Raise an error if NULL.

createHyperCubeSuperFactTable(t, e, u, nu, ln, xr, ce, tr) is equivalent to createStarSchemaFromHC(t, e, u, nu, ln, xr, ce, tr, 1, 0), if t is not NULL.

createHyperCubeSuperFactTable(NULL, e, u, nu, ln, xr, ce tr) is equivalent to createStarSchemaFromHC('DUMMY', e, u, nu, ln, xr, ce, tr, 0, 0).

Example 3-8 createHyperCubeSuperFactTable

createHyperCubeSuperFactTable(
   'BOA_STATEMENT',
   '0000070858', 
   'http://xbrl.boa.com/2006-12-31', 
   'http://xbrl.us/us-gaap/2008-03-31',
   'StatementTable', 
   'http://xbrl.boa.com/2006-12-31/ext/StockholdersEquity', 
   'segment', 'http://xbrl.boa.com/2006-12-31/ext/StockholdersEquity');

This creates fact table user_STATEMENTTABLE, dimension table user_EQUITYCOMPONENTSAXIS, and join view BOA_STATEMENT, where user is the database user logged in when createHyperCubeSuperFactTable is invoked. These tables and view are shown in Example 3-9, Example 3-10, and Example 3-11.

Example 3-9 Fact Table user_STATEMENTTABLE

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
ITEM_NAME                                          VARCHAR2(4000)
CONTEXT_ID                                         VARCHAR2(2048)
COMPANY_NAME                                       VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
INSTANT_DATE                                       DATE
VALUE                                              CLOB

Example 3-10 Join View BOA_STATEMENT

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
ITEM_NAME                                          VARCHAR2(4000)
COMPANY_NAME                                       VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
INSTANT_DATE                                       DATE
EQUITYCOMPONENTSAXIS                               VARCHAR2(4000)
VALUE                                              CLOB

Example 3-11 Dimension Table user_EQUITYCOMPONENTSAXIS

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
ITEM_NAME                                          VARCHAR2(4000)
CONTEXT_ID                                         VARCHAR2(2048)
COMPANY_NAME                                       VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
INSTANT_DATE                                       DATE
VALUE                                              CLOB

createStarSchemaFromFact

Search the hypercube network of the primary item to find valid dimensions. Create a star schema, which is a fact table plus dimension tables. Optionally create a join view between the fact table and the dimension tables. Optionally cache the names of the created tables. Return a list of the names of the created tables.

FUNCTION createStarSchemaFromFact(tableName VARCHAR2,
                                  entity VARCHAR2,
                                  entryURI VARCHAR2,
                                  conceptNamespaceURI VARCHAR2,
                                  conceptLocalName VARCHAR2,
                                  xlinkRole VARCHAR2,
                                  createJoin PLS_INTEGER DEFAULT 1,
                                  cache PLS_INTEGER DEFAULT 0)
  RETURN ORAXBRL_STARSCHEMA;

Together, entity plus entryURI uniquely identify an XBRL instance document.

Table 3-35 DBMS_ORAXBRV.CREATESTARSCHEMAFROMFACT Parameters

Parameter Description

tableName

Unique identifier for the set comprising the created fact table and dimension tables. If NULL, raise an error.

If createJoin is 1, tableName is also the name of the join view between the fact table and the dimension tables.

If cache is 1, cache tableName and the names of the fact table and dimension tables in a system table.

If neither createJoin nor cache is 1, then tableName is ignored.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into a taxonomy. The target namespace of the schema that is specified in element schemaRef of the instance document. Raise an error if NULL.

conceptNamespaceURI

Concept namespace URI. Raise an error if NULL.

conceptLocalName

Concept local name. Raise an error if NULL.

xLinkRole

Extended link role (xlink:role) of the base sets that contain the has-hypercube arcs. Raise an error if NULL.

createJoin

If and only if 1, create a join view between the fact table and the dimension tables.

cache

If and only if 1, cache tableName and the names of the fact table and dimension tables in a system table.

The fact table created is named F_entity_conceptLocalName_####, where entity and conceptLocalName are the entity and concept local name inputs, and #### is a four-digit (decimal) number guaranteed to make the name unique.

The dimension tables created are named D_entity_dimensionLocalName_####, where entity is the entity input, dimensionLocalName is the local name of the valid dimension found in the hypercube network given the primary item, and #### is a four-digit (decimal) number guaranteed to make the name unique.

Database table names are limited to a maximum of 30 characters. The concept and dimension local names are truncated as needed to ensure this.

createStarSchemaFromHC

Search for primary items that include the given hypercube. Create a super fact table and dimension tables. Optionally create a join view between the super fact table and the dimension tables. Optionally cache the names of the created tables. Return a list of the names of the created tables.

FUNCTION createStarSchemaFromHC(tableName VARCHAR2,
                                entity VARCHAR2,
                                entryURI VARCHAR2,
                                HCNamespaceURI VARCHAR2,
                                HCLocalName VARCHAR2,
                                HCxlinkRole VARCHAR2,
                                createJoin PLS_INTEGER DEFAULT 1,
                                cache PLS_INTEGER DEFAULT 0)
  RETURN ORAXBRL_STARSCHEMA;

Together, entity plus entryURI uniquely identify an XBRL instance document.

Table 3-36 DBMS_ORAXBRV.CREATESTARSCHEMAFROMHC Parameters

Parameter Description

tableName

Unique identifier for the set comprising the created super fact table and dimension tables. If NULL, raise an error.

If createJoin is 1, tableName is also the name of the join view between the super fact table and the dimension tables.

If cache is 1, cache tableName and the names of the fact table and dimension tables in a system table.

If neither createJoin nor cache is 1, then tableName is ignored.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into a taxonomy. The target namespace of the schema that is specified in element schemaRef of the instance document. Raise an error if NULL.

HCNamespaceURI

Namespace URI of the hypercube. Raise an error if NULL.

HCLocalName

Local name of the hypercube. Raise an error if NULL.

HCXLinkRole

XLink role (xlink:role) of the base sets that contain arc has-hypercube. Raise an error if NULL.

HCContextElement

Value of attribute contextElement specified in arc has-hypercube. Raise an error if NULL.

HCTargetRole

Value of attribute targetRole specified in arc has-hypercube. Raise an error if NULL.

createJoin

If and only if 1, create a join view between the super fact table and the dimension tables.

cache

If and only if 1, cache tableName and the names of the super fact table and dimension tables in a system table.

The created fact table and dimension tables are named using the same convention as for createStarSchemaFromFact.

createSuperFactTable (Deprecated)

Create a super fact table as a view. It contains all of the primary items of the specified hypercube.

Note:

Procedure createSuperFactTable is deprecated as of XBRL Extension to Oracle XML DB 11g Release 2 (11.2.0.2.1).
PROCEDURE createSuperFactTable(tableName VARCHAR2,
                               entity VARCHAR2,
                               entryURI VARCHAR2,
                               hcNamespaceURI VARCHAR2,
                               hcLocalName VARCHAR2,
                               hcXlinkRole VARCHAR2,
                               hcContextElement VARCHAR2,
                               hcTargetRole VARCHAR2);

Table 3-37 DBMS_ORAXBRV.CREATESUPERFACTTABLE Parameters (Deprecated)

Parameter Description

tableName

Name of the super fact-table view. Raise an error if NULL.

entity

Entity identifier. Raise an error if NULL.

entryURI

Entry URI into the taxonomy. Raise an error if NULL.

It must be the target namespace of the schema specified in element schemaRef of the instance document. (entity, entryURI) uniquely identifies an XBRL instance document.

hcNamespaceURI

URI for the hypercube namespace. Raise an error if NULL.

hcLocalName

Local name of the hypercube. Raise an error if NULL.

hcXlinkRole

Extended link role (xlink:role) of the base sets that contain arc has-hypercube.

hcContextElement

Value of attribute contextElement that is specified in arc has-hypercube.

hcTargetRole

Value of attribute targetRole that is specified in arc has-hypercube.

Example 3-12 createSuperFactTable

createSuperFactTable('STATEMENT', '0000070858', 'http://xbrl.boa.com/2006-12-31',
                     'http://xbrl.us/us-gaap/2008-03-31', 'StatementTable', 
                     'http://xbrl.boa.com/2006-12-31/ext/StockholdersEquity', 
                     'segment', 
                     'http://xbrl.boa.com/2006-12-31/ext/StockholdersEquity');

This creates super fact-table view STATEMENT. See Example 3-9.

createViewForConceptRoots

Create a relational view for PL/SQL function concept_roots.

PROCEDURE createViewForConceptRoots(viewName             VARCHAR2,
                                    entryURI             VARCHAR2,
                                    network_eLinkRoleURI VARCHAR2,
                                    network_arcNSURI     VARCHAR2,
                                    network_arcLocalName VARCHAR2,
                                    network_arcRoleURI   VARCHAR2, 
                                    labeleLinkRoleURI    VARCHAR2,
                                    labelarcRoleURI      VARCHAR2, 
                                    label_roleURI        VARCHAR2,
                                    lang                 VARCHAR2);

Table 3-38 DBMS_ORAXBRV.CREATEVIEWFORCONCEPTROOTS Parameters

Parameter Description

viewName

Name of the view. Raise an error if NULL.

entryURI

Entry into a taxonomy. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

Example 3-13 createViewForConceptRoots

createViewForConceptRoots(
  'my_concept_roots',
  'http://xbrl.us/us-gaap-entryPoint-std/2008-03-31',
  'http://xbrl.us/us-gaap/role/statement/StatementOfIncome',
  NULL,  
  'presentationArc',
  'http://www.xbrl.org/2003/arcrole/parent-child', 
  NULL,
  NULL,  
  NULL,
  'en-US');

createViewForConceptTree

Create a relational view for PL/SQL functions concepts_network and concepts_in_tree.

PROCEDURE createViewForConceptTree(viewName             VARCHAR2,
                                   entryURI             VARCHAR2,
                                   concept_namespaceURI VARCHAR2,
                                   concept_name         VARCHAR2,
                                   network_eLinkRoleURI VARCHAR2,
                                   network_arcNSURI     VARCHAR2,
                                   network_arcLocalName VARCHAR2,
                                   network_arcRoleURI   VARCHAR2,
                                   labeleLinkRoleURI    VARCHAR2, 
                                   labelarcRoleURI      VARCHAR2, 
                                   label_roleURI        VARCHAR2,
                                   lang                 VARCHAR2, 
                                   treeDepth            PLS_INTEGER);

Table 3-39 DBMS_ORAXBRV.CREATEVIEWFORCONCEPTTREE Parameters

Parameter Description

viewName

Name of the view. Raise an error if NULL.

entryURI

Entry into a taxonomy. Raise an error if NULL.

concept_namespaceURI

Concept namespace URI. Raise an error if NULL.

concept_name

Concept name. By default (NULL), create a relational view for PL/SQL function concepts_network. If not NULL, create a relational view for PL/SQL function concepts_in_tree.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label Role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

treeDepth

If 1, create a view on the children of the concept. Otherwise, create a view on the descendents of the concept.

Example 3-14 createViewForConceptTree

createViewForConceptTree(
  'my_network',
  'http://xbrl.us/us-gaap-entryPoint-std/2008-03-31',
  'http://xbrl.us/us-gaap/2008-03-31',
  'NetIncomeLossAbstract',
  'http://xbrl.us/us-gaap/role/statement/StatementOfIncome', 
   NULL,  
  'presentationArc',
  'http://www.xbrl.org/2003/arcrole/parent-child',
  NULL,
  NULL,
  NULL,
  'en-US',
  -1);

This creates view my_network:

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
NAMESPACEURI                                        VARCHAR2(4000)
PREFERREDPREFIX                                     VARCHAR2(4000)
NAME                                                VARCHAR2(4000)
ID                                                  VARCHAR2(4000)
BALANCE                                             VARCHAR2(4000)
PERIODTYPE                                          VARCHAR2(4000)
ABSTRACT                                            VARCHAR2(4000)
NILLABLE                                            VARCHAR2(4000)
TYPEURI                                             VARCHAR2(4000)
TYPELOCALNAME                                       VARCHAR2(4000)
SGURI                                               VARCHAR2(4000)
SGLOCALNAME                                         VARCHAR2(4000)
ELEM_HREF                                           VARCHAR2(4000)
LABEL                                               VARCHAR2(4000)
PREFERRED_LABEL                                     VARCHAR2(4000)

createViewForInstanceNetwork

Create a relational view for PL/SQL functions instance_network and multiple_instance_network.

PROCEDURE createViewForInstanceNetwork(viewName             VARCHAR2,
                                       entryURI             VARCHAR2,
                                       entityList           VARCHAR2, 
                                       startDate            DATE,
                                       endDate              DATE,
                                       network_eLinkRoleURI VARCHAR2,
                                       network_arcNSURI     VARCHAR2,
                                       network_arcLocalName VARCHAR2, 
                                       network_arcRoleURI   VARCHAR2, 
                                       labeleLinkRoleURI    VARCHAR2, 
                                       labelarcRoleURI      VARCHAR2,
                                       label_roleURI        VARCHAR2, 
                                       lang                 VARCHAR2);

Table 3-40 DBMS_ORAXBRV.CREATEVIEWFORINSTANCENETWORK Parameters

Parameter Description

viewName

Name of the view. Raise an error if NULL.

entryURI

Entry URI to the taxonomy. Raise an error if NULL.

entityList

A comma-delimited list of entities: entity1,entity2,… entityN. Raise an error if NULL.

If there is only one entity, it is equivalent to function instance_network.

periodStart

Starting period. Raise an error if NULL.

periodEnd

Ending period. Raise an error if NULL.

network_eLinkRoleURI

Base set extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

network_arcNSURI

Base set namespace URI.

Default (NULL): http://www.xbrl.org/2003/linkbase

network_arcLocalName

Base set local name.

Default (NULL): presentationArc

network_arcRoleURI

Base set resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/parent-child

label_eLinkRoleURI

Label extended link role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/link

label_arcRoleURI

Label resource arc role (xlink:arcrole).

Default (NULL): http://www.xbrl.org/2003/arcrole/concept-label

label_roleURI

Label role (xlink:role).

Default (NULL): http://www.xbrl.org/2003/role/label

lang

Language (xml:lang).

Default (NULL): en

Example 3-15 createViewForInstanceNetwork

createViewForInstanceNetwork(
  'my_instance_network',
  'http://xbrl.boa.com/2006-12-31',
  '0000070858',
  1-JAN-05',
    '01-JAN-06', 
  'http://xbrl.boa.com/2006-12-31/ext/IncomeStatement', 
  NULL, 
  'presentationArc', 
  'http://www.xbrl.org/2003/arcrole/parent-child',
  NULL,
  NULL, 
  NULL,
  'en-US');

This creates view my_instance_network:

Name                                      Null?    Type
----------------------------------------- -------- ---------------------
NAMESPACEURI                                       VARCHAR2(4000)
PREFIX                                             VARCHAR2(4000)
NAME                                               VARCHAR2(4000)
LABEL                                              VARCHAR2(4000)
PREFERRED_LABEL                                    VARCHAR2(4000)
ENTITY_SCHEME                                      VARCHAR2(4000)
ENTITY_IDENTIFIER                                  VARCHAR2(4000)
START_DATE                                         DATE
END_DATE                                           DATE
CONTEXTREF                                         VARCHAR2(4000)
UNITREF                                            VARCHAR2(4000)
DECIMALS                                           VARCHAR2(4000)
VALUE                                              CLOB

dropStarSchema

Drop a star schema: the fact table or super fact table, dimension tables, and join view (if any) that are collectively identified by the given tableName, provided such information is cached in a system table. (Do nothing if the information is not cached.)

PROCEDURE dropStarSchema(tableName VARCHAR2);

Table 3-41 DBMS_ORAXBRV.DROPSTARSCHEMA Parameters

Parameter Description

tableName

tableName argument specified in the invocation of createStarSchemaFromFact or createStarSchemaFromHC that created the star schema to be dropped.

XBRL Extension to Oracle XML DB Data Types

This section describes data types specific to XBRL Extension to Oracle XML DB. They are all in PL/SQL package XBRLSYS.

ORAXBRL_CONCEPT, ORAXBRL_CONCEPTLIST

Data type ORAXBRL_CONCEPTLIST is a varray of ORAXBRL_CONCEPT, which is an object type with the following attributes that pertain to a concept:

Table 3-42 ORAXBRL_CONCEPT Object Type Attributes

Attribute Type Description

NAMESPACEURI

VARCHAR2(4000)

Namespace URI of the XML schema that defines the concept.

PREFERREDPREFIX

VARCHAR2(4000)

Preferred prefix for the namespace specified by NAMESPACEURI.

NAME

VARCHAR2(4000)

Local name of the concept.

ID

VARCHAR2(4000)

Unique identifier of the concept.

BALANCE

VARCHAR2(4000)

The credit/debit balance associated with the concept.

PERIODTYPE

VARCHAR2(4000)

Type of the reporting period associated with the concept. Possible values: duration and instant.

ABSTRACT

VARCHAR2(4000)

True means that the concept can be used only in a hierarchy, to group related concepts. Possible values: true and false.

NILLABLE

VARCHAR2(4000)

True means that facts for the concept can be empty. Possible values: true and false.

TYPEURI

VARCHAR2(4000)

Namespace URI of the schema type of the concept.

TYPELOCALNAME

VARCHAR2(4000)

Local name of the schema type of the concept.

SGURI

VARCHAR2(4000)

Namespace URI of the substitution group for the concept.

SGLOCALNAME

VARCHAR2(4000)

Local name of the substitution group for the concept.

ELEM_HREF

VARCHAR2(4000)

Absolute path of the concept in the taxonomy schema.

LABEL

VARCHAR2(4000)

Human-readable name for the concept, unique across the taxonomy.

PREFERRED_LABEL

VARCHAR2(4000)

The preferred label derived from the preferredLabel arc from concept parent to the concept. If the concept has no parent then this is NULL.

ORAXBRL_ITEM, ORAXBRL_ITEMLIST

Data type ORAXBRL_ITEMLIST is a varray of ORAXBRL_ITEM, which is an object type with the following attributes that pertain to a fact:

Table 3-43 ORAXBRL_ITEM Object Type Attributes

Attribute Type Description

NAMESPACEURI

VARCHAR2(4000)

Namespace URI of the XML schema that defines the fact.

PREFIX

VARCHAR2(4000)

Prefix for the namespace specified by NAMESPACEURI.

NAME

VARCHAR2(4000)

Local name of the fact.

LABEL

VARCHAR2(4000)

Human-readable name for the fact, unique across the taxonomy.

PREFERRED_LABEL

VARCHAR2(4000)

Same as PREFERRED_LABEL attribute for type ORAXBRL_CONCEPT.

ID

VARCHAR2(4000)

Unique identifier of the fact.

ENTITY_SCHEME

VARCHAR2(4000)

Namespace of the entity identification scheme for the fact.

ENTITY_IDENTIFIER

VARCHAR2(4000)

Value of the entity identifier for the fact.

START_DATE

DATE

Start date for the fact, if the period type is duration. Otherwise, NULL.

END_DATE

DATE

End date for the fact, if the period type is duration. Otherwise, NULL.

CONTEXTREF

VARCHAR2(4000)

A reference to the context associated with the fact.

UNITREF

VARCHAR2(4000)

A reference to the unit associated with the fact.

DECIMALS

VARCHAR2(4000)

Number of decimal places to which numbers have been rounded.

VALUE

VARCHAR2(4000)

Value of the fact.

ORAXBRL_LOCLIST, ORAXBRL_STARSCHEMA

Data type ORAXBRL_LOCLIST is a varray of VARCHAR2(4000). Data type ORAXBRL_STARSCHEMA is an object type with the following attributes that pertain to a star schema:

Table 3-44 ORAXBRL_STARSCHEMA Object Type Attributes

Attribute Type Description

FACT_TABLE

VARCHAR2(4000)

Name of the fact table in the star schema.

DIMENSION_LIST

ORAXBRL_LOCLIST

Names of the dimension tables in the star schema.

ORAXBRL_DTSURLLIST, ORAXBRL_DTSURL_T

Data type ORAXBRL_DTSURLLIST is a varray of ORAXBRL_DTSURL_T, which is an object type with the following attributes that pertain to a file in the XBRL repository.

Table 3-45 ORAXBRL_DTSURL_T Object Type Attributes

Attribute Type Description

NAME

VARCHAR2(4000)

Name of the file.

TYPE

VARCHAR2(4000)

Type of the file: SCHEMA or LINKBASE.

DOCPATH

VARCHAR2(4000)

Location of the file in the XBRL repository.

XDBREPOPATH

VARCHAR2(4000)

Location of the file in the Oracle XML DB Repository.

OID

RAW(16)

Document or file object ID.


Footnote Legend

Footnote 1: Function instance_network2 has the same signature, except it returns an instance of data type ORAXBRL_CONCEPTLIST.
Footnote 2: Function multiple_instance_network2 has the same signature, except it returns an instance of data type ORAXBRL_ITEMLIST.
Footnote 3: Function concepts_network2 has the same signature, except it returns an instance of data type ORAXBRL_CONCEPTLIST.
Footnote 4: Function concept_roots2 has the same signature, except it returns an instance of data type ORAXBRL_CONCEPTLIST.
Footnote 5: Function concepts_in_tree2 has the same signature, except it returns an instance of data type ORAXBRL_CONCEPTLIST.
Footnote 6: A fact in an instance document corresponds to a concept in a taxonomy.