PK ˘FJoaŤ,mimetypeapplication/epub+zipPK˘FJOEBPS/d_xmldom.htm€˙ DBMS_XMLDOM

171 DBMS_XMLDOM

The DBMS_XMLDOM package is used to access XMLType objects, and implements the Document Object Model (DOM), an application programming interface for HTML and XML documents.

This chapter contains the following topics:


Using DBMS_XMLDOM


Overview

The Document Object Model (DOM) is an application programming interface (API) for HTML and XML documents. It defines the logical structure of documents, and the manner in which they are accessed and manipulated. In the DOM specification, the term "document" is used in the broad sense. XML is being increasingly used to represent many different kinds of information that may be stored in diverse systems. This information has been traditionally be seen as "data"; nevertheless, XML presents this data as documents, and the DBMS_XMLDOM package allows you access to both schema-based and non schema-based documents.


Note:

  • Before database startup, the read-from and write-to directories in the init.ORA file must be specified; for example: UTL_FILE_DIR=/mypath/insidemypath.

  • Read-from and write-to files must be on the server file system.


With DOM, anything found in an HTML or XML document can be accessed, changed, deleted, or added using the Document Object Model, with a few exceptions. In particular, the DOM interfaces for the XML internal and external subsets have not yet been specified.

One important objective of the W3C DOM specification is to provide a standard programming interface that can be used in a wide variety of environments, programming languages, and applications. Because the DOM standard is object-oriented while PL/SQL is essentially a procedural language, some changes had to be made:

  • Various DOM interfaces such as Node, Element, and others have equivalent PL/SQL types DOMNode, DOMElement, respectively.

  • Various DOMException codes such as WRONG_DOCUMENT_ERR, HIERARCHY_REQUEST_ERR, and others, have similarly named PL/SQL exceptions.

  • Various DOM Node type codes such as ELEMENT_NODE, ATTRIBUTE_NODE, and others, have similarly named PL/SQL constants.

  • Subprograms defined on a DOM type become functions or procedures that accept it as a parameter. For example, to perform APPENDCHILD Function on a DOMNode n, the APPENDCHILD FunctionPL/SQL function is provided.

  • To perform setAttribute on a DOMElement elemSETATTRIBUTE Procedures, use PL/SQL procedure .

DOM defines an inheritance hierarchy. For example, Document, Element, and Attr are defined to be subtypes of Node (see Figure 171-1). Thus, a method defined in the Node interface should be available in these as well. Since such inheritance is not supported in PL/SQL, it is implemented through direct invocation of the MAKENODE function. Calling MAKENODE on various DOM types converts these types into a DOMNode. The appropriate functions or procedures that accept DOMNodes can then be called to operate on these types. If, subsequently, type specific functionality is desired, the DOMNode can be converted back into the original type by the makeXXX functions, where DOMXXX is the desired DOM type.

Figure 171-1 Inheritance Diagram for DOM Types

Description of Figure 171-1 follows

The implementation of this interface follows the REC-DOM-Level-1-19981001.


Security Model

Owned by XDB, the DBMS_XMLDOM package must be created by SYS or XDB. The EXECUTE privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user.


Constants

Defined constants of DBMS_XMLDOM are listed in Table 171-1.

Table 171-1 Defined Constants for DBMS_XMLDOM

ConstantTypeValueDescription

ELEMENT_NODE

PLS_INTEGER

1

The Node is an Element.

ATTRIBUTE_NODE

PLS_INTEGER

2

The Node is an Attribute.

TEXT_NODE

PLS_INTEGER

3

The Node is a Text node.

CDATA_SECTION_NODE

PLS_INTEGER

4

The Node is a CDataSection.

ENTITY_REFERENCE_NODE

PLS_INTEGER

5

The Node is an Entity Reference.

ENTITY_NODE

PLS_INTEGER

6

The Node is an Entity.

PROCESSING_INSTRUCTION_NODE

PLS_INTEGER

7

The Node is a Processing Instruction.

COMMENT_NODE

PLS_INTEGER

8

The Node is a Comment.

DOCUMENT_NODE

PLS_INTEGER

9

The Node is a Document.

DOCUMENT_TYPE_NODE

PLS_INTEGER

10

The Node is a Document Type Definition.

DOCUMENT_FRAGMENT_NODE

PLS_INTEGER

11

The Node is a Document fragment.

NOTATION_NODE

PLS_INTEGER

12

The Node is a Notation.



Types

The following types for DBMS_XMLDOM.DOMTYPE are defined in Table 171-2:

Table 171-2 XDB_XMLDOM Types

TypeDescription

DOMATTR

Implements the DOM Attribute interface.

DOMCDATASECTION

Implements the DOM CDataSection interface.

DOMCHARACTERDATA

Implements the DOM Character Data interface.

DOMCOMMENT

Implements the DOM Comment interface.

DOMDOCUMENT

Implements the DOM Document interface.

DOMDOCUMENTFRAGMENT

Implements the DOM DocumentFragment interface.

DOMDOCUMENTTYPE

Implements the DOM Document Type interface.

DOMELEMENT

Implements the DOM Element interface.

DOMENTITY

Implements the DOM Entity interface.

DOMENTITYREFERENCE

Implements the DOM EntityReference interface.

DOMIMPLEMENTATION

Implements the DOM Implementation interface.

DOMNAMEDNODEMAP

Implements the DOM Named Node Map interface.

DOMNODE

Implements the DOM Node interface.

DOMNODELIST

Implements the DOM NodeList interface.

DOMNOTATION

Implements the DOM Notation interface.

DOMPROCESSINGINSTRUCTION

Implements the DOM Processing instruction interface.

DOMTEXT

Implements the DOM Text interface.



Exceptions

The exceptions listed in Table 171-3 are defined for DBMS_XMLDOM:

Table 171-3 Exceptions for DBMS_XMLDOM

ExceptionDescription

DOMSTRING_SIZE_ERR

If the specified range of text does not fit into a DOMString.

HIERARCHY_REQUEST_ERR

If any node is inserted somewhere it doesn't belong.

INDEX_SIZE_ERR

If index or size is negative, or greater than the allowed value.

INUSE_ATTRIBUTE_ERR

If an attempt is made to add an attribute that is already in use elsewhere.

INVALID_CHARACTER_ERR

If an invalid or illegal character is specified, such as in a name. See production 2 in the XML specification for the definition of a legal character, and production 5 for the definition of a legal name character.

NO_DATA_ALLOWED_ERROR

If data is specified for a node that does not support data.

NOT_FOUND_ERR

If an attempt is made to reference a node in a context where it does not exist.

NO_MODIFICATION_ALLOWED_ERR

If an attempt is made to modify an object where modifications are not allowed.

NOT_SUPPORTED_ERR

If the implementation does not support the requested type of object or operation.

WRONG_DOCUMENT_ERR

If a node is used in a different document than the one that created it (that doesn't support it).



Subprogram Groups

DBMS_XMLDOM subprograms are divided into groups according to W3C Interfaces.


DOMNode Subprograms

Table 171-4 Summary of DOMNode Subprograms; DBMS_XMLDOM

SubprogramDescription

ADOPTNODE Function


Adopts a node from another document

APPENDCHILD Function


Appends a new child to the node

CLONENODE Function


Clones the node

FREENODE Procedure


Frees all resources associated with the node

GETATTRIBUTES Function


Retrieves the attributes of the node

GETCHILDNODES Function


Retrieves the children of the node

GETEXPANDEDNAME Procedure and Functions


Retrieves the expanded name of the node

GETFIRSTCHILD Function


Retrieves the first child of the node

GETLASTCHILD Function


Retrieves the last child of the node

GETLOCALNAME Procedure and Functions


Retrieves the local part of the qualified name

GETNAMESPACE Procedure and Functions


Retrieves the node's namespace URI

GETNEXTSIBLING Function


Retrieves the next sibling of the node

GETNODENAME Function


Retrieves the Name of the Node

GETNODETYPE Function


Retrieves the Type of the node

GETNODEVALUE Function


Retrieves the Value of the Node

GETNODEVALUEASBINARYSTREAM Function & Procedure


Retrieves Node Value as binary stream

GETNODEVALUEASCHARACTERSTREAM Function & Procedure


Retrieves Node Value as character stream

GETOWNERDOCUMENT Function


Retrieves the owner document of the node

GETPARENTNODE Function

Retrieves the parent of this node

GETPREFIX Function


Retrieves the namespace prefix

GETPREVIOUSSIBLING Function


Retrieves the previous sibling of the node

GETSCHEMANODE Function


Retrieves the associated schema URI

HASATTRIBUTES Function


Tests if the node has attributes

HASCHILDNODES Function


Tests if the node has child nodes

IMPORTNODE Function


Imports a node from another document

INSERTBEFORE Function


Inserts a child before the reference child

ISNULL Functions


Tests if the node is NULL

MAKEATTR Function


Casts the node to an Attribute

MAKECDATASECTION Function


Casts the node to a CData Section

MAKECHARACTERDATA Function


Casts the node to Character Data

MAKECOMMENT Function


Casts the node to a Comment

MAKEDOCUMENT Function


Casts the node to a DOM Document

MAKEDOCUMENTFRAGMENT Function


Casts the node to a DOM Document Fragment

MAKEDOCUMENTTYPE Function


Casts the node to a DOM Document Type

MAKEELEMENT Function


Casts the node to a DOM Element

MAKEENTITY Function


Casts the node to a DOM Entity

MAKEENTITYREFERENCE Function


Casts the node to a DOM Entity Reference

MAKENOTATION Function


Casts the node to a DOM Notation

MAKEPROCESSINGINSTRUCTION Function


Casts the node to a DOM Processing Instruction

MAKETEXT Function


Casts the node to a DOM Text

REMOVECHILD Function


Removes a specified child from a node

REPLACECHILD Function


Replaces the old child with a new child

SETNODEVALUE Procedure


Sets the Value of the node

SETNODEVALUEASBINARYSTREAM Function & Procedure


Sets the Node Value as binary stream

SETNODEVALUEASCHARACTERSTREAM Function & Procedure


Sets the Node Value as a character stream

SETPREFIX Procedure


Sets the namespace prefix

USEBINARYSTREAM Function


Establishes that the stream is valid

WRITETOBUFFER Procedures


Writes the contents of the node to a buffer

WRITETOCLOB Procedures


Writes the contents of the node to a CLOB

WRITETOFILE Procedures


Writes the contents of the node to a file



DOMAttr Subprograms

Table 171-5 Summary of DOMAttr Subprograms; DBMS_XMLDOM

MethodDescription

GETEXPANDEDNAME Procedure and Functions


Retrieves the expanded name of the attribute

GETLOCALNAME Procedure and Functions


Retrieves the local name of the attribute

GETNAME Functions


Retrieves the name of the attribute

GETNAMESPACE Procedure and Functions


Retrieves the NS URI of the attribute

GETOWNERELEMENT Function


Retrieves the Element node, parent of the attribute

GETQUALIFIEDNAME Functions


Retrieves the Qualified Name of the attribute

GETSPECIFIED Function


Tests if attribute was specified in the element

GETVALUE Function


Retrieves the value of the attribute

ISNULL Functions


Tests if the Attribute node is NULL

MAKENODE Functions


Casts the Attribute to a node

SETVALUE Procedure


Sets the value of the attribute



DOMCDataSection Subprograms

Table 171-6 Summary of DOMCdata Subprograms; DBMS_XMLDOM

MethodDescription

ISNULL Functions


Tests if the CDataSection is NULL

MAKENODE Functions


Casts the CDatasection to a node



DOMCharacterData Subprograms

Table 171-7 Summary of DOMCharacterData Subprograms; DBMS_XMLDOM

MethodDescription

APPENDDATA Procedure


Appends the specified data to the node data

DELETEDATA Procedure


Deletes the data from the specified offSets

GETDATA Functions


Retrieves the data of the node

GETLENGTH Functions


Retrieves the length of the data

INSERTDATA Procedure


Inserts the data in the node at the specified offSets

ISNULL Functions


Tests if the CharacterData is NULL

MAKENODE Functions


Casts the CharacterData to a node

REPLACEDATA Procedure


Changes a range of characters in the node

SETDATA Procedures


Sets the data to the node

SUBSTRINGDATA Function


Retrieves the substring of the data



DOMComment Subprograms

Table 171-8 Summary of DOMComment Subprograms; DBMS_XMLDOM

MethodDescription

ISNULL Functions


Tests if the comment is NULL

MAKENODE Functions


Casts the Comment to a node



DOMDocument Subprograms

Table 171-9 Summary of DOMDocument Subprograms; DBMS_XMLDOM

MethodDescription

CREATEATTRIBUTE Functions


Creates an Attribute

CREATECDATASECTION Function


Creates a CDataSection node

CREATECOMMENT Function


Creates a Comment node

CREATEDOCUMENT Function


Creates a new Document

CREATEDOCUMENTFRAGMENT Function


Creates a new Document Fragment

CREATEELEMENT Functions


Creates a new Element

CREATEENTITYREFERENCE Function


Creates an Entity reference

CREATEPROCESSINGINSTRUCTION Function


Creates a Processing Instruction

CREATETEXTNODE Function


Creates a Text node

FREEDOCFRAG Procedure


Frees the document fragment

FREEDOCUMENT Procedure


Frees the document

GETCHARSET Function


Retrieves the characterset of the DOM document

GETDOCTYPE Function


Retrieves the DTD of the document

GETDOCUMENTELEMENT Function


Retrieves the root element of the document

GETELEMENTSBYTAGNAME Functions


Retrieves

  • the elements in the DOMNODELIST by tag name

  • elements in the subtree of a DOMNODELIST by tagname

GETIMPLEMENTATION Function


Retrieves the DOM implementation

GETSTANDALONE Function


Retrieves the standalone property of the document

GETVERSION Function


Retrieves the version of the document

GETXMLTYPE Function


Retrieves the XMLType associated with the DOM Document

ISNULL Functions


Tests if the document is NULL

MAKENODE Functions


Casts the document to a node

NEWDOMDOCUMENT Functions


Creates a new document

SETCHARSET Procedure


Sets the characterset of the DOM document

SETDOCTYPE Procedure


Sets the DTD of the document

SETSTANDALONE Procedure


Sets the standalone property of the document

SETVERSION Procedure


Sets the version of the document

WRITETOBUFFER Procedures


Writes the document to a buffer

WRITETOCLOB Procedures


Writes the document to a CLOB

WRITETOFILE Procedures


Writes the document to a file



DOMDocumentFragment Subprograms

Table 171-10 Summary of DOMDocumentFragment Subprograms; DBMS_XMLDOM

MethodDescription

FREEDOCFRAG Procedure


Frees the specified document fragment

ISNULL Functions


Tests if the DocumentFragment is NULL

MAKENODE Functions


Casts the Document Fragment to a node

WRITETOBUFFER Procedures


Writes the contents of a document fragment into a buffer



DOMDocumentType Subprograms

Table 171-11 Summary of DOMDocumentType Subprograms; DBMS_XMLDOM

MethodDescription

FINDENTITY Function


Finds the specified entity in the document type

FINDNOTATION Function


Finds the specified notation in the document type

GETENTITIES Function


Retrieves the nodemap of entities in the Document type

GETNAME Functions


Retrieves the name of the Document type

GETNOTATIONS Function


Retrieves the nodemap of the notations in the Document type

GETPUBLICID Functions


Retrieves the public ID of the document type

GETSYSTEMID Functions


Retrieves the system ID of the document type

ISNULL Functions


Tests if the Document Type is NULL

MAKENODE Functions


Casts the document type to a node



DOMElement Subprograms

Table 171-12 Summary of DOMElement Subprograms; DBMS_XMLDOM

MethodDescription

FREEELEMENT Procedure


Frees memory allocated to a DOMElement handle

GETATTRIBUTE Functions


Retrieves the attribute node by name

GETATTRIBUTENODE Functions


Retrieves the attribute node by name

GETCHILDRENBYTAGNAME Functions


Retrieves children of the element by tag name

GETELEMENTSBYTAGNAME Functions


Retrieves

  • the elements in the DOMNODELIST by tag name

  • elements in the subtree of a DOMNODELIST by tagname

GETEXPANDEDNAME Procedure and Functions


Retrieves the expanded name of the element

GETLOCALNAME Procedure and Functions


Retrieves the local name of the element

GETNAMESPACE Procedure and Functions


Retrieves the NS URI of the element

GETQUALIFIEDNAME Functions


Retrieves the qualified name of the element

GETTAGNAME Function


Retrieves the Tag name of the element

HASATTRIBUTE Functions


Tests if an attribute exists

ISNULL Functions


Tests if the Element is NULL

MAKENODE Functions


Casts the Element to a node

NORMALIZE Procedure


Normalizes the text children of the element

REMOVEATTRIBUTE Procedures


Removes the attribute specified by the name

REMOVEATTRIBUTENODE Function


Removes the attribute node in the element

RESOLVENAMESPACEPREFIX Function


Resolve the prefix to a namespace URI

SETATTRIBUTE Procedures


Sets the attribute specified by the name

SETATTRIBUTENODE Functions


Sets the attribute node in the element



DOMEntity Subprograms

Table 171-13 Summary of DOMEntity Subprograms; DBMS_XMLDOM

MethodDescription

GETNOTATIONNAME Function


Retrieves the notation name of the entity

GETPUBLICID Functions


Retrieves the public Id of the entity

GETSYSTEMID Functions


Retrieves the system Id of the entity

ISNULL Functions


Tests if the Entity is NULL

MAKENODE Functions


Casts the Entity to a node



DOMEntityReference Subprograms

Table 171-14 Summary of DOMEntityReference Subprograms; DBMS_XMLDOM

MethodDescription

ISNULL Functions


Tests if the DOMEntityReference is NULL

MAKENODE Functions


Casts the DOMEntityReference to NULL



DOMImplementation Subprograms

Table 171-15 Summary of DOMImplementation Subprograms; DBMS_XMLDOM

MethodDescription

ISNULL Functions


Tests if the DOMImplementation node is NULL

HASFEATURE Function


Tests if the DOMImplementation implements a feature



DOMNamedNodeMap Subprograms

Table 171-16 Summary of DOMNamedNodeMap Subprograms; DBMS_XMLDOM

MethodDescription

GETLENGTH Functions


Retrieves the number of items in the map

GETNAMEDITEM Function


Retrieves the item specified by the name

ISNULL Functions


Tests if the NamedNodeMap is NULL

ITEM Functions


Retrieves the item given the index in the map

REMOVENAMEDITEM Function


Removes the item specified by name

SETNAMEDITEM Function


Sets the item in the map specified by the name



DOMNodeList Subprograms

Table 171-17 Summary of DOMNodeList Subprograms; DBMS_XMLDOM

MethodDescription

FREENODELIST Procedure


Frees all resources associated with a nodelist

GETLENGTH Functions


Retrieves the number of items in the list

ISNULL Functions


Tests if the NodeList is NULL

ITEM Functions


Retrieves the item given the index in the list



DOMNotation Subprograms

Table 171-18 Summary of DOMNotation Subprograms; DBMS_XMLDOM

MethodDescription

GETPUBLICID Functions


Retrieves the public Id of the notation

GETSYSTEMID Functions


Retrieves the system Id of the notation

ISNULL Functions


Tests if the Notation is NULL

MAKENODE Functions


Casts the notation to a node



DOMProcessingInstruction Subprograms

Table 171-19 Summary of DOMProcessingInstruction Subprograms; DBMS_XMLDOM

MethodDescription

GETDATA Functions


Retrieves the data of the processing instruction

GETTARGET Function


Retrieves the target of the processing instruction

ISNULL Functions


Tests if the Processing Instruction is NULL

MAKENODE Functions


Casts the Processing Instruction to a node

SETDATA Procedures


Sets the data of the processing instruction



DOMText Subprograms

Table 171-20 Summary of DOMText Subprograms; DBMS_XMLDOM

MethodDescription

ISNULL Functions


Tests if the text is NULL

MAKENODE Functions


Casts the text to a node

SPLITTEXT Function


Splits the contents of the text node into 2 text nodes



Summary of DBMS_XMLDOM Subprograms

Table 171-21 Summary of DBMS_XMLDOM Package Subprogram

SubprogramDescriptionGroup

ADOPTNODE Function


Adopts a node from another document

DOMNode Subprograms


APPENDCHILD Function


Appends a new child to the node

DOMNode Subprograms


APPENDDATA Procedure


Appends the specified data to the node data

DOMCharacterData Subprograms


CLONENODE Function


Clones the node

DOMNode Subprograms


CREATEATTRIBUTE Functions


Creates an Attribute

DOMDocument Subprograms


CREATECDATASECTION Function


Creates a CDataSection node

DOMDocument Subprograms


CREATECOMMENT Function


Creates a Comment node

DOMDocument Subprograms


CREATEDOCUMENT Function


Creates a new Document

DOMDocument Subprograms


CREATEDOCUMENTFRAGMENT Function


Creates a new Document Fragment

DOMDocument Subprograms


CREATEELEMENT Functions


Creates a new Element

DOMDocument Subprograms


CREATEENTITYREFERENCE Function


Creates an Entity reference

DOMDocument Subprograms


CREATEPROCESSINGINS€˙TRUCTION Function


Creates a Processing Instruction

DOMDocument Subprograms


CREATETEXTNODE Function


Creates a Text node

DOMDocument Subprograms


DELETEDATA Procedure


Deletes the data from the specified offSets

DOMCharacterData Subprograms


FINDENTITY Function


Finds the specified entity in the document type

DOMDocumentType Subprograms


FINDNOTATION Function


Finds the specified notation in the document type

DOMDocumentType Subprograms


FREEDOCFRAG Procedure


Frees the document fragment

DOMDocument Subprograms and DOMDocumentFragment Subprograms

FREEDOCUMENT Procedure


Frees the document

DOMDocument Subprograms


FREEELEMENT Procedure


Frees memory allocated to a DOMElement handle

DOMElement Subprograms


FREENODE Procedure


Frees all resources associated with the node

DOMNode Subprograms


FREENODELIST Procedure


Frees all resources associated with a nodelist

DOMNodeList Subprograms


GETATTRIBUTE Functions


Retrieves the attribute node by name

DOMElement Subprograms


GETATTRIBUTENODE Functions


Retrieves the attribute node by name

DOMElement Subprograms


GETATTRIBUTES Function


Retrieves the attributes of the node

DOMNode Subprograms


GETCHARSET Function


Retrieves the characterset of the DOM document

DOMDocument Subprograms


GETCHILDNODES Function


Retrieves the children of the node

DOMNode Subprograms


GETCHILDRENBYTAGNAME Functions


Retrieves children of the element by tag name

DOMCharacterData Subprograms


GETDATA Functions


Retrieves

  • the data of the node

  • the data of the processing instruction


GETDOCTYPE Function


Retrieves the DTD of the document

DOMDocument Subprograms


GETDOCUMENTELEMENT Function


Retrieves the root element of the document

DOMDocument Subprograms


GETELEMENTSBYTAGNAME Functions


Retrieves

  • the elements in the DOMNODELIST by tag name

  • elements in the subtree of a DOMNODELIST by tagname


GETENTITIES Function


Retrieves the nodemap of entities in the Document type

DOMDocumentType Subprograms


GETEXPANDEDNAME Procedure and Functions


Retrieves

  • the expanded name of the node

  • the expanded name of the attribute

  • the expanded name of the element


GETFIRSTCHILD Function


Retrieves the first child of the node

DOMNode Subprograms


GETIMPLEMENTATION Function


Retrieves the DOM implementation

DOMDocument Subprograms


GETLASTCHILD Function


Retrieves the last child of the node

DOMNode Subprograms


GETLENGTH Functions


Retrieves

  • the length of the data

  • the number of items in the map

  • the number of items in the list


GETLOCALNAME Procedure and Functions


Retrieves

  • the local part of the qualified name

  • the local name of the attribute

  • the local name of the element


GETNAME Functions


Retrieves

  • the name of the attribute

  • the name of the Document type


GETNAMEDITEM Function


Retrieves

  • an item specified by name

  • and namespace URI )


GETNAMESPACE Procedure and Functions


Retrieves

  • the node's namespace URI

  • the NS URI of the attribute

  • the NS URI of the element


GETNEXTSIBLING Function


Retrieves the next sibling of the node

DOMNode Subprograms


GETNODENAME Function


Retrieves the Name of the Node

DOMNode Subprograms


GETNODETYPE Function


Retrieves the Type of the node

DOMNode Subprograms


GETNODEVALUE Function


Retrieves the Value of the Node

DOMNode Subprograms


GETNODEVALUEASBINARYSTREAM Function & Procedure


Retrieves the Node Value as binary stream

DOMNode Subprograms


GETNODEVALUEASCHARACTERSTREAM Function & Procedure


Retrieves the Node Value as character stream

DOMNode Subprograms


GETNOTATIONNAME Function


Retrieves the notation name of the entity

DOMEntity Subprograms


GETNOTATIONS Function


Retrieves the nodemap of the notations in the Document type

DOMDocumentType Subprograms


GETTARGET Function


Retrieves the target of the processing instruction

DOMProcessingInstruction Subprograms


GETOWNERDOCUMENT Function


Retrieves the owner document of the node

DOMNode Subprograms


GETOWNERELEMENT Function


Retrieves the Element node, parent of the attribute

DOMAttr Subprograms


GETPARENTNODE Function

Retrieves the parent of this node

DOMNode Subprograms


GETPREFIX Function


Retrieves the namespace prefix

)

DOMNode Subprograms


GETPREVIOUSSIBLING Function


Retrieves the previous sibling of the node

DOMNode Subprograms


GETPUBLICID Functions


Retrieves

  • the public ID of the document type

  • the public Id of the entity

  • the public Id of the notation


GETQUALIFIEDNAME Functions


Retrieves

  • the Qualified Name of the attribute

  • the qualified name of the element


GETSCHEMANODE Function


Retrieves the associated schema URI

DOMNode Subprograms


GETSPECIFIED Function


Tests if attribute was specified in the element.

DOMAttr Subprograms


GETSTANDALONE Function


Retrieves the standalone property of the document

DOMDocument Subprograms


GETSYSTEMID Functions


Retrieves

  • the system ID of the document type

  • the system Id of the entity

  • the system Id of the notation


GETTAGNAME Function


Retrieves the Tag name of the element

DOMElement Subprograms


GETVALUE Function


Retrieves the value of the attribute

DOMAttr Subprograms


GETVERSION Function


Retrieves the version of the document

DOMDocument Subprograms)

GETXMLTYPE Function


Retrieves the XMLType associated with the DOM Document

DOMDocument Subprograms


HASATTRIBUTES Function


Tests if the node has attributes

DOMNode Subprograms


HASATTRIBUTE Functions


Tests if an attribute exists

DOMElement Subprograms


HASCHILDNODES Function


Tests if the node has child nodes

DOMNode Subprograms


HASFEATURE Function


Tests if the DOMImplementation implements a feature

DOMImplementation Subprograms


IMPORTNODE Function


Imports a node from another document

DOMNode Subprograms


INSERTBEFORE Function


Inserts a child before the reference child

DOMNode Subprograms


INSERTDATA Procedure


Inserts the data in the node at the specified offSets

DOMCharacterData Subprograms


ISNULL Functions


Tests

  • if the node is NULL

  • if the Attribute node is NULL

  • if the CDataSection is NULL

  • if the CharacterData is NULL

  • if the comment is NULL

  • if the document is NULL

  • if the DocumentFragment is NULL

  • if the Document Type is NULL

  • if the Element is NULL

  • if the Entity is NULL

  • if the DOMEntityReference is NULL

  • if the DOMImplementation node is NULL

  • if the NamedNodeMap is NULL

  • if the NodeList is NULL

  • if the Notation is NULL

  • if the Processing Instruction is NULL

  • if the text is NULL


ITEM Functions


Retrieves

  • the item given the index in the map

  • the item given the index in the NodeList


MAKEATTR Function


Casts the node to an Attribute

DOMNode Subprograms


MAKECDATASECTION Function


Ca€˙sts the node to a CData Section

DOMNode Subprograms


MAKECHARACTERDATA Function


Casts the node to Character Data

DOMNode Subprograms


MAKECOMMENT Function


Casts the node to a Comment

DOMNode Subprograms


MAKEDOCUMENT Function


Casts the node to a DOM Document

DOMNode Subprograms


MAKEDOCUMENTFRAGMENT Function


Casts the node to a DOM Document Fragment

DOMNode Subprograms)

MAKEDOCUMENTTYPE Function


Casts the node to a DOM Document Type

DOMNode Subprograms


MAKEELEMENT Function


Casts the node to a DOM ElemenT

DOMNode Subprograms


MAKEENTITY Function


Casts the node to a DOM Entity

DOMNode Subprograms


MAKEENTITYREFERENCE Function


Casts the node to a DOM Entity Reference

DOMNode Subprograms


MAKENODE Functions


Casts

  • the Attribute to a node

  • the CDatasection to a node

  • the CharacterData to a node

  • the Comment to a node

  • the document to a node

  • the Document Fragment to a node

  • the document type to a node

  • the Element to a node

  • the Entity to a node

  • the DOMEntityReference to NULL

  • the notation to a node

  • the Processing Instruction to a node

  • the text to a node


MAKENOTATION Function


Casts the node to a DOM Notation

DOMNode Subprograms


MAKEPROCESSINGINSTRUCTION Function


Casts the node to a DOM Processing Instruction

DOMNode Subprograms


MAKETEXT Function


Casts the node to a DOM Text

DOMNode Subprograms


NEWDOMDOCUMENT Functions


Creates a new document

DOMDocument Subprograms


NORMALIZE Procedure


Normalizes the text children of the element

DOMElement Subprograms


REMOVEATTRIBUTE Procedures


Removes the attribute specified by the name

DOMElement Subprograms


REMOVEATTRIBUTENODE Function


Removes the attribute node in the element

DOMElement Subprograms


REMOVECHILD Function


Removes a specified child from a node

DOMNode Subprograms


REMOVENAMEDITEM Function


Removes the item specified by name

DOMNamedNodeMap Subprograms


REPLACECHILD Function


Replaces the old child with a new child

DOMNode Subprograms


REPLACEDATA Procedure


Changes a range of characters in the node

DOMCharacterData Subprograms


RESOLVENAMESPACEPREFIX Function


Resolve the prefix to a namespace URI

DOMElement Subprograms


SETATTRIBUTE Procedures


Sets the attribute specified by the name

DOMElement Subprograms


SETATTRIBUTENODE Functions


Sets the attribute node in the element

DOMElement Subprograms


SETCHARSET Procedure


Sets the characterset of the DOM document

DOMDocument Subprograms


SETDATA Procedures


Sets

  • the data to the node

  • the data of the processing instruction


SETDOCTYPE Procedure


Sets the DTD of the document.

DOMDocument Subprograms


SETNAMEDITEM Function


Sets the item in the map specified by the name

DOMNamedNodeMap Subprograms


SETNODEVALUE Procedure


Sets the Value of the node

DOMNode Subprograms


SETNODEVALUEASBINARYSTREAM Function & Procedure


Sets the Node Value as a binary stream

DOMNode Subprograms


SETNODEVALUEASCHARACTERSTREAM Function & Procedure


Sets the Node Value as a character stream

DOMNode Subprograms


SETPREFIX Procedure


Sets the namespace prefix

DOMNode Subprograms


SETSTANDALONE Procedure


Sets the standalone property of the document

DOMDocument Subprograms


SETVALUE Procedure


Sets the value of the attribute

DOMAttr Subprograms


SETVERSION Procedure


Sets the version of the document

DOMDocument Subprograms


SPLITTEXT Function


Splits the contents of the text node into 2 text nodes

DOMText Subprograms


SUBSTRINGDATA Function


Retrieves the substring of the data

DOMCharacterData Subprograms


USEBINARYSTREAM Function


Strabismus that the stream is valid for use

DOMNode Subprograms


WRITETOBUFFER Procedures


Writes

  • the contents of the node to a buffer

  • the document to a buffer

  • the contents of a document fragment into a buffer


WRITETOCLOB Procedures


Writes

  • the contents of the node to a CLOB

  • the document to a CLOB


WRITETOFILE Procedures


Writes

  • the contents of the node to a file

  • the document to a file




ADOPTNODE Function

This function adopts a node from another document, and returns this new node.


See Also:

DOMNode Subprograms for other subprograms in this group

Syntax

DBMS_XMLDOM.ADOPTNODE(
   doc            IN   DOMDocument,
   importedNode   IN   DOMNode)
 RETURN DOMNODE;

Parameters

Table 171-22 ADOPTNODE Function Parameters

ParameterDescription

doc

Document that is adopting the node

importedNode

Node to adopt


Usage Notes

Note that the ADOPTNODE Function removes the node from the source document while the IMPORTNODE Function clones the node in the source document.


APPENDCHILD Function

This function adds the node newchild to the end of the list of children of this node, and returns the newly added node. If the newchild is already in the tree, it is first removed.

Syntax

DBMS_XMLDOM.APPENDCHILD(
   n          IN    DOMNode,
   newchild   IN    DOMNode)
 RETURN DOMNODE;

Parameters

Table 171-23 APPENDCHILD Function Parameters

ParameterDescription

n

DOMNode

newchild

The child to be appended to the list of children of node n



APPENDDATA Procedure

This procedure appends the string to the end of the character data of the node. Upon success, data provides access to the concatenation of data and the specified string argument.

Syntax

DBMS_XMLDOM.APPENDDATA(
   cd      IN    DOMCHARACTERDATA, 
   arg     IN    VARCHAR2);

Parameters

Table 171-24 APPENDDATA Procedure Parameters

ParameterDescription

cd

DOMCHARACTERDATA

arg

The data to append to the existing data



CLONENODE Function

This function returns a duplicate of this node, and serves as a generic copy constructor for nodes. The duplicate node has no parent, its parent node is NULL.

Syntax

DBMS_XMLDOM.CLONENODE(
   n       IN    DOMNODE,
   deep    IN    BOOLEAN)
 RETURN DOMNODE;

Parameters

Table 171-25 CLONENODE Function Parameters

ParameterDescription

n

DOMNODE

deep

Determines if children are to be cloned


Usage Notes

  • Cloning an Element copies all attributes and their values, including those generated by the XML processor to represent defaulted attributes, but this method does not copy any text it contains unless it is a deep clone, since the text is contained in a child Text node.

  • Cloning an Attribute directly, as opposed to be cloned as part of an Element cloning operation, returns a specified attribute (specified is TRUE).

  • Cloning any other type of node simply returns a copy of this node.


CREATEATTRIBUTE Functions

This function creates a DOMATTR node.

Syntax

Creates a DOMATTR with the specified name:

DBMS_XMLDOM.CREATEATTRIBUTE(
   doc     IN    DOMDOCUMENT,
   name    IN    VARCHAR2)
 RETURN DOMATTR;

Creates a DOMATTR with the specified name and namespace URI:

DBMS_XMLDOM.CREATEATTRIBUTE(
   doc     IN    DOMDOCUMENT,
   qname    IN    VARCHAR2,
   ns      IN     VARCHAR2)
RETURN DOMATTR;

Parameters

Table 171-26 CREATEATTRIBUTE Function Parameters

ParameterDescription

doc

DOMDOCUMENT

qname

New attribute qualified name

ns

Namespace



CREATECDATASECTION Function

This function creates a DOMCDATASECTION node.

Syntax

DBMS_XMLDOM.CREATECDATASECTION(
   doc     IN      DOMDOCUMENT,
   data    IN      VARCHAR2)
 RETURN DOMCDATASECTION;

Parameters

Table 171-27 CREATECDATASECTION Function Parameters

ParameterDescription

doc

DOMDOCUMENT

data

Content of the DOMCDATASECTION node



CREATECOMMENT Function

This function creates a DOMCOMMENT node.

Syntax

DBMS_XMLDOM.CREATECOMMENT(
   doc      IN      DOMDOCUMENT,
   data     IN      VARCHAR2)
 RETURN DOMCOMMENT;

Parameters

Table 171-28 CREATECOMMENT Function Parameters

ParameterDescription

doc

DOMDOCUMENT

data

Content of the DOMComment node



CREATEDOCUMENT Function

This function creates a DOMDOCUMENT with specified namespace URI, root element name, DTD.

Syntax

DBMS_XMLDOM.CREATEDOCUMENT(
   namespaceURI      IN     VARCHAR2,
   qualifiedName     IN     VARCHAR2,
   doctype           IN     DOMTYPE := NULL)
 RETURN DOMDOCUMENT;

Parameters

Table 171-29 CREATEDOCUMENT Function Parameters

ParameterDescription

namespaceURI

Namespace URI

qualifiedName

Root element name

doctype

Document type



CREATEDOCUMENTFRAGMENT Function

This function creates a DOMDOCUMENTFRAGMENT.

Syntax

DBMS_XMLDOM.CREATEDOCUMENTFRAGMENT(
   doc      IN     DOMDOCUMENT)
 RETURN DOMDOCUMENTFRAGMENT;

Parameters

Table 171-30 CREATEDOCUMENTFRAGMENT Function Parameters

ParameterDescription

doc

DOMDocument



CREATEELEMENT Functions

This function creates a DOMELEMENT.

Syntax

Creates a DOMElement with specified name:

DBMS_XMLDOM.CREATEELEMENT(
   doc        IN      DOMDOCUMENT,
   tagName    IN      VARCHAR2)
 RETURN DOMELEMENT;

Creates a DOMElement with specified name and namespace URI:

DBMS_XMLDOM.CREATEELEMENT(
   doc        IN     DOMDOCUMENT,
   tagName    IN     VARCHAR2,
   ns         IN     VARCHAR2)
 RETURN DOMELEMENT;

Parameters

Table 171-31 CREATEELEMENT Function Parameters

ParameterDescription

doc

DOMDOCUMENT

tagName

Tagname for new DOMELEMENT

ns

Namespace



CREATEENTITYREFERENCE Function

This function creates a DOMENTITYREFERENCE node.

Syntax

DBMS_XMLDOM.CREATEENTITYREFERENCE(
   doc        IN     DOMDOCUMENT,
   name       IN     VARCHAR2)
 RETURN DOMENTITYREFERENCE;

Parameters

Table 171-32 CREATEENTITYREFERENCE Function Parameters

ParameterDescription

doc

DOMDOCUMENT

name

New entity reference name



CREATEPROCESSINGINSTRUCTION Function

This function creates a DOMPROCESSINGINSTRUCTION node.

Syntax

DBMS_XMLDOM.CREATEPROCESSINGINSTRUCTION(
   doc       IN      DOMDocument,
   target    IN      VARCHAR2,
   data      IN      VARCHAR2)
 RETURN DOMPROCESSINGINSTRUCTION;

Parameters

Table 171-33 CREATEPROCESSINGINSTRUCTION Function Parameters

ParameterDescription

doc

DOMDOCUMENT

target

Target of the new processing instruction

data

Content data of the new processing instruction



CREATETEXTNODE Function

This function creates a DOMTEXT node.

Syntax

DBMS_XMLDOM.CREATETEXTNODE(
   doc      IN     DOMDocument,
   data     IN     VARCHAR2)
 RETURN DOMTEXT;

Parameters

Table 171-34 CREATETEXTNODE Function Parameters

ParameterDescription

doc

DOMDOCUMENT

data

Content of the DOMText node



DELETEDATA Procedure

This procedure removes a range of characters from the node. Upon success, data and length reflect the change.

Syntax

DBMS_XMLDOM.DELETEDATA(
   cd        IN     DOMCHARACTERDATA,
   offset    IN     NUMBER,
   cnt       IN     NUMBER);

Parameters

Table 171-35 DELETEDATA PROCEDURE Parameters

ParameterDescription

cd

DOMCHARACTERDATA

offset

The offset from which to delete the data

cnt

The number of characters (starting from offset) to delete



FINDENTITY Function

This function finds an entity in the specified DTD, and returns that entity if found.

Syntax

DBMS_XMLDOM.FINDENTITY(
   dt     IN     DOMDOCUMENTTYPE,
   name   IN     VARCHAR2,
   par    IN     BOOLEAN) 
 RETURN  DOMENTITY;

Parameters

Table 171-36 FINDENTITY Function Parameters

ParameterDescription

dt

The DTD

name

Entity to find

par

Flag to indicate type of entity; TRUE for parameter entity and FALSE for normal entity



FINDNOTATION Function

This function finds the notation in the specified DTD, and returns it, if found.

Syntax

DBMS_XMLDOM.FINDNOTATION(
   dt        IN     DOMDocumentType,
   name      IN     VARCHAR2) 
 RETURN DOMNOTATION;

Parameters

Table 171-37 FINDNOTATION Function Parameters

ParameterDescription

dt

The DTD

name

The notation to find



FREEDOCFRAG Procedure

This procedure frees the specified document fragment.

Syntax

DBMS_XMLDOM.FREEDOCFRAG(
   df    IN    DOMDOCUMENTFRAGMENT);

Parameters

Table 171-38 FREEDOCFRAG Procedure Parameters

ParameterDescription

df

DOM document fragment



FREEDOCUMENT Procedure

This procedure frees DOMDOCUMENT object.

Syntax

DBMS_XMLDOM.FREEDOCUMENT(
   doc     IN     DOMDOCUMENT);

Parameters

Table 171-39 FREEDOCUMENT Procedure Parameters

ParameterDescription

doc

DOMDOCUMENT



FREEELEMENT Procedure

This procedure frees memory allocated to a DOMElement handle.

Syntax

DBMS_XMLDOM.FREENODE(
   elem      IN     DOMELEMENT);

Parameters

Table 171-40 FREENODE Procedure Parameters

ParameterDescription

elem

Of type DOMELEMENT



FREENODE Procedure

This procedure frees all resources associated with a DOMNODE.

Syntax

DBMS_XMLDOM.FREENODE(
   n      IN     DOMNODE);

Parameters

Table 171-41 FREENODE Procedure Parameters

ParameterDescription

n

DOMNODE



FREENODELIST Procedure

This procedure frees all resources associated with a nodelist.

Syntax

DBMS_XMLDOM.FREENODE(
   nl    IN    DOMNodeList);

Parameters

Table 171-42 FREENODE Procedure Parameters

ParameterDescription

nl

Of type DOMNODELIST



GETATTRIBUTE Functions

This function returns the value of an attribute of an DOMELEMENT by name.

Syntax

Returns the value of a DOMELEMENT's attribute by name:

DBMS_XMLDOM.GETATTRIBUTE(
   elem       IN      DOMELEMENT,
   name       IN      VARCHAR2)
 RETURN VARCHAR2;

Returns the value of a DOMELEMENT's attribute by name and namespace URI:

DBMS_XMLDOM.GETATTRIBUTE(
   elem      IN     DOMELEMENT,
   name      IN     VARCHAR2,
   ns        IN     VARCHAR2)
 RETURN VARCHAR2;

Parameters

Table 171-43 GETATTRIBUTE Function Parameters

ParameterDescription

elem

The DOMELEMENT

name

Attribute name

ns

Namespace



GETATTRIBUTENODE Functions

This function returns an attribute node from the DOMELEMENT by name. The function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns an attribute node from the DOMELEMENT by name:

DBMS_XMLDOM.GETATTRIBUTENODE(
   elem      IN     DOMElement,
   name      IN     VARCHAR2)
 RETURN DOMATTR;

Returns an attribute node from the DOMELEMENT by name and namespace URI:

DBMS_XMLDOM.GETATTRIBUTENODE(
   elem      IN     DOMElement,
   name      IN     VARCHAR2,
   ns        IN     VARCHAR2)
RETURN DOMATTR;

Parameters

Table 171-44 GETATTRIBUTENODE Function Parameters

ParameterDescription

elem

The DOMELEMENT

name

Attribute name; * matches any attribute

ns

Namespace



GETATTRIBUTES Function

This function retrieves a NAMEDNODEMAP containing the attributes of this node (if it is an Element) or NULL otherwise.

Syntax

DBMS_XMLDOM.GETATTRIBUTES(
   n      IN      DOMNode)
 RETURN DOMNAMEDNODEMAP;

Parameters

Table 171-45 GETATTRIBUTES Function Parameters

ParameterDescription

n

DOMNODE



GETCHARSET Function

This function retrieves the characterset of the DOM document.

Syntax

DBMS_XMLDOM.GETCHARSET(
   doc IN    DOMDocument)
 RETURN VARCHAR2;

Parameters

Table 171-46 GETCHARSET Function Parameters

ParameterDescription

doc

DOM document


Usage Notes

For a newly parsed document, we return the database characterset. Once the SETCHARSET Procedure is called with a non-NULL value for charset, that charset is returned.


GETCHILDNODES Function

This function retrieves a DOMNODELIST that contains all children of this node. If there are no children, this is a DOMNODELIST containing no nodes.

Syntax

DBMS_XMLDOM.GETCHILDNODES(
   n      IN    DOMNode)
 RETURN DOMNodeList;

Parameters

Table 171-47 GETCHILDNODES Function Parameters

ParameterDescription

n

DOMNODE



GETCHILDRENBYTAGNAME Functions

This function returns the children of the DOMELEMENT.

Syntax

Returns children of the DOMELEMENT given the tag name:

DBMS_XMLDOM.GETCHILDRENBYTAGNAME(
   elem      IN      DOMElement, 
   name      IN      VARCHAR2) 
 RETURN DOMNODELIST;

Returns children of the DOMELEMENT given the tag name and namespace:

DBMS_XMLDOM.GETCHILDRENBYTAGNAME(
   elem      IN      DOMElement, 
   name      IN      VARCHAR2, 
   ns        IN      VARCHAR2) 
RETURN DOMNODELIST;

Parameters

Table 171-48 GETCHILDRENBYTAGNAME Function Parameters

ParameterDescription

elem

DOMELEMENT

name

Tag name

ns

Namespace



GETDATA Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Gets the character data of the node that implements this interface (See Also: DOMCharacterData Subprograms):

DBMS_XMLDOM.GETDATA(
   cd      IN    DOMCHARACTERDATA)
 RETURN VARCHAR2;

Returns the content data of the DOMProcessingInstruction (See Also: DOMProcessingInstruction Subprograms):

DBMS_XMLDOM.GETDATA(
   pi      IN    DOMPROCESSINGINSTRUCTION)
 RETURN VARCHAR2;

Parameters

Table 171-49 GETDATA Function Parameters

ParameterDescription

cd

DOMCHARACTERDATA

pi

The DOMPROCESSINGINSTRUCTION



GETDOCTYPE Function

This function returns the DTD associated to the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETDOCTYPE(
   doc      IN     DOMDOCUMENT)
RETURN DOMDOCUMENTTYPE;

Parameters

Table 171-50 GETDOCTYPE Function Parameters

ParameterDescription

doc

DOMDOCUMENT



GETDOCUMENTELEMENT Function

This function returns the root element of the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETDOCUMENTELEMENT(
   doc      IN      DOMDOCUMENT)
 RETURN DOMELEMENT;

Parameters

Table 171-51 GETDOCUMENTELEMENT Function Parameters

ParameterDescription

doc

DOMDOCUMENT



GETELEMENTSBYTAGNAME Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns a DOMNODELIST of all the elements with a specified tagname (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.GETELEMENTSBYTAGNAME(
   doc         IN      DOMDOCUMENT,
   tagname     IN      VARCHAR2)
 RETURN DOMNODELIST;

Returns the element children of the DOMELEMENT given the tag name (See Also: DOMElement Subprograms):

DBMS_XMLDOM.GETELEMENTSBYTAGNAME(
   elem      IN     DOMELEMENT,
   name      IN     VARCHAR2) 
 RETURN DOMNODELIST;

Returns the element children of the DOMELEMENT given the tag name and namespace (See Also: DOMElement Subprograms):

DBMS_XMLDOM.GETELEMENTSBYTAGNAME(
   elem      IN     DOMELEMENT,
   name      IN     VARCHAR2, 
   ns        IN     VARCHAR2) 
 RETURN DOMNODELIST;

Parameters

Table 171-52 GETELEMENTSBYTAGNAME Function Parameters

ParameterDescription

doc

DOMDOCUMENT

tagname

Name of the tag to match on

elem

The DOMELEMENT

name

Tag name; using a wildcard(*) would match any tag

ns

Namespace



GETENTITIES Function

This function retrieves a DOMNAMEDNODEMAP containing the general entities, both external and internal, declared in the DTD.

Syntax

DBMS_XMLDOM.GETENTITIES(
   dt      IN     DOMDocumentType)
 RETURN DOMNAMEDNODEMAP;

Parameters

Table 171-53 GETENTITIES Function Parameters

ParameterDescription

dt

DOMDOCUMENTTYPE



GETEXPANDEDNAME Procedure and Functions

This subprogram is overloaded as a€˙ procedure and two functions. The specific forms of functionality are described along with the syntax declarations.

Syntax

Retrieves the expanded name of the Node if is in an Element or Attribute type; otherwise, returns NULL (See Also: DOMNode Subprograms)

DBMS_XMLDOM.GETEXPANDEDNAME(
   n       IN      DOMNODE
   data    OUT     VARCHAR);

Returns the expanded name of the DOMAttr (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.GETEXPANDEDNAME(
   a       IN     DOMAttr)
 RETURN VARCHAR2;

Returns the expanded name of the DOMElement (See Also: DOMElement Subprograms):

DBMS_XMLDOM.GETEXPANDEDNAME(
   elem      IN    DOMELEMENT)
 RETURN VARCHAR2;

Parameters

Table 171-54 GETEXPANDEDNAME Procedure and Function Parameters

ParameterDescription

n

DOMNODE

data

Returned expanded name of the Node

a

DOMATTR

elem

DOMELEMENT



GETFIRSTCHILD Function

This function retrieves the first child of this node. If there is no such node, this returns NULL.

Syntax

DBMS_XMLDOM.GETFIRSTCHILD(
   n      IN      DOMNODE)
 RETURN DOMNODE;

Parameters

Table 171-55 GETFIRSTCHILD Function Parameters

ParameterDescription

n

DOMNODE



GETIMPLEMENTATION Function

This function returns the DOMIMPLEMENTATION object that handles this DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETIMPLEMENTATION(
   doc      IN     DOMDOCUMENT)
 RETURN DOMIMPLEMENTATION;

Parameters

Table 171-56 GETIMPLEMENTATION Function Parameters

ParameterDescription

doc

DOMDOCUMENT



GETLASTCHILD Function

This function retrieves the last child of this node. If there is no such node, this returns NULL.

Syntax

DBMS_XMLDOM.GETLASTCHILD(
   n     IN   DOMNODE)
 RETURN DOMNODE;

Parameters

Table 171-57 GETLASTCHILD Function Parameters

ParameterDescription

n

DOMNODE



GETLENGTH Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Gets the number of characters in the data. This may have the value zero, because CharacterData nodes may be empty (See Also: DOMCharacterData Subprograms):

DBMS_XMLDOM.GETLENGTH(
   cd     IN     DOMCHARACTERDATA)
 RETURN NUMBER;

Gets the number of nodes in this map. The range of valid child node indexes is 0 to length-1, inclusive (See Also: DOMNamedNodeMap Subprograms):

DBMS_XMLDOM.GETLENGTH(
   nnm      IN     DOMNAMEDNODEMAP)
 RETURN NUMBER;

Gets the number of nodes in the list. The range of valid child node indexes is 0 to length-1, inclusive (See Also: DOMNodeList Subprograms):

DBMS_XMLDOM.GETLENGTH(
   nl     IN    DOMNODELIST)
 RETURN NUMBER;

Parameters

Table 171-58 GETLENGTH Function Parameters

ParameterDescription

cd

DOMCHARACTERDATA

nnm

DOMNAMEDNODEMAP

nl

DOMNODELIST



GETLOCALNAME Procedure and Functions

This function is overloaded as a procedure and two functions. The specific forms of functionality are described alongside the syntax declarations.

Syntax

Retrieves the local part of the node's qualified name (See Also: DOMNode Subprograms):

DBMS_XMLDOM.GETLOCALNAME(
   n       IN     DOMNODE,
   data    OUT    VARCHAR2);

Returns the local name of the DOMAttr (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.GETLOCALNAME(
   a       IN     DOMATTR)
 RETURN VARCHAR2;

Returns the local name of the DOMElement (See Also: DOMElement Subprograms)

DBMS_XMLDOM.GETLOCALNAME(
   elem       IN     DOMELEMENT)
 RETURN VARCHAR2;

Parameters

Table 171-59 GETLOCALNAME Procedure and Function Parameters

ParameterDescription

n

DOMNode

data

Returned local name.

a

DOMAttr.

elem

DOMElement.



GETNAME Functions

This function is overloaded. The specific forms of functionality are described alongside the syntax declarations.

Syntax

Returns the name of this attribute (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.GETNAME(
   a       IN     DOMATTR)
 RETURN VARCHAR2;

Retrieves the name of DTD, or the name immediately following the DOCTYPE keyword (See Also: DOMDocumentType Subprograms):

DBMS_XMLDOM.GETNAME(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN VARCHAR2;

Parameters

Table 171-60 GETNAME Function Parameters

ParameterDescription

a

DOMATTR

dt

DOMDOCUMENTTYPE



GETNAMEDITEM Function

This function retrieves a node specified by name.

Syntax

Retrieves a node specified by name:

DBMS_XMLDOM.GETNAMEDITEM(
   nnm    IN  DOMNAMEDNODEMAP,
   name   IN  VARCHAR2)
 RETURN DOMNODE;

Retrieves a node specified by name and namespace URI:

DBMS_XMLDOM.GETNAMEDITEM(
   nnm    IN  DOMNAMEDNODEMAP,
   name   IN  VARCHAR2,
   ns     IN  VARCHAR2)
 RETURN DOMNODE;

Parameters

Table 171-61 GETNAMEDITEM Function Parameters

ParameterDescription

nnm

DOMNAMEDNODEMAP

name

Name of the item to be retrieved

ns

Namespace



GETNAMESPACE Procedure and Functions

This subprogram is overloaded as a procedure and two functions. The specific forms of functionality are described alongside the syntax declarations.

Syntax

Retrieves the namespace URI associated with the node (See Also: DOMNode Subprograms):

DBMS_XMLDOM.GETNAMESPACE(
   n       IN     DOMNODE,
   data    OUT    VARCHAR2);

Retrieves the namespace of the DOMATTR (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.GETNAMESPACE(
   a       IN     DOMATTR)
 RETURN VARCHAR2;

Retrieves the namespace of the DOMELEMENT (See Also: DOMElement Subprograms):

DBMS_XMLDOM.GETNAMESPACE(
   elem       IN     DOMELEMENT)
 RETURN VARCHAR2;

Parameters

Table 171-62 GETNAMESPACE Procedure and Function Parameters

ParameterDescription

n

DOMNODE

data

Returned namespace URI

a

DOMATTR

elem

DOMELEMENT



GETNEXTSIBLING Function

This function retrieves the node immediately following this node. If there is no such node, this returns NULL.

Syntax

DBMS_XMLDOM.GETNEXTSIBLING(
   n       IN     DOMNODE)
 RETURN DOMNode;

Parameters

Table 171-63 GETNEXTSIBLING Function Parameters

ParameterDescription

n

DOMNODE



GETNODETYPE Function

This function retrieves a code representing the type of the underlying object.

Syntax

DBMS_XMLDOM.GETNODETYPE(
   n       IN     DOMNODE)
 RETURN NUMBER;

Parameters

Table 171-64 GETNODETYPE Function Parameters

ParameterDescription

n

DOMNODE



GETNODENAME Function

This function gets the name of the node depending on its type.

Syntax

DBMS_XMLDOM.GETNODENAME(
   n       IN     DOMNODE)
 RETURN VARCHAR2;

Parameters

Table 171-65 GETNODENAME Function Parameters

ParameterDescription

n

DOMNODE



GETNODEVALUE Function

This function gets the value of this node, depending on its type.

Syntax

DBMS_XMLDOM.GETNODEVALUE(
   n       IN     DOMNODE)
 RETURN VARCHAR2;*

Parameters

Table 171-66 GETNODEVALUE Function Parameters

ParameterDescription

n

DOMNODE



GETNODEVALUEASBINARYSTREAM Function & Procedure

The operation of these subprograms is described with each syntax implementation.

Syntax

This function returns an instance of the PL/SQL XMLBinaryInputStream. The node data type must be RAW or BLOB – if not an exception is raised.

DBMS_XMLDOM.GETNODEVALUEASBINARYSTREAM (
   n      IN     DOMNODE)
 RETURN SYS.UTL_BINARYINPUTSTREAM;

Using this procedure, the application passes an implementation of SYS.UTL_BINARYOUTPUTSTREAM into which XDB writes the contents of the node. The data type of the node must be RAW or CLOB – if not an exception is raised.

DBMS_XMLDOM.GETNODEVALUEASBINARYSTREAM (
   n        in   DOMNODE, 
   value    in   SYS.UTL_BINARYOUTPUTSTREAM);

Parameters

Table 171-67 GETNODEVALUEASBINARYSTREAM Function & Procedure Parameters

ParameterDescription

n

DOMNODE

value

BINARYOUTPUTSTREAM



GETNODEVALUEASCHARACTERSTREAM Function & Procedure

The operation of these subprograms is described with each syntax implementation.

Syntax

This function returns an instance of the PL/SQL XMLCharacterInputStream. If the node data is character it is converted to the current session character set. If the node data is not character data, it is first converted to character data.

DBMS_XMLDOM.GETNODEVALUEASCHARACTERSTREAM  (
   n        IN     DOMNODE)
 RETURN SYS.UTL_CHARACTERINPUTSTREAM;

Using this procedure, the node data is converted, as necessary, to the session character set and then "pushed" into the SYS.UTL_CHARACTEROUTPUTSTREAM.

DBMS_XMLDOM.GETNODEVALUEASCHARACTERSTREAM  (
   n        IN   DOMNODE,
   value    IN   SYS.UTL_CHARACTEROUTPUTSTREAM);

Parameters

Table 171-68 GETNODEVALUEASCHARACTERSTREAM Function & Procedure Parameters

ParameterDescription

n

DOMNODE

value

CHARACTEROUTPUTSTREAM



GETNOTATIONNAME Function

This function returns the notation name of the DOMENTITY.

Syntax

DBMS_XMLDOM.GETNOTATIONNAME(
   ent       IN     DOMENTITY)
 RETURN VARCHAR2;

Parameters

Table 171-69 GETNOTATIONNAME Function Parameters

ParameterDescription

ent

DOMENTITY



GETNOTATIONS Function

This function retrieves a DOMNAMEDNODEMAP containing the notations declared in the DTD.

Syntax

DBMS_XMLDOM.GETNOTATIONS(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN DOMNAMEDNODEMAP;

Parameters

Table 171-70 GETNOTATIONS Function Parameters

ParameterDescription

dt

DOMDOCUMENTTYPE



GETTARGET Function

This function returns the target of the DOMPROCESSINGINSTRUCTION.

Syntax

DBMS_XMLDOM.GETTARGET(
   pi       IN     DOMPROCESSINGINSTRUCTION)
 RETURN VARCHAR2;

Parameters

Table 171-71 GETTARGET Function Parameters

ParameterDescription

pi

DOMPROCESSINGINSTRUCTION



GETOWNERDOCUMENT Function

This function retrieves the Document object associated with this node. This is also the Document object used to create new nodes. When this node is a Document or a Document Type that is not used with any Document yet, this is NULL.

Syntax

DBMS_XMLDOM.GETOWNERDOCUMENT(
   n       IN     DOMNODE)
 RETURN DOMDOCUMENT;

Parameters

Table 171-72 GETOWNERDOCUMENT Function Parameters

ParameterDescription

n

DOMNODE



GETOWNERELEMENT Function

This function retrieves the Element node to which the specified Attribute is attached.

Syntax

DBMS_XMLDOM.GETOWNERELEMENT(
   a       IN     DOMATTR)
 RETURN DOMElement;

Parameters

Table 171-73 GETOWNERELEMENT Function Parameters

ParameterDescription

a

Attribute



GETPARENTNODE Function

This function retrieves the parent of this node. All nodes, except Attr, Document, DocumentFragment, Entity, and Notation may have a parent. However, if a node has just been created and not yet added to the tree, or if it has been removed from the tree, this is NULL.

Syntax

DBMS_XMLDOM.GETPARENTNODE(
   n       IN     DOMNODE)
 RETURN DOMNODE;

Parameters

Table 171-74 GETPARENTNODE Function Parameters

ParameterDescription

n

DOMNODE



GETPREFIX Function

This function retrieves the namespace prefix of the node.

Syntax

DBMS_XMLDOM.GETPREFIX(
   n       IN     DOMNODE)
 RETURN VARCHAR2;

Parameters

Table 171-75 GETPREFIX Function Parameters

ParameterDescription

n

DOMNODE



GETPREVIOUSSIBLING Function

This function retrieves the node immediately preceding this node. If there is no such node, this returns NULL.

Syntax

DBMS_XMLDOM.GETPREVIOUSSIBLING(
   n       IN     DOMNODE)
 RETURN DOMNODE;

Parameters

Table 171-76 GETPREVIOUSSIBLING Function Parameters

ParameterDescription

n

DOMNODE



GETPUBLICID Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns the public identifier of the specified DTD (See Also: DOMDocumentType Subprograms):

DBMS_XMLDOM.GETPUBLICID(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN VARCHAR2;

Returns the public identifier of the DOMENTITY (See Also: DOMEntity Subprograms):

DBMS_XMLDOM.GETPUBLICID(
   ent      IN     DOMENTITY)
 RETURN VARCHAR2;

Returns the public identifier of the DOMNOTATION (See Also: DOMNotation Subprograms):

DBMS_XMLDOM.GETPUBLICID(
   n        IN     DOMNOTATION)
 RETURN VARCHAR2;

Parameters

Table 171-77 GETPUBLICID Function Parameters

ParameterDescription

dt

The DTD

ent

DOMENTITY

n

DOMNOTATION



GETQUALIFIEDNAME Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns the qualified name of the DOMATTR (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.GETQUALIFIEDNAME(
   a        IN     DOMATTR)
 RETURN VARCHAR2;

Returns the qualified name of the DOMElement (See Also: DOMElement Subprograms):

DBMS_XMLDOM.GETQUALIFIEDNAME(
   elem     IN     DOMELEMENT)
 RETURN VARCHAR2;

Parameters

Table 171-78 GETQUALIFIEDNAME Functions Parameters

ParameterDescription

a

DOMATTR

elem

DOMELEMENT



GETSCHEMANODE Function

This function retrieves the schema URI associated with the node.

Syntax

DBMS_XMLDOM.GETSCHEMANODE(
   n       IN     DOMNODE)
 RETURN DOMNODE;

Parameters

Table 171-79 GETSCHEMANODE Function Parameters

ParameterDescription

n

DOMNODE



GETSPECIFIED Function

If this attribute was explicitly specified, a value in the original document, this is true; otherwise, it is false.

Syntax

DBMS_XMLDOM.GETSPECIFIED(
   a       IN     DOMATTR)
 RETURN BOOLEAN;

Parameters

Table 171-80 GETSPECIFIED Function Parameters

ParameterDescription

a

DOMATTR



GETSTANDALONE Function

This function returns the standalone property associated with the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETSTANDALONE(
   doc       IN     DOMDOCUMENT)
 RETURN VARCHAR2;

Parameters

Table 171-81 GETSTANDALONE Function Parameters

ParameterDescription

doc

DOMDOCUMENT.



GETSYSTEMID Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns the system id of the specified DTD (See Also: DOMDocumentType Subprograms):

DBMS_XMLDOM.GETSYSTEMID(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN VARCHAR2;

Returns the system identifier of the DOMENTITY (See Also: DOMEntity Subprograms):

DBMS_XMLDOM.GETSYSTEMID(
   ent      IN     DOMENTITY)
 RETURN VARCHAR2;

Returns the system identifier of the DOMNOTATION (See Also: DOMNotation Subprograms):

DBMS_XMLDOM.GETSYSTEMID(
   n        IN     DOMNOTATION)
 RETURN VARCHAR2;

Parameters

Table 171-82 GETSYSTEMID Function Parameters

ParameterDescription

dt

The DTD.

ent

DOMEntity.

n

DOMNotation.



GETTAGNAME Function

This function returns the name of the DOMELEMENT.

Syntax

DBMS_XMLDOM.GETTAGNAME(
   elem       IN     DOMELEMENT)
 RETURN VARCHAR2;

Parameters

Table 171-83 GETTAGNAME Function Parameters

ParameterDescription

elem

The DOMELEMENT



GETVALUE Function

This function retrieves the value of the attribute.

Syntax

DBMS_XMLDOM.GETVALUE(
   a       IN     DOMATTR)
 RETURN VARCHAR2;

Parameters

Table 171-84 GETVALUE Function Parameters

ParameterDescription

a

DOMATTR



GETVERSION Function

This function returns the version of the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETVERSION(
   doc       IN     DOMDOCUMENT)
 RETURN VARCHAR2;

Parameters

Table 171-85 GETVERSION Function Parameters

ParameterDescription

doc

DOMDOCUMENT



GETXMLTYPE Function

This function returns the XMLType associated with the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.GETXMLTYPE(
   doc       IN     DOMDOCUMENT)
 RETURN SYS.XMLTYPE;

Parameters

Table 171-86 GETXMLTYPE Function Parameters

ParameterDescription

doc

DOMDOCUMENT



HASATTRIBUTE Functions

Verifies whether an attribute has been defined for DOMELEMENT, or has a default value.

Syntax

Verifies whether an attribute with the specified name has been defined for DOMElement:

DBMS_XMLDOM.HASATTRIBUTE(
   elem     IN  DOMELEMENT,
   name     IN  VARCHAR2)
 RETURN VARCHAR2;

Verifies whether an attribute with specified name and namespace URI has been defined for DOMELEMENT; namespace enabled:

DBMS_XMLDOM.HASATTRIBUTE(
   elem     IN  DOMELEMENT,
   name     IN  VARCHAR2,
   ns       IN  VARCHAR2)
 RETURN VARCHAR2;

Parameters

Table 171-87 HASATTRIBUTE Function Parameters

ParameterDescription

elem

The DOMELEMENT

name

Attribute name; * matches any attribute

ns

Namespace



HASATTRIBUTES Function

This function returns whether this node has any attributes.

Syntax

DBMS_XMLDOM.HASATTRIBUTES(
   n       IN     DOMNODE)
 RETURN BOOLEAN;

Parameters

Table 171-88 HASATTRIBUTES Function Parameters

ParameterDescription

n

DOMNODE



HASCHILDNODES Function

This function determines whether this node has any children.

Syntax

DBMS_XMLDOM.HASCHILDNODES(
   n       IN     DOMNODE)
 RETURN BOOLEAN;

Parameters

Table 171-89 HASCHILDNODES Function Parameters

ParameterDescription

n

DOMNODE



HASFEATURE Function

This function tests if the DOMIMPLEMENTATION implements a specific feature.

Syntax

DBMS_XMLDOM.HASFEATURE(
   di       IN     DOMIMPLEMENTATION,
   feature  IN     VARCHAR2,
   version  IN     VARCHAR2)
 RETURN BOOLEAN;

Parameters

Table 171-90 HASFEATURE Function Parameters

ParameterDescription

di

DOMIMPLEMENTATION

feature

The feature to check for

version

The version of the DOM to check in



IMPORTNODE Function

This function imports a node from an external document and returns this new node.

Syntax

DBMS_XMLDOM.IMPORTNODE(
   doc            IN  DOMDOCUMENT,
   importedNode   IN  DOMNODE,
   deep           IN  BOOLEAN)
  RETURN DOMNODE;

Parameters

Table 171-91 IMPORTNODE Function Parameters

ParameterDescription

doc

Document from which the node is imported

importedNode

Node to import

deep

Setting for recursive import.

  • If this value is TRUE, the entire subtree of the node will be imported with the node.

  • If this value is FALSE, only the node itself will be imported.


Usage Notes

Note that the ADOPTNODE Function removes the node from the source document while the IMPORTNODE Function clones the node in the source document.


INSERTBEFORE Function

This function inserts the node newchild before the existing child node refchild. If refchild is NULL, insert newchild at the end of the list of children.

If newchild is a DOCUMENTFRAGMENT object, all of its children are inserted, in the same order, before refchild. If the newchild is already in the tree, it is first removed.

Syntax

DBMS_XMLDOM.INSERTBEFORE(
   n          IN     DOMNODE,
   newchild   IN     DOMNODE,
   refchild   IN     DOMNODE)
  RETURN DOMNode;

Parameters

Table 171-92 INSERTBEFORE Function Parameters

ParameterDescription

n

DOMNODE

newChild

The child to be inserted in the DOMNODE

refChild

The reference node before which the newchild is to be inserted



INSERTDATA Procedure

This procedure inserts a string at the specified character offset.

Syntax

DBMS_XMLDOM.INSERTDATA(
   cd       IN     DOMCHARACTERDATA,
   offset   IN     NUMBER,
   arg      IN     VARCHAR2);

Parameters

Table 171-93 INSERTDATA Procedure Parameters

ParameterDescription

cd

DOMCHARACTERDATA

offset

The offset at which to insert the data

arg

The value to be inserted



ISNULL Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Checks if the specified DOMNODE is NULL. Returns TRUE if it is NULL, FALSE otherwise (See Also: DOMNode Subprograms):

DBMS_XMLDOM.ISNULL(
  n        IN     DOMNODE) 
 RETURN BOOLEAN;

Checks that the specified DOMATTR is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.ISNULL(
   a       IN     DOMATTR)
 RETURN BOOLEAN;

Checks that the specified DOMCDATASECTION is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMCDataSection Subprograms):

DBMS_XMLDOM.ISNULL(
   cds      IN     DOMCDATASECTION)
 RETURN BOOLEAN;

Checks that the specified DOMCHARACTERDATA is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMCharacterData Subprograms):

DBMS_XMLDOM.ISNULL(
   cd       IN     DOMCHARACTERDATA)
 RETURN BOOLEAN;

Checks that the specified DOMCOMMENT is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMComment Subprograms):

DBMS_XMLDOM.ISNULL(
   com       IN     DOMCOMMENT)
 RETURN BOOLEAN;

Checks that the specified DOMDOCUMENT is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.ISNULL(
   doc       IN     DOMDOCUMENT)
 RETURN BOOLEAN;

Checks that the specified DOMDOCUMENTFRAGMENT is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMDocumentFragment Subprograms):

DBMS_XMLDOM.ISNULL(
   df       IN     DOMDOCUMENTFRAGMENT)
 RETURN BOOLEAN;

Checks that the specified DOMDOCUMENTTYPE is NULL; r€˙eturns TRUE if it is NULL, FALSE otherwise (See Also: DOMDocumentType Subprograms):

DBMS_XMLDOM.ISNULL(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN BOOLEAN;

Checks that the specified DOMELEMENT is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMElement Subprograms):

DBMS_XMLDOM.ISNULL(
   elem     IN     DOMELEMENT)
 RETURN BOOLEAN;

Checks that the specified DOMENTITY is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMEntity Subprograms):

DBMS_XMLDOM.ISNULL(
   ent       IN     DOMENTITY)
 RETURN BOOLEAN;

Checks that the specified DOMENTITYREFERENCE is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMEntityReference Subprograms):

DBMS_XMLDOM.ISNULL(
   EREF       IN     DOMENTITYREFERENCE)
 RETURN BOOLEAN;

Checks that the specified DOMIMPLEMENTATION is NULL; returns TRUE if it is NULL (See Also: DOMImplementation Subprograms):

DBMS_XMLDOM.ISNULL(
   di       IN     DOMIMPLEMENTATION)
 RETURN BOOLEAN;

Checks that the specified DOMNAMEDNODEMAP is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMNamedNodeMap Subprograms):

DBMS_XMLDOM.ISNULL(
   nnm       IN     DOMNAMEDNODEMAP)
 RETURN BOOLEAN;

Checks that the specified DOMNODELIST is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMNodeList Subprograms):

DBMS_XMLDOM.ISNULL(
   nl       IN     DOMNODELIST)
 RETURN BOOLEAN;

Checks that the specified DOMNOTATION is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMNotation Subprograms):

DBMS_XMLDOM.ISNULL(
   n       IN     DOMNOTATION)
 RETURN BOOLEAN;

Checks that the specified DOMPROCESSINGINSTRUCTION is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMProcessingInstruction Subprograms):

DBMS_XMLDOM.ISNULL(
   pi       IN     DOMPROCESSINGINSTRUCTION)
 RETURN BOOLEAN;

Checks that the specified DOMTEXT is NULL; returns TRUE if it is NULL, FALSE otherwise (See Also: DOMText Subprograms):

DBMS_XMLDOM.ISNULL(
   t       IN     DOMTEXT)
 RETURN BOOLEAN;

Parameters

Table 171-94 ISNULL Function Parameters

ParameterDescription

n

DOMNODE to check

a

DOMATTR to check

cds

DOMCDATASECTION to check

cd

DOMCHARACTERDATA to check

com

DOMCOMMENT to check

doc

DOMDOCUMENT to check

dF

DOMDOCUMENTFRAGMENT to check

dt

DOMDOCUMENTTYPE to check

elem

DOMELEMENT to check

ent

DOMENTITY to check

eref

DOMENTITYREFERENCE to check

di

DOMIMPLEMENTATION to check

nnm

DOMNAMENODEMAP to check

nl

DOMNODELIST to check

n

DOMNOTATION to check

pi

DOMPROCESSINGINSTRUCTION to check

t

DOMTEXT to check



ITEM Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Returns the item in the map which corresponds to the INDEX parameter. If INDEX is greater than or equal to the number of nodes in this map, this returns NULL (See Also: DOMNamedNodeMap Subprograms):

DBMS_XMLDOM.ITEM(
   nnm       IN     DOMNAMEDNODEMAP,
   index     IN     NUMBER)
 RETURN DOMNODE;

Returns the item in the collection which corresponds to the INDEX parameter. If index is greater than or equal to the number of nodes in the list, this returns NULL (See Also: DOMNodeList Subprograms):

DBMS_XMLDOM.ITEM(
   nl       IN     DOMNODELIST,
   index    IN     NUMBER)
 RETURN DOMNODE;

Parameters

Table 171-95 ITEM Function Parameters

ParameterDescription

nnm

DOMNAMEDNODEMAP

index

The index in the node map at which the item is to be retrieved

nl

DOMNODELIST

index

The index in the NodeList used to retrieve the item



MAKEATTR Function

This function casts a specified DOMNODE to a DOMATTR, and returns the DOMATTR.

Syntax

DBMS_XMLDOM.MAKEATTR(
   n       IN     DOMNODE)
 RETURN DOMATTR;

Parameters

Table 171-96 MAKEATTR Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKECDATASECTION Function

This function casts a specified DOMNODE to a DOMCDATASECTION.

Syntax

DBMS_XMLDOM.MAKECDATASECTION(
   n       IN     DOMNODE)
 RETURN DOMCDATASECTION;

Parameters

Table 171-97 MAKECDATASECTION Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKECHARACTERDATA Function

This function casts a specified DOMNODE to a DOMCHARACTERDATA, and returns the DOMCHARACTERDATA.

Syntax

DBMS_XMLDOM.MAKECHARACTERDATA(
   n       IN     DOMNode)
 RETURN DOMCharacterData;

Parameters

Table 171-98 MAKECHARACTERDATA Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKECOMMENT Function

This function casts a specified DOMNODE to a DOMCOMMENT, and returns the DOMCOMMENT.

Syntax

DBMS_XMLDOM.MAKECOMMENT(
   n       IN     DOMNODE)
 RETURN DOMCOMMENT;

Parameters

Table 171-99 MAKECOMMENT Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEDOCUMENT Function

This function casts a specified DOMNODE to a DOMDOCUMENT, and returns the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.MAKEDOCUMENT(
   n       IN     DOMNODE)
 RETURN DOMDocument;

Parameters

Table 171-100 MAKEDOCUMENT Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEDOCUMENTFRAGMENT Function

This function casts a specified DOMNODE to a DOMDOCUMENTFRAGMENT, and returns the DOMDOCUMENTFRAGMENT.

Syntax

DBMS_XMLDOM.MAKEDOCUMENTFRAGMENT(
   n       IN     DOMNODE)
 RETURN DOMDOCUMENTFRAGMENT;

Parameters

Table 171-101 MAKEDOCUMENTFRAGMENT Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEDOCUMENTTYPE Function

This function casts a specified DOMNODE to a DOMDOCUMENTTYPE and returns the DOMDOCUMENTTYPE.

Syntax

DBMS_XMLDOM.MAKEDOCUMENTTYPE(
   n       IN     DOMNODE)
 RETURN DOMDOCUMENTTYPE;

Parameters

Table 171-102 MAKEDOCUMENTTYPE Function Parameters

ParameterDescription

n

DOMNODE to cast.



MAKEELEMENT Function

This function casts a specified DOMNODE to a DOMELEMENT, and returns the DOMELEMENT.

Syntax

DBMS_XMLDOM.MAKEELEMENT(
   n       IN     DOMNODE)
 RETURN DOMELEMENT;

Parameters

Table 171-103 MAKEELEMENT Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEENTITY Function

This function casts a specified DOMNODE to a DOMENTITY, and returns the DOMENTITY.

Syntax

DBMS_XMLDOM.MAKEENTITY(
   n       IN     DOMNODE)
 RETURN DOMENTITY;

Parameters

Table 171-104 MAKEENTITY Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEENTITYREFERENCE Function

This function casts a specified DOMNODE to a DOMENTITYREFERENCE, and returns the DOMENTITYREFERENCE.

Syntax

DBMS_XMLDOM.MAKEENTITYREFERENCE(
   n       IN     DOMNODE)
 RETURN DOMENTITYREFERENCE;

Parameters

Table 171-105 MAKEENTITYREFERENCE Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKENODE Functions

This function is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Casts specified DOMATTR to a DOMNODE, and returns the DOMNODE (See Also: DOMAttr Subprograms):

DBMS_XMLDOM.MAKENODE(
   a        IN     DOMATTR)
 RETURN DOMNODE;

Casts the DOMCDATASECTION to a DOMNODE, and returns that DOMNODE (See Also: DOMCDataSection Subprograms):

DBMS_XMLDOM.MAKENODE(
   cds      IN     DOMCDATASECTION)
 RETURN DOMNODE;

Casts the specified DOMCHARACTERDATA as a DOMNODE, and returns that DOMNODE (See Also: DOMCharacterData Subprograms):

DBMS_XMLDOM.MAKENODE(
   cd       IN     DOMCHARACTERDATA)
 RETURN DOMNODE;

Casts the specified DOMCOMMENT to a DOMNODE, and returns that DOMNODE (See Also: DOMComment Subprograms):

DBMS_XMLDOM.MAKENODE(
   com      IN     DOMCOMMENT)
 RETURN DOMNODE;

Casts the DOMDOCUMENT to a DOMNODE, and returns that DOMNODE (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.MAKENODE(
   doc      IN     DOMDOCUMENT)
 RETURN DOMNODE;

Casts the specified DOMDOCUMENTFRAGMENT to a DOMNODE, and returns that DOMNODE (See Also: DOMDocumentFragment Subprograms):

DBMS_XMLDOM.MAKENODE(
   df       IN     DOMDOCUMENTFRAGMENT)
 RETURN DOMNode;

Casts the specified DOMDOCUMENTTYPE to a DOMNODE, and returns that DOMNODE (See Also: DOMDocumentType Subprograms):

DBMS_XMLDOM.MAKENODE(
   dt       IN     DOMDOCUMENTTYPE)
 RETURN DOMNODE;

Casts the specified DOMELEMENT to a DOMNODE, and returns that DOMNODE (See Also: DOMElement Subprograms):

DBMS_XMLDOM.MAKENODE(
   elem       IN     DOMELEMENT)
 RETURN DOMNODE;

Casts specified DOMENTITY to a DOMNODE, and returns that DOMNODE (See Also: DOMEntity Subprograms):

DBMS_XMLDOM.MAKENODE(
   ent       IN     DOMENTITY)
 RETURN DOMNODE;

Casts the DOMENTITYREFERENCE to a DOMNODE, and returns that DOMNODE (See Also: DOMEntityReference Subprograms):

DBMS_XMLDOM.MAKENODE(
   eref       IN     DOMENTITYREFERENCE)
 RETURN DOMNODE;

Casts the DOMNOTATION to a DOMNODE, and returns that DOMNODE (See Also: DOMNotation Subprograms):

DBMS_XMLDOM.MAKENODE(
   n       IN     DOMNOTATION)
 RETURN DOMNODE;

Casts the DOMPROCESSINGINSTRUCTION to a DOMNODE, and returns the DOMNODE (See Also: DOMProcessingInstruction Subprograms):

DBMS_XMLDOM.MAKENODE(
   pi       IN     DOMPROCESSINGINSTRUCTION)
 RETURN DOMNODE;

Casts the DOMTEXT to a DOMNODE, and returns that DOMNODE (See Also: DOMText Subprograms):

DBMS_XMLDOM.MAKENODE(
   t       IN     DOMTEXT)
 RETURN DOMNODE;

Parameters

Table 171-106 MAKENODE Function Parameters

ParameterDescription

a

DOMATTR to cast

cds

DOMCDATASECTION to cast

cd

DOMCHARACTERDATA to cast

com

DOMCOMMENT to cast

doc

DOMDOCUMENT to cast

df

DOMDOCUMENTFRAGMENT to cast

dt

DOMDOCUMENTTYPE to cast

elem

DOMELEMENT to cast

ent

DOMENTITY to cast

eref

DOMENTITYREFERENCE to cast

n

DOMNOTATION to cast

pi

DOMPROCESSINGINSTRUCTION to cast

t

DOMTEXT to cast



MAKENOTATION Function

This function casts a specified DOMNODE to a DOMNOTATION, and returns the DOMNOTATION.

Syntax

DBMS_XMLDOM.MAKENOTATION(
   n       IN     DOMNODE)
 RETURN DOMNOTATION;

Parameters

Table 171-107 MAKENOTATION Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKEPROCESSINGINSTRUCTION Function

This function casts a specified DOMNODE to a DOMPROCESSINGINSTRUCTION, and returns the Domprocessinginstruction.

Syntax

DBMS_XMLDOM.MAKEPROCESSINGINSTRUCTION(
   n       IN     DOMNODE)
 RETURN DOMPROCESSINGINSTRUCTION;

Parameters

Table 171-108 MAKEPROCESSINGINSTRUCTION Function Parameters

ParameterDescription

n

DOMNODE to cast



MAKETEXT Function

This function casts a specified DOMNODE to a DOMTEXT, and returns the DOMTEXT.

Syntax

DBMS_XMLDOM.MAKETEXT(
   n       IN     DOMNODE)
 RETURN DOMTEXT;

Parameters

Table 171-109 MAKETEXT Function Parameters

ParameterDescription

n

DOMNODE to cast



NEWDOMDOCUMENT Functions

This function returns a new DOMDOCUMENT instance.

Syntax

Returns a new DOMDOCUMENT instance:

DBMS_XMLDOM.NEWDOMDOCUMENT
 RETURN DOMDOCUMENT;

Returns a new DOMDOCUMENT instance created from the specified XMLType object:

DBMS_XMLDOM.NEWDOMDOCUMENT(
   xmldoc    IN SYS.XMLTYPE)
 RETURN DOMDOCUMENT;

Returns a new DOMDOCUMENT instance created from the specified CLOB:

DBMS_XMLDOM.NEWDOMDOCUMENT(
   cl       IN    CLOB)
 RETURN DOMDOCUMENT;

Parameters

Table 171-110 NEWDOMDOCUMENT Function Parameters 

ParameterDescription

xmldoc

XMLType source for the DOMDOCUMENT

cl

CLOB source for the DOMDOCUMENT



NORMALIZE Procedure

This procedure normalizes the text children of the DOMELEMENT.

Syntax

DBMS_XMLDOM.NORMALIZE(
   elem       IN     DOMELEMENT);

Parameters

Table 171-111 NORMALIZE Procedure Parameters

ParameterDescription

elem

The DOMELEMENT



REMOVEATTRIBUTE Procedures

This procedure removes an attribute from the DOMELEMENT by name.

Syntax

Removes the value of a DOMELEMENT's attribute by name:

DBMS_XMLDOM.REMOVEATTRIBUTE(
   elem     IN    DOMELEMENT,
   name     IN    VARCHAR2);

Removes the value of a DOMELEMENT's attribute by name and namespace URI.

DBMS_XMLDOM.REMOVEATTRIBUTE(
   elem     IN    DOMELEMENT,
   name     IN    VARCHAR2,
   ns       IN    VARCHAR2);

Parameters

Table 171-112 REMOVEATTRIBUTE Procedure Parameters

ParameterDescription

elem

The DOMELEMENT

name

Attribute name

ns

Namespace



REMOVEATTRIBUTENODE Function

This function removes the specified attribute node from the DOMELEMENT. The method returns the removed node.

Syntax

DBMS_XMLDOM.REMOVEATTRIBUTENODE(
   elem       IN     DOMELEMENT,
   oldAttr    IN     DOMATTR)
  RETURN DOMAttr;

Parameters

Table 171-113 REMOVEATTRIBUTENODE Function Parameters

ParameterDescription

elem

The DOMELEMENT.

oldAttr

The old DOMATTR.



REMOVECHILD Function

This function removes the child node indicated by oldchild from the list of children, and returns it.

Syntax

DBMS_XMLDOM.REMOVECHILD(
   n          IN     DOMNode,
   oldchild   IN     DOMNode)
 RETURN DOMNODE;

Parameters

Table 171-114 REMOVECHILD Function Parameters

ParameterDescription

n

DOMNODE

oldCHild

The child of the node n to be removed



REMOVENAMEDITEM Function

This function removes from the map a node specified by name, and returns this node. When this map contains the attributes attached to an element, if the removed attribute is known to have a default value, an attribute immediately appears containing the default value as well as the corresponding namespace URI, local name, and prefix when applicable.

Syntax

Removes a node specified by name:

DBMS_XMLDOM.REMOVENAMEDITEM(
   nnm      IN     DOMNamedNodeMap,
   name     IN     VARCHAR2)
 RETURN DOMNode;

Removes a node specified by name and namespace URI:

DBMS_XMLDOM.REMOVENAMEDITEM(
   nnm      IN     DOMNamedNodeMap,
   name     IN     VARCHAR2,
   ns       IN     VARCHAR2)
 RETURN DOMNode;

Parameters

Table 171-115 REMOVENAMEDITEM Function Parameters

ParameterDescription

nnm

DOMNamedNodeMap

name

The name of the item to be removed from the map

ns

Namespace



REPLACECHILD Function

This function replaces the child node oldchild with newchild in the list of children, and returns the oldchild node. If newchild is a DocumentFragment object, oldchild is replaced by all of the DocumentFragment children, which are inserted in the same order. If the newchild is already in the tree, it is first removed.

Syntax

DBMS_XMLDOM.REPLACECHILD(
   n           IN     DOMNode,
   newchild    IN     DOMNode,
   oldchild    IN     DOMNode)
 RETURN DOMNode;

Parameters

Table 171-116 REPLACECHILD Function Parameters

ParameterDescription

n

DOMNode

newchild

The new child which is to replace the old child

oldchild

The child of the node n which is to be replaced



REPLACEDATA Procedure

This procedure changes a range of characters in the node. Upon success, data and length reflect the change.

Syntax

DBMS_XMLDOM.REPLACEDATA(
   cd        IN     DOMCHARACTERDATA,
   offset    IN     NUMBER,
   cnt       IN     NUMBER,
   arg       IN     VARCHAR2);

Parameters

Table 171-117 REPLACEDATA Procedure Parameters

ParameterDescription

cd

DOMCHARACTERDATA

offset

The offset at which to replace

cnt

The number of characters to replace

arg

The value to replace with



RESOLVENAMESPACEPREFIX Function

This function resolves the specified namespace prefix, and returns the resolved namespace.

Syntax

DBMS_XMLDOM.RESOLVENAMESPACEPREFIX(
   elem       IN     DOMELEMENT,
   prefix     IN     VARCHAR2) 
 RETURN VARCHAR2;

Parameters

Table 171-118 RESOLVENAMESPACEPREFIX Function Parameters

ParameterDescription

elem

The DOMELEMENT

prefix

Namespace prefix



SETATTRIBUTE Procedures

Sets the value of a DOMELEMENT's attribute by name.

Syntax

Sets the value of a DOMELEMENT's attribute by name:

DBMS_XMLDOM.SETATTRIBUTE(
   elem       IN  DOMELEMENT,
   name       IN  VARCHAR2,
   newvalue   IN  VARCHAR2);

Sets the value of a DOMElement's attribute by name and namespace URI:

DBMS_XMLDOM.SETATTRIBUTE(
   elem       IN  DOMELEMENT,
   name       IN  VARCHAR2,
   newvalue   IN  VARCHAR2,
   ns         IN  VARCHAR2);

Parameters

Table 171-119 SETATTRIBUTE Procedure Parameters

ParameterDescription

elem

The DOMELEMENT

name

Attribute name

newvalue

Attribute value

ns

Namespace



SETATTRIBUTENODE Functions

Adds a new attribute node to the DOMELEMENT.

Syntax

Adds a new attribute node to the DOMELEMENT:

DBMS_XMLDOM.SETATTRIBUTENODE(
   elem      IN  DOMELEMENT,
   newAttr   IN  DOMATTR)
 RETURN DOMATTR;

Adds a new attribute node to the DOMElement; namespace enabled:

DBMS_XMLDOM.SETATTRIBUTENODE(
   elem      IN  DOMELEMENT,
   newAttr   IN  DOMATTR,
   ns        IN  VARCHAR2)
 RETURN DOMATTR;

Parameters

Table 171-120 SETATTRIBUTENODE Function Parameters

ParameterDescription

elem

The DOMELEMENT

newAttr

The new DOMATTR

ns

The namespace



SETCHARSET Procedure

This function sets the characterset of the DOM document.

Syntax

DBMS_XMLDOM.SETCHARSET(
   doc      IN    DOMDocument,
   charset  IN    VARCHAR2);

Parameters

Table 171-121 SETCHARSET Procedure Parameters

ParameterDescription

doc

DOM document

charset

Characterset


Usage Notes

This is used for WRITETOFILE Procedures if not explicitly specified at that time.


SETDATA Procedures

This procedure is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Sets the character data of the node that implements this interface (See Also: DOMCharacterData Subprograms):

DBMS_XMLDOM.SETDATA(
   cd       IN     DOMCHARACTERDATA,
   data     IN     VARCHAR2);

Sets the content data of the DOMPROCESSINGINSTRUCTION (See Also: DOMProcessingInstruction Subprograms):

DBMS_XMLDOM.SETDATA(
   pi       IN     DOMPROCESSINGINSTRUCTION,
   data     IN     VARCHAR2);

Parameters

Table 171-122 SETDATA Procedure Parameters

ParameterDescription

cd

DOMCHARACTERDATA

data

The data to which the node is set

pi

DOMPROCESSINGINSTRUCTION

data

New processing instruction content data



SETDOCTYPE Procedure

Given a DOM document, this procedure creates a new DTD with the specified name, system id and public id and sets it in the document. This DTD can later be retrieved using the GETDOCTYPE Function.

Syntax

DBMS_XMLDOM.SETDOCTYPE(
  doc     IN   DOMDocument, 
  name    IN   VARCHAR2,
  sysid   IN   VARCHAR2, 
  pubid   IN   VARCHAR2);

Parameters

Table 171-123 SETDOCTYPE Procedure Parameters

ParameterDescription

doc

The document whose DTD has to be set

name

The name that the doctype needs to be initialized with

sysid

The system ID that the doctype needs to be initialized with

pubid

The public ID that the doctype needs to be initialized with



SETNAMEDITEM Function

This function adds a node using its NodeName attribute. If a node with that name is already present in this map, it is replaced by the new one. The old node is returned on replacement; if no replacement is made, NULL is returned.

As the NodeName attribute is used to derive the name under which the node must be stored, multiple nodes of certain types, those that have a "special" string value, cannot be stored because the names would clash. This is seen as preferable to allowing nodes to be aliased.

Syntax

Adds a node using its NodeName attribute:

DBMS_XMLDOM.SETNAMEDITEM(
   nnm     IN     DOMNAMEDNODEMAP,
   arg     IN     DOMNODE)
 RETURN DOMNode;

Adds a node using its NodeName attribute and namespace URI:

DBMS_XMLDOM.SETNAMEDITEM(
   nnm     IN     DOMNAMEDNODEMAP,
   arg      IN    DOMNODE,
   ns      IN     VARCHAR2)
 RETURN DOMNode;

Parameters

Table 171-124 SETNAMEDITEM Function Parameters

ParameterDescription

nnm

DOMNAMEDNODEMAP

arg

The Node to be added using its NodeName attribute

ns

Namespace



SETNODEVALUE Procedure

This procedure sets the value of this node, depending on its type. When it is defined to be NULL, setting it has no effect.

Syntax

DBMS_XMLDOM.SETNODEVALUE(
   n         IN     DOMNODE,
   nodeValue IN     VARCHAR2);

Parameters

Table 171-125 SETNODEVALUE Procedure Parameters

ParameterDescription

n

DOMNode

nodeValue

The value to which node is set



SETNODEVALUEASBINARYSTREAM Function & Procedure

The operation of these subprograms is described with each syntax implementation.

Syntax

This function returns an instance of the PL/SQL XMLBINARYOUTPUTSTREAM into which the caller can write the node value. The data type of the node must be RAW or BLOB – if not, an exception is raised.

DBMS_XMLDOM.SETNODEVALUEASBINARYSTREAM (
   n      IN     DOMNODE)
 RETURN SYS.UTL_BINARYOUTPUTSTREAM;

Using this procedure, the application passes in an implementation of sys.utl_BinaryInputStream from which XDB reads data to populate the node. The data type of the node must be RAW or BLOB – if not an exception is raised.

DBMS_XMLDOM.SETNODEVALUEASBINARYSTREAM (
   n        in   DOMNODE, 
   value    in   SYS.UTL_BINARYINPUTSTREAM);

Parameters

Table 171-126 SETNODEVALUEASBINARYSTREAM Function & Procedure Parameters

ParameterDescription

n

DOMNODE

value

BINARYINPUTSTREAM



SETNODEVALUEASCHARACTERSTREAM Function & Procedure

The operation of these subprograms is described with each syntax implementation.

Syntax

This function returns an instance of the PL/SQL XMLCHARACTEROUTPUTSTREAM type into which the caller can write the node value. The data type of the node can be any valid XDB data type. If the type is not character or CLOB, the character data written to the stream is converted to the node data type. If the data type of the node is character or CLOB, then the character data written to the stream is converted from PL/SQL session character set to the character set of the node.

DBMS_XMLDOM.SETNODEVALUEASCHARACTERSTREAM  (
   n        IN     DOMNODE)
 RETURN SYS.UTL_CHARACTEROUTPUTSTREAM;

Using this procedure, the application passes in an implementation of SYS.UTL_CHARACTERINPUTSTREAM from which XDB reads to populate the node. The data type of the node may be any valid type supported by XDB. If a non-character data type, the character data read from the stream is converted to the data type of the node. If the data type of the node is either character or CLOB, then no conversion occurs and the character set of the node becomes the character set of the PL/SQL session.

DBMS_XMLDOM.SETNODEVALUEASCHARACTERSTREAM  (
   n        IN   DOMNODE,
   value    IN   SYS.UTL_CHARACTERINPUTSTREAM);

Parameters

Table 171-127 SETNODEVALUEASCHARACTERSTREAM Function & Procedure Parameters

ParameterDescription

n

DOMNODE

value

CHARACTERINPUTSTREAM



SETPREFIX Procedure

This procedure sets the namespace prefix for this node to the specified value.

Syntax

DBMS_XMLDOM.SETPREFIX(
   n       IN     DOMNODE,
   prefix  IN     VARCHAR2);

Parameters

Table 171-128 SETPREFIX Procedure Parameters

ParameterDescription

n

DOMNODE

prefix

The value for the namespace prefix of the node



SETSTANDALONE Procedure

This procedure sets the standalone property of the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.SETSTANDALONE(
   doc         IN     DOMDOCUMENT,
   newvalue    IN     VARCHAR2);

Parameters

Table 171-129 SETSTANDALONE Procedure Parameters

ParameterDescription

doc

DOMDOCUMENT

newvalue

Value of the standalone property of the document



SETVALUE Procedure

This procedure sets the value of the attribute.

Syntax

DBMS_XMLDOM.SETVALUE(
   a       IN     DOMATTR,
   value   IN     VARCHAR2);

Parameters

Table 171-130 SETVALUE Procedure Parameters

ParameterDescription

a

DOMATTR

value

The value to which to set the attribute



SETVERSION Procedure

This procedure sets the version of the DOMDOCUMENT.

Syntax

DBMS_XMLDOM.SETVERSION(
   doc        IN     DOMDOCUMENT,
   version    IN     VARCHAR2);

Parameters

Table 171-131 SETVERSION Procedure Parameters

ParameterDescription

doc

DOMDOCUMENT

version

The version of the document



SPLITTEXT Function

This function breaks this DOMTEXT node into two DOMTEXT nodes at the specified offset.

Syntax

DBMS_XMLDOM.SPLITTEXT(
   t        IN     DOMTEXT,
   offset   IN     NUMBER)
 RETURN DOMText;

Parameters

Table 171-132 SPLITTEXT Function Parameters

ParameterDescription

t

DOMTEXT

offset

Offset at which to split



SUBSTRINGDATA Function

This function extracts a range of data from the node.

Syntax

DBMS_XMLDOM.SUBSTRINGDATA(
   cd        IN     DOMCHARACTERDATA,
   offset    IN     NUMBER,
   cnt       IN     NUMBER)
 RETURN VARCHAR2;

Parameters

Table 171-133 SUBSTRINGDATA Function Parameters

ParameterDescription

cd

DOMCHARACTERDATA

offset

The starting offset of the data from which to get the data

cnt

The number of characters (from the offset) of the data to get



USEBINARYSTREAM Function

This function returns TRUE if the data type of the node is RAW or BLOB so that the node value may be read or written using an UTL_BINARYINPUTSTREAM or UTL_BINARYOUTPUTSTREAM. If a value of FALSE is returned, the node value may only be accessed through an UTL_CHARACTERINPUTSTREAM or UTL_CHARACTEROUTPUTSTREAM.

Syntax

DBMS_XMLDOM.USEBINARYSTREAM   (
   n        IN     DOMNODE)
 RETURN BOOLEAN;

Parameters

Table 171-134 USEBINARYSTREAM Function Parameters

ParameterDescription

n

DOMNODE



WRITETOBUFFER Procedures

This procedure is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Writes XML node to specified buffer using the database character set (See Also: DOMNode Subprograms):

DBMS_XMLDOM.WRITETOBUFFER( 
   n        IN      DOMNODE, 
   buffer   IN OUT  VARCHAR2);

Writes XML document to a specified buffer using database character set (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.WRITETOBUFFER( 
   doc       IN      DOMDOCUMENT, 
   buffer    IN OUT  VARCHAR2);

Writes the contents of the specified document fragment into a buffer using the database character set (See Also: DOMDocumentFragment Subprograms):

DBMS_XMLDOM.WRITETOBUFFER(
   df        IN      DOMDOCUMENTFRAGMENT,
   buffer    IN OUT  VARCHAR2);

Parameters

Table 171-135 WRITETOBUFFER Procedure Parameters

ParameterDescription

n

DOMNODE

buffer

Buffer to which to write

doc

DOMDOCUMENT

df

DOM document fragment



WRITETOCLOB Procedures

This procedure is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Writes XML node to specified CLOB using the database character set (See Also: DOMNode Subprograms):

DBMS_XMLDOM.WRITETOCLOB(
   n       IN      DOMNODE, 
   cl      IN OUT  CLOB);

Writes XML document to a specified CLOB using database character set (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.WRITETOCLOB(
   doc     IN      DOMDOCUMENT, 
   cl      IN OUT  CLOB);

Parameters

Table 171-136 WRITETOCLOB Procedure Parameters

ParameterDescription

n

DOMNODE

cl

CLOB to which to write

doc

DOMDOCUMENT



WRITETOFILE Procedures

This procedure is overloaded. The specific forms of functionality are described along with the syntax declarations.

Syntax

Writes XML node to specified file using the database character set (See Also: DOMNode Subprograms):

DBMS_XMLDOM.WRITETOFILE(
   n          IN      DOMNODE,
   fileName   IN      VARCHAR2);

Writes XML node to specified file using the specified character set, which is passed in as a separate parameter (See Also: DOMNode Subprograms):

DBMS_XMLDOM.WRITETOFILE(
   n          IN      DOMNODE,
   fileName   IN      VARCHAR2,
   charset    IN      VARCHAR2);

Writes an XML document to a specified file using database character set (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.WRITETOFILE(
   doc        IN   DOMDOCUMENT,
   filename   IN   VARCHAR2);

Writes an XML document to a specified file using specified character set (See Also: DOMDocument Subprograms):

DBMS_XMLDOM.WRITETOFILE(
   doc       IN   DOMDOCUMENT,
   fileName  IN   VARCHAR2,
   charset   IN   VARCHAR2);

Parameters

Table 171-137 WRITETOFILE Procedure Parameters

ParameterDescription

n

DOMNODE

fileName

File to which to write. The filename should be in standard directory to filename format, for example /root/folder1/filename (on windows, use \ instead of /).

charset

specified character set

doc

DOMDOCUMENT

charset

Character set


PKf&uŸĺYĺPK˘FJOEBPS/s_wfs_proc.htm ô÷ SDO_WFS_PROCESS

216 SDO_WFS_PROCESS

The SDO_WFS_PROCESS package contains subprograms for various processing operations related to support for Web Feature Services.


Documentation of SDO_WFS_PROCESS

For a complete description of this package within the context of Oracle Spatial, see SDO_WFS_PROCESS in the Oracle Spatial Developer's Guide.

PKwF PK˘FJOEBPS/d_datpmp.htm€˙ DBMS_DATAPUMP

46 DBMS_DATAPUMP

The DBMS_DATAPUMP package is used to move all, or part of, a database between databases, including both data and metadata.


See Also:

Oracle Database Utilities for more information on the concepts behind the DBMS_DATAPUMP API, how it works, and how it is implemented in the Data Pump Export and Import utilities

This chapter contains the following topics:


Using DBMS_DATAPUMP

This section contains topics that relate to using the DBMS_DATAPUMP package.


Overview

The support and functionality provided by DBMS_DATAPUMP is as follows:

  • The source and target databases can have different hardware, operating systems, character sets, and time zones.

  • All object types and data types existing in Oracle Database 11g are supported.

  • Data and metadata can be transferred between databases without using any intermediary files.

  • A subset of a database can be moved based upon object type and names of objects.

  • Schema names, datafile names, and tablespace names can be transformed at import time.

  • Previously aborted export and import jobs can be restarted without duplicating or omitting any data or metadata from the original job.

  • The resources applied to an export or import job can be modified.

  • Data in an Oracle proprietary format can be unloaded and loaded.


Security Model

Security for the DBMS_DATAPUMP package is implemented through roles.

Roles

The DATAPUMP_EXP_FULL_DATABASE and DATAPUMP_IMP_FULL_DATABASE roles allow privileged users to take full advantage of the API. The Data Pump API will use these roles to determine whether privileged application roles should be assigned to the processes comprising the job.

DATAPUMP_EXP_FULL_DATABASE

The DATAPUMP_EXP_FULL_DATABASE role affects only Export operations. It allows users running these operations to do the following:

  • Perform the operation outside of the scope of their schema

  • Monitor jobs that were initiated by another user

  • Export objects (for example, TABLESPACE definitions) that unprivileged users cannot reference

Although the SYS schema does not have the DATAPUMP_EXP_FULL_DATABASE role assigned to it, all security checks performed by Data Pump that require the DATAPUMP_EXP_FULL_DATABASE role will also grant access to the SYS schema.


DATAPUMP_IMP_FULL_DATABASE

The DATAPUMP_IMP_FULL_DATABASE role affects only Import and SQL_FILE operations. It allows users running these operations to do the following:

  • Perform the operation outside of the scope of their schema

  • Monitor jobs that were initiated by another user

  • Import objects (for example, DIRECTORY definitions) that unprivileged users cannot create

Although the SYS schema does not have the DATAPUMP_IMP_FULL_DATABASE role assigned to it, all security checks performed by Data Pump that require the DATAPUMP_IMP_FULL_DATABASE role will also grant access to the SYS schema.



Constants

There are several public constants defined for use with the DBMS_DATAPUMP.GET_STATUS procedure. All such constants are defined as part of the DBMS_DATAPUMP package. Any references to these constants must be prefixed by DBMS_DATAPUMP. and followed by the symbols in the following lists:

Mask Bit Definitions

The following mask bit definitions are used for controlling the return of data through the DBMS_DATAPUMP.GET_STATUS procedure.

  • KU$_STATUS_WIP CONSTANT BINARY_INTEGER := 1;

  • KU$_STATUS_JOB_DESC CONSTANT BINARY_INTEGER := 2;

  • KU$_STATUS_JOB_STATUS CONSTANT BINARY_INTEGER := 4;

  • KU$_STATUS_JOB_ERROR CONSTANT BINARY_INTEGER := 8;

Dump File Type Definitions

The following definitions are used for identifying types of dump files returned through the DBMS_DATAPUMP.GET_STATUS procedure.

  • KU$_DUMPFILE_TYPE_DISK CONSTANT BINARY_INTEGER := 0;

  • KU$_DUMPFILE_TYPE_TEMPLATE CONSTANT BINARY_INTEGER := 3;


Data Structures

The DBMS_DATAPUMP package defines OBJECT types. The types described in this section are defined in the SYS schema for use by the GET_STATUS function. The way in which these types are defined and used may be different than what you are accustomed to. Be sure to read this section carefully.

The collection of types defined for use with the GET_STATUS procedure are version-specific and include version information in the names of the types. Once introduced, these types will always be provided and supported in future versions of Oracle Database and will not change. However, in future releases of Oracle Database, new versions of these types might be created that provide new or different information. The new versions of these types will have different version information embedded in the type names.

For example, in Oracle Database 10g, release 1 (10.1), there is a sys.ku$_Status1010 type, and in the next Oracle Database release, there could be a sys.ku$_Status1110 type defined. Both types could be used with the GET_STATUS procedure.

Public synonyms have been defined for each of the types used with the GET_STATUS procedure. This makes it easier to use the types and means that you do not have to be concerned with changes to the actual type names or schemas where they reside. Oracle recommends that you use these synonyms whenever possible.

For each of the types, there is a version-specific synonym and a generic synonym. For example, the version-specific synonym ku$_Status1010 is defined for the sys.ku$_Status1010 type.

The generic synonym always describes the latest version of that type. For example, in Oracle Database 10g, release 1, the generic synonym ku$_Status is defined as ku$_Status1010. In a future release, there might be a ku$_Status1110 synonym for sys.ku$Status1110. Because the ku$_Status generic synonym always points to the latest definition, it would now point to ku$_Status1110 rather than to ku$_Status1010.

The choice of whether to use version-specific synonyms or generic synonyms makes a significant difference in how you work. Using version-specific names protects your code from changes in future releases of Oracle Database because those types will continue to exist and be supported. However, access to new information will require code changes to use new synonym names for each of the types. Using the generic names implies that you always want the latest definition of the types and are prepared to deal with changes in different releases of Oracle Database.

When the version of Oracle Database that you are using changes, any C code that accesses types through generic synonym names will need to be recompiled.


Note:

Languages other than PL/SQL must ensure that their type definitions are properly aligned with the version-specific definitions.


See Also:

GET_STATUS Procedure for additional information about how types are used


Data Structures - Object Types

The DBMS_DATAPUMP package defines the following kinds of OBJECT types:

Worker Status Types

The worker status types describe what each worker process in a job is doing. The schema, object name, and object type of an object being processed will be provided. For workers processing user data, the partition name for a partitioned table (if any), the number of bytes processed in the partition, and the number of rows processed in the partition are also returned. Workers processing metadata provide status on the last object that was processed. No status for idle threads is returned.

The percent_done refers to the amount completed for the current data item being processed. It is not updated for metadata objects.

The worker status types are defined as follows:

CREATE TYPE sys.ku$_WorkerStatus1010 AS OBJECT (
               worker_number      NUMBER,
               process_name       VARCHAR2(30),
               state              VARCHAR2(30),
               schema             VARCHAR2(30),
               name               VARCHAR2(4000),
               object_type        VARCHAR2(200),
               partition          VARCHAR2(30),
               completed_objects  NUMBER,
               total_objects      NUMBER,
               completed_rows     NUMBER,
               completed_bytes    NUMBER,
               percent_done       NUMBER) 
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatus1010
  FOR sys.ku$_WorkerStatus1010;
CREATE TYPE sys.ku$_WorkerStatus1020 AS OBJECT (
               worker_number     NUMBER,        --  Worker process identifier
               process_name      VARCHAR2(30),  --  Worker process name
               state             VARCHAR2(30),  --  Worker process state
               schema            VARCHAR2(30),  --  Schema name
               name              VARCHAR2(4000),--  Object name
               object_type       VARCHAR2(200), --  Object type
               partition         VARCHAR2(30),  --  Partition name
               completed_objects NUMBER,        --  Completed number of objects
               total_objects     NUMBER,        --  Total number of objects
               completed_rows    NUMBER,        --  Number of rows completed
               completed_bytes   NUMBER,        --  Number of bytes completed
               percent_done      NUMBER,        --  Percent done current object
               degree            NUMBER         --  Degree of parallelism)
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatus1020
  FOR sys.ku$_WorkerStatus1020;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatus FOR ku$_WorkerStatus1020;
 
CREATE TYPE sys.ku$_WorkerStatusList1010 AS TABLE OF sys.ku$_WorkerStatus1010
 
CREATE TYPE sys.ku$_WorkerStatusList1020 AS TABLE OF sys.ku$_WorkerStatus1020
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatusList1010
  FOR sys.ku$_WorkerStatusList1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatusList1020
  FOR sys.ku$_WorkerStatusList1020;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_WorkerStatusList
  FOR ku$_WorkerStatusList1020;

Log Entry and Error Types

These types provide informational and error text to attached clients and the log stream. The ku$LogLine.errorNumber type is set to NULL for informational messages but is specified for error messages. Each log entry may contain several lines of text messages.

The log entry and error types are defined as follows:

CREATE TYPE sys.ku$_LogLine1010 AS OBJECT (
               logLineNumber   NUMBER,
               errorNumber     NUMBER,
               LogText         VARCHAR2(2000))
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogLine1010 FOR sys.ku$_LogLine1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogLine1020 FOR sys.ku$_LogLine1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogLine FOR ku$_LogLine1010;
CREATE TYPE sys.ku$_LogEntry1010 AS TABLE OF sys.ku$_LogLine1010
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogEntry1010 FOR sys.ku$_LogEntry1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogEntry1020 FOR sys.ku$_LogEntry1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_LogEntry FOR ku$_LogEntry1010;

Job Status Types

The job status type returns status about a job. Usually, the status concerns a running job but it could also be about a stopped job when a client attaches. It is typically requested at attach time, when the client explicitly requests status from interactive mode and every N seconds when the client has requested status periodically.

The job status types are defined as follows (percent_done applies to data only):

CREATE TYPE sys.ku$_DumpFile1010 IS OBJECT (
               file_name          VARCHAR2(4000), -- Fully-qualified name
               file_type          NUMBER,         -- 0=Disk, 1=Pipe, etc.
               file_size          NUMBER,         -- Its length in bytes
               file_bytes_written NUMBER          -- Bytes written so far)

CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFile1010 FOR sys.ku$_DumpFile1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFile1020 FOR sys.ku$_DumpFile1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFile FOR ku$_DumpFile1010;
 
CREATE TYPE sys.ku$_DumpFileSet1010 AS TABLE OF sys.ku$_DumpFile1010;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFileSet1010 FOR 
  sys.ku$_DumpFileSet1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFileSet1020 FOR 
  sys.ku$_DumpFileSet1010;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_DumpFileSet FOR ku$_DumpFileSet1010;
 
CREATE TYPE sys.ku$_JobStatus1010 IS OBJECT (
               job_name            VARCHAR2(30),
               operation           VARCHAR2(30),
               job_mode            VARCHAR2(30),
               bytes_processed     NUMBER,
               percent_done        NUMBER,
               degree              NUMBER,
               error_count         NUMBER,
               state               VARCHAR2(30),
               phase               NUMBER,
               restart_count       NUMBER,
               worker_status_list  ku$_WorkerStatusList1010,
               files               ku$_DumpFileSet1010)
 
CREATE PUBLIC SYNONYM ku$_JobStatus1010 FOR 
   sys.ku$_JobStatus1010;
 
CREATE TYPE sys.ku$_JobStatus1020 IS OBJECT (
              job_name           VARCHAR2(30),             -- Name of the job
              operation          VARCHAR2(30),             -- Current operation
              job_mode           VARCHAR2(30),             -- Current mode
              bytes_processed    NUMBER,                   -- Bytes so far
              total_bytes        NUMBER,                   -- Total bytes for job
              percent_done       NUMBER,                   -- Percent done
              degree             NUMBER,                   -- Of job parallelism
              error_count        NUMBER,                   -- #errors so far
              state              VARCHAR2(30),             -- Current job state
              phase              NUMBER,                   -- Job phase
              restart_count      NUMBER,                   -- #Job restarts
              worker_status_list ku$_WorkerStatusList1020, -- job worker processes
              files              ku$_DumpFileSet1010       -- Dump file info)
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_JobStatus1020 FOR   sys.ku$_JobStatus1020;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_JobStatus FOR ku$_JobStatus1020;

Job Description Types

The job description type holds all the environmental information about the job such as parameter settings and dump file set members. There are a couple of subordinate types required as well.

The job description types are defined as follows:

CREATE TYPE sys.ku$_ParamValue1010 AS OBJECT (
               param_name     VARCHAR2(30),
               param_op       VARCHAR2(30),
               param_type     VARCHAR2(30),
               param_length   NUMBER,
               param_value_n  NUMBER,
               param_value_t  VARCHAR2(4000));
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValue1010 FOR sys.ku$_ParamValue1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValue1020 FOR sys.ku$_ParamValue1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValue FOR ku$_ParamValue1010;
 
CREATE TYPE sys.ku$_ParamValues1010 AS TABLE OF sys.ku$_ParamValue1010;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValues1010 FOR 
  sys.ku$_ParamValues1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValues1020 FOR 
  sys.ku$_ParamValues1010;
CREATE OR REPLACE PUBLIC SYNONYM ku$_ParamValues FOR ku$_ParamValues1010;
 
CREATE TYPE sys.ku$_JobDesc1010 AS OBJECT (
               job_name       VARCHAR2(30),
               guid           RAW(16),
               operation      VARCHAR2(30),
               job_mode       VARCHAR2(30),
               remote_link    VARCHAR2(4000),
               owner          VARCHAR2(30),
               instance       VARCHAR2(16),
               db_version     VARCHAR2(30),
               creator_privs  VARCHAR2(30),
               start_time     DATE,
               max_degree     NUMBER,
               log_file       VARCHAR2(4000),
               sql_file       VARCHAR2(4000),
               params         ku$_ParamValues1010)
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_JobDesc1010 FOR sys.ku$_JobDesc1010;
 
CREATE TYPE sys.ku$_JobDesc1020 IS OBJECT (
               job_name        VARCHAR2(30),     -- The job name
               guid            RAW(16),          -- The job GUID
               operation       VARCHAR2(30),     -- Current operation
               job_mode        VARCHAR2(30),     -- Current mode
               remote_link     VARCHAR2(4000),   -- DB link, if any
               owner           VARCHAR2(30),     -- Job owner
               platform        VARCHAR2(101),    -- Current job platform
               exp_platform    VARCHAR2(101),    -- Export platform
               global_name     VARCHAR2(4000),   -- Global name of DB
               exp_global_name VARCHAR2(4000),   -- Export global name
               instance        VARCHAR2(16),     -- The instance name
               db_version      VARCHAR2(30),     -- Version of objects
               exp_db_version  VARCHAR2(30),     -- Export version
               scn             NUMBER,           -- Job SCN   
               creator_privs   VARCHAR2(30),     -- Privs of job
               start_time      DATE,             -- This job start time
               exp_start_time  DATE,             -- Export start time
               term_reason     NUMBER,           -- Job termination code
               max_degree      NUMBER,           -- Max. parallelism
               log_file        VARCHAR2(4000),   -- Log file name
               sql_file        VARCHAR2(4000),   -- SQL file name
               params          ku$_ParamValues1010  -- Parameter list)
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_JobDesc1020 FOR sys.ku$_JobDesc1020;
CREATE OR REPLACE PUBLIC SYNONYM ku$_JobDesc FOR ku$_JobDesc1020;

Status Types

The status type is an aggregate of some the previous types defined and is the return value for the GET_STATUS call. The mask attribute indicates which types of information are being returned to the caller. It is created by a client's shadow process from information it retrieves off the status queue or directly from the master table.

For errors, the ku$_LogEntry that is returned has already had its log lines ordered for proper output. That is, the original ku$_LogEntry objects have been ordered from outermost context to innermost.

The status types are defined as follows:

CREATE TYPE sys.ku$_Status1010 AS OBJECT
(
mask NUMBER,         /* Indicates which status types are present*/
wip ku$_LogEntry1010,  /* Work-In-Progress: std. exp/imp msgs */
job_description    ku$_JobDesc1010,   /* Complete job description */
job_status         ku$_JobStatus1010, /* Detailed job status + per-worker sts */
error              ku$_LogEntry1010   /* Multi-level contextual errors */
)
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1010 FOR sys.ku$_Status1010;
 
CREATE TYPE sys.ku$_Status1020 IS OBJECT
        (
               mask             NUMBER,            -- Status types present
               wip              ku$_LogEntry1010,  -- Work in progress
               job_description  ku$_JobDesc1020,   -- Complete job description
               job_status       ku$_JobStatus1020, -- Detailed job status
               error            ku$_LogEntry1010   -- Multi-level context errors
        )
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status1020 FOR sys.ku$_Status1020;
 
CREATE OR REPLACE PUBLIC SYNONYM ku$_Status FOR ku$_Status1020;

Summary of DBMS_DATAPUMP Subprograms

Table 46-1 DBMS_DATAPUMP Package Subprograms

SubprogramDescription

ADD_FILE Procedure


Adds dump files to the dump file set for an Export, Import, or SQL_FILE operation. In addition to dump files, other types of files can also be added by using the FILETYPE parameter provided with this procedure.

ATTACH Function


Used to gain access to a Data Pump job that is in the Defining, Executing, Idling, or Stopped state

DATA_FILTER Procedures


Specifies restrictions on the rows that are to be retrieved

DETACH Procedure


Specifies that the user has no further interest in using the handle

GET_DUMPFILE_INFO Procedure


Retrieves information about a specified dump file

GET_STATUS Procedure


Monitors the status of a job or waits for the completion of a job or for more details on API errors

LOG_ENTRY Procedure


Inserts a message into the log file

METADATA_FILTER Procedure


Provides filters that allow you to restrict the items that are included in a job

METADATA_REMAP Procedure


Specifies a remapping to be applied to objects as they are processed in the specified job

METADATA_TRANSFORM Procedure


Specifies transformations to be applied to objects as they are processed in the specified job

OPEN Function


Declares a new job using the Data Pump API, the handle returned being used as a parameter for calls to all other procedures (but not to the ATTACH function)

SET_PARALLEL Procedure


Adjusts the degree of parallelism within a job

SET_PARAMETER Procedures


Specifies job-processing options

START_JOB Procedure


Begins or resumes execution of a job

STOP_JOB Procedure


Terminates a job, but optionally, preserves the state of the job

WAIT_FOR_JOB Procedure


Runs a job until it either completes normally or stops for some other reason



ADD_FILE Procedure

This procedure adds files to the dump file set for an Export, Import, or SQL_FILE operation or specifies the log file or the output file for a SQL_FILE operation.

Syntax

DBMS_DATAPUMP.ADD_FILE (
   handle     IN NUMBER,
€˙   filename   IN VARCHAR2,
   directory  IN VARCHAR2,
   filesize   IN VARCHAR2 DEFAULT NULL,
   filetype   IN NUMBER DEFAULT DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE),
   reusefile  IN NUMBER DEFAULT NULL;

Parameters

Table 46-2 ADD_FILE Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function.

filename

The name of the file being added. filename must be a simple filename without any directory path information. For dump files, the filename can include a substitution variable, %U, which indicates that multiple files may be generated with the specified filename as a template. The %U is expanded in the resulting file names into a two-character, fixed-width, incrementing integer starting at 01. For example, the dump filename of export%U would cause export01, export02, export03, and so on, to be created depending on how many files are needed to perform the export. For filenames containing the % character, the % must be represented as %% to avoid ambiguity. Any % in a filename must be followed by either a % or a U.

directory

The name of a directory object within the database that is used to locate filename. A directory must be specified. See the Data Pump Export chapter in Oracle Database Utilities for information about the DIRECTORY command-line parameter.

filesize

The size of the dump file that is being added. It may be specified as the number of bytes, number of kilobytes (if followed by K), number of megabytes (if followed by M) or number of gigabytes (if followed by G). An Export operation will write no more than the specified number of bytes to the file. Once the file is full, it will be closed. If there is insufficient space on the device to write the specified number of bytes, the Export operation will fail, but it can be restarted. If not specified, filesize will default to an unlimited size. For Import and SQL_FILE operations, filesize is ignored. The minimum value for filesize is ten times the default Data Pump block size, which is 4 kilobytes. A filesize can only be specified for dump files.

filetype

The type of the file to be added. The legal values are as follows and must be preceded by DBMS_DATAPUMP.:

  • KU$_FILE_TYPE_DUMP_FILE (dump file for a job)

  • KU$_FILE_TYPE_LOG_FILE (log file for a job)

  • KU$_FILE_TYPE_SQL_FILE (output for SQL_FILE job)

reusefile

If 0, a preexisting file will cause an error. If 1, a preexisting file will be overwritten. If NULL, the default action for the file type will be applied (that is, dump files will not be overwritten). This parameter should only be non-NULL for dump files. The reusefile parameter is restricted to export jobs.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_ARGVAL. An invalid value was supplied for an input parameter.

  • INVALID_STATE. The job is completing, or the job is past the defining state for an import or SQL_FILE job or is past the defining state for LOG and SQL files.

  • INVALID_OPERATION. A dump file was specified for a Network Import or ESTIMATE_ONLY export operation.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • Adds files to a Data Pump job. Three types of files may be added to jobs: Dump files to contain the data that is being moved, log files to record the messages associated with an operation, and SQL files to record the output of a SQL_FILE operation. Log and SQL files will overwrite previously existing files. Dump files will never overwrite previously existing files. Instead, an error will be generated.

  • Import and SQL_FILE operations require that all dump files be specified during the definition phase of the job. For Export operations, dump files can be added at any time. For example, if the user ascertains that the file space is running low during an Export, additional dump files may be added through this API. If the specified dump file already exists for an Export operation and reusefile is not set to 1, an error will be returned.

  • For Export operations, the parallelism setting should be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the job will not be able to maximize parallelism to the degree specified by the SET_PARALLEL procedure.

  • For Import operations, the parallelism setting should also be less than or equal to the number of dump files in the dump file set. If there are not enough dump files, the performance will not be optimal as multiple threads of execution try to access the same dump file.

  • If the substitution variable (%U) is included in a filename, multiple dump files may be specified through a single call to ADD_FILE. For Export operations, the new dump files will be created as they are needed. Enough dump files will be created to allow all of the processes specified by the current SET_PARALLEL value to be active. If one of the dump files fills, it will be closed and a new dump file (with a new generated name) will be created to take its place. If multiple ADD_FILEs with substitution variables have been specified for dump files in a job, they will be used to generate dump files in a round robin fashion. For example, if expa%U, expb%U and expc%U were all specified for a job having a parallelism of 6, the initial dump files created would look like: expa01, expb01, expc01, expa02, expb02, and expc02.

  • If presented with dump file specifications, expa%U, expb%U and expc%U, an Import or SQL_FILE operation will begin by attempting to open the dump files, expa01, expb01, and expc01.If the dump file containing the master table is not found in this set, the operation will expand its search for dump files by incrementing the substitution variable and looking up the new filenames (for example, expa02, expb02, and expc02). The DataPump API will keep expanding the search until it locates the dump file containing the master table. If the DataPump API determines that the dump file does not exist or is not part of the current dump set at any iteration, the DataPump API will stop incrementing the substitution variable for the dump file specification that was in error. Once the master table is found, the master table will be used to ascertain when all of dump files in the dump file set have been located.


ATTACH Function

This function gains access to a previously-created job.

Syntax

DBMS_DATAPUMP.ATTACH(
   job_name    IN VARCHAR2 DEFAULT NULL,
   job_owner    IN VARCHAR2 DEFAULT NULL) 
 RETURN NUMBER;

Parameters

Table 46-3 ATTACH Function Parameters

ParameterDescription

job_name

The name of the job. The default is the job name owned by the user who is specified in the job_owner parameter (assuming that user has only one job in the Defining, Executing, or Idling states).

job_owner

The user who originally started the job. If NULL, the value defaults to the owner of the current session. To specify a job owner other than yourself, you must have either the DATAPUMP_EXP_FULL_DATABASE role (for export operations) or the DATAPUMP_IMP_FULL_DATABASE role (for import and SQL_FILE operations). Being a privileged user allows you to monitor another user's job, but you cannot restart another user's job.


Return Values

An opaque handle for the job. This handle is used as input to the following procedures: ADD_FILE, DATA_FILTER, DETACH, GET_STATUS, LOG_ENTRY, METADATA_FILTER, METADATA_REMAP, METADATA_TRANSFORM, SET_PARALLEL, SET_PARAMETER,START_JOB, STOP_JOB, and WAIT_FOR_JOB.

Exceptions

  • INVALID_ARGVAL. An invalid value was supplied for an input parameter.

  • OBJECT_NOT_FOUND. The specified job no longer exists or the user specified a job owned by another schema, but the user did not have the DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role.

  • SUCCESS_WITH_INFO. The function succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • If the job was in the Stopped state, the job is placed into the Idling state. Once the ATTACH succeeds, you can monitor the progress of the job or control the job. The stream of KU$_STATUS_WIP and KU$_STATUS_JOB_ERROR messages returned through the GET_STATUS procedure will be returned to the newly attached job starting at the approximate time of the client's attachment. There will be no repeating of status and error messages that were processed before the client attached to a job.

  • If you want to perform a second attach to a job, you must do so from a different session.

  • If the ATTACH fails, use a null handle in a subsequent call to GET_STATUS for more information about the failure.


DATA_FILTER Procedures

This procedure specifies restrictions on the rows that are to be retrieved.

Syntax

DBMS_DATAPUMP.DATA_FILTER (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN NUMBER,
   table_name  IN VARCHAR2 DEFAULT NULL,
   schema_name IN VARCHAR2 DEFAULT NULL);

DBMS_DATAPUMP.DATA_FILTER(
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN VARCHAR2,
   table_name  IN VARCHAR2 DEFAULT NULL,
   schema_name IN VARCHAR2 DEFAULT NULL);

DBMS_DATAPUMP.DATA_FILTER(
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN CLOB,
   table_name  IN VARCHAR2 DEFAULT NULL,
   schema_name IN VARCHAR2 DEFAULT NULL);

Parameters

Table 46-4 DATA_FILTER Procedure Parameters

ParameterDescription

handle

The handle that is returned from the OPEN function

name

The name of the filter

value

The value of the filter

table_name

The name of the table on which the data filter is applied. If no table name is supplied, the filter applies to all tables in the job.

schema_name

The name of the schema that owns the table on which the filter is applied. If no schema name is specified, the filter applies to all schemas in the job. If you supply a schema name you must also supply a table name.


Exceptions

  • INVALID_ARGVAL. There can be several reasons for this message:

    • A bad filter name is specified

    • The mode is TRANSPORTABLE, which does not support data filters

    • The specified table does not exist

    • The filter has already been set for the specified values of schema_name and table_name

  • INVALID_STATE. The user called DATA_FILTER when the job was not in the Defining state.

  • INCONSISTENT_ARGS. The value parameter is missing or its datatype does not match the filter name. Or a schema name was supplied, but not a table name.

  • PRIVILEGE_ERROR. A schema name was supplied, but the user did not have the DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • Each data filter can only appear once in each table (for example, you cannot supply multiple SUBQUERY filters to a table) or once in each job. If different filters using the same name are applied to both a particular table and to the whole job, the filter parameter supplied for the specific table will take precedence.

    With the exception of the INCLUDE_ROWS filter, data filters are not supported on tables having nested tables or domain indexes defined upon them. Data filters are not supported in jobs performed in Transportable Tablespace mode.

    The available data filters are described in Table 46-5.

Table 46-5 Data Filters

NameDatatypeOperations that Support FilterDescription

INCLUDE_ROWS

NUMBER

EXPORT, IMPORT

If nonzero, this filter specifies that user data for the specified table should be included in the job. The default is 1.

PARTITION_EXPR

PARTITION_LIST

text

EXPORT, IMPORT

For Export jobs, these filters specify which partitions are unloaded from the database. For Import jobs, they specify which table partitions are loaded into the database. Partition names are included in the job if their names satisfy the specified expression (for PARTITION_EXPR) or are included in the list (for PARTITION_LIST). Whereas the expression version of the filter offers more flexibility, the list version provides for full validation of the partition names.

Double quotation marks around partition names are required only if the partition names contain special characters.

PARTITION_EXPR is not supported on jobs across a network link.

Default=All partitions are processed

SAMPLE

NUMBER

EXPORT, IMPORT

For Export jobs, specifies a percentage for sampling the data blocks to be moved. This filter allows subsets of large tables to be extracted for testing purposes.

SUBQUERY

text

EXPORT, IMPORT

Specifies a subquery that is added to the end of the SELECT statement for the table. If you specify a WHERE clause in the subquery, you can restrict the rows that are selected. Specifying an ORDER BY clause orders the rows dumped in the export which improves performance when migrating from heap-organized tables to index-organized tables.



DATA_REMAP Procedure

This procedure specifies transformations to be applied to column data as it is exported from, or imported into, a database.

Syntax

DBMS_DATAPUMP.DATA_REMAP(
   handle          IN NUMBER,
   name            IN VARCHAR2,
   table_name      IN VARCHAR2,
   column          IN VARCHAR2,
   remap_function  IN VARCHAR2),
   schema          IN VARCHAR2 DEFAULT NULL);

Parameters

Table 46-6 DATA_REMAP Procedure Parameters

ParameterDescription

handle

The handle of the current job. The current session must have previously attached to the handle through a call to an OPEN function.

name

The name of the remap

table_name

The table containing the column to be remapped

column

The name of the column to be remapped

remap_function

The meaning of remap_function is dependent upon the value of name. See Table 46-7 for a list of possible names.

schema

The schema containing the column to be remapped. If NULL, the remapping applies to all schemas moved in the job that contain the specified table.


Exceptions

  • INVALID_ARGVAL. The mode is transportable (which does not support data modifications) or it has specified that no data to be included in the job. An invalid remap name was supplied.

  • INVALID_OPERATION. Data remaps are only supported for Export and Import operations.

  • INVALID_STATE. The DATA_REMAP procedure was called after the job started (that is, it was not in the defining state).

  • NO_SUCH_JOB. The job handle is no longer valid.

Usage Notes

  • The DATA_REMAP procedure is only supported for Export and Import operations. It allows you to manipulate user data being exported or imported. The name of the remap determines the remap operation to be performed.

  • For export operations, you might wish to define a data remap to obscure sensitive data such as credit card numbers from a dump file, but leave the remainder of the data so that it can be read. To accomplish this, the remapping should convert each unique source number into a distinct generated number. So that the mapping is consistent across the dump file set, the same function should be called for every column that contains the credit card number.

  • For import operations, you might wish to define a data remap to reset the primary key when data is being merged into an existing table that contains colliding primary keys. A single remapping function should be provided for all columns defining or referencing the primary key to ensure that remapping is consistent.


Note:

If the called function uses package state variables, then to ensure that remapping is performed consistently across all tables, the job should be run with a SET_PARALLEL value of 1 and no restart operations should be performed.

The Data Remap functions are listed in Table 46-7.

Table 46-7 Names of Data Remap Functions

NameMeaning of remap_functionMeaning

COLUMN_FUNCTION

String having the format:

[schema.]package.function

The name parameter references a PL/SQL package function which is called to modify the data for the specified column. The function accepts a single parameter, which has the same datatype as the remapped column, and returns a value having the same datatype as the remapped column. Note that the default for the schema is the schema of the user performing the export.



DETACH Procedure

This procedure specifies that the user has no further interest in using the handle.

Syntax

DBMS_DATAPUMP.DETACH(
   handle  IN NUMBER);

Parameters

Table 46-8 DETACH Procedure Parameters

ParameterDescription

handle

The handle of the job. The current session must have previously attached to the handle through a call to either an OPEN or ATTACH function.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • Through this call, you specify that you have no further interest in using the handle. Resources associated with a completed job cannot be reclaimed until all users are detached from the job. An implicit detach from a handle is performed when the user's session is exited or aborted. An implicit detach from a handle is also performed upon the expiration of the timeout associated with a STOP_JOB that was applied to the job referenced by the handle. All previously allocated DBMS_DATAPUMP handles are released when an instance is restarted.


GET_DUMPFILE_INFO Procedure

This procedure retrieves information about a specified dump file.

Syntax

DBMS_DATAPUMP.GET_DUMPFILE_INFO(
   filename    IN VARCHAR2,
   directory   IN VARCHAR2,
   info_table  OUT ku$_dumpfile_info,
   filetype    OUT NUMBER);

Parameters

Table 46-9 GET_DUMPFILE_INFO Procedure Parameters

ParameterDescription

filename

A simple filename with no directory path information

directory

A directory object that specifies where the file can be found

info_table

A PL/SQL table for storing information about the dump file

filetype

The type of file (Data Pump dump file, original Export dump file, external tables dump file, or unknown)


Exceptions

The GET_DUMPFILE_INFO procedure is a utility routine that operates outside the context of any Data Pump job. Exceptions are handled differently for this procedure than for procedures associated in some way with a Data Pump job. A full exception stack should be available directly, without the need to call the GET_STATUS procedure to retrieve the detailed information. The exception for this procedure is as follows:

  • NO_DUMPFILE_INFO. Unable to retrieve dump file information as specified.

Usage Notes

You can use the GET_DUMPFILE_INFO procedure to request information about a specific file. If the file is not recognized as any type of dump file, then a filetype of 0 (zero) is returned and the dump file info_table remains empty.

A filetype value of 1 indicates a Data Pump dump file. A filetype value of 2 indicates an original Export dump file. A filetype value of 3 indicates an external tables dump file. In all cases, the dump file info_table will be populated with information retrieved from the dump file header. Rows of this table consist of item code and value pairs, where the item code indicates the type of information and the value column is a VARCHAR2 containing the actual data (converted to a string in some cases). The table is defined as follows:

CREATE TYPE sys.ku$_dumpfile_item IS OBJECT (
                item_code       NUMBER,           -- Identifies header item
                value           VARCHAR2(2048)    -- Text string value)/
 
GRANT EXECUTE ON sys.ku$_dumpfile_item TO PUBLIC; 
CREATE OR REPLACE PUBLIC SYNONYM ku$_dumpfile_item FOR sys.ku$_dumpfile_item;
 
CREATE TYPE sys.ku$_dumpfile_info AS TABLE OF sys.ku$_dumpfile_item/
 
GRANT EXECUTE ON sys.ku$_dumpfile_info TO PUBLIC; 
CREATE OR REPLACE PUBLIC SYNONYM ku$_dumpfile_info FOR sys.ku$_dumpfile_info;
 

The item codes, which can easily be extended to provide more information as needed, are currently defined as shown inTable 46-10 (prepended with the package name, DBMS_DATAPUMP.). Assume the following with regard to these item codes:

  • Unless otherwise stated, all item codes may be returned only for Oracle Data Pump and external tables dump files (filetypes 1 and 3).

  • Unless otherwise stated, all item codes have been available since Oracle Database 10g Release 2 (10.2).

Table 46-10 Item Codes for the DBMS_DATAPUMP.GET_DUMPFILE_INFO Procedure

Item CodeDescription

KU$_DFHDR_FILE_VERSION

The internal file version of the dump file.

KU$_DFHDR_MASTER_PRESENT

If the Data Pump master table is present in the dump file, then the value for this item code is 1; otherwise the value is 0. Returned only for filetype 1.

KU$_DFHDR_GUID

A unique identifier assigned to the Data Pump export job or the external tables unload job that produced the dump file. For a multifile dump set, each file in the set has the same value for this item code.

KU$_DFHDR_FILE_NUMBER

A numeric identifier assigned to the dump file.Each dump file in a multifile dump set has its own identifier, unique only within the dump set.

KU$_DFHDR_CHARSET_ID

A numeric code that represents the character set in use at the source system when the dump file was created.

Returned for all filetypes.

KU$_DFHDR_CREATION_DATE

The date and time that the dump file was created.

KU$_DFHDR_FLAGS

Internal flag values.

KU$_DFHDR_JOB_NAME

The name assigned to the export job that created the dump file.

Returned only for filetype 1.

KU$_DFHDR_PLATFORM

The operating system name of the source system on which the dump file was created.

KU$_DFHDR_INSTANCE

The instance name of the source system on which the dump file was created.

KU$_DFHDR_LANGUAGE

The language name that corresponds to the character set of the source system where the export dump file was created.

KU$_DFHDR_BLOCKSIZE

The blocksize, in bytes, of the dump file.

KU$_DFHDR_DIRPATH

If direct path mode was used when the dump file was created, then the value for this item code is 1, otherwise the value is 0.

Returned only for filetype 2.

KU$_DFHDR_METADATA_COMPRESSED

If the system metadata is stored in the dump file in compressed format, then the value for this item code is 1, otherwise the value is 0.

Returned only for filetype 1.

KU$_DFHDR_DB_VERSION

The database job version used to create the dump file.

Returned for all filetypes.

KU$_DFHDR_MASTER_PIECE_COUNT

The Data Pump master table may be split into multiple pieces and written to multiple dump files in the set, one piece per file. The value returned for this item code indicates the number of dump files that contain pieces of the master table. The value for this item code is only meaningful if the Data Pump master table is present in the dump file, as indicated by the item code KU$_DFHDR_MASTER_PRESENT.

Returned only for filetype 1.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_MASTER_PIECE_NUMBER

The Data Pump master table may be split into multiple pieces and written to multiple dump files in the set, one piece per file. The value returned for this item code indicates which master table piece is contained in the dump file. The value for this item code is only meaningful if the Data Pump master table is present in the dump file, as indicated by the item code KU$_DFHDR_MASTER_PRESENT.

Returned only for filetype 1.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_DATA_COMPRESSED

If the table data is stored in the dump file in compressed format, then the value for this item code is 1, otherwise the value is 0.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_METADATA_ENCRYPTED

If the system metadata is stored in the dump file in encrypted format, then the value for this item code is 1, otherwise the value is 0.

Returned only for filetype 1.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_DATA_ENCRYPTED

If the table data is stored in the dump file in encrypted format, then the value for this item code is 1, otherwise the value is 0.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_COLUMNS_ENCRYPTED

If encrypted column data is stored in the dump file in encrypted format, then the value for this item code is 1, otherwise the value is 0.

Returned only for filetype 1.

Only available since Oracle Database 11g Release 1 (11.1).

KU$_DFHDR_ENCRYPTION_MODE

The encryption mode indicates whether a user-provided password or the Oracle Encryption Wallet was used to encrypt data written to the dump file. The possible values returned for this item code are:

  • KU$_DFHDR_ENCMODE_NONE

    No data was written to the dump file in encrypted format.

  • KU$_DFHDR_ENCMODE_PASSWORD

    Data was written to the dump file in encrypted format using a provided password.

  • KU$_DFHDR_ENCMODE_DUAL

    Data was written to the dump file in encrypted format using both a provided password as well as an Oracle Encryption Wallet.

  • KU$_DFHDR_ENCMODE_TRANS

    Data was written to the dump file in encrypted format transparently using an Oracle Encryption Wallet.

Only available since Oracle Database 11g Release 1 (11.1).



GET_STATUS Procedure

This procedure monitors the status of a job or waits for the completion of a job.

Syntax

DBMS_DATAPUMP.GET_STATUS(
   handle    IN NUMBER,
   mask      IN BINARY_INTEGER,
   timeout   IN NUMBER DEFAULT NULL,
   job_state OUT VARCHAR2,
   status    OUT ku$_Status1010);

Parameters

Table 46-11 GET_STATUS Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function. A null handle can be used to retrieve error information after OPEN and ATTACH failures.

mask

A bit mask that indicates which of four types of information to return:

  • KU$_STATUS_WIP

  • KU$_STATUS_JOB_DESC

  • KU$_STATUS_JOB_STATUS

  • KU$_STATUS_JOB_ERROR

Each status has a numerical value. You can request multiple types of information by adding together different combinations of values. See Data Structures - Object Types.

timeout

Maximum number of seconds to wait before returning to the user. A value of 0 requests an immediate return. A value of -1 requests an infinite wait. If KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR information is requested and becomes available during the timeout period, then the procedure returns before the timeout period is over.

job_state

Current state of the job. If only the job state is needed, it is much more efficient to use this parameter than to retrieve the full ku$_Status structure.

status

A ku$_Status is returned. The ku$_Status mask indicates what kind of information is included. This could be none if only KU$_STATUS_WIP or KU$_STATUS_JOB_ERROR information is requested and the timeout period expires. This can be a ku$_Status1010 or ku$_Status1020 object type.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_VALUE. The mask or timeout contains an illegal value.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

The GET_STATUS procedure is used to monitor the progress of an ongoing job and to receive error notification. You can request various type of information using the mask parameter. The KU$_STATUS_JOB_DESC and KU$_STATUS_JOB_STATUS values are classified as synchronous information because the information resides in the master table. The KU$_STATUS_WIP and KU$_STATUS_JOB_ERROR values are classified as asynchronous because the messages that embody these types of information can be generated at any time by various layers in the Data Pump architecture.

  • If synchronous information only is requested, the interface will ignore the timeout parameter and simply return the requested information.

  • If asynchronous information is requested, the interface will wait a maximum of timeout seconds before returning to the client. If a message of the requested asynchronous information type is received, the call will complete prior to timeout seconds. If synchronous information was also requested, it will be returned whenever the procedure returns.

  • If the job_state returned by GET_STATUS does not indicate a terminating job, it is possible that the job could still terminate before the next call to GET_STATUS. This would result in an INVALID_HANDLE exception. Alternatively, the job could terminate during the call to GET_STATUS, which would result in a NO_SUCH_JOB exception. Callers should be prepared to handle these cases.

Error Handling

There are two types of error scenarios that need to be handled using the GET_STATUS procedure:

  • Errors resulting from other procedure calls: For example, the SET_PARAMETER procedure may produce an INCONSISTENT_ARGS exception. The client should immediately call GET_STATUS with mask=8 (errors) and timeout=0. The returned ku$_Status.error will contain a ku$_LogEntry that describes the inconsistency in more detail.

  • Errors resulting from events asynchronous to the client(s): An example might be Table already exists when trying to create a table. The ku$_Status.error will contain a ku$_LogEntry with all error lines (from all processing layers that added context about the error) properly ordered.

After a job has begun, a client's main processing loop will typically consist of a call to GET_STATUS with an infinite timeout (-1) "listening" for KU$_STATUS_WIP and KU$_STATUS_JOB_ERROR messages. If status was requested, then JOB_STATUS information will also be in the request.

When the ku$_Status is interpreted, the following guidelines should be used:

  • ku$_Status.ku$_JobStatus.percent_done refers only to the amount of data that has been processed in a job. Metadata is not considered in the calculation. It is determined using the following formulas:

    • EXPORT or network IMPORT--(bytes_processed/estimated_bytes) * 100

    • IMPORT--(bytes_processed/total_expected_bytes) * 100

    • SQL_FILE or estimate-only EXPORT--0.00 if not done or 100.00 if done

    The effects of the QUERY and PARTITION_EXPR data filters are not considered in computing percent_done.

    It is expected that the status returned will be transformed by the caller into more user-friendly status. For example, when percent done is not zero, an estimate of completion time could be produced using the following formula:

    ((SYSDATE - start time) / ku$_Status.ku$_JobStatus.percent_done) * 100
    
  • The caller should not use ku$_Status.ku$_JobStatus.percent_done for determining whether the job has completed. Instead, the caller should only rely on the state of the job as found in job_state.


LOG_ENTRY Procedure

This procedure inserts a message into the log file.

Syntax

DBMS_DATAPUMP.LOG_ENTRY(
   handle         IN NUMBER,
   message        IN VARCHAR2
   log_file_only  IN NUMBER DEFAULT 0);

Parameters

Table 46-12 LOG_ENTRY Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function.

message

A text line to be added to the log file

log_file_only

Specified text should be written only to the log file. It should not be returned in GET_STATUS work-in-progress (KU$_STATUS_WIP) messages.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

The message is added to the log file. If log_file_only is zero (the default), the message is also broadcast as a KU$_STATUS_WIP message through the GET_STATUS procedure to all users attached to the job.

The LOG_ENTRY procedure allows applications to tailor the log stream to match the abstractions provided by the application. For example, the command-line interface supports INCLUDE and EXCLUDE parameters defined by the user. Identifying these values as calls to the underlying METADATA_FILTER procedure would be confusing to users. Instead, the command-line interface can enter text into the log describing the settings for the INCLUDE and EXCLUDE parameters.

Lines entered in the log stream from LOG_ENTRY are prefixed by the string, ";;; "


METADATA_FILTER Procedure

This procedure provides filters that allow you to restrict the items that are included in a job.

Syntax

DBMS_DATAPUMP.METADATA_FILTER(
   handle       IN NUMBER,
   name         IN VARCHAR2,
   value        IN VARCHAR2,
   object_path  IN VARCHAR2 DEFAULT NULL);

DBMS_DATAPUMP.METADATA_FILTER(
   handle       IN NUMBER,
   name         IN VARCHAR2,
   value        IN CLOB,
   object_path  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 46-13 METADATA_FILTER Procedure Parameters

ParameterDescription

handle

The handle returned from the OPEN function

name

The name of the filter. See Table 46-14 for descriptions of the available filters.

value

The value of the filter

object_path

The object path to which the filter applies. If the default is used, the filter applies to all applicable objects. Lists of the object paths supported for each mode are contained in the catalog views for DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, and TABLE_EXPORT_OBJECTS. (Note that the TABLE_EXPORT_OBJECTS view is applicable to both Table and Tablespace mode because their object paths are the same.)

For an import operation, object paths reference the mode used to create the dump file rather than the mode being used for the import.


Table 46-14 describes the name, the object type, and the meaning of the filters available with the METADATA_FILTER procedure. The datatype for all the filters is a text expression. All operations support all filters.

Table 46-14 Filters Provided by METADATA_FILTER Procedure

NameObject TypeMeaning

NAME_EXPR

NAME_LIST

Named objects

Defines which object names are included in the job. You use the object type parameter to limit the filter to a particular object type.

For Table mode, identifies which tables are to be processed.

SCHEMA_EXPR

SCHEMA_LIST

Schema objects

Restricts the job to objects whose owning schema name is satisfied by the expression.

For Table mode, only a single SCHEMA_EXPR filter is supported. If specified, it must only specify a single schema (for example, 'IN (''SCOTT'')').

For Schema mode, identifies which users are to be processed.

TABLESPACE_EXPR

TABLESPACE_LIST

TABLE, CLUSTER, INDEX, ROLLBACK_SEGMENT

Restricts the job to objects stored in a tablespace whose name is satisfied by the expression.

For Tablespace mode, identifies which tablespaces are to be processed. If a partition of an object is stored in the tablespace, the entire object is added to the job.

For Transportable mode, identifies which tablespaces are to be processed. If a table has a single partition in the tablespace set, all partitions must be in the tablespace set. An index is not included within the tablespace set unless all of its partitions are in the tablespace set. A domain index is not included in the tablespace set unless all of its secondary objects are included in the tablespace set.

INCLUDE_PATH_EXPR

INCLUDE_PATH_LIST

EXCLUDE_PATH_EXPR

EXCLUDE_PATH_LIST

All

Defines which object paths are included in, or excluded from, the job. You use these filters to select only certain object types from the database or dump file set. Objects of paths satisfying the condition are included (INCLUDE_PATH_*) or excluded (EXCLUDE_PATH_*) from the operation. The object_path parameter is not supported for these filters.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_ARGVAL. This exception can indicate any of the following conditions:

    • An object_path was specified for an INCLUDE_PATH_EXPR or EXCLUDE_PATH_EXPR filter.

    • The specified object_path is not supported for the current mode.

    • The SCHEMA_EXPR filter specified multiple schemas for a Table mode job.

  • INVALID_STATE. The user called the METADATA_FILTER procedure after the job left the defining state.

  • INCONSISTENT_ARGS. The filter value is of the wrong datatype or is missing.

  • SUCCESS_WITH_INFO. The procedure succeeded but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • Metadata filters identify a set of objects to be included or excluded from a Data Pump operation. Except for EXCLUDE_PATH_EXPR and INCLUDE_PATH_EXPR, dependent objects of an identified object will be processed along with the identified object. For example, if an index is identified for inclusion by a filter, grants upon that index will also be included by the filter. Likewise, if a table is excluded by a filter, then indexes, constraints, grants and triggers upon the table will also be excluded by the filter.

  • Two versions of each filter are supported: SQL expression and List. The SQL expression version of the filters offer maximum flexibility for identifying objects (for example the use of LIKE to support use of wild cards). The names of the expression filters are as follows:

    • NAME_EXPR

    • SCHEMA_EXPR

    • TABLESPACE_EXPR

    • INCLUDE_PATH_EXPR

    • EXCLUDE_PATH_EXPR

    The list version of the filters allow maximum validation of the filter. An error will be reported if one of the elements in the filter is not found within the source database (for Export and network-based jobs) or is not found within the dump file (for file-based Import and SQLFILE jobs). The names of the list filters are as follows:

    • NAME_LIST

    • SCHEMA_LIST

    • TABLESPACE_LIST

    • INCLUDE_PATH_LIST

    • EXCLUDE_PATH_LIST

  • Filters allow a user to restrict the items that are included in a job. For example, a user could request a full export, but without Package Specifications or Package Bodies.

  • If multiple filters are specified for a object type, they are implicitly 'ANDed' together (that is, objects participating in the job must pass all of the filters applied to their object types).

  • The same filter name can be specified multiple times within a job. For example, specifying NAME_EXPR as '!=''EMP''' and NAME_EXPR as '!=''DEPT''' on a Table mode export would produce a file set containing all of the tables except for EMP and DEPT.


METADATA_REMAP Procedure

This procedure specifies a remapping to be applied to objects as they are processed in the specified job.

Syntax

DBMS_DATAPUMP.METADATA_REMAP (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   old_value   IN VARCHAR2,
   value       IN VARCHAR2,
   object_type IN VARCHAR2 DEFAULT NULL);

Parameters

Table 46-15 METADATA_REMAP Procedure Parameters

ParameterDescription

handle

The handle for the current job. The current session must have previously attached to the handle through a call to the OPEN function.

name

The name of the remap. See Table 46-16 for descriptions of the available remaps.

old_value

Specifies which value in the dump file set should be reset to value

value

The value of the parameter for the remap. This signifies the new value that old_value should be translated into.

object_type

Designates the object type to which the remap applies. The list of object types supported for each mode are contained in the DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS, and TABLESPACE_EXPORT_OBJECTS catalog views.

By default, the remap applies to all applicable objects within the job. The object_type parameter allows a caller to specify different parameters for different object types within a job. Remaps that explicitly specify an object type override remaps that apply to all object types.


Table 46-16 describes the remaps provided by the METADATA_REMAP procedure.

Table 46-16 Remaps Provided by the METADATA_REMAP Procedure

NameDatatypeObject TypeMeaning

REMAP_SCHEMA

Text

Schema objects

Any schema object in the job that matches the object_type parameter and was located in the old_value schema will be moved to the value schema.

Privileged users can perform unrestricted schema remaps.

Nonprivileged users can perform schema remaps only if their schema is the target schema of the remap.

For example, SCOTT can remap his BLAKE's objects to SCOTT, but SCOTT cannot remap SCOTT's objects to BLAKE.

REMAP_TABLESPACE

Text

TABLE, INDEX, ROLLBACK_SEGMENT, MATERIALIZED_VIEW, MATERIALIZED_VIEW_LOG,TABLE_SPACE

Any storage segment in the job that matches the object_type parameter and was located in the old_value tablespace will be relocated to the value tablespace.

REMAP_DATAFILE

Text

LIBRARY, TABLESPACE, DIRECTORY

Any datafile reference in the job that matches the object_type parameter and referenced the old_value datafile will be redefined to use the value datafile.

REMAP_TABLE

Text

TABLE

Any reference to a table in the job that matches the old_value table name will be replaced with the value table name. The old_value parameter may refer to a partition such as employees.low. This allows names for tables constructed the by PARTITION_OPTIONS=DEPARTITION parameter to be specified by the user.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_ARGVAL. This message can indicate any of the following:

    • The job's mode does not include the specified object_type.

    • The remap has already been specified for the specified old_value and object_type.

  • INVALID_OPERATION. Remaps are only supported for SQL_FILE and Import operations. The job's operation was Export, which does not support the use of metadata remaps.

  • INVALID_STATE. The user called METADATA_REMAP after the job had started (that is, the job was not in the defining state).

  • INCONSISTENT_ARGS. There was no value supplied or it was of the wrong datatype for the remap.

  • PRIVILEGE_ERROR. A nonprivileged user attempted to do a REMAP_SCHEMA to a different user's schema or a REMAP_DATAFILE.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • The METADATA_REMAP procedure is only supported for Import and SQL_FILE operations. It enables you to apply commonly desired, predefined remappings to the definition of objects as part of the transfer. If you need remaps that are not supported within this procedure, you should do a preliminary SQL_FILE operation to produce a SQL script corresponding to the dump file set. By editing the DDL directly and then executing it, you can produce any remappings that you need.

  • Transforms for the DataPump API are a subset of the remaps implemented by the DBMS_METADATA.SET_TRANSFORM_PARAMETER API. Multiple remaps can be defined for a single job. However, each remap defined must be unique according its parameters. That is, two remaps cannot specify conflicting or redundant remaps.


METADATA_TRANSFORM Procedure

This procedure specifies transformations to be applied to objects as they are processed in the specified job.

Syntax

DBMS_DATAPUMP.METADATA_TRANSFORM (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN VARCHAR2,
   object_type IN VARCHAR2 DEFAULT NULL);
DBMS_DATAPUMP.METADATA_TRANSFORM (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN NUMBER,
   object_type IN VARCHAR2 DEFAULT NULL);

Parameters

Table 46-17 METADATA_TRANSFORM Procedure Parameters

ParameterDescription

handle

The handle for the current job. The current session must have previously attached to the handle through a call to the OPEN function.

name

The name of the transformation. See Table 46-18 for descriptions of the available transforms.

value

The value of the parameter for the transform

object_type

Designates the object type to which the transform applies. The list of object types supported for each mode are contained in the DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS, TABLE_EXPORT_OBJECTS, and TABLESPACE_EXPORT_OBJECTS catalog views.

By default, the transform applies to all applicable objects within the job. The object_type parameter allows a caller to specify different transform parameters for different object types within a job. Transforms that explicitly specify an object type override transforms that apply to all object types.


Table 46-18 describes the transforms provided by the METADATA_TRANSFORM procedure.

Table 46-18 Transforms Provided by the METADATA_TRANFORM Procedure

NameDatatypeObject TypeMeaning

PCTSPACE

NUMBER

TABLE

INDEX

TABLESPACE

Specifies a percentage multiplier used to alter extent allocations and datafile sizes. Used to shrink large tablespaces for testing purposes.

Defaults to 100.

SEGMENT_ATTRIBUTES

NUMBER

TABLE, INDEX

If nonzero (TRUE), emit storage segment parameters.

Defaults to 1.

STORAGE

NUMBER

TABLE

If nonzero (TRUE), emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is zero.)

Defaults to nonzero (TRUE).

OID

NUMBER

TYPE

TABLE

If zero, inhibits the assignment of the exported OID during type or table creation. Instead, a new OID will be assigned.

Use of this transform on Object Tables will cause breakage in REF columns that point to the table.

Defaults to 1.

SEGMENT_CREATION

NUMBER

TABLE

If nonzero (TRUE), the SQL SEGMENT CREATION clause is added to the CREATE TABLE statement. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE.

If the value is FALSE, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to FALSE to use the default segment creation attributes for the table(s) being loaded.

Defaults to nonzero (TRUE).

(This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).)


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_ARGVAL. This message can indicate any of the following:

    • The mode is transportable, which doesn't support transforms.

    • The job's mode does not include the specified object_type.

    • The transform has already been specified for the specified value and object_type.

  • INVALID_OPERATION. Transforms are only supported for SQL_FILE and Import operations. The job's operation was Export which does not support the use of metadata transforms.

  • INVALID_STATE. The user called METADATA_TRANSFORM after the job had started (that is, the job was not in the defining state).

  • INCONSISTENT_ARGS. There was no value supplied or it was of the wrong datatype for the transform.

  • PRIVILEGE_ERROR. A nonprivileged user attempted to do a REMAP_SCHEMA to a different user's schema or a REMAP_DATAFILE.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • The METADATA_TRANSFORM procedure is only supported for Import and SQL_FILE operations. It enables you to apply commonly desired, predefined transformations to the definition of objects as part of the transfer. If you need transforms that are not supported within this procedure, you should do a preliminary SQL_FILE operation to produce a SQL script corresponding to the dump file set. By editing the DDL directly and then executing it, you can produce any transformations that you need.

  • Transforms for the DataPump API are a subset of the transforms implemented by the DBMS_METADATA.SET_TRANSFORM_PARAMETER API. Multiple transforms can be defined for a single job. However, each transform defined must be unique according its parameters. That is, two transforms cannot specify conflicting or redundant transformations.


OPEN Function

This function is used to declare a new job using the Data Pump API. The handle that is returned is used as a parameter for calls to all other procedures (but not to the ATTACH function).

Syntax

DBMS_DATAPUMP.OPEN (
   operation    IN VARCHAR2,
   job_mode         IN VARCHAR2,
   remote_link  IN VARCHAR2 DEFAULT NULL,
   job_name     IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE'
 RETURN NUMBER;

Parameters

Table 46-19 OPEN Function Parameters

ParameterMeaning

operation

The type of operation to be performed. Table 46-20 contains descriptions of valid operation types.

job_mode

The scope of the operation to be performed. Table 46-21 contains descriptions of valid modes. Specifying NULL generates an error.

remote_link

If the value of this parameter is non-null, it provides the name of a database link to the remote database that will be the source of data and metadata for the current job.

job_name

The name of the job. The name is limited to 30 characters; it will be truncated if more than 30 characters are used. It may consist of printable characters and spaces. It is implicitly qualified by the schema of the user executing the OPEN function and must be unique to that schema (that is, there cannot be other Data Pump jobs using the same name).

The name is used to identify the job both within the API and with other database components such as identifying the job in the DBA_RESUMABLE view if the job becomes suspended through lack of resources. If no name is supplied, a system generated name will be provided for the job in the following format: "SYS_<OPERATION>_<MODE>_%N".

The default job name is formed where %N expands to a two-digit incrementing integer starting at '01' (for example, "SYS_IMPORT_FULL_03"). The name supplied for the job will also be used to name the master table and other resources associated with the job.

version

The version of database objects to be extracted. This option is only valid for Export, network Import, and SQL_FILE operations. Database objects or attributes that are incompatible with the version will not be extracted. Legal values for this parameter are as follows:

  • COMPATIBLE - (default) the version of the metadata corresponds to the database compatibility level and the compatibility release level for feature (as given in the V$COMPATIBILITY view). Database compatibility must be set to 9.2 or higher.

  • LATEST - the version of the metadata corresponds to the database version.

  • A specific database version, for example, '10.0.0'. In Oracle Database10g, this value cannot be lower than 10.0.0.


Table 46-20 describes the valid operation types for the OPEN function.

Table 46-20 Valid Operation Types for the OPEN Function

OperationDescription

EXPORT

Saves data and metadata to a dump file set or obtains an estimate of the size of the data for an operation.

IMPORT

Restores data and metadata from a dump file set or across a database link.

SQL_FILE

Displays the metadata within a dump file set, or from across a network link, as a SQL script. The location of the SQL script is specified through the ADD_FILE procedure.


Table 46-21 describes the valid modes for the OPEN function.

Table 46-21 Valid Modes for the OPEN Function

ModeDescription

FULL

Operates on the full database or full dump file set except for the SYS, XDB,ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, and LBACSYS schemas.

SCHEMA

Operates on a set of selected schemas. Defaults to the schema of the current user. All objects in the selected schemas are processed. Users cannot specify SYS, XDB, ORDSYS, MDSYS, CTXSYS, ORDPLUGINS, or LBACSYS schemas for this mode.

TABLE

Operates on a set of selected tables. Defaults to all of the tables in the current user's schema. Only tables and their dependent objects are processed.

TABLESPACE

Operates on a set of selected tablespaces. No defaulting is performed. Tables that have storage in the specified tablespaces are processed in the same manner as in Table mode.

TRANSPORTABLE

Operates on metadata for tables (and their dependent objects) within a set of selected tablespaces to perform a transportable tablespace export/import.


Return Values

  • An opaque handle for the job. This handle is used as input to the following procedures: ADD_FILE, CREATE_JOB_VIEW, DATA_FILTER, DETACH, GET_STATUS, LOG_ENTRY, LOG_ERROR,METADATA_FILTER, METADATA_REMAP, METADATA_TRANSFORM, SET_PARALLEL,SET_PARAMETER, START_JOB,STOP_JOB, and WAIT_FOR_JOB

Exceptions

  • INVALID_ARGVAL. An invalid operation or mode was specified. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

  • JOB_EXISTS. A table already exists with the specified job name.

  • PRIVILEGE_ERROR. The user does not have the necessary privileges or roles to use the specified mode.

  • INTERNAL_ERROR. The job was created under the wrong schema or the master table was of the wrong format.

  • SUCCESS_WITH_INFO. The function succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • When the job is created, a master table is created for the job under the caller's schema within the caller's default tablespace. A handle referencing the job is returned that attaches the current session to the job. Once attached, the handle remains valid until either an explicit or implicit detach occurs. The handle is only valid in the caller's session. Other handles can be attached to the same job from a different session by using the ATTACH function.

  • If the call to the OPEN function fails, call the GET_STATUS procedure with a null handle to retrieve additional information about the failure.


SET_PARALLEL Procedure

This procedure adjusts the degree of parallelism within a job.

Syntax

DBMS_DATAPUMP.SET_PARALLEL(
   handle      IN NUMBER,
   degree      IN NUMBER);

Parameters

Table 46-22 SET_PARALLEL Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function.

degree

The maximum number of worker processes that can be used for the job. You use this parameter to adjust the amount of resources used for a job.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_OPERATION. The SET_PARALLEL procedure is only valid for export and import operations.

  • INVALID_ARGVAL. An invalid value was supplied for an input parameter.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • The SET_PARALLEL procedure is only available in the Enterprise Edition of the Oracle database.

  • The SET_PARALLEL procedure can be executed by any session attached to a job. The job must be in one of the following states: Defining, Idling, or Executing.

  • The effect of decreasing the degree of parallelism may be delayed because ongoing work needs to find an orderly completion point before SET_PARALLEL can take effect.

  • Decreasing the parallelism will not result in fewer worker processes associated with the job. It will only decrease the number of worker processes that will be executing at any given time.

  • Increasing the parallelism will take effect immediately if there is work that can be performed in parallel.

  • The degree of parallelism requested by a user may be decreased based upon settings in the resource manager or through limitations introduced by the PROCESSES or SESSIONS initialization parameters in the init.ora file.

  • To parallelize an Export job to a degree of n, the user should supply n files in the dump file set or specify a substitution variable in a file specification. Otherwise, some of the worker processes will be idle while waiting for files.

  • SQL_FILE operations always operate with a degree of 1. Jobs running in the Transportable mode always operate with a degree of 1.


SET_PARAMETER Procedures

This procedure is used to specify job-processing options.

Syntax

DBMS_DATAPUMP.SET_PARAMETER(
   handle       IN NUMBER,
   name         IN VARCHAR2,
   value        IN VARCHAR2);

DBMS_DATAPUMP.SET_PARAMETER (
   handle      IN NUMBER,
   name        IN VARCHAR2,
   value       IN NUMBER);

Parameters

Table 46-23 SET_PARAMETER Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to the OPEN function.

name

The name of the parameter. Table 46-24 describes the valid parameter names.

value

The value for the specified parameter


Table 46-24 describes the valid options for the name parameter of the SET_PARAMETER procedure.

Table 46-24 Valid Options for the name Parameter in the SET_PARAMETER Procedure

Parameter NameDatatypeSupported OperationsMeaning

CLIENT_COMMAND

Text

All

An opaque string used to describe the current operation from the client's perspective. The command-line procedures will use this string to store the original command used to invoke the job.

COMPRESSION

Text

Export

Allows you to trade off the size of the dump file set versus the time it takes to perform export and import operations.

The DATA_ONLY option compresses only user data in the dump file set.

The METADATA_ONLY option compresses only metadata in the dump file set.

The ALL option compresses both user data and metadata.

The NONE option stores the dump file set in an uncompressed format.

The METADATA_ONLY and NONE options require a job version of 10.2 or later. All other options require a job version of 11.1 or later.

Default=METADATA_ONLY

DATA_OPTIONS

Number

Export and Import

A bitmask to supply special options for processing the job. The possible values are as follows:

  • KU$_DATAOPT_SKIP_CONST_ERR

  • KU$_DATAOPT_XMLTYPE_CLOB

  • KU$_DATAOPT_DISABL_APPEND_HINT

Export supports the value KU$_DATAOPT_XMLTYPE_CLOB. This option stores compressed XMLType columns in the dump file as CLOBs rather than as XML documents.

Import supports the value KU$_DATAOPT_SKIP_CONST_ERR. This option specifies that if constraint violations occur while data is being imported into user tables, the rows that cause the violations will be rejected and the load will continue. If this option is not set, a constraint error will abort the loading of the entire partition (or table for unpartitioned tables). Setting this option may affect performance, especially for pre-existing tables with unique indexes or constraints.

Import also supports the value KU$_DATAOPT_DISABL_APPEND_HINT. This option prevents the append hint from being applied to the data load. Disabling the APPEND hint can be useful if there is a small set of data objects to load that already exist in the database and some other application may be concurrently accessing one or more of the data objects.

Use of this parameter requires that the version on the OPEN function be set to 11.1 or later.

Default=0

ENCRYPTION

Text

Export

Specifies what to encrypt in the dump file set, as follows:

ALL enables encryption for all data and metadata in the export operation.

DATA_ONLY specifies that only data is written to the dump file set in encrypted format.

ENCRYPTED_COLUMNS_ONLY specifies that only encrypted columns are written to the dump file set in encrypted format.

METADATA_ONLY specifies that only metadata is written to the dump file set in encrypted format.

NONE specifies that no data is written to the dump file set in encrypted format.

This parameter requires a job version of 11.1 or later.

The default value depends upon the combination of encryption-related parameters that are used. To enable encryption, either ENCRYPTION or ENCRYPTION_PASSWORD or both, must be specified. If only ENCRYPTION_PASSWORD is specified, then ENCRYPTION defaults to ALL. If neither ENCRYPTION nor ENCRYPTION_PASSWORD is specified, then ENCRYPTION defaults to NONE.

To specify ALL, DATA_ONLY, or METADATA_ONLY, the COMPATIBLE initialization parameter must be set to at least 11.1.

NOTE: If the data being exported includes SecureFiles that you want to be encrypted, then you must specify ENCRYPTION=ALL to encrypt the entire dump file set. Encryption of the entire dump file set is the only way to achieve encryption security for SecureFiles during a Data Pump export operation.

ENCRYPTION_ALGORITHM

Text

Export

Identifies which cryptographic algorithm should be used to perform encryption. Possible values are AES128, AES192, and AES256.

The ENCRYPTION_ALGORITHM parameter requires that you also specify either ENCRYPTION or ENCRYPTION_PASSWORD; otherwise an error is returned. See Oracle Database Advanced Security Administrator's Guide for information about encryption algorithms.

This parameter requires a job version of 11.1 or later.

Default=AES128

ENCRYPTION_MODE

Text

Export

Identifies the types of security used for encryption and decryption. The values are as follows:

PASSWORD requires that you provide a password when creating encrypted dump file sets. You will need to provide the same password when you import the dump file set. PASSWORD mode requires that you also specify the ENCRYPTION_PASSWORD parameter. The PASSWORD mode is best suited for cases in which the dump file set will be imported into a different or remote database, but which must remain secure in transit.

TRANSPARENT allows an encrypted dump file set to be created without any intervention from a database administrator (DBA), provided the required Oracle Encryption Wallet is available. Therefore, the ENCRYPTION_PASSWORD parameter is not required, and will in fact, cause an error if it is used in TRANSPARENT mode. This encryption mode is best suited for cases in which the dump file set will be imported into the same database from which it was exported.

DUAL creates a dump file set that can later be imported using either the Oracle Encryption Wallet or the password that was specified with the ENCRYPTION_PASSWORD parameter. DUAL mode is best suited for cases in which the dump file set will be imported on-site using the Oracle Encryption Wallet, but which may also need to be imported offsite where the Oracle Encryption Wallet is not available.

When you use the ENCRYPTION_MODE parameter, you must also use either the ENCRYPTION or ENCRYPTION_PASSWORD parameter. Otherwise, an error is returned.

To use DUAL or TRANSPARENT mode, the COMPATIBLE initialization parameter must be set to at least 11.1.

The default mode depends on which other encryption-related parameters are used. If only ENCRYPTION is specified, then the default mode is TRANSPARENT. If ENCRYPTION_PASSWORD is specified and the Oracle Encryption Wallet is open, then the default is DUAL. If ENCRYPTION_PASSWORD is specified and the Oracle Encryption Wallet is closed, then the default is PASSWORD.

ENCRYPTION_PASSWORD

Text

Export and Import

Specifies a key for re-encrypting encrypted table columns, metadata, or table data so that they are not written as clear text in the dump file set. If the export operation involves encrypted table columns, but an encryption password is not supplied, then the encrypted columns will be written to the dump file set as clear text and a warning will be issued.

NOTE: Data Pump encryption functionality has changed as of Oracle Database 11g release 1 (11.1). Prior to release 11.1, the ENCRYPTION_PASSWORD parameter applied only to encrypted columns. However, as of release 11.1, the new ENCRYPTION parameter provides options for encrypting other types of data. This means that if you now specify ENCRYPTION_PASSWORD without also specifying ENCRYPTION and a specific option, then all data written to the dump file will be encrypted (equivalent to specifying ENCRYPTION=ALL). If you want to re-encrypt only encrypted columns, you must now specify ENCRYPTION=ENCRYPTED_COLUMNS_ONLY in addition to ENCRYPTION_PASSWORD.

For export operations, this parameter is required if ENCRYPTION_MODE is set to either PASSWORD or DUAL.

If ENCRYPTION_PASSWORD is specified but ENCRYPTION_MODE is not specified, then it is not necessary to have Transparent Data Encryption set up since ENCRYPTION_MODE will default to PASSWORD.

The ENCRYPTION_PASSWORD parameter is not valid if the requested encryption mode is TRANSPARENT.

To use the ENCRYPTION_PASSWORD parameter if ENCRYPTION_MODE is set to DUAL, you must have Transparent Data Encryption set up. See Oracle Database Advanced Security Administrator's Guide for more information about Transparent Data Encryption.

For network exports, the ENCRYPTION_PASSWORD parameter in conjunction with ENCRYPTION=ENCRYPTED_COLUMNS_ONLY is not supported with user-defined external tables that have encrypted columns. The table will be skipped and an error message will be displayed, but the job will continue.

Encryption attributes for all columns must match between the exported table definition and the target table.

This parameter requires a job version of 10.2 or later.

ESTIMATE

Text

Export and Import

Specifies that the estimate method for the size of the tables should be performed before starting the job.

If BLOCKS, a size estimate for the user tables is calculated using the count of blocks allocated to the user tables.

If STATISTICS, a size estimate for the user tables is calculated using the statistics associated with each table. If no statistics are available for a table, the size of the table is estimated using BLOCKS.

The ESTIMATE parameter cannot be used in Transportable Tablespace mode.

Default=BLOCKS

ESTIMATE_ONLY

Number

Export

Specifies that only the estimation portion of an export job should be performed. This option is useful for estimating the size of dump files when the size of the export is unknown.

FLASHBACK_SCN

NUMBER

Export and network Import

System change number (SCN) to serve as transactionally consistent point for reading user data. If neither FLASHBACK_SCN nor FLASHBACK_TIME is specified, there will be no transactional consistency between partitions, except for logical standby databases and Streams targets. FLASHBACK_SCN is not supported in Transportable mode.

FLASHBACK_TIME

Text

Export and network Import

Either the date and time used to determine a consistent point for reading user data or a string of the form TO_TIMESTAMP(...).

If neither FLASHBACK_SCN nor FLASHBACK_TIME is specified, there will be no transactional consistency between partitions.

FLASHBACK_SCN and FLASHBACK_TIME cannot both be specified for the same job. FLASHBACK_TIME is not supported in Transportable mode.

INCLUDE_METADATA

NUMBER

Export and Import

If nonzero, metadata for objects will be moved in addition to user table data.

If zero, metadata for objects will not moved. This parameter converts an Export operation into an unload of user data and an Import operation into a load of user data.

INCLUDE_METADATA is not supported in Transportable mode.

Default=1

PARTITION_OPTIONS

Text

Import

Specifies how partitioned tables should be handled during an import operation. The options are as follows:

NONE means that partitioning is reproduced on the target database as it existed in the source database.

DEPARTITION means that each partition or subpartition that contains storage in the job is reproduced as a separate unpartitioned table. Intermediate partitions that are subpartitioned are not re-created (although their subpartitions are converted into tables). The names of the resulting tables are system-generated from the original table names and partition names unless the name is overridden by the REMAP_TABLE metadata transform.

MERGE means that each partitioned table is re-created in the target database as an unpartitioned table. The data from all of the source partitions is merged into a single storage segment. This option is not supported for transportable jobs or when the TRANSPORTABLE parameter is set to ALWAYS.

This parameter requires a job version of 11.1 or later.

Default=NONE

SKIP_UNUSABLE_INDEXES

NUMBER

Import

If nonzero, rows will be inserted into tables having unusable indexes. SKIP_UNUSABLE_INDEXES is not supported in Transportable mode.

Default=1

SOURCE_EDITION

Text

Export and network Import

The application edition that will be used for determining the objects that will be unloaded for export and for network import.

TABLE_EXISTS_ACTION

Text

Import

Specifies the action to be performed when data is loaded into a preexisting table. The possible actions are: TRUNCATE, REPLACE, APPEND, and SKIP.

If INCLUDE_METADATA=0, only TRUNCATE and APPEND are supported.

If TRUNCATE, rows are removed from a preexisting table before inserting rows from the Import.

Note that if TRUNCATE is specified on tables referenced by foreign key constraints, the TRUNCATE will be modified into a REPLACE.

If REPLACE, preexisting tables are replaced with new definitions. Before creating the new table, the old table is dropped.

If APPEND, new rows are added to the existing rows in the table.

If SKIP, the preexisting table is left unchanged.

TABLE_EXISTS_ACTION is not supported in Transportable mode.

The default is SKIP if metadata is included in the import. The default is APPEND if INCLUDE_METADATA is set to 0.

TABLESPACE_DATAFILE

Text

Import

Specifies the full file specification for a datafile in the transportable tablespace set. TABLESPACE_DATAFILE is only valid for transportable mode imports.

TABLESPACE_DATAFILE can be specified multiple times, but the value specified for each occurrence must be different.

TARGET_EDITION

Text

Import

The application edition that will be used for determining where the objects will be loaded for import and for network import.

TRANSPORTABLE

Text

Export

For export operations done in table mode, allows the data to be moved using transportable tablespaces. Storage segments in the moved tablespaces that are not associated with the parent schemas (tables) will be reclaimed at import time. If individual partitions are selected in a table-mode job, only the tablespaces referenced by those partitions will be moved. During import, the moved partitions can only be reconstituted as tables by using the PARTITION_OPTIONS=DEPARTITION parameter.

Use of the TRANSPORTABLE parameter prohibits the subsequent import of the dump file into a database at a lower version or using different character sets. Additionally, the data files may need to be converted if the target database is on a different platform. The TRANSPORTABLE parameter is not allowed if a network link is supplied on the OPEN call.

The possible values for this parameter are as follows:

ALWAYS - data is always moved by moving data files

NEVER - data files are never used for copying user data

This parameter requires a job version of 11.1 or later

Default=NEVER

TTS_FULL_CHECK

NUMBER

Export

If nonzero, verifies that a transportable tablespace set has no dependencies (specifically, IN pointers) on objects outside the set, and vice versa. Only valid for Transportable mode Exports.

Default=0

USER_METADATA

NUMBER

Export and network Import

For schema-mode operations, specifies that the metadata to re-create the users' schemas (for example, privilege grants to the exported schemas) should also be part of the operation if set to nonzero. Users must be privileged to explicitly set this parameter.

The USER_METADATA parameter cannot be used in Table, Tablespace, or Transportable Tablespace mode.

Default=1 if user has DATAPUMP_EXP_FULL_DATABASE role; 0 otherwise.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_ARGVAL. This exception could be due to any of the following causes:

    • An invalid name was supplied for an input parameter

    • The wrong datatype was used for value

    • A value was not supplied

    • The supplied value was not allowed for the specified parameter name

    • A flashback parameter had been established after a different flashback parameter had already been established

    • A parameter was specified that did not support duplicate definitions

  • INVALID_OPERATION. The operation specified is invalid in this context.

  • INVALID_STATE. The specified job is not in the Defining state.

  • INCONSISTENT_ARGS. Either the specified parameter is not supported for the current operation type or it is not supported for the current mode.

  • PRIVILEGE_ERROR. The user does not have the DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role required for the specified parameter.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • The SET_PARAMETER procedure is used to specify optional features for the current job. See Table 46-24 for a list of supported options.


START_JOB Procedure

This procedure begins or resumes execution of a job.

Syntax

DBMS_DATAPUMP.START_JOB (
   handle       IN NUMBER,
   skip_current    IN  NUMBER DEFAULT 0,
   abort_step      IN  NUMBER DEFAULT 0,
   cluster_ok      IN  NUMBER DEFAULT 1,
   service_name    IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 46-25 START_JOB Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function.

skip_current

If nonzero, causes actions that were 'in progress' on a previous execution of the job to be skipped when the job restarts. The skip will only be honored for Import jobs. This mechanism allows the user to skip actions that trigger fatal bugs and cause the premature termination of a job. Multiple actions can be skipped on a restart. The log file will identify which actions are skipped. If a domain index was being processed, all pieces of the domain index are skipped even if the error occurred in only a subcomponent of the domain index.

A description of the actions skipped is entered into the log file. skip_current is ignored for the initial START_JOB in a job.

If zero, no data or metadata is lost upon a restart.

abort_step

Value must be 0. Inserting values other than 0 into this argument will have unintended consequences.

cluster_ok

If = 0, all workers are started on the current instance. Otherwise, workers are started on instances usable by the job.

service_name

If specified, indicates a service name used to constrain the job to specific instances or to a specific resource group.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID_STATE. The causes of this exception can be any of the following:

    • No files have been defined for an Export, non-network Import, or SQL_FILE job

    • An ADD_FILE procedure has not been called to define the output for a SQL_FILE job

    • A TABLESPACE_DATAFILE parameter has not been defined for a Transportable Import job

    • A TABLESPACE_EXPR metadata filter has not been defined for a Transportable or Tablespace mode Export or Network job

    • The dump file set on an Import or SQL_FILE job was either incomplete or missing a master table specification

  • INVALID_OPERATION. Unable to restore master table from a dump file set.

  • INTERNAL_ERROR. An inconsistency was detected when the job was started. Additional information may be available through the GET_STATUS procedure.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • When this procedure is called to request that the corresponding job be started or restarted, the state of the job is changed from either the Defining or Idling state to the Executing state.

  • If the SET_PARALLEL procedure was not called prior to the START_JOB procedure, the initial level of parallelism used in the job will be 1. If SET_PARALLEL was called prior to the job starting, the degree specified by the last SET_PARALLEL call determines the parallelism for the job. On restarts, the parallelism is determined by the previous parallel setting for the job, unless it is overridden by another SET_PARALLEL call.

  • To restart a stopped job, an ATTACH function must be performed prior to executing the START_JOB procedure.


STOP_JOB Procedure

This procedure terminates a job, but optionally, preserves the state of the job.

Syntax

DBMS_DATAPUMP.STOP_JOB (
   handle      IN NUMBER,
   immediate   IN NUMBER DEFAULT 0,
   keep_master IN NUMBER DEFAULT NULL,
   delay       IN NUMBER DEFAULT 60);

Parameters

Table 46-26 STOP_JOB Procedure Parameters

ParameterDescription

handle

The handle of a job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function. At the end of the procedure, the user is detached from the handle.

immediate

If nonzero, the worker processes are aborted immediately. This halts the job quickly, but parts of the job will have to be rerun if the job is ever restarted.

If zero, the worker processes are allowed to complete their current work item (either metadata or table data) before they are terminated. The job is placed in a Stop Pending state while the workers finish their current work.

keep_master

If nonzero, the master table is retained when the job is stopped. If zero, the master table is dropped when the job is stopped. If the master table is dropped, the job will not be restartable. If the master table is dropped during an export job, the created dump files are deleted.

delay

The number of seconds to wait until other attached sessions are forcibly detached. The delay allows other sessions attached to the job to be notified that a stop has been performed. The job keeps running until either all clients have detached or the delay has been satisfied. If no delay is specified, then the default delay is 60 seconds. If a shorter delay is used, clients might not be able to retrieve the final messages for the job through the GET_STATUS procedure.


Exceptions

  • INVALID_HANDLE. The specified handle is not attached to a Data Pump job.

  • INVALID STATE. The job is already in the process of being stopped or completed.

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS procedure.

  • NO_SUCH_JOB. The specified job does not exist.

Usage Notes

  • This procedure is used to request that the corresponding job stop executing.

  • The termination of a job that is in an Executing state may take several minutes to complete in an orderly fashion.

  • For jobs in the Defining, Idling, or Completing states, this procedure is functionally equivalent to the DETACH procedure.

  • Once a job is stopped, it can be restarted using the ATTACH function and START_JOB procedures, provided the master table and the dump file set are left intact.

  • If the KEEP_MASTER parameter is not specified, and the job is in the Defining state or has a mode of Transportable, the master table is dropped. Otherwise, the master table is retained.


WAIT_FOR_JOB Procedure

This procedure runs a job until it either completes normally or stops for some other reason.

Syntax

DBMS_DATAPUMP.WAIT_FOR_JOB (
  handle      IN   NUMBER,
  job_state   OUT  VARCHAR2);

Parameters

Table 46-27 WAIT_FOR_JOB Procedure Parameters

ParameterDescription

handle

The handle of the job. The current session must have previously attached to the handle through a call to either the OPEN or ATTACH function. At the end of the procedure, the user is detached from the handle.

job_state

The state of the job when it has stopped executing. This will be either Stopped or Completed.


Exceptions

  • SUCCESS_WITH_INFO. The procedure succeeded, but further information is available through the GET_STATUS API.

  • INVALID_HANDLE. The job handle is no longer valid.

Usage Notes

This procedure provides the simplest mechanism for waiting for the completion of a Data Pump job. The job should be started before calling WAIT_FOR_JOB. When WAIT_FOR_JOB returns, the job will no longer be executing. If the job completed normally, the final status will be Completed. If the job stopped executing because of a STOP_JOB request or an internal error, the final status will be Stopped.

PK­řŕŕěÂěPK˘FJOEBPS/d_datmin.htm€˙ DBMS_DATA_MINING

44 DBMS_DATA_MINING

Oracle Data Mining is an analytical technology that derives actionable information from data in an Oracle Database. You can use Oracle Data Mining to evaluate the probability of future events and discover unsuspected associations and groupings within your data.

The DBMS_DATA_MINING package is the programmatic interface for creating and managing data mining models (mining model schema objects). Oracle Data Mining also supports a family of SQL functions for deploying data mining models.

Oracle Data Miner, a graphical interface to Oracle Data Mining, is available for download from the Oracle Technology Network at: http://www.oracle.com/technetwork/database/options/odm/


See Also:


This chapter contains the following topics:


Using DBMS_DATA_MINING

This section contains topics that relate to using the DBMS_DATA_MINING package.


Overview

Oracle Data Mining supports both supervised and unsupervised data mining. Supervised data mining predicts a target value based on historical data. Unsupervised data mining discovers natural groupings and does not use a target.


See Also:

Oracle Data Mining Concepts for more information

A data mining function refers to the methods for solving a given class of data mining problems. The mining function must be specified when a model is created. See "Mining Functions".


Note on Terminology:

In data mining terminology, a function is a general type of problem to be solved by a given approach to data mining. In SQL language terminology, a function is an operator that returns a value.

In Oracle Data Mining documentation, the term function, or mining function refers to a data mining function; the term SQL function or SQL Data Mining function refers to a SQL function for scoring (deploying) data mining models. The SQL Data Mining functions are documented in Oracle Database SQL Language Reference.


Supervised data mining functions include:

  • Classification

  • Regression

  • Attribute Importance

Unsupervised data mining functions include:

  • Clustering

  • Association

  • Feature Extraction

  • Anomaly Detection (one-class classification)

The steps you use to build and apply a mining model depend on the data mining function and the algorithm being used. The algorithms supported by Oracle Data Mining are listed in Table 44-1.

Table 44-1 Oracle Data Mining Algorithms

AlgorithmAbbreviationFunction

Adaptive Bayes Network (deprecated)

ABN

Classification

Apriori

AP

Association

Decision Tree

DT

Classification

Generalized Linear Model

GLM

Classification and Regression

k-Means (default clustering algorithm)

KM

Clustering

Minimal Descriptor Length

MDL

Attribute Importance

Naive Bayes (default classification algorithm)

NB

Classification

Non-Negative Matrix Factorization

NMF

Feature Extraction

Orthogonal Partitioning Clustering

O-Cluster

Clustering

Support Vector Machine (default regression algorithm)

SVM

Classification and regression (and anomaly detection through one-class classification)



Mining Model Objects

Mining models are Oracle Database schema objects. They support the standard security features of Oracle Database. Mining models are also supported by SQL COMMENT and SQL AUDIT.


See Also:


ALL_MINING_MODELS

You can query the data dictionary view ALL_MINING_MODELS to obtain a list of accessible mining models.

Example 44-1 ALL_MINING_MODELS

SQL> describe all_mining_models
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 MINING_FUNCTION                                    VARCHAR2(30)
 ALGORITHM                                          VARCHAR2(30)
 CREATION_DATE                             NOT NULL DATE
 BUILD_DURATION                                     NUMBER
 MODEL_SIZE                                         NUMBER
 COMMENTS                                           VARCHAR2(4000)

See Also:

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODELS and related views

Mining Model Naming Restrictions

The naming rules for models are more restrictive than the naming rules for most database schema objects. A model name must satisfy the following additional requirements:

  • It must be 25 or fewer characters long.

  • It must be a nonquoted identifier. Oracle requires that nonquoted identifiers contain only alphanumeric characters, the underscore (_), dollar sign ($), and pound sign (#); the initial character must be alphabetic. Oracle strongly discourages the use of the dollar sign and pound sign in nonquoted literals.

Naming requirements for schema objects are fully documented in Oracle Database SQL Language Reference.

ALL_MINING_MODEL_ATTRIBUTES

You can query the data dictionary view ALL_MINING_MODEL_ATTRIBUTES to obtain a list of the data attributes for each accessible mining model. Data attributes are the columns of data used by an algorithm to build a model. Some or all of these columns must be present in the data to which the model is applied.

Data attributes are referred to as the model signature. The ALL_MINING_MODEL_ATTRIBUTES view lists the data attributes in the model signature, including the target if the model is supervised.

An algorithm builds an internal representation of the data attributes and uses them as either categoricals (data that classifies or categorizes) or as numericals (continuous data). These internal model attributes can be viewed using the GET_MODEL_DETAILS functions.

Example 44-2 ALL_MINING_MODEL_ATTRIBUTES

SQL> describe all_mining_model_attributes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 ATTRIBUTE_NAME                            NOT NULL VARCHAR2(30)
 ATTRIBUTE_TYPE                                     VARCHAR2(11)
 DATA_TYPE                                          VARCHAR2(12)
 DATA_LENGTH                                        NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 USAGE_TYPE                                         VARCHAR2(8)
 TARGET                                             VARCHAR2(3)

See Also:

Oracle Data Mining Application Developer's Guide for more information about attributes and ALL_MINING_MODEL_ATTRIBUTES

ALL_MINING_MODEL_SETTINGS

The view ALL_MINING_MODEL_SETTINGS returns the settings for each accessible mining model. Settings control various characteristics of mining models.

All settings have default values. The values of some settings are generated by the algorithm by default. You can override the default value of a setting by specifying its value in a settings table for the model. All settings, both default and user-specified, are listed in ALL_MINING_MODEL_SETTINGS.

Example 44-3 ALL_MINING_MODEL_SETTINGS

SQL> describe all_mining_model_settings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 SETTING_NAME                              NOT NULL VARCHAR2(30)
 SETTING_VALUE                                      VARCHAR2(4000)
 SETTING_TYPE                                       VARCHAR2(7)

See Also:



Security Model

The DBMS_DATA_MINING package is owned by user SYS and is installed as part of database installation. Execution privilege on the package is granted to public. The routines in the package are run with invokers' rights (run with the privileges of the current user).

The DBMS_DATA_MINING package exposes APIs that are leveraged by the Oracle Data Mining option. Users who wish to create mining models in their own schema require the CREATE MINING MODEL system privilege (as well as the CREATE TABLE and CREATE VIEW system privilege). Users who wish to create mining models in other schemas require the CREATE ANY MINING MODEL system privilege (as well as the corresponding table and view creation privileges).

Users have full control over managing models that exist within their own schema. Additional system privileges necessary for managing data mining models in other schemas include ALTER ANY MINING MODEL, DROP ANY MINING MODEL, SELECT ANY MINING MODEL, COMMENT ANY MINING MODEL, and AUDIT ANY.

Individual object privileges on mining models, ALTER MINING MODEL and SELET MINING MODEL, can be used to selectively grant privileges on a model to a different user.


See Also:

Oracle Data Mining Administrator's Guide for more information about the security features of Oracle Data Mining


Deprecated Subprograms

The following subprograms were deprecated in Oracle Data Mining 11g Release 1 (11.1).

  • GET_DEFAULT_SETTINGS

    Replaced with data dictionary views: USER/ALL/DBA_MINING_MODEL_SETTINGS

  • GET_MODEL_SETTINGS

    Replaced with data dictionary views: USER/ALL/DBA_MINING_MODEL_SETTINGS

  • GET_MODEL_SIGNATURE

    Replaced with data dictionary views: USER/ALL/DBA_MINING_MODEL_ATTRIBUTES

The following view was deprecated in Oracle Data Mining 11g Release 1 (11.1).

  • DM_USER_MODELS

    Replaced with data dictionary views: USER/ALL/DBA_MINING_MODELS

The Adaptive Bayes Network algorithm was deprecated in Oracle Data Mining 11g Release 1 (11.1).


Note:

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

Since 11g Release 1 (11.1), the DMSYS schema is no longer present in the database. Oracle Data Mining metadata now resides in SYS.


Mining Functions

The constants that specify the mining function of a model are listed in Table 44-2. The concept of a "mining function" is introduced in "Overview".

All models are created with a mining function. The mining function is a required argument to the CREATE_MODEL Procedure.

Table 44-2 Mining Functions

ValueDescription

ASSOCIATION

Association is a descriptive mining function. An association model identifies relationships and the probability of their occurrence within a data set.

Association models use the Apriori algorithm.

ATTRIBUTE_IMPORTANCE

Attribute Importance is a predictive mining function. An attribute importance model identifies the relative importance of an attribute in predicting a given outcome.

Attribute Importance models use the Minimal Descriptor Length algorithm.

CLASSIFICATION

Classification is a predictive mining function. A classification model uses historical data to predict a categorical target.

Classification models can use: Naive Bayes, Adaptive Bayes Network (deprecated), Decision Tree, Logistic Regression, or Support Vector Machine algorithms. The default is Naive Bayes.

The classification function can also be used for anomaly detection. In this case, the SVM algorithm with a null target is used (One-Class SVM).

CLUSTERING

Clustering is a descriptive mining function. A clustering model identifies natural groupings within a data set.

Clustering models can use: k-Means or O-Cluster algorithms. The default is k-Means.

FEATURE_EXTRACTION

Feature Extraction is a descriptive mining function. A feature extraction model creates an optimized data set on which to base a model.

Feature extraction models use the Non-Negative Matrix Factorization algorithm.

REGRESSION

Regression is a predictive mining function. A regression model uses historical data to predict a numerical target.

Regression models can use Support Vector Machine or Linear Regression. The default is Support Vector Machine.



Model Settings

Oracle Data Mining uses settings to specify the algorithm and other characteristics of a model. Some settings are general, some are specific to a mining function, and some are specific to an algorithm.

All settings have default values. If you want to override one or more of the settings for a model, you must create a settings table. The settings table must have the column names and data types shown in Table 44-3.

Table 44-3 Required Columns in the Model Settings Table

Column NameData Type

SETTING_NAME

VARCHAR2(30)

SETTING_VALUE

VARCHAR2(4000)


The information you provide in the settings table is used by the model at build time. The name of the settings table is an optional argument to the CREATE_MODEL Procedure.

You can find the settings used by a model by querying the data dictionary view ALL_MINING_MODEL_SETTINGS. This view lists the model settings used by the mining models to which you have access. All the setting values are included in the view, whether default or user-specified. See "ALL_MINING_MODEL_SETTINGS".

Algorithm Names

The ALGO_NAME setting specifies the model algorithm. The values for the ALGO_NAME setting are listed in Table 44-4.

Table 44-4 Algorithm Names

ALGO_NAME ValueDescriptionMining Function

ALGO_ADAPTIVE_BAYES€˙_NETWORK

Adaptive Bayes Network (deprecated)

Classification

ALGO_DECISION_TREE

Decision Tree

Classification

ALGO_NAIVE_BAYES

Naive Bayes

Classification

ALGO_GENERALIZED_LINEAR_MODEL

Generalized Linear Model

Classification and Regression

ALGO_SUPPORT_VECTOR_MACHINES

Support Vector Machine

Classification and Regression

ALGO_KMEANS

Enhanced k_Means

Clustering

ALGO_O_CLUSTER

O-Cluster

Clustering

ALGO_AI_MDL

Minimum Description Length

Attribute Importance

ALGO_APRIORI_ASSOCIATION_RULES

Apriori

Association Rules

ALGO_NONNEGATIVE_MATRIX_FACTOR

Non-Negative Matrix Factorization

Feature Extraction


Oracle Data Mining supports more than one algorithm for the classification, regression, and clustering mining functions. Each of these mining functions has a default algorithm, as shown in Table 44-5.

Table 44-5 Default Algorithms

Mining FunctionDefault Algorithm

Classification

Naive Bayes

Regression

Support Vector Machine

Clustering

k-Means


Automatic Data Preparation

The PREP_AUTO setting indicates whether or not the model will use Automatic Data Preparation (ADP). By default ADP is disabled.

When you enable ADP, the model uses heuristics to transform the build data according to the requirements of the algorithm. The transformation instructions are stored with the model and reused whenever the model is applied. You can view the transformation instructions in the model details.

You can choose to supplement automatic data preparations by specifying additional transformations in the xform_list parameter when you build the model. (See "CREATE_MODEL Procedure".)

If you do not use ADP (default) and do not specify transformations in the xform_list parameter to CREATE_MODEL (also the default), you will continue to operate in 10.2 mode. This means that you must implement your own transformations separately in the build, test, and scoring data; you must take special care to implement the exact same transformations in each data set.

If you do not use ADP, but you do specify transformations in the xform_list parameter to CREATE_MODEL, Oracle Data Mining embeds the transformation definitions in the model and prepares the test and scoring data to match the build data. Because of automatic and embedded data preparation, mining models are known as supermodels.

The values for the PREP_AUTO setting are described in Table 44-6.

Table 44-6 PREP_AUTO Setting

PREP_AUTO ValueDescription

PREP_AUTO_OFF

Disable Automatic Data Preparation (default).

PREP_AUTO_ON

Enable Automatic Data Preparation.



See Also:

Oracle Data Mining Concepts for information about data preparation

Mining Function Settings

The settings described in Table 44-7 apply to a mining function.

Table 44-7 Mining Function Settings

Mining FunctionSetting NameSetting ValueDescription

Association

ASSO_MAX_RULE_LENGTH

TO_CHAR( 2<= numeric_expr <=20)

Maximum rule length for association rules.

Default is 4.

Association

ASSO_MIN_CONFIDENCE

TO_CHAR( 0<= numeric_expr <=1)

Minimum confidence for association rules.

Default is 0.1.

Association

ASSO_MIN_SUPPORT

TO_CHAR( 0<= numeric_expr <=1)

Minimum support for association rules.

Default is 0.1.

Classification

CLAS_COST_TABLE_NAME

table_name

(Decision Tree only) Name of a table that stores a cost matrix to be used by the algorithm in building the model. The cost matrix specifies the costs associated with misclassifications.

Only Decision Tree models can use a cost matrix at build time. All classification algorithms can use a cost matrix at apply time.

The cost matrix table is user-created. See "ADD_COST_MATRIX Procedure" for the column requirements.

See Oracle Data Mining Concepts for information about costs.

Classification

CLAS_PRIORS_TABLE_NAME

table_name

(Naive Bayes) Name of a table that stores prior probabilities to offset differences in distribution between the build data and the scoring data.

The priors table is user-created. See Oracle Data Mining Application Developer's Guide for the column requirements. See Oracle Data Mining Concepts for additional information about priors.

Classification

CLAS_WEIGHTS_TABLE_NAME

table_name

(GLM and SVM only) Name of a table that stores weighting information for individual target values in SVM classification and GLM logistic regression models. The weights are used by the algorithm to bias the model in favor of higher weighted classes.

The class weights table is user-created. See Oracle Data Mining Application Developer's Guide for the column requirements. See Oracle Data Mining Concepts for additional information about class weights.

Clustering

CLUS_NUM_CLUSTERS

TO_CHAR( numeric_expr >=1)

Maximum number of leaf clusters generated by a clustering algorithm. (Oracle Data Mining clustering algorithms are hierarchical, as described in Oracle Data Mining Concepts .)

Enhanced k-Means usually produces the exact number of clusters specified by CLUS_NUM_CLUSTERS, unless there are fewer distinct data points.

O-Cluster may produce fewer clusters than the number specified by CLUS_NUM_CLUSTERS, depending on the data.

Default is 10.

Feature Extraction

FEAT_NUM_FEATURES

TO_CHAR(numeric_expr >=1)

Number of features to be extracted by a feature extraction model.

The default is estimated from the data by the algorithm.



See Also:

Oracle Data Mining Concepts for information about mining functions

Global Settings

The settings in Table 44-8 are applicable to any type of model, but are currently only implemented for specific algorithms.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-8 Global Settings

Setting NameSetting ValueDescription

ODMS_ITEM_ID_COLUMN_NAME

column_name

(Association Rules only) Name of a column that contains the items in a transaction. When this setting is specified, the algorithm expects the data to be presented in native transactional format, consisting of two columns:

  • Case ID, either categorical or numerical

  • Item ID, either categorical or numerical, specified by ODMS_ITEM_ID_COLUMN_NAME

A typical example of transactional data is market basket data, wherein a case represents a basket that may contain many items. Each item is stored in a separate row, and many rows may be needed to represent a case. The case ID values do not uniquely identify each row. Transactional data is also called multi-record case data.

Association Rules is normally used with transactional data, but it can also be applied to single-record case data (similar to other algorithms).

For more information about single-record and multi-record case data, see Oracle Data Mining Application Developer's Guide.

ODMS_ITEM_VALUE_COLUMN_NAME

column_name

(Association Rules only) Name of a column that contains a value associated with each item in a transaction. This setting is only used when a value has been specified for ODMS_ITEM_ID_COLUMN_NAME indicating that the data is presented in native transactional format.

When ODMS_ITEM_VALUE_COLUMN_NAME is specified, the algorithm expects the build data to consist of three columns:

  • Case ID, either categorical or numerical

  • Item ID, either categorical or numerical, specified by ODMS_ITEM_ID_COLUMN_NAME

  • Item value, either categorical or numerical, specified by ODMS_ITEM_VALUE_COLUMN_NAME

The item value column may specify information such as the number of items (for example, three apples) or the type of the item (for example, macintosh apples).

ODMS_MISSING_VALUE_TREATMENT

ODMS_MISSING_VALUE_MEAN_MODE

ODMS_MISSING_VALUE_DELETE_ROW

(GLM only) How to treat missing values in the training data. This setting does not affect the scoring data.

Oracle Data Mining replaces missing values with the mean (numeric attributes) or the mode (categorical attributes) both at build time and apply time. You can set ODMS_MISSING_VALUE_TREATMENT to ODMS_MISSING_VALUE_DELETE_ROW to override this behavior in the training data. When ODMS_MISSING_VALUE_TREATMENT is set to ODMS_MISSING_VALUE_DELETE_ROW, the rows in the training data that contain missing values are deleted. However, if you want to replicate this missing value treatment in the scoring data, you must perform the transformation explicitly. For instructions, see Oracle Data Mining Concepts.

The value ODMS_MISSING_VALUE_DELETE_ROW is only valid for tables without nested columns. If this value is used with nested data, an exception is raised.

ODMS_ROW_WEIGHT_COLUMN_NAME

column_name

(GLM only) Name of a column in the training data that contains a weighting factor for the rows.

Row weights can be used as a compact representation of repeated rows, as in the design of experiments where a specific configuration is repeated several times. Row weights can also be used to emphasize certain rows during model construction. For example, to bias the model towards rows that are more recent and away from potentially obsolete data.



See Also:

Oracle Data Mining Concepts for information about GLM

Oracle Data Mining Concepts for information about Association Rules


Algorithm Settings: Adaptive Bayes Network (deprecated)

These settings affect the behavior of the Adaptive Bayes Network algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-9 ABN Settings

SettingValueDescription

ABNS_MAX_BUILD_MINUTES

TO_CHAR( numeric_expr >=0)

Maximum time to complete an ABN model build.

Default is 0, which implies no time limit.

ABNS_MAX_NB_PREDICTORS

TO_CHAR( numeric_expr >0)

Maximum number of predictors, measured by their MDL ranking, to be considered for building an ABN model of type abns_naive_bayes.

Default is 10.

ABNS_MAX_PREDICTORS

TO_CHAR(numeric_expr >0)

Maximum number of predictors, measured by their MDL ranking, to be considered for building an ABN model of type abns_single_feature or abns_multi_feature.

Default is 25.

ABNS_MODEL_TYPE

ABNS_MULTI_FEATURE

ABNS_NAIVE_BAYES

ABNS_SINGLE_FEATURE

Type of ABN model.

The default is multi_feature.


Algorithm Settings: Decision Tree

These settings affect the behavior of the Decision Tree algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-10 Decision Tree Settings

SettingValueDescription

TREE_IMPURITY_METRIC

TREE_IMPURITY_ENTROPY

TREE_IMPURITY_GINI

Tree impurity metric for Decision Tree.

Tree algorithms seek the best test question for splitting data at each node. The best splitter and split value are those that result in the largest increase in target value homogeneity (purity) for the entities in the node. Purity is measured in accordance with a metric. Decision trees can use either gini (TREE_IMPURITY_GINI) or entropy (TREE_IMPURITY_ENTROPY) as the purity metric. By default, the algorithm uses gini.

TREE_TERM_MAX_DEPTH

TO_CHAR( 2<= numeric_expr <=20)

Criteria for splits: maximum tree depth (the maximum number of nodes between the root and any leaf node, including the leaf node).

Default is 7.

TREE_TERM_MINPCT_MODE

TO_CHAR( 0<= numeric_expr <=10)

No child shall have fewer records than this number, which is expressed as a percentage of the training rows.

Default is 0.05, indicating 0.05%.

TREE_TERM_MINPCT_SPLIT

TO_CHAR( 0 <= numeric_expr <=20)

Criteria for splits: minimum number of records in a parent node expressed as a percent of the total number of records used to train the model. No split is attempted if number of records is below this value.

Default is 0.1, indicating 0.1%.

TREE_TERM_MINREC_NODE

TO_CHAR(numeric_expr >=0)

No child shall have fewer records than this number.

Default is 10.

TREE_TERM_MINREC_SPLIT

TO_CHAR( numeric_expr >=0)

Criteria for splits: minimum number of records in a parent node expressed as a value. No split is attempted if number of records is below this value.

Default is 20.


Algorithm Settings: Generalized Linear Models

These settings affect the behavior of GLM models. GLM can be used for classification (logistic regression) or regression (linear regression).

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-11 GLM Settings

Setting NameSetting ValueDescription

GLMS_CONF_LEVEL

TO_CHAR(0< numeric_expr <1)

The confidence level for coefficient confidence intervals.

The default confidence level is 0.95.

GLMS_DIAGNOSTICS_TABLE_NAME

table_name

The name of a table to contain row-level diagnostic information for a GLM model. The table is created during model build.

If you want to create a diagnostics table, you must specify a case ID when you build the model. (See the CREATE_MODEL Procedure.) If you specify a diagnostics table but do not provide a case ID, an exception is raised.

For information on GLM diagnostics, see Oracle Data Mining Concepts.

GLMS_REFERENCE_CLASS_NAME

target_value

The target value to be used as the reference value in a logistic regression model. Probabilities will be produced for the other (non-reference) class.

By default, the algorithm chooses the value with the highest prevalence (the most cases) for the reference class.

GLMS_RIDGE_REGRESSION

GLMS_RIDGE_REG_ENABLE

GLMS_RIDGE_REG_DISABLE

Whether or not ridge regression will be enabled.

By default, the algorithm determines whether or not to use ridge. You can explicitly enable ridge by setting GLMS_RIDGE_REGRESSION to GLMS_RIDGE_REG_ENABLE.

Ridge applies to both regression and classification mining functions.

When ridge is enabled, no prediction bounds are produced by the PREDICTION_BOUNDS SQL operator.

GLMS_RIDGE_VALUE

TO_CHAR(0< numeric_expr)

The value for the ridge parameter used by the algorithm. This setting is only used when you explicitly enable ridge regression by setting GLMS_RIDGE_REGRESSION to GLMS_RIDGE_REG_ENABLE.

If ridge regression is enabled internally by the algorithm, the ridge parameter is determined by the algorithm.

GLMS_VIF_FOR_RIDGE

GLMS_VIF_RIDGE_ENABLE

GLMS_VIF_RIDGE_DISABLE

(Linear regression only) Whether or not to produce Variance Inflation Factor (VIF) statistics when ridge is being used.

By default, VIF is not produced when ridge is enabled.

When you explicitly enable ridge regression by setting GLMS_RIDGE_REGRESSION to GLMS_RIDGE_REG_ENABLE, you can request VIF statistics by setting GLMS_VIF_FOR_RIDGE to GLMS_VIF_RIDGE_ENABLE; the algorithm will produce VIF if enough system resources are available.



See Also:

Oracle Data Mining Concepts for information about GLM

Algorithm Settings: k-Means

These settings affect the behavior of the k-Means algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-12 k-Means Settings

Setting NameSetting ValueDescription

KMNS_BLOCK_GROWTH

TO_CHAR(1< numeric_expr <=5)

Growth factor for memory allocated to hold cluster data

Default value is 2

KMNS_CONV_TOLERANCE

TO_CHAR(0< numeric_expr <=0.5)

Convergence tolerance for k-Means algorithm

Default is 0.01

KMNS_DISTANCE

KMNS_COSINE

KMNS_EUCLIDEAN

KMNS_FAST_COSINE

Distance Function for k-Means Clustering. The default is euclidean.

KMNS_ITERATIONS

TO_CHAR(0< numeric_expr <=20)

Number of iterations for k-Means algorithm

Default is 3

KMNS_MIN_PCT_ATTR_SUPPORT

TO_CHAR(0<= numeric_expr <=1)

The fraction of attribute values that must be non-null in order for the attribute to be included in the rule description for the cluster.

Setting the parameter value too high in data with missing values can result in very short or even empty rules.

Default is 0.1.

KMNS_NUM_BINS

TO_CHAR(numeric_expr >0)

Number of histogram bins. Specifies the number of bins in the attribute histogram produced by k-Means. The bin boundaries for each attribute are computed globally on the entire training data set. The binning method is equi-width. All attributes have the same number of bins with the exception of attributes with a single value that have only one bin.

Default is 10.

KMNS_SPLIT_CRITERION

KMNS_SIZE

KMNS_VARIANCE

Split criterion for k-Means Clustering. The default criterion is the variance.



See Also:

Oracle Data Mining Concepts for information about k-Means.

Algorithm Settings: Naive Bayes

These settings affect the behavior of the Naive Bayes Algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-13 Naive Bayes Settings

Setting NameSetting ValueDescription

NABS_PAIRWISE_THRESHOLD

TO_CHAR(0<= numeric_expr <=1)

Value of pairwise threshold for NB algorithm

Default is 0.01.

NABS_SINGLETON_THRESHOLD

TO_CHAR(0<= numeric_expr <=1)

Value of singleton threshold for NB algorithm

Default value is 0.01



See Also:

Oracle Data Mining Concepts for information about Naive Bayes

Algorithm Settings: Non-Negative Matrix Factorization

These settings affect the behavior of the Non-Negative Matrix Factorization algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-14 NMF Settings

Setting NameSetting ValueDescription

NMFS_CONV_TOLERANCE

TO_CHAR(0< numeric_expr <=0.5)

Convergence tolerance for NMF algorithm

Default is 0.05

NMFS_NONNEGATIVE_SCORING

NMFS_NONNEG_SCORING_ENABLE

NMFS_NONNEG_SCORING_DISABLE

Whether negative numbers should be allowed in scoring results. When set to NMFS_NONNEG_SCORING_ENABLE, negative feature values will be replaced with zeros. When set to NMFS_NONNEG_SCORING_DISABLE, negative feature values will be allowed.

Default is NMFS_NONNEG_SCORING_ENABLE

NMFS_NUM_ITERATIONS

TO_CHAR(1 <= numeric_expr <=500)

Number of iterations for NMF algorithm

Default is 50

NMFS_RANDOM_SEED

TO_CHAR(numeric_expr)

Random seed for NMF algorithm.

Default is –1.



See Also:

Oracle Data Mining Concepts for information about NMF

Algorithm Settings: O-Cluster

These settings affect the behavior of the O-Cluster algorithm.

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_ALL_MINING_MODEL_SETTINGS.

Table 44-15 O-CLuster Settings

Setting NameSetting ValueDescription

OCLT_MAX_BUFFER

TO_CHAR(numeric_expr >0)

Buffer size for O-Cluster.

Default is 50,000.

OCLT_SENSITIVITY

TO_CHAR(0 <=numeric_expr <=1)

A fraction that specifies the peak density required for separating a new cluster. The fraction is related to the global uniform density.

Default is 0.5.



See Also:

Oracle Data Mining Concepts for information about O-Cluster

Algorithm Settings: Support Vector Machine

These settings affect the behavior of the Support Vector Machine algorithm. SVM can be used for classification or regression, or for anomaly detection (classification with a null target).

You can query the data dictionary view *_MINING_MODEL_SETTINGS (using the ALL, USER, or DBA prefix) to find the setting values for a model. See Oracle Data Mining Application Developer's Guide for information about *_MINING_MODEL_SETTINGS.

Table 44-16 SVM Settings

Setting NameSetting ValueDescription

SVMS_ACTIVE_LEARNING

SVMS_AL_DISABLE

SVMS_AL_ENABLE

Whether active learning is enabled or disabled. By default, active learning is enabled.

When active learning is enabled, the SVM algorithm uses active learning to build a reduced size model. When active learning is disabled, the SVM algorithm builds a standard model.

SVMS_COMPLEXITY_FACTOR

TO_CHAR(numeric_expr >0)

Value of complexity factor for SVM algorithm (both classification and regression).

Default value estimated from the data by the algorithm.

SVMS_CONV_TOLERANCE

TO_CHAR(numeric_expr >0)

Convergence tolerance for SVM algorithm.

Default is 0.001.

SVMS_EPSILON

TO_CHAR(numeric_expr >0)

Value of epsilon factor for SVM regression.

Default value estimated from the data by the algorithm.

SVMS_KERNEL_CACHE_SIZE

TO_CHAR(numeric_expr >0)

Value of kernel cache size for SVM algorithm. Applies to Gaussian kernel only.

Default is 50000000 bytes.

SVMS_KERNEL_FUNCTION

svm_gaussian

svms_linear

Kernel for Support Vector Machine. The default is determined by the algorithm based on the number of attributes in the training data. When there are many attributes, the algorithm uses a linear kernel, otherwise it uses a nonlinear (Gaussian) kernel.

The number of attributes does not correspond to the number of columns in the training data. The algorithm explodes categorical attributes to binary, numeric attributes. In addition, Oracle Data Mining handles each row in a nested column as a separate attribute. SVM takes these factors into account when choosing the kernel function.

SVMS_OUTLIER_RATE

TO_CHAR(0< numeric_expr <1)

The desired rate of outliers in the training data. Valid for One-Class SVM models only (anomaly detection).

Default is.1.

SVMS_STD_DEV

TO_CHAR(numeric_expr >0)

Value of standard deviation for SVM algorithm.

This is applicable only for Gaussian kernel.

Default value estimated from the data by the algorithm.



See Also:

Oracle Data Mining Concepts for information about SVM


Data Types

The DBMS_DATA_MINING package uses object data types to store information about model attributes. Most of these types are returned by the table functions GET_n, where n identifies the type of information to return. These functions take a model name as input and return the requested information as a collection of rows.

For a list of the GET functions, see "Summary of DBMS_DATA_MINING Subprograms".

Oracle Data Mining also uses object data types for handling transactional data. These types, DM_NESTED_NUMERICALS and DM_NESTED_CATEGORICALS specify nested tables that can be used for storing a set of mining attributes in a single column. For more information on nested tables, see the Oracle Data Mining Application Developer's Guide.

All the table functions use pipelining, which causes each row of output to be materialized as it is read from model storage, without waiting for the generation of the complete table object. For more information on pipelined, parallel table functions, consult the Oracle Database PL/SQL Language Reference.

The Data Mining object data types are described in Table 44-17.

Table 44-17 DBMS_DATA_MINING Summary of Data Types

Data TypeDescription

DM_ABN_DETAIL

Information about an attribute in an Adaptive Bayes Network model.

DM_ABN_DETAILS

A collection of DM_ABN_DETAIL. Returned by GET_MODEL_DETAILS_ABN Function.

DM_CENTROID

The centroid of a cluster.

DM_CENTROIDS

A collection of DM_CENTROID. A member of DM_CLUSTER.

DM_CHILD

A child node of a cluster.

DM_CHILDREN

A collection of DM_CHILD. A member of DM_CLUSTER.

DM_CLUSTER

A cluster. A cluster includes DM_PREDICATES, DM_CHILDREN, DM_CENTROIDS, and DM_HISTOGRAMS. It also includes a DM_RULE.

DM_CLUSTERS

A collection of DM_CLUSTER. Returned by GET_MODEL_DETAILS_KM Function and GET_MODEL_DETAILS_OC Function.

DM_CONDITIONAL

The conditional probability of an attribute in a Naive Bayes model.

DM_CONDITIONALS

A collection of DM_CONDITIONAL. Returned by GET_MODEL_DETAILS_NB Function.

DM_COST_ELEMENT

The actual and predicted values in a cost matrix.

DM_COST_MATRIX

A collection of DM_COST_ELEMENT. Returned by GET_MODEL_COST_MATRIX Function.

DM_GLM_COEFF

The coefficient and associated statistics of an attribute in a Generalized Linear Model.

DM_GLM_COEFF_SET

A collection of DM_GLM_COEFF. Returned by GET_MODEL_DETAILS_GLM Function.

DM_HISTOGRAM_BIN

A histogram associated with a cluster.

DM_HISTOGRAMS

A collection of DM_HISTOGRAM_BIN. A member of DM_CLUSTER.

DM_ITEM

An item in an association rule.

DM_ITEMS

A collection of DM_ITEM.

DM_ITEMSET

A collection of DM_ITEMS.

DM_ITEMSETS

A collection of DM_ITEMSET. Returned by GET_FREQUENT_ITEMSETS Function.

DM_MODEL_GLOBAL_DETAIL

High-level statistics about a model.

DM_MODEL_GLOBAL_DETAILS

A collection of DM_MODEL_GLOBAL_DETAIL. Returned by GET_MODEL_DETAILS_GLOBAL Function.

DM_MODEL_SETTING

A model setting.

DM_MODEL_SETTINGS

A collection of DM_MODEL_SETTING. Returned by GET_MODEL_SETTINGS Function and GET_DEFAULT_SETTINGS Function.

DM_MODEL_SIGNATURE_ATTRIBUTE

An attribute in the model signature.

DM_MODEL_SIGNATURE

A collection of DM_MODEL_SIGNATURE. Returned by GET_MODEL_SIGNATURE Function.

DM_NB_DETAIL

Information about an attribute in a Naive Bayes model.

DM_NB_DETAILS

A collection of DM_DB_DETAIL. Returned by GET_MODEL_DETAILS_NB Function.

DM_NESTED_CATEGORICAL

The name and value of a categorical attribute.

DM_NESTED_CATEGORICALS

A collection of DM_NESTED_CATEGORICAL. A collection of attributes defined as a single model attribute. Transactional data must be defined as nested attributes for Data Mining.

DM_NESTED_NUMERICAL

The name and value of a numerical attribute.

DM_NESTED_NUMERICALS

A collection of DM_NESTED_NUMERICAL. A collection of attributes defined as a single model attribute. Transactional data must be defined as nested attributes for Data Mining.

DM_NMF_ATTRIBUTE

An attribute in a feature of a Non-Negative Matrix Factorization model.

DM_NMF_ATTRIBUTE_SET

A collection of DM_NMF_ATTRIBUTE. A member of DM_NMF_FEATURE.

DM_NMF_FEATURE

A feature in a Non-Negative Matrix Factorization model.

DM_NMF_FEATURE_SET

A collection of DM_NMF_FEATURE. Returned by GET_MODEL_DETAILS_NMF Function.

DM_PREDICATE

Antecedent and consequent attributes.

DM_PREDICATES

A collection of DM_PREDICATE. A member of DM_RULE, DM_CLUSTER, and DM_ABN_DETAIL.

DM_RANKED_ATTRIBUTE

An attribute ranked by its importance in an Attribute Importance model.

DM_RANKED_ATTRIBUTES

A collection of DM_RANKED_ATTRIBUTE. Returned by GET_MODEL_DETAILS_AI Function.

DM_RULE

A rule that defines a conditional relationship.

The rule can be one of the association rules returned by GET_ASSOCIATION_RULES Function, or it can be a rule associated with a cluster in the collection of clusters returned by GET_MODEL_DETAILS_KM Function and GET_MODEL_DETAILS_OC Function.

DM_RULES

A collection of DM_RULE. Returned by GET_ASSOCIATION_RULES Function.

DM_SVM_ATTRIBUTE

The name, value, and coefficient of an attribute in a Support Vector Machine model.

DM_SVM_ATTRIBUTE_SET

A collection of DM_SVM_ATTRIBUTE. Returned by GET_MODEL_DETAILS_SVM Function. Also a member of DM_SVM_LINEAR_COEFF.

DM_SVM_LINEAR_COEFF

The linear coefficient of each attribute in a Support Vector Machine model.

DM_SVM_LINEAR_COEFF_SET

A collection of DM_SVM_LINEAR_COEFF. Returned by GET_MODEL_DETAILS_SVM Function for an SVM model built using the linear kernel.

DM_TRANSFORM

The transformation and reverse transformation expressions for an attribute.

DM_TRANSFORMS

A collection of DM_TRANSFORM. Returned by GET_MODEL_TRANSFORMATIONS Function.

TRANSFORM_LIST

A list of user-specified transformations for a model. Accepted as a parameter by the CREATE_MODEL Procedure.

This collection type is defined in the DBMS_DATA_MINING_TRANSFORM package.



Summary of DBMS_DATA_MINING Subprograms

Table 44-18 summarizes the subprograms included in the DBMS_DATA_MINING package.

Table 44-18 DBMS_DATA_MINING Package Subprograms

Data TypePurpose

ADD_COST_MATRIX Procedure


Adds a cost matrix to a classification model

ALTER_REVERSE_EXPRESSION Procedure


Changes the reverse transformation expression to an expression that you specify

APPLY Procedure

Applies a model to a data set (scores the data)

COMPUTE_CONFUSION_MATRIX Procedure

Computes the confusion matrix from the APPLY results on test data for a classification model; also provides the accuracy of the model

COMPUTE_LIFT Procedure


Computes lift for a given positive target value from the APPLY results on test data for a classification model

COMPUTE_ROC Procedure

Computes Receiver Operating Characteristic (ROC) for a classification model

CREATE_MODEL Procedure

Creates (builds) a model

DROP_MODEL Procedure


Drops a model

EXPORT_MODEL Procedure


Exports a model to a dump file

GET_ASSOCIATION_RULES Function

Returns the rules from an association model

GET_DEFAULT_SETTINGS Function

Returns all the default settings for all mining functions and algorithms

GET_FREQUENT_ITEMSETS Function

Returns the frequent itemsets for an association model

GET_MODEL_COST_MATRIX Function


Returns the cost matrix for a model

GET_MODEL_DETAILS_ABN Function

Returns the details of an Adaptive Bayes Network model

GET_MODEL_DETAILS_AI Function


Returns the details of an Attribute Importance model

GET_MODEL_DETAILS_GLM Function


Returns the details of a Generalized Linear Model

GET_MODEL_DETAILS_GLOBAL Function


Returns high-level statistics about a model

GET_MODEL_DETAILS_KM Function


Returns the details of a k-Means model

GET_MODEL_DETAILS_NB Function

Returns the details of a Naive Bayes model

GET_MODEL_DETAILS_NMF Function


Returns the details of an NMF model

GET_MODEL_DETAILS_OC Function

Returns the details of an O-Cluster model

GET_MODEL_DETAILS_SVM Function

Returns the details of an SVM model with a linear kernel

GET_MODEL_DETAILS_XML Function

Returns the details of a Decision Tree model

GET_MODEL_SETTINGS Function

Returns the settings used to build a model

GET_MODEL_SIGNATURE Function

Returns the signature of a model

GET_MODEL_TRANSFORMATIONS Function


Returns the user-specified transformation definitions embedded in the model, as well as many of the ADP transformations

GET_TRANSFORM_LIST Procedure


Converts between two different transformation specification formats

IMPORT_MODEL Procedure

Imports a model into a user schema

RANK_APPLY Procedure


Ranks the predictions from the APPLY results for a classification model

REMOVE_COST_MATRIX Procedure


Removes a cost matrix from a model

RENAME_MODEL Procedure

Renames a model



ADD_COST_MATRIX Procedure

This procedure associates a cost matrix table with a classification model. The cost matrix biases the model by assigning costs or benefits to specific model outcomes.

The cost matrix is stored with the model and taken into account when the model is scored. The stored cost matrix is the default scoring matrix for the model.

You can also specify a cost matrix inline when you invoke a Data Mining SQL function for scoring. When an inline cost matrix is specified, it is used instead of the default, stored cost matrix (if one exists).

To obtain the default scoring matrix for a model, use the GET_MODEL_COST_MATRIX function. To remove the default scoring matrix from a model, use the REMOVE_COST_MATRIX procedure. See "GET_MODEL_COST_MATRIX Function" and "REMOVE_COST_MATRIX Procedure".


See Also:


Syntax

DBMS_DATA_MINING.ADD_COST_MATRIX (
       model_name                IN VARCHAR2,
       cost_matrix_table_name    IN VARCHAR2,
       cost_matrix_schema_name   IN VARCHAR2 DEFAULT NULL);

Parameters

Table 44-19 ADD_COST_MATRIX Procedure Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is assumed.

cost_matrix_table_name

Name of the cost matrix table (described in Table 44-20).

cost_matrix_schema_name

Schema of the cost matrix table. If no schema is specified, the current schema is used.


Usage Notes

  1. If the model is not in your schema, then ADD_COST_MATRIX requires the ALTER ANY MINING MODEL system privilege or the ALTER object privilege for the mining model.

  2. The cost matrix table must have the columns shown in Table 44-20. Note that the actual and predicted target values must have the same data type.

    Table 44-20 Required Columns in a Cost Matrix Table

    Column NameData Type

    ACTUAL_TARGET_VALUE

    VARCHAR2(4000) for categorical targets

    NUMBER for numeric targets

    PREDICTED_TARGET_VALUE

    VARCHAR2(4000)for categorical targets

    NUMBER for numeric targets

    COST

    NUMBER


  3. Since a benefit can be viewed as a negative cost, you can specify a benefit for a given outcome by providing a negative number in the costs column of the cost matrix table.

  4. All classification algorithms can use a cost matrix for scoring. The Decision Tree algorithm can also use a cost matrix at build time.If you want to build a Decision Tree model with a cost matrix, specify the cost matrix table name in the CLAS_COST_TABLE_NAME setting in the settings table for the model. See Table 44-7, "Mining Function Settings".

    The cost matrix used to create a Decision Tree model becomes the default scoring matrix for the model. If you want to specify different costs for scoring, use the REMOVE_COST_MATRIX procedure to remove the cost matrix and the ADD_COST_MATRIX procedure to add a new one.

Example

This example creates a cost matrix table called COSTS_NB and adds it to a Naive Bayes model called NB_SH_CLAS_SAMPLE. The model has a binary target: 1 means that the customer responds to a promotion; 0 means that the customer does not respond. The cost matrix assigns a cost of .25 to misclassifications of customers who do not respond and a cost of .75 to misclassifications of customers who do respond. This means that it is three times more costly to misclassify responders than it is to misclassify non-responders.

CREATE TABLE costs_nb (
  actual_target_value           NUMBER,
  predicted_target_value        NUMBER,
  cost                          NUMBER);
INSERT INTO costs_nb values (0, 0, 0);
INSERT INTO costs_nb values (0, 1, .25);
INSERT INTO costs_nb values (1, 0, .75);
INSERT INTO costs_nb values (1, 1, 0);
COMMIT;
 
EXEC dbms_data_mining.add_cost_matrix('nb_sh_clas_sample', 'costs_nb');
 
SELECT cust_gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age
   FROM mining_data_apply_v
   WHERE PREDICTION(nb_sh_clas_sample COST MODEL
      USING cust_marital_status, education, household_size) = 1
   GROUP BY cust_gender
   ORDER BY cust_gender;
   
C        CNT    AVG_AGE
- ---------- ----------
F         72         39
M        555         44

ALTER_REVERSE_EXPRESSION Procedure

This procedure replaces a reverse transformation expression with an expression that you specify. If the attribute does not have a reverse expression, the procedure creates one from the specified expression.

You can also use this procedure to customize the output of clustering, feature extraction, and anomaly detection models.

Syntax

DBMS_DATA_MINING. ALTER_REVERSE_EXPRESSION (
         model_name             VARCHAR2,
         expression             CLOB,
         attribute_name         VARCHAR2 DEFAULT NULL,
         attribute_subname      VARCHAR2 DEFAULT NULL);

Parameters

Table 44-21 ALTER_REVERSE_EXPRESSION Procedure Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

expression

A SQL expression

attribute_name

Name of the attribute. Specify NULL if you wish to apply expression to a cluster, feature, or One-Class SVM prediction.

attribute_subname

Name of the nested attribute if attribute_name is a nested column, otherwise NULL.


Usage Notes

  1. For purposes of model transparency, Oracle Data Mining provides reverse transformations for transformations that are embedded in a model. Reverse transformations are used in model details and in the results of scoring.


    Note:

    Use caution when altering the reverse expression for the target of a model that has a cost matrix. If you specify a reverse expression that is inconsistent with the target values in the cost matrix table, you will not be able to score the model.

    See "ADD_COST_MATRIX Procedure" and Oracle Data Mining Concepts for information about cost matrixes.


  2. To prevent reverse transformation of an attribute, you can specify NULL for expression.

  3. You can use ALTER_REVERSE_EXPRESSION to label clusters produced by clustering models and features produced by feature extraction.

    You can use ALTER_REVERSE_EXPRESSION to replace the zeros and ones returned by anomaly-detection models. By default, anomaly-detection models label anomalous records with 0 and all other records with 1.


    See Also:

    Oracle Data Mining Concepts for information about anomaly detection

Examples

  1. In this example, the target (affinity_card) of the model CLASS_MODEL is manipulated internally as yes or no instead of 1 or 0 but returned as 1s and 0s when scored. The ALTER_REVERSE_EXPRESSION procedure causes the target values to be returned as TRUE or FALSE.

    The data sets MINING_DATA_BUILD and MINING_DATA_TEST are included with the Oracle Data Mining sample programs. See Oracle Data Mining Administrator's Guide for information about the sample programs.

    DECLARE
            v_xlst dbms_data_mining_transform.TRANSFORM_LIST;
      BEGIN
        dbms_data_mining_transform.SET_TRANSFORM(v_xlst,
              'affinity_card', NULL,
              'decode(affinity_card, 1, ''yes'', ''no'')',
              'decode(affinity_card, ''yes'', 1, 0)');
        dbms_data_mining.CREATE_MODEL(
          model_name             => 'CLASS_MODEL',
          mining_function        => dbms_data_mining.classification,
          data_table_name        => 'mining_data_build',
          case_id_column_name    => 'cust_id',
          target_column_name     => 'affinity_card',
          settings_table_name    => NULL,
          data_schema_name       => 'dmuser',
          settings_schema_name   => NULL,
          xform_list             => v_xlst );
      END;
    /
    SELECT cust_income_level, occupation,
               PREDICTION(CLASS_MODEL USING *) predict_response
          FROM mining_data_test WHERE age = 60 AND cust_gender IN 'M'
          ORDER BY cust_income_level;
     
    CUST_INCOME_LEVEL              OCCUPATION                PREDICT_RESPONSE
    ------------------------------ --------------------- --------------------
    A: Below 30,000                Transp.                                  1
    E: 90,000 - 109,999            Transp.                                  1
    E: 90,000 - 109,999            Sales                                    1
    G: 130,000 - 149,999           Handler                                  0
    G: 130,000 - 149,999           Crafts                                   0
    H: 150,000 - 169,999           Prof.                                    1
    J: 190,000 - 249,999           Prof.                                    1
    J: 190,000 - 249,999           Sales                                    1
     
    BEGIN
      dbms_data_mining.ALTER_REVERSE_EXPRESSION (
         model_name      => 'CLASS_MODEL',
         expression      => 'decode(affinity_card, ''yes'', ''TRUE'', ''FALSE'')',
         attribute_name  => 'affinity_card');
    END;
    /
    column predict_response on
    column predict_response format a20
    SELECT cust_income_level, occupation,
                 PREDICTION(CLASS_MODEL USING *) predict_response
          FROM mining_data_test WHERE age = 60 AND cust_gender IN 'M'
          ORDER BY cust_income_level;
     
    CUST_INCOME_LEVEL              OCCUPATION            PREDICT_RESPONSE
    ------------------------------ --------------------- --------------------
    A: Below 30,000                Transp.               TRUE
    E: 90,000 - 109,999            Transp.               TRUE
    E: 90,000 - 109,999            Sales                 TRUE
    G: 130,000 - 149,999           Handler               FALSE
    G: 130,000 - 149,999           Crafts                FALSE
    H: 150,000 - 169,999           Prof.                 TRUE
    J: 190,000 - 249,999           Prof.                 TRUE
    J: 190,000 - 249,999           Sales                 TRUE
    
  2. This example specifies labels for the clusters that result from the sh_clus model. The labels consist of the word "Cluster" and the internal numeric identifier for the cluster.

    BEGIN
      dbms_data_mining.ALTER_REVERSE_EXPRESSION( 'sh_clus', '''Cluster ''||value');
    END;
    /
     
    SELECT cust_id, cluster_id(sh_clus using *) cluster_id
       FROM sh_aprep_num
           WHERE cust_id < 100011
           ORDER by cust_id;
     
    CUST_ID CLUSTER_ID
    ------- ------------------------------------------------
     100001 Cluster 18
     100002 Cluster 14
     100003 Cluster 14
     100004 Cluster 18
     100005 Cluster 19
     100006 Cluster 7
     100007 Cluster 18
     100008 Cluster 14
     100009 Cluster 8
     100010 Cluster 8
    

APPLY Procedure

This procedure applies a mining model to the data of interest, and generates the results in a table. The apply process is also referred to as scoring.

For predictive mining functions, the apply process generates predictions in a target column. For descriptive mining functions such as clustering, the apply process assigns each case to a cluster with a probability.

In Oracle Data Mining, the apply operation is not applicable to association models and attribute importance models.


Note:

Scoring can also be performed directly in SQL using the Data Mining functions. See

Syntax

DBMS_DATA_MINING.APPLY (
      model_name           IN VARCHAR2,
      data_table_name      IN VARCHAR2,
      case_id_column_name  IN VARCHAR2,
      result_table_name    IN VARCHAR2,
      data_schema_name     IN VARCHAR2 DEFAULT NULL);

Parameters

Table 44-22 APPLY Procedure Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

data_table_name

Name of table or view representing data to be scored

case_id_column_name

Name of the case identifier column

result_table_name

Name of the table to store apply results

data_schema_name

Name of the schema containing the data to be scored


Usage Notes

  1. The data provided for APPLY must undergo the same preprocessing as the data used to create and test the model. When you use Automatic Data Preparation, the preprocessing required by the algorithm is handled for you by the model — both at build time and apply time. (See "Automatic Data Preparation".)

  2. APPLY creates a table in the user's schema to hold the results. The columns are algorithm-specific.

    The columns in the results table are listed in Table 44-23 through Table 44-27. The case ID column name in the results table will match the case ID column name provided by you. The type of the incoming case ID column is also preserved in APPLY output.


    Note:

    Make sure that the case ID column does not have the same name as one of the columns that will be created by APPLY. For example, when applying a classification model, the case ID in the scoring data must not be 'PREDICTION' or 'PROBABILITY' (See Table 44-23).

  3. The data type for the 'PREDICTION', 'CLUSTER_ID', and 'FEATURE_ID' output columns is influenced by any reverse expression that is embedded in the model by the user. If the user does not provide a reverse expression that alters the scored value type, then the types will conform to the descriptions in the following tables. See "ALTER_REVERSE_EXPRESSION Procedure".

Classification

The results table for classification has the columns described in Table 44-23. If the target of the model is categorical, the PREDICTION column will have a VARCHAR2 data type. If the target is numerical, the PREDICTION column will have a NUMBER data type.

Table 44-23 APPLY Results Table for Classification

Column NameData Type

Case ID column name

VARCHAR2 or NUMBER

PREDICTION

VARCHAR2 or NUMBER

PROBABILITY

NUMBER


One-Class SVM (Anomaly Detection)

The results table for anomaly detection has the columns described in Table 44-24.

Table 44-24 APPLY Results Table for Anomaly Detection

Column NameData Type

Case ID column name

VARCHAR2 or NUMBER

PREDICTION

NUMBER

PROBABILITY

NUMBER


Values in the PREDICTION column can be either 0 or 1. When the prediction is 1, the case is a typical example. When the prediction is 0, the case is an outlier.

Regression using SVM or GLM

The results table for regression has the columns described in Table 44-25.

Table 44-25 APPLY Results Table for Regression

Column NameData Type

Case ID column name

VARCHAR2 or NUMBER

PREDICTION

NUMBER


Clustering using k-Means or O-Cluster

Clustering is an unsupervised mining function, and hence there are no targets. The results of an APPLY operation will contain simply the cluster identifier corresponding to a case, and the associated probability. The results table has the columns described in Table 44-26.

Table 44-26 APPLY Results Table for Clustering

Column NameData Type

Case ID column name

VARCHAR2 or NUMBER

CLUSTER_ID

NUMBER

PROBABILITY

NUMBER


Feature Extraction using NMF

Feature extraction is also an unsupervised mining function, and hence there are no targets. The results of an APPLY operation will contain simply the feature identifier corresponding to a case, and the associated match quality. The results table has the columns described in Table 44-27.

Table 44-27 APPLY Results Table for Feature Extraction

Column NameData Type

Case ID column name

VARCHAR2 or NUMBER

FEATURE_ID

NUMBER

MATCH_QUALITY

NUMBER


Examples

This example applies the GLM regression model GLMR_SH_REGR_SAMPLE to the data in the MINING_DATA_APPLY_V view. The apply results are output to the table REGRESSION_APPLY_RESULT.

SQL> BEGIN
       DBMS_DATA_MINING.APPLY (
       model_name     => 'glmr_sh_regr_sample',
       data_table_name     => 'mining_data_apply_v',
       case_id_column_name => 'cust_id',
       result_table_name   => 'regression_apply_result');
    END;
    /
 
SQL> SELECT * FROM regression_apply_result WHERE cust_id >  101485;
 
   CUST_ID PREDICTION
---------- ----------
    101486 22.8048824
    101487 25.0261101
    101488 48.6146619
    101489   51.82595
    101490 22.6220714
    101491 61.3856816
    101492 24.1400748
    101493  58.034631
    101494 45.7253149
    101495 26.9763318
    101496 48.1433425
    101497 32.0573434
    101498 49.8965531
    101499  56.270656
    101500 21.1153047

COMPUTE_CONFUSION_MATRIX Procedure

This procedure computes a confusion matrix, stores it in a table in the user's schema, and returns the model accuracy.

A confusion matrix is a test metric for classification models. It compares the predictions generated by the model with the actual target values in a set of test data. The matrix is n-by-n, where n is the number of classes. The confusion matrix lists the number of times each class was correctly predicted and the number of times it was predicted to be one of the other classes.

COMPUTE_CONFUSION_MATRIX accepts three input streams:

  • The predictions generated on the test data. T€˙he information is passed in three columns:

    • Case ID column

    • Prediction column

    • Scoring criterion column containing either probabilities or costs

  • The known target values in the test data. The information is passed in two columns:

    • Case ID column

    • Target column containing the known target values

  • (Optional) A cost matrix table with predefined columns. See the Usage Notes for the column requirements.


See Also:

Oracle Data Mining Concepts for more details about confusion matrixes and other test metrics for classification

"COMPUTE_LIFT Procedure"

"COMPUTE_ROC Procedure"


Syntax

DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
      accuracy                     OUT NUMBER,
      apply_result_table_name      IN  VARCHAR2,
      target_table_name            IN  VARCHAR2,
      case_id_column_name          IN  VARCHAR2,
      target_column_name           IN  VARCHAR2,
      confusion_matrix_table_name  IN  VARCHAR2,
      score_column_name            IN  VARCHAR2 DEFAULT 'PREDICTION',
      score_criterion_column_name  IN  VARCHAR2 DEFAULT 'PROBABILITY',
      cost_matrix_table_name       IN  VARCHAR2 DEFAULT NULL,
      apply_result_schema_name     IN  VARCHAR2 DEFAULT NULL,
      target_schema_name           IN  VARCHAR2 DEFAULT NULL,
      cost_matrix_schema_name      IN  VARCHAR2 DEFAULT NULL,
      score_criterion_type         IN  VARCHAR2 DEFAULT 'PROBABILITY');

Parameters

Table 44-28 COMPUTE_CONFUSION_MATRIX Procedure Parameters

ParameterDescription

accuracy

Output parameter containing the overall percentage accuracy of the predictions.

apply_result_table_name

Table containing the predictions.

target_table_name

Table containing the known target values from the test data.

case_id_column_name

Case ID column in the apply results table. Must match the case identifier in the targets table.

target_column_name

Target column in the targets table. Contains the known target values from the test data.

confusion_matrix_table_name

Table containing the confusion matrix. The table will be created by the procedure in the user's schema.

The columns in the confusion matrix table are described in the Usage Notes.

score_column_name

Column containing the predictions in the apply results table.

The default column name is PREDICTION, which is the default name created by the APPLY procedure (See "APPLY Procedure").

score_criterion_column_name

Column containing the scoring criterion in the apply results table. Contains either the probabilities or the costs that determine the predictions.

By default, scoring is based on probability; the class with the highest probability is predicted for each case. If scoring is based on cost, the class with the lowest cost is predicted.

The score_criterion_type parameter indicates whether probabilities or costs will be used for scoring.

The default column name is 'PROBABILITY', which is the default name created by the APPLY procedure (See "APPLY Procedure").

See the Usage Notes for additional information.

cost_matrix_table_name

(Optional) Table that defines the costs associated with misclassifications. If a cost matrix table is provided and the score_criterion_type parameter is set to 'COSTS', the costs in this table will be used as the scoring criteria.

The columns in a cost matrix table are described in the Usage Notes.

apply_result_schema_name

Schema of the apply results table.

If null, the user's schema is assumed.

target_schema_name

Schema of the table containing the known targets.

If null, the user's schema is assumed.

cost_matrix_schema_name

Schema of the cost matrix table, if one is provided.

If null, the user's schema is assumed.

score_criterion_type

Whether to use probabilities or costs as the scoring criterion. Probabilities or costs are passed in the column identified in the score_criterion_column_name parameter.

The default value of score_criterion_type is 'PROBABILITY'. To use costs as the scoring criterion, specify 'COST'.

If score_criterion_type is set to 'COST' but no cost matrix is provided and if there is a scoring cost matrix associated with the model, then the associated costs are used for scoring.

See the Usage Notes and the Examples.


Usage Notes

  • The predictive information you pass to COMPUTE_CONFUSION_MATRIX may be generated using SQL PREDICTION functions, the DBMS_DATA_MINING.APPLY procedure, or some other mechanism. As long as you pass the appropriate data, the procedure can compute the confusion matrix.

  • Instead of passing a cost matrix to COMPUTE_CONFUSION_MATRIX, you can use a scoring cost matrix associated with the model. A scoring cost matrix can be embedded in the model or it can be defined dynamically when the model is applied. To use a scoring cost matrix, invoke the SQL PREDICTION_COST function to populate the score criterion column.

  • The predictions that you pass to COMPUTE_CONFUSION_MATRIX are in a table or view specified in apply_result_table_name.

    CREATE TABLE apply_result_table_name AS (
                case_id_column_name            VARCHAR2, 
                score_column_name              VARCHAR2,
                score_criterion_column_name    VARCHAR2);
    
  • A cost matrix must have the columns described in Table 44-29.

    Table 44-29 Columns in a Cost Matrix

    Column NameData Type

    actual_target_value

    NUMBER or VARCHAR2

    predicted_target_value

    NUMBER or VARCHAR2

    cost

    NUMBER



    See Also:

    Oracle Data Mining Concepts for more information about cost matrixes

  • The confusion matrix created by COMPUTE_CONFUSION_MATRIX has the columns described in Table 44-30.

    Table 44-30 Columns in a Confusion Matrix

    Column NameData Type

    actual_target_value

    NUMBER or VARCHAR2

    predicted_target_value

    NUMBER or VARCHAR2

    value

    NUMBER



    See Also:

    Oracle Data Mining Concepts for more information about confusion matrixes

Examples

These examples use the Naive Bayes model nb_sh_clas_sample, which is created by one of the Oracle Data Mining sample programs.

Compute a Confusion Matrix Based on Probabilities

The following statement applies the model to the test data and stores the predictions and probabilities in a table.

CREATE TABLE nb_apply_results AS
       SELECT cust_id,
              PREDICTION(nb_sh_clas_sample USING *) prediction,
              PREDICTION_PROBABILITY(nb_sh_clas_sample USING *) probability
       FROM mining_data_test_v;

Using probabilities as the scoring criterion, you can compute the confusion matrix as follows.

DECLARE
   v_accuracy    NUMBER;
      BEGIN
        DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
                   accuracy                     => v_accuracy,
                   apply_result_table_name      => 'nb_apply_results',
                   target_table_name            => 'mining_data_test_v',
                   case_id_column_name          => 'cust_id',
                   target_column_name           => 'affinity_card',
                   confusion_matrix_table_name  => 'nb_confusion_matrix',
                   score_column_name            => 'PREDICTION',
                   score_criterion_column_name  => 'PROBABILITY'
                   cost_matrix_table_name       =>  null,
                   apply_result_schema_name     =>  null,
                   target_schema_name           =>  null,
                   cost_matrix_schema_name      =>  null,
                   score_criterion_type         => 'PROBABILITY');
        DBMS_OUTPUT.PUT_LINE('**** MODEL ACCURACY ****: ' || ROUND(v_accuracy,4));
      END;
      /

The confusion matrix and model accuracy are shown as follows.

 **** MODEL ACCURACY ****: .7847

SQL>SELECT * from nb_confusion_matrix;
ACTUAL_TARGET_VALUE PREDICTED_TARGET_VALUE      VALUE
------------------- ---------------------- ----------
                  1                      0         60
                  0                      0        891
                  1                      1        286
                  0                      1        263

Compute a Confusion Matrix Based on a Cost Matrix Table

The confusion matrix in the previous example shows a high rate of false positives. For 263 cases, the model predicted 1 when the actual value was 0. You could use a cost matrix to minimize this type of error.

The cost matrix table nb_cost_matrix specifies that a false positive is 3 times more costly than a false negative.

SQL> SELECT * from nb_cost_matrix;
ACTUAL_TARGET_VALUE PREDICTED_TARGET_VALUE       COST
------------------- ---------------------- ----------
                  0                      0          0
                  0                      1        .75
                  1                      0        .25
                  1                      1          0

This statement shows how to generate the predictions using APPLY.

BEGIN
    DBMS_DATA_MINING.APPLY(
          model_name          => 'nb_sh_clas_sample',
          data_table_name     => 'mining_data_test_v',
          case_id_column_name => 'cust_id',
          result_table_name   => 'nb_apply_results');
 END;
/

This statement computes the confusion matrix using the cost matrix table. The score criterion column is named 'PROBABILITY', which is the name generated by APPLY.

DECLARE
  v_accuracy    NUMBER;
     BEGIN
       DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
                accuracy                     => v_accuracy,
                apply_result_table_name      => 'nb_apply_results',
                target_table_name            => 'mining_data_test_v',
                case_id_column_name          => 'cust_id',
                target_column_name           => 'affinity_card',
                confusion_matrix_table_name  => 'nb_confusion_matrix',
                score_column_name            => 'PREDICTION',
                score_criterion_column_name  => 'PROBABILITY',
                cost_matrix_table_name       => 'nb_cost_matrix',
                apply_result_schema_name     => null,
                target_schema_name           => null,
                cost_matrix_schema_name      => null,
                score_criterion_type         => 'COST');
       DBMS_OUTPUT.PUT_LINE('**** MODEL ACCURACY ****: ' || ROUND(v_accuracy,4));
    END;
    /

The resulting confusion matrix shows a decrease in false positives (212 instead of 263).

**** MODEL ACCURACY ****: .798

SQL> SELECT * FROM nb_confusion_matrix;
ACTUAL_TARGET_VALUE PREDICTED_TARGET_VALUE      VALUE
------------------- ---------------------- ----------
                  1                      0         91
                  0                      0        942
                  1                      1        255
                  0                      1        212

Compute a Confusion Matrix Based on Embedded Costs

You can use the ADD_COST_MATRIX procedure to embed a cost matrix in a model. The embedded costs can be used instead of probabilities for scoring. This statement adds the previously-defined cost matrix to the model.

BEGIN    DBMS_DATA_MINING.ADD_COST_MATRIX ('nb_sh_clas_sample', 'nb_cost_matrix');END;/

The following statement applies the model to the test data using the embedded costs and stores the results in a table.

CREATE TABLE nb_apply_results AS
         SELECT cust_id,
              PREDICTION(nb_sh_clas_sample COST MODEL USING *) prediction,
              PREDICTION_COST(nb_sh_clas_sample COST MODEL USING *) cost
          FROM mining_data_test_v;

You can compute the confusion matrix using the embedded costs.

DECLARE
   v_accuracy         NUMBER;
   BEGIN
       DBMS_DATA_MINING.COMPUTE_CONFUSION_MATRIX (
            accuracy                     => v_accuracy,
            apply_result_table_name      => 'nb_apply_results',
            target_table_name            => 'mining_data_test_v',
            case_id_column_name          => 'cust_id',
            target_column_name           => 'affinity_card',
            confusion_matrix_table_name  => 'nb_confusion_matrix',
            score_column_name            => 'PREDICTION',
            score_criterion_column_name  => 'COST',
            cost_matrix_table_name       => null,
            apply_result_schema_name     => null,
            target_schema_name           => null,
            cost_matrix_schema_name      => null,
            score_criterion_type         => 'COST');
   END;
   /

The results are:

**** MODEL ACCURACY ****: .798

SQL> SELECT * FROM nb_confusion_matrix;
ACTUAL_TARGET_VALUE PREDICTED_TARGET_VALUE      VALUE
------------------- ---------------------- ----------
                  1                      0         91
                  0                      0        942
                  1                      1        255
                  0                      1        212

COMPUTE_LIFT Procedure

This procedure computes lift and stores the results in a table in the user's schema.

Lift is a test metric for binary classification models. To compute lift, one of the target values must be designated as the positive class. COMPUTE_LIFT compares the predictions generated by the model with the actual target values in a set of test data. Lift measures the degree to which the model's predictions of the positive class are an improvement over random chance.

Lift is computed on scoring results that have been ranked by probability (or cost) and divided into quantiles. Each quantile includes the scores for the same number of cases.

COMPUTE_LIFT calculates quantile-based and cumulative statistics. The number of quantiles and the positive class are user-specified. Additionally, COMPUTE_LIFT accepts three input streams:

  • The predictions generated on the test data. The information is passed in three columns:

    • Case ID column

    • Prediction column

    • Scoring criterion column containing either probabilities or costs associated with the predictions

  • The known target values in the test data. The information is passed in two columns:

    • Case ID column

    • Target column containing the known target values

  • (Optional) A cost matrix table with predefined columns. See the Usage Notes for the column requirements.


See Also:

Oracle Data Mining Concepts for more details about lift and test metrics for classification

"COMPUTE_CONFUSION_MATRIX Procedure"

"COMPUTE_ROC Procedure"


Syntax

DBMS_DATA_MINING.COMPUTE_LIFT (
      apply_result_table_name      IN VARCHAR2,
      target_table_name            IN VARCHAR2,
      case_id_column_name          IN VARCHAR2,
      target_column_name           IN VARCHAR2,
      lift_table_name              IN VARCHAR2,
      positive_target_value        IN VARCHAR2,
      score_column_name            IN VARCHAR2 DEFAULT 'PREDICTION',
      score_criterion_column_name  IN VARCHAR2 DEFAULT 'PROBABILITY',
      num_quantiles                IN NUMBER DEFAULT 10,
      cost_matrix_table_name       IN VARCHAR2 DEFAULT NULL,
      apply_result_schema_name     IN VARCHAR2 DEFAULT NULL,
      target_schema_name           IN VARCHAR2 DEFAULT NULL,
      cost_matrix_schema_name      IN VARCHAR2 DEFAULT NULL
      score_criterion_type         IN VARCHAR2 DEFAULT 'PROBABILITY');

Parameters

Table 44-31 COMPUTE_LIFT Procedure Parameters

ParameterDescription

apply_result_table_name

Table containing the predictions.

target_table_name

Table containing the known target values from the test data.

case_id_column_name

Case ID column in the apply results table. Must match the case identifier in the targets table.

target_column_name

Target column in the targets table. Contains the known target values from the test data.

lift_table_name

Table containing the lift statistics. The table will be created by the procedure in the user's schema.

The columns in the lift table are described in the Usage Notes.

positive_target_value

The positive class. This should be the class of interest, for which you want to calculate lift.

If the target column is a NUMBER, you can use the TO_CHAR() operator to provide the value as a string.

score_column_name

Column containing the predictions in the apply results table.

The default column name is 'PREDICTION', which is the default name created by the APPLY procedure (See "APPLY Procedure").

score_criterion_column_name

Column containing the scoring criterion in the apply results table. Contains either the probabilities or the costs that determine the predictions.

By default, scoring is based on probability; the class with the highest probability is predicted for each case. If scoring is based on cost, the class with the lowest cost is predicted.

The score_criterion_type parameter indicates whether probabilities or costs will be used for scoring.

The default column name is 'PROBABILITY', which is the default name created by the APPLY procedure (See "APPLY Procedure").

See the Usage Notes for additional information.

num_quantiles

Number of quantiles to be used in calculating lift. The default is 10.

cost_matrix_table_name

(Optional) Table that defines the costs associated with misclassifications. If a cost matrix table is provided and the score_criterion_type parameter is set to 'COST', the costs will be used as the scoring criteria.

The columns in a cost matrix table are described in the Usage Notes.

apply_result_schema_name

Schema of the apply results table.

If null, the user's schema is assumed.

target_schema_name

Schema of the table containing the known targets.

If null, the user's schema is assumed.

cost_matrix_schema_name

Schema of the cost matrix table, if one is provided.

If null, the user's schema is assumed.

score_criterion_type

Whether to use probabilities or costs as the scoring criterion. Probabilities or costs are passed in the column identified in the score_criterion_column_name parameter.

The default value of score_criterion_type is 'PROBABILITY'. To use costs as the scoring criterion, specify 'COST'.

If score_criterion_type is set to 'COST' but no cost matrix is provided and if there is a scoring cost matrix associated with the model, then €˙the associated costs are used for scoring.

See the Usage Notes and the Examples.


Usage Notes

  • The predictive information you pass to COMPUTE_LIFT may be generated using SQL PREDICTION functions, the DBMS_DATA_MINING.APPLY procedure, or some other mechanism. As long as you pass the appropriate data, the procedure can compute the lift.

  • Instead of passing a cost matrix to COMPUTE_LIFT, you can use a scoring cost matrix associated with the model. A scoring cost matrix can be embedded in the model or it can be defined dynamically when the model is applied. To use a scoring cost matrix, invoke the SQL PREDICTION_COST function to populate the score criterion column.

  • The predictions that you pass to COMPUTE_LIFT are in a table or view specified in apply_results_table_name.

    CREATE TABLE apply_result_table_name AS (
                case_id_column_name            VARCHAR2, 
                score_column_name              VARCHAR2,
                score_criterion_column_name    VARCHAR2);
    
  • A cost matrix must have the columns described in Table 44-32.

    Table 44-32 Columns in a Cost Matrix

    Column NameData Type

    actual_target_value

    NUMBER or VARCHAR2

    predicted_target_value

    NUMBER or VARCHAR2

    cost

    NUMBER



    See Also:

    Oracle Data Mining Concepts for more information about cost matrixes

  • The table created by COMPUTE_LIFT has the columns described in Table 44-33

    Table 44-33 Columns in a Lift Table

    Column NameData Type

    quantile_number

    NUMBER

    probability_threshold

    NUMBER

    gain_cumulative

    NUMBER

    quantile_total_count

    NUMBER

    quantile_target_count

    NUMBER

    percent_records_cumulative

    NUMBER

    lift_cumulative

    NUMBER

    target_density_cumulative

    NUMBER

    targets_cumulative

    NUMBER

    non_targets_cumulative

    NUMBER

    lift_quantile

    NUMBER

    target_density

    NUMBER



    See Also:

    Oracle Data Mining Concepts for details about the information in the lift table

  • When a cost matrix is passed to COMPUTE_LIFT, the cost threshold is returned in the probability_threshold column of the lift table.

Examples

This example uses the Naive Bayes model nb_sh_clas_sample, which is created by one of the Oracle Data Mining sample programs.

The example illustrates lift based on probabilities. For examples that show computation based on costs, see "COMPUTE_CONFUSION_MATRIX Procedure".

The following statement applies the model to the test data and stores the predictions and probabilities in a table.

CREATE TABLE nb_apply_results AS
    SELECT cust_id, t.prediction, t.probability
    FROM mining_data_test_v, TABLE(PREDICTION_SET(nb_sh_clas_sample USING *)) t;

Using probabilities as the scoring criterion, you can compute lift as follows.

BEGIN
           DBMS_DATA_MINING.COMPUTE_LIFT (
              apply_result_table_name              => 'nb_apply_results',
              target_table_name                => 'mining_data_test_v',
              case_id_column_name              => 'cust_id',
              target_column_name               => 'affinity_card',
              lift_table_name                             => 'nb_lift',
              positive_target_value                 =>  to_char(1),
              score_column_name                => 'PREDICTION',
              score_criterion_column_name    => 'PROBABILITY',
              num_quantiles                                =>  10,
              cost_matrix_table_name                =>  null,
              apply_result_schema_name            =>  null,
              target_schema_name               =>  null,
              cost_matrix_schema_name              =>  null,
              score_criterion_type                   =>  'PROBABILITY');
        END;
        /

This query displays some of the statistics from the resulting lift table.

SQL>SELECT quantile_number, probability_threshold, gain_cumulative,
           quantile_total_count
           FROM nb_lift;

QUANTILE_NUMBER PROBABILITY_THRESHOLD GAIN_CUMULATIVE QUANTILE_TOTAL_COUNT 
--------------- --------------------- --------------- --------------------  
              1            .989335775       .15034965                   55 
              2            .980534911       .26048951                   55  
              3            .968506098      .374125874                   55  
              4            .958975196      .493006993                   55 
              5            .946705997      .587412587                   55  
              6            .927454174       .66958042                   55  
              7            .904403627      .748251748                   55  
              8            .836482525      .839160839                   55  
             10            .500184953               1                   54  

COMPUTE_ROC Procedure

This procedure computes receiver operating characteristic (ROC), stores the results in a table in the user's schema, and returns a measure of the model accuracy.

ROC is a test metric for binary classification models. To compute ROC, one of the target values must be designated as the positive class. COMPUTE_ROC compares the predictions generated by the model with the actual target values in a set of test data.

ROC measures the impact of changes in the probability threshold. The probability threshold is the decision point used by the model for predictions. In binary classification, the default probability threshold is 0.5. The value predicted for each case is the one with a probability greater than 50%.

ROC can be plotted as a curve on an X-Y axis. The false positive rate is placed on the X axis. The true positive rate is placed on the Y axis. A false positive is a positive prediction for a case that is negative in the test data. A true positive is a positive prediction for a case that is positive in the test data.

COMPUTE_ROC accepts two input streams:

  • The predictions generated on the test data. The information is passed in three columns:

    • Case ID column

    • Prediction column

    • Scoring criterion column containing probabilities

  • The known target values in the test data. The information is passed in two columns:

    • Case ID column

    • Target column containing the known target values


See Also:

Oracle Data Mining Concepts for more details about ROC and test metrics for classification

"COMPUTE_CONFUSION_MATRIX Procedure"

"COMPUTE_LIFT Procedure"


Syntax

DBMS_DATA_MINING.COMPUTE_ROC (
      roc_area_under_curve         OUT NUMBER,
      apply_result_table_name      IN  VARCHAR2,
      target_table_name            IN  VARCHAR2,
      case_id_column_name          IN  VARCHAR2,
      target_column_name           IN  VARCHAR2,
      roc_table_name               IN  VARCHAR2,
      positive_target_value        IN  VARCHAR2,
      score_column_name            IN  VARCHAR2 DEFAULT 'PREDICTION',
      score_criterion_column_name  IN  VARCHAR2 DEFAULT 'PROBABILITY',
      apply_result_schema_name     IN  VARCHAR2 DEFAULT NULL,
      target_schema_name           IN  VARCHAR2 DEFAULT NULL);

Parameters

Table 44-34 COMPUTE_ROC Procedure Parameters

ParameterDescription

roc_area_under_the_curve

Output parameter containing the area under the ROC curve (AUC). The AUC measures the likelihood that an actual positive will be predicted as positive.

The greater the AUC, the greater the flexibility of the model in accommodating trade-offs between positive and negative class predictions. AUC can be especially important when one target class is rarer or more important to identify than another.

apply_result_table_name

Table containing the predictions.

target_table_name

Table containing the known target values from the test data.

case_id_column_name

Case ID column in the apply results table. Must match the case identifier in the targets table.

target_column_name

Target column in the targets table. Contains the known target values from the test data.

roc_table_name

Table containing the ROC output. The table will be created by the procedure in the user's schema.

The columns in the ROC table are described in the Usage Notes.

positive_target_value

The positive class. This should be the class of interest, for which you want to calculate ROC.

If the target column is a NUMBER, you can use the TO_CHAR() operator to provide the value as a string.

score_column_name

Column containing the predictions in the apply results table.

The default column name is 'PREDICTION', which is the default name created by the APPLY procedure (See "APPLY Procedure").

score_criterion_column_name

Column containing the scoring criterion in the apply results table. Contains the probabilities that determine the predictions.

The default column name is 'PROBABILITY', which is the default name created by the APPLY procedure (See "APPLY Procedure").

apply_result_schema_name

Schema of the apply results table.

If null, the user's schema is assumed.

target_schema_name

Schema of the table containing the known targets.

If null, the user's schema is assumed.


Usage Notes

  • The predictive information you pass to COMPUTE_ROC may be generated using SQL PREDICTION functions, the DBMS_DATA_MINING.APPLY procedure, or some other mechanism. As long as you pass the appropriate data, the procedure can compute the receiver operating characteristic.

  • The predictions that you pass to COMPUTE_ROC are in a table or view specified in apply_results_table_name.

    CREATE TABLE apply_result_table_name AS (
                case_id_column_name            VARCHAR2, 
                score_column_name              VARCHAR2,
                score_criterion_column_name    VARCHAR2);
    
  • The table created by COMPUTE_ROC has the columns shown in Table 44-35.

    Table 44-35 COMPUTE_ROC Output

    ColumnData Type

    probability

    NUMBER

    true_positives

    NUMBER

    false_negatives

    NUMBER

    false_positives

    NUMBER

    true_negatives

    NUMBER

    true_positive_fraction

    NUMBER

    false_positive_fraction

    NUMBER



    See Also:

    Oracle Data Mining Concepts for details about the output of COMPUTE_ROC

  • ROC is typically used to determine the most desirable probability threshold. This can be done by examining the true positive fraction and the false positive fraction. The true positive fraction is the percentage of all positive cases in the test data that were correctly predicted as positive. The false positive fraction is the percentage of all negative cases in the test data that were incorrectly predicted as positive.

    Given a probability threshold, the following statement returns the positive predictions in an apply result table ordered by probability.

    SELECT case_id_column_name 
           FROM apply_result_table_name 
           WHERE probability > probability_threshold 
           ORDER BY probability DESC;
    
  • There are two approaches to identifying the most desirable probability threshold. Which approach you use depends on whether or not you know the relative cost of positive versus negative class prediction errors.

    If the costs are known, you can apply the relative costs to the ROC table to compute the minimum cost probability threshold. Suppose the relative cost ratio is: Positive Class Error Cost / Negative Class Error Cost = 20. Then execute a query like this.

    WITH cost AS (
      SELECT probability_threshold, 20 * false_negatives + false_positives cost 
        FROM ROC_table 
      GROUP BY probability_threshold), 
        minCost AS (
          SELECT min(cost) minCost 
            FROM cost)
          SELECT max(probability_threshold)probability_threshold 
            FROM cost, minCost 
        WHERE cost = minCost;
    

    If relative costs are not well known, you can simply scan the values in the ROC table (in sorted order) and make a determination about which of the displayed trade-offs (misclassified positives versus misclassified negatives) is most desirable.

    SELECT * FROM ROC_table 
             ORDER BY probability_threshold;
    

Examples

This example uses the Naive Bayes model nb_sh_clas_sample, which is created by one of the Oracle Data Mining sample programs.

The following statement applies the model to the test data and stores the predictions and probabilities in a table.

CREATE TABLE nb_apply_results AS
    SELECT cust_id, t.prediction, t.probability
    FROM mining_data_test_v, TABLE(PREDICTION_SET(nb_sh_clas_sample USING *)) t;

Using the predictions and the target values from the test data, you can compute ROC as follows.

DECLARE
     v_area_under_curve NUMBER;
  BEGIN
         DBMS_DATA_MINING.COMPUTE_ROC (
               roc_area_under_curve                  => v_area_under_curve,
               apply_result_table_name       => 'nb_apply_results',
               target_table_name               => 'mining_data_test_v',
               case_id_column_name            => 'cust_id',
               target_column_name             => 'affinity_card',
               roc_table_name                     => 'nb_roc',
               positive_target_value         => '1',
               score_column_name               => 'PREDICTION',
               score_criterion_column_name   => 'PROBABILITY');
           DBMS_OUTPUT.PUT_LINE('**** AREA UNDER ROC CURVE ****: ' ||
           ROUND(v_area_under_curve,4));
  END;
 /

The resulting AUC and a selection of columns from the ROC table are shown as follows.

**** AREA UNDER ROC CURVE ****: .8212

SQL> SELECT probability, true_positive_fraction, false_positive_fraction 
            FROM nb_roc;
 
PROBABILITY  TRUE_POSITIVE_FRACTION  FALSE_POSITIVE_FRACTION
-----------  ----------------------  -----------------------
     .00000                       1                        1
     .50018              .826589595               .227902946
     .53851              .823699422               .221837088
     .54991              .820809249               .217504333
     .55628              .815028902               .215771231
     .55628              .817919075               .215771231
     .57563              .800578035               .214904679
     .57563              .812138728               .214904679
      .                   .                        .
      .                   .                        .
      .                   .                        .


CREATE_MODEL Procedure

This procedure creates a mining model with a given mining function.

By passing an xform_list to CREATE_MODEL, you can specify a list of transformations to be performed on the input data. If the PREP_AUTO setting is on, the transformations are used in addition to the automatic transformations. If the PREP_AUTO setting is off, the specified transformations are the only ones implemented by the model. In both cases, the transformation definitions are embedded in the model and executed automatically whenever the model is applied. See "Automatic Data Preparation".

Syntax

DBMS_DATA_MINING.CREATE_MODEL (
      model_name            IN VARCHAR2,
      mining_function       IN VARCHAR2,
      data_table_name       IN VARCHAR2,
      case_id_column_name   IN VARCHAR2,
      target_column_name    IN VARCHAR2 DEFAULT NULL,
      settings_table_name   IN VARCHAR2 DEFAULT NULL,
      data_schema_name      IN VARCHAR2 DEFAULT NULL,
      settings_schema_name  IN VARCHAR2 DEFAULT NULL,
      xform_list            IN TRANSFORM_LIST DEFAULT NULL);

Parameters

Table 44-36 CREATE_MODEL Procedure Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used. See also "Mining Model Naming Restrictions".

mining_function

The mining function. Va€˙lues are listed in Table 44-2, "Mining Functions".

data_table_name

Table or view containing the build data.

case_id_column_name

Case identifier column in the build data.

target_column_name

For supervised models, the target column in the build data. NULL for unsupervised models.

settings_table_name

Table containing build settings for the model. NULL if there is no settings table (only default settings are used).

data_schema_name

Schema hosting the build data. If NULL, the user's schema is assumed.

settings_schema_name

Schema hosting the settings table. If NULL, the user's schema is assumed.

xform_list

A list of transformations to be used in addition to or instead of automatic transformations, depending on the value of the PREP_AUTO setting. (See "Automatic Data Preparation".)

The data type is TRANSFORM_LIST, which consists of records of type TRANSFORM_REC.

TYPE
  TRANFORM_REC     IS RECORD (
     attribute_name       VARCHAR2(4000),
     attribute_subname    VARCHAR2(4000),
     expression           EXPRESSION_REC,
     reverse_expression   EXPRESSION_REC,
     attribute_spec       VARCHAR2(4000));

Each TRANSFORM_REC describes the transformation and reverse transformation for an attribute. EXPRESSION_REC stores a SQL expression.

The SQL expression stored in EXPRESSION_REC can be manipulated using routines in the DBMS_DATA_MINING_TRANSFORM package. Examples are: SET_EXPRESSION, GET_EXPRESSION, and SET_TRANSFORM.

You can use ATTRIBUTE_SPEC to disable Automatic Data Preparation (ADP) for an individual attribute. Specify 'NOPREP' to prevent automatic preparation of this attribute when ADP is on. When ADP is off, the ' NOPREP' value is ignored.

The 'NOPREP' value cannot be used for an individual subname of a nested attribute. If 'NOPREP' is specified for an individual subname when ADP is on, an error is generated.


Usage Notes

You can obtain information about a model by querying these data dictionary views.


ALL_MINING_MODELS
ALL_MINING_MODEL_ATTRIBUTES
ALL_MINING_MODEL_SETTINGS

Specify the USER prefix instead of ALL to obtain information about models in your own schema only.


See Also:

Oracle Data Mining Application Developer's Guide for information on the data dictionary views

Examples

The first example builds a classification model using the Support Vector Machine algorithm.

-- Create the settings table 
CREATE TABLE svm_model_settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));

-- Populate the settings table
-- Specify SVM. By default, Naive Bayes is used for classification.
-- Specify ADP. By default, ADP is not used.
BEGIN 
  INSERT INTO svm_model_settings (setting_name, setting_value) VALUES
     (dbms_data_mining.algo_name, dbms_data_mining.algo_support_vector_machines);
  INSERT INTO svm_model_settings (setting_name, setting_value) VALUES
     (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
  COMMIT;
END;
/
-- Create the model using the specified settings 
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'svm_model',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'mining_data_build_v',
    case_id_column_name => 'cust_id',
    target_column_name  => 'affinity_card',
    settings_table_name => 'svm_model_settings');
END;
/

You can display the model settings with the following query.

SELECT * FROM user_mining_model_settings 
       WHERE model_name IN 'SVM_MODEL';

MODEL_NAME     SETTING_NAME            SETTING_VALUE                  SETTING
-------------  ----------------------  -----------------------------  -------
SVM_MODEL      ALGO_NAME               ALGO_SUPPORT_VECTOR_MACHINES  INPUT
SVM_MODEL      SVMS_KERNEL_CACHE_SIZE  50000000                      DEFAULT
SVM_MODEL      SVMS_ACTIVE_LEARNING    SVMS_AL_ENABLE                DEFAULT
SVM_MODEL      SVMS_STD_DEV            3.004524                      DEFAULT
SVM_MODEL      PREP_AUTO               ON                            INPUT
SVM_MODEL      SVMS_COMPLEXITY_FACTOR  1.887389                      DEFAULT
SVM_MODEL      SVMS_KERNEL_FUNCTION    SVMS_GAUSSIAN                 DEFAULT
SVM_MODEL      SVMS_CONV_TOLERANCE     .001                          DEFAULT

The second example creates an anomaly detection model. Anomaly detection uses SVM classification without a target. This example uses the same settings table created for the SVM classification model in the first example.

BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => 'anomaly_detect_model',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'mining_data_build_v',
    case_id_column_name => 'cust_id',
    target_column_name  => null,
    settings_table_name => 'svm_model_settings');
END;
/

This query shows that the models created in these examples are the only ones in your schema.

SELECT model_name, mining_function, algorithm FROM user_mining_models;
 
MODEL_NAME              MINING_FUNCTION      ALGORITHM
----------------------  -------------------- ------------------------------
SVM_MODEL               CLASSIFICATION       SUPPORT_VECTOR_MACHINES
ANOMALY_DETECT_MODEL    CLASSIFICATION       SUPPORT_VECTOR_MACHINES

This query shows that only the SVM classification model has a target.

SELECT model_name, attribute_name, attribute_type, target 
       FROM user_mining_model_attributes 
       WHERE target = 'YES';
 
MODEL_NAME          ATTRIBUTE_NAME   ATTRIBUTE_TYPE     TARGET
------------------  ---------------  -----------------  ------
SVM_MODEL           AFFINITY_CARD    CATEGORICAL         YES

DROP_MODEL Procedure

This procedure deletes the specified mining model.

Syntax

DBMS_DATA_MINING.DROP_MODEL (model_name IN VARCHAR2,
                             force      IN BOOLEAN DEFAULT FALSE);

Parameters

Table 44-37 DROP_MODEL Procedure Parameters

ParameterDescription

model_name

Name of the mining model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

force

Forces the mining model to be dropped even if it is invalid. A mining model may be invalid if a serious system error interrupted the model build process.


Usage Note

To drop a mining model, you must be the owner or you must have the DROP ANY MINING MODEL privilege. See Oracle Data Mining Administrator's Guide for information about privileges for data mining.

Example

You can use the following command to delete a valid mining model named nb_sh_clas_sample that exists in your schema.

BEGIN
  DBMS_DATA_MINING.DROP_MODEL(model_name => 'nb_sh_clas_sample');
END;
/

EXPORT_MODEL Procedure

This procedure exports the specified data mining models to a dump file set. To import the models from the dump file set, use the IMPORT_MODEL Procedure. EXPORT_MODEL and IMPORT_MODEL use Oracle Data Pump technology.

When Oracle Data Pump is used to export/import an entire schema or database, the mining models in the schema or database are included. However, EXPORT_MODEL and IMPORT_MODEL are the only utilities that support the export/import of individual models.


See Also:

Oracle Database Utilities for information about Oracle Data Pump

Oracle Data Mining Administrator's Guide for more information about exporting and importing mining models


Syntax

DBMS_DATA_MINING.EXPORT_MODEL (
      filename          IN VARCHAR2,
      directory         IN VARCHAR2,
      model_filter      IN VARCHAR2 DEFAULT NULL,
      filesize          IN VARCHAR2 DEFAULT NULL,
      operation         IN VARCHAR2 DEFAULT NULL,
      remote_link       IN VARCHAR2 DEFAULT NULL,
      jobname           IN VARCHAR2 DEFAULT NULL);

Parameters

Table 44-38 EXPORT_MODEL Procedure Parameters

ParameterDescription

filename

Name of the dump file set to which the models should be exported. The name must be unique within the schema.

The dump file set can contain one or more files. The number of files in a dump file set is determined by the size of the models being exported (both metadata and data) and a specified or estimated maximum file size. You can specify the file size in the filesize parameter, or you can use the operation parameter to cause Oracle Data Pump to estimate the file size. If the size of the models to export is greater than the maximum file size, one or more additional files are created.

When the export operation completes successfully, the name of the dump file set is automatically expanded to filename01.dmp, even if there is only one file in the dump set. If there are additional files, they are named sequentially as filename02.dmp, filename03.dmp, and so forth.

directory

Name of a pre-defined directory object that specifies where the dump file set should be created.

The exporting user must have read/write privileges on the directory object and on the file system directory that it identifies.

See Oracle Database SQL Language Reference for information about directory objects.

model_filter

Optional parameter that specifies which model or models to export. If you do not specify a value for model_filter, all models in the schema are exported. You can also specify NULL (the default) or 'ALL' to export all models.

You can export individual models by name and groups of models based on mining function or algorithm. For instance, you could export all regression models or all Naive Bayes models. Examples are provided in Table 44-39.

filesize

Optional parameter that specifies the maximum size of a file in the dump file set. The size may be specified in bytes, kilobytes (K), megabytes (M), or gigabytes (G). The default size is 50 MB.

If the size of the models to export is larger than filesize, one or more additional files are created within the dump set. See the description of the filename parameter for more information.

operation

Optional parameter that specifies whether or not to estimate the size of the files in the dump set. By default the size is not estimated and the value of the filesize parameter determines the size of the files.

You can specify either of the following values for operation:

  • 'EXPORT' — Export all or the specified models. (Default)

  • 'ESTIMATE' — Estimate the size of the exporting models.

remote_link

Optional parameter not used in this release. Set to NULL.

jobname

Optional parameter that specifies the name of the export job. By default, the name has the form username_exp_nnnn, where nnnn is a number. For example, a job name in the SCOTT schema might be SCOTT_exp_134.

If you specify a job name, it must be unique within the schema. The maximum length of the job name is 30 characters.

A log file for the export job, named jobname.log, is created in the same directory as the dump file set.


Usage Notes

The model_filter parameter specifies which models to export. You can list the models by name, or you can specify all models that have the same mining function or algorithm. You can query the USER_MINING_MODELS view to list the models in your schema.

SQL> describe user_mining_models
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 MINING_FUNCTION                                    VARCHAR2(30)
 ALGORITHM                                          VARCHAR2(30)
 CREATION_DATE                             NOT NULL DATE
 BUILD_DURATION                                     NUMBER
 MODEL_SIZE                                         NUMBER
 COMMENTS                                           VARCHAR2(4000)

For more information on data dictionary views of mining models, see "Mining Model Objects".

Examples of model filters are provided in Table 44-39.

Table 44-39 Sample Values for the Model Filter Parameter

Sample ValueMeaning

'mymodel'

Export the model named mymodel

'name= ''mymodel'''

Export the model named mymodel

'name IN (''mymodel2'',''mymodel3'')'

Export the models named mymodel2 and mymodel3

'ALGORITHM_NAME = ''NAIVE_BAYES'''

Export all Naive Bayes models. See Table 44-4 for a list of algorithm names.

'FUNCTION_NAME =''CLASSIFICATION'''

Export all classification models. See Table 44-2 for a list of mining functions.


Examples

The following statement exports all the models in the DMUSER3 schema to a dump file set called models_out in the directory $ORACLE_HOME/rdbms/log. This directory is mapped to a directory object called DATA_PUMP_DIR. The DMUSER3 user has read/write access to the directory and to the directory object.

SQL>execute dbms_data_mining.export_model ('models_out', 'DATA_PUMP_DIR');

You can exit SQL*Plus and list the resulting dump file and log file.

SQL>exit
>cd $ORACLE_HOME/rdbms/log
>ls
>DMUSER3_exp_1027.log  models_out01.dmp  

The following example uses the same directory object and is executed by the same user. It exports the models called NMF_SH_SAMPLE and SVMR_SH_REGR_SAMPLE to a different dump file set in the same directory.

SQL>execute dbms_data_mining.export_model ( 'models2_out', 'DATA_PUMP_DIR',
            'name in (''NMF_SH_SAMPLE'', ''SVMR_SH_REGR_SAMPLE'')');
SQL>exit
>cd $ORACLE_HOME/rdbms/log
>ls
>DMUSER3_exp_1027.log  models_out01.dmp
 DMUSER3_exp_924.log  models2_out01.dmp

GET_ASSOCIATION_RULES Function

This table function returns the rules from an association model.

You can specify filtering criteria to cause GET_ASSOCIATION_RULES to return a subset of the rules. Filtering criteria can improve the performance of the table function. If the number of rules is large, the greatest performance improvement will result from specifying the topn parameter.

Syntax

DBMS_DATA_MINING.GET_ASSOCIATION_RULES (
   model_name            IN VARCHAR2,
   topn                  IN NUMBER DEFAULT NULL,
   rule_id               IN INTEGER DEFAULT NULL,
   min_confidence        IN NUMBER DEFAULT NULL,
   min_support           IN NUMBER DEFAULT NULL,
   max_rule_length       IN INTEGER DEFAULT NULL,
   min_rule_length       IN INTEGER DEFAULT NULL,
   sort_order            IN ORA_MINING_VARCHAR2_NT DEFAULT NULL,
   antecedent_items      IN DM_ITEMS DEFAULT NULL,
   consequent_items      IN DM_ITEMS DEFAULT NULL,
   min_lift              IN NUMBER DEFAULT NULL)
 RETURN DM_RULES PIPELINED;

Parameters

Table 44-40 GET_ASSOCIATION_RULES Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

This is the only required parameter of GET_ASSOCIATION_RULES. All other parameters specify optional filters on the rules to return.

topn

Return the n top rules ordered by confidence and then support, both descending. If you specify a sort order, the top n rules are derived after the sort is performed.

If topn is specified and no maximum or minimum rule length is specified, then the only columns allowed in the sort order are RULE_CONFIDENCE and RULE_SUPPORT. If topn is specified and a maximum or minimum rule length is specified, then RULE_CONFIDENCE, RULE_SUPPORT, and NUMBER_OF_ITEMS are allowed in the sort order.

rule_id

Identifier of the rule to return. If you specify a value for rule_id, do not specify values for the other filtering parameters.

min_confidence

Return the rules with confidence greater than or equal to this number

min_support

Return the rules with support greater than or equal to this number

max_rule_length

Return the rules with a length less than or equal to this number.

Rule length refers to the number of items in the rule (See NUMBER_OF_ITEMS in Table 44-41). For example, in the rule A=>B (if A, then B), the number of items is 2.

If max_rule_length is specified, then the NUMBER_OF_ITEMS column is permitted in the sort order.

min_rule_length

Return the rules with a length greater than or equal to this number. See max_rule_length for a description of rule length.

If min_rule_length is specified, then the NUMBER_OF_ITEMS column is permitted in the sort order.

sort_order

Sort the rules by the values in one or more of the returned columns. Specify one or more column names, each followed by ASC for ascending order or DESC for descending order.

For example, to sort the result set in descending order first by the NUMBER_OF_ITEMS column, then by the RULE_CONFIDENCE column, you would specify:

ORA_MINING_VARCHAR2_NT('NUMBER_OF_ITEMS DESC', 'RULE_CONFIDENCE DESC')

If you specify topn, the results will vary depending on the sort order.

By default, the results are sorted by confidence in descending order, then by support in descending order.

See the examples.

antecedent_items

Return the rules with these items in the antecedent. See the examples.

consequent_items

Return the rules with this item in the consequent. See the examples.

min_lift

Return the rules with lift greater than or equal to this number.


Return Values

Table 44-41 GET_ASSOCIATION RULES Function Return Values

Return ValueDescription

DM_RULES

Represents a set of rows of type DM_RULE. The rows have the following columns:

(rule_id              INTEGER,
 antecedent           DM_PREDICATES,
 consequent           DM_PREDICATES,
 rule_support         NUMBER,
 rule_confidence      NUMBER,
 rule_lift            NUMBER,
 antecedent_support   NUMBER,
 consequent_support   NUMBER,
 number_of_items      INTEGER )

The antecedent and consequent columns each return nested tables of type DM_PREDICATES.The rows, of type DM_PREDICATE, have the following columns:

     (attribute_name            VARCHAR2(4000),
      attribute_subname         VARCHAR2(4000),
      conditional_operator      CHAR(2)/*=,<>,<,>,<=,>=*/,
      attribute_num_value       NUMBER,
      attribute_str_value       VARCHAR2(4000),
      attribute_support         NUMBER,
      attribute_confidence      NUMBER)

Usage Notes

This table function pipes out rows of type DM_RULES. For information on Data Mining data types and piped output from table functions, see "Data Types".

The ORA_MINING_VARCHAR2_NT type is defined as a table of VARCHAR2(4000).

Examples

The following example demonstrates an Association model build followed by several invocations of the GET_ASSOCIATION_RULES table function.

-- prepare a settings table to override default settings
CREATE TABLE market_settings AS
SELECT *
  FROM TABLE(DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'ASSO_%';
BEGIN
-- update the value of the minimum confidence
UPDATE census_settings
   SET setting_value = TO_CHAR(0.081)
 WHERE setting_name = DBMS_DATA_MINING.asso_min_confidence;

-- build an AR model 
DBMS_DATA_MINING.CREATE_MODEL(
  model_name => 'market_model',
  function => DBMS_DATA_MINING.ASSOCIATION,
  data_table_name => 'market_build',
  case_id_column_name => 'item_id',
  target_column_name => NULL,
  settings_table_name => 'census_settings');
END;
/
-- View the (unformatted) rules 
SELECT rule_id, antecedent, consequent, rule_support,
       rule_confidence
  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('market_model'));

In the previous example, you view all rules. To view just the top 20 rules, use the following statement.

-- View the top 20 (unformatted) rules
SELECT rule_id, antecedent, consequent, rule_support,
       rule_confidence
  FROM TABLE(DBMS_DATA_MINING.GET_ASSOCIATION_RULES('market_model', 20));

The following query uses the association model AR_SH_SAMPLE, which is created from one of the Oracle Data Mining sample programs. (See Oracle Data Mining Administrator's Guide for information about the sample programs.)

SELECT * FROM TABLE (
   DBMS_DATA_MINING.GET_ASSOCIATION_RULES (
      'AR_SH_SAMPLE', 10, NULL, 0.5, 0.01, 2, 1,
         ORA_MINING_VARCHAR2_NT (
         'NUMBER_OF_ITEMS DESC', 'RULE_CONFIDENCE DESC', 'RULE_SUPPORT DESC'),
         DM_ITEMS(DM_ITEM('CUSTPRODS', 'Mouse Pad', 1, NULL), 
                  DM_ITEM('CUSTPRODS', 'Standard Mouse', 1, NULL)),
         DM_ITEMS(DM_ITEM('CUSTPRODS', 'Extension Cable', 1, NULL))));

The query returns three rules, shown as follows.

13  DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Mouse Pad', '= ', 1, NULL, NULL, NULL), 
       DM_PREDICATE('CUSTPRODS', 'Standard Mouse', '= ', 1, NULL, NULL, NULL))
    DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Extension Cable', '= ', 1, NULL, NULL, NULL))
    .15532      .84393   2.7075     .18404     .3117   2
 
11  DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Standard Mouse', '= ', 1, NULL, NULL, NULL))
    DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Extension Cable', '= ', 1, NULL, NULL, NULL))
    .18085      .56291   1.8059     .32128     .3117   1
 
9   DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Mouse Pad', '= ', 1, NULL, NULL, NULL))
    DM_PREDICATES(
       DM_PREDICATE('CUSTPRODS', 'Extension Cable', '= ', 1, NULL, NULL, NULL))
      .17766    .55116   1.7682     .32234     .3117   1




GET_DEFAULT_SETTINGS Function

The GET_DEFAULT_SETTINGS function was deprecated in Oracle Data Mining 11g Release 1 (11.1). It was replaced with the data dictionary view *_MINING_MODEL_SETTINGS. USER_, ALL_, and DBA_ versions of the view are available. See Oracle Data Mining Application Developer's Guide.

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

This table function returns the default settings for all mining functions and algorithms supported in the DBMS_DATA_MINING package.

Syntax

DBMS_DATA_MINING.GET_DEFAULT_SETTINGS
  RETURN DM_MODEL_SETTINGS PIPELINED;

Return Values

Table 44-42 GET_DEFAULT_SETTINGS Function Return Values

Return ValueDescription

DM_MODEL_SETTINGS

Represents a set of rows of type DM_MODEL_SETTING. The rows have the following columns:

(setting_name    VARCHAR2(30),
 setting_value   VARCHAR2(128))

Usage Notes

This table function pipes out rows of type DM_MODEL_SETTING. For information on Data Mining data types and piped output from table functions, see "Data Types".

This function is particularly useful if you do not know what settings are associated with a particular function or algorithm, and you want to override some or all of them.

Examples

For example, if you want to override some or all of k-Means clustering settings, you can create a settings table as shown, and update individual settings as required.

BEGIN
  CREATE TABLE mysettings AS
  SELECT * 
  FROM TABLE(DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
   WHERE setting_name LIKE 'KMNS%';
  -- now update individual settings as required
  UPDATE mysettings
     SET setting_value = 0.02
   WHERE setting_name = DBMS_DATA_MINING.KMNS_MIN_PCT_ATTR_SUPPORT;
END;
/

GET_FREQUENT_ITEMSETS Function

This table function returns a set of rows that represent the frequent itemsets from an Association model. For a detailed description of frequent itemsets, consult Oracle Data Mining Concepts.

Syntax

DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS (
    model_name          IN VARCHAR2,
    topn                IN NUMBER DEFAULT NULL,
    max_itemset_length  IN NUMBER DEFAULT NULL)
  RETURN DM_ITEMSETS PIPELINED;

Parameters

Table 44-43 GET_FREQUENT_ITEMSETS Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

topn

When not NULL, return the top n rows ordered by support in descending order

max_itemset_length

Maximum length of an item set.


Return Values

Table 44-44 GET_FREQUENT_ITEMSETS Function Return Values

Return ValueDescription

DM_ITEMSETS

Represents a set of rows of type DM_ITEMSET. The rows have the following columns:

(itemsets_id      NUMBER,
items             DM_ITEMS,
support           NUMBER,
number_of_items   NUMBER)

The items column returns a nested table of type DM_ITEMS. The rows have type DM_ITEM:

(attribute_name      VARCHAR2(4000),
attribute_subname    VARCHAR2(4000),
attribute_num_value  NUMBER,
attribute_str_value  VARCHAR2(4000))

Usage Notes

This table function pipes out rows of type DM_ITEMSETS. For information on Data Mining data types and piped output from table functions, see "Data Types".

Examples

The following example demonstrates an Association model build followed by an invocation of GET_FREQUENT_ITEMSETS table function from Oracle SQL.

-- prepare a settings table to override default settings
CREATE TABLE market_settings AS
    SELECT *
  FROM TABLE(DBMS_DATA_MINING.GET_DEFAULT_SETTINGS)
 WHERE setting_name LIKE 'ASSO_%';
BEGIN
-- update the value of the minimum confidence
UPDATE market_settings
   SET setting_value = TO_CHAR(0.081)
 WHERE setting_name = DBMS_DATA_MINING.asso_min_confidence;

/* build a AR model */
DBMS_DATA_MINING.CREATE_MODEL(
  model_name           => 'market_model',
  function             => DBMS_DATA_MINING.ASSOCIATION,
  data_table_name      => 'market_build',
  case_id_column_name  => 'item_id',
  target_column_name   => NULL,
  settings_table_name  => 'census_settings');
END;
/

-- View the (unformatted) Itemsets from SQL*Plus
SELECT itemset_id, items, support, number_of_items
  FROM TABLE(DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS('market_model'));

In the example above, you view all itemsets. To view just the top 20 itemsets, use the following statement:

-- View the top 20 (unformatted) Itemsets from SQL*Plus
SELECT itemset_id, items, support, number_of_items
  FROM TABLE(DBMS_DATA_MINING.GET_FREQUENT_ITEMSETS('market_model', 20));

GET_MODEL_COST_MATRIX Function

This function returns the rows of the default scoring matrix associated with the specified model.

By default, this function returns the scoring matrix that was added to the model with the ADD_COST_MATRIX procedure. If you wish to obtain the cost matrix used to create a model, specify cost_matrix_type_create as the matrix_type. See Table 44-45.

See also ADD_COST_MATRIX Procedure.

Syntax

DBMS_DATA_MINING.GET_MODEL_COST_MATRIX (
      model_name        IN VARCHAR2,
      matrix_type       IN VARCHAR2 DEFAULT cost_matrix_type_score)
RETURN DM_COST_MATRIX PIPELINED;

Parameters

Table 44-45 GET_MODEL_COST_MATRIX Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

matrix_type

The type of cost matrix.

COST_MATRIX_TYPE_SCORE — cost matrix used for scoring. (Default.)

COST_MATRIX_TYPE_CREATE — cost matrix used to create the model (Decision Tree only).


Return Values

Table 44-46 GET_MODEL_COST_MATRIX Function Return Values

Return ValueDescription

DM_COST_MATRIX

Represents a set of rows of type DM_COST_ELEMENT. The rows have the following columns:

actual          VARCHAR2(4000), predicted       VARCHAR2(4000), cost            NUMBER)

Usage Notes

Only Decision Tree models can be built with a cost matrix. If you want to build a Decision Tree model with a cost matrix, specify the cost matrix table name in the CLAS_COST_TABLE_NAME setting in the settings table for the model. See Table 44-7, "Mining Function Settings".

The cost matrix used to create a Decision Tree model becomes the default scoring matrix for the model. If you want to specify different costs for scoring, you can modify the values in the cost matrix table or you can use the REMOVE_COST_MATRIX procedure to remove the cost matrix and the ADD_COST_MATRIX procedure to add a new one

Example

This example returns the scoring cost matrix associated with the Naive Bayes model NB_SH_CLAS_SAMPLE.

column actual format a10
column predicted format a10
SELECT *
    FROM TABLE(dbms_data_mining.get_model_cost_matrix('nb_sh_clas_sample'))
    ORDER BY predicted, actual;
 
ACTUAL     PREDICTED   COST
---------- ---------- -----
0          0            .00
1          0            .75
0          1            .25
1          1            .00

GET_MODEL_DETAILS_ABN Function

The Adaptive Bayes Network algorithm ABN algorithm was deprecated in Oracle Data Mining 11g Release 1 (11.1).

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

This table function returns a set of rows that provide the details of an Adaptive Bayes Network model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_ABN (
    model_name         IN VARCHAR2)
  RETURN DM_ABN_DETAILS PIPELINED;

Parameters

Table 44-47 GET_MODEL_DETAILS_ABN Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-48 GET_MODEL_DETAILS_ABN Function Return Values

Return ValueDescription

DM_ABN_DETAILS

Represents a set of rows of type DM_ABN_DETAIL. The rows have the following columns:

(rule_id           INTEGER,
 antecedent        DM_PREDICATES,
 consequent        DM_PREDICATES,
 rule_support      NUMBER)

The antecedent and consequent columns of DM_ABN_DETAIL each return nested tables of type DM_PREDICATES. The rows, of type DM_PREDICATE, have the following columns:

     (attribute_name          VARCHAR2(4000),
      attribute_subname       VARCHAR2(4000),
      conditional_operator    CHAR(2), /*=,<>,<,>,<=,>=*/
      attribute_num_value     NUMBER,
      attribute_str_value     VARCHAR2(4000),
      attribute_support       NUMBER,
      attribute_confidence    NUMBER)

Usage Notes

This table function pipes out rows of type DM_ABN_DETAIL. For information on Data Mining data types and piped output from table functions, see "Data Types".

This function returns details only for a single feature ABN model.

Examples

The following example demonstrates an ABN model build followed by an invocation of GET_MODEL_DETAILS_ABN table function from Oracle SQL.

BEGIN
  -- prepare a settings table to override default algorithm and model type
  CREATE TABLE abn_settings (setting_name VARCHAR2(30),
  setting_value 
VARCHAR2(128));
  INSERT INTO abn_settings VALUES (DBMS_DATA_MINING.ALGO_NAME,
    DBMS_DATA_MINING.ALGO_ADAPTIVE_BAYES_NETWORK);
  INSERT INTO abn_settings VALUES    (DBMS_DATA_MINING.ABNS_MODEL_TYPE,     DBMS_DATA_MINING.ABNS_SINGLE_FEATURE);
   COMMIT;
  -- create a model
  DBMS_DATA_MINING.CREATE_MODEL (
    model_name           => 'abn_model',
    function             => DBMS_DATA_MINING.CLASSIFICATION,
    data_table_name      => 'abn_build',
    case_id_column_name  => 'id',
    target_column_name   => NULL,
    settings_table_name  => 'abn_settings');
END;
/
-- View the (unformatted) results from SQL*Plus
SELECT *
    FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_ABN('abn_model'));

GET_MODEL_DETAILS_AI Function

This table function returns a set of rows that provide the details of an Attribute Importance model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_AI (
  model_name         IN VARCHAR2)
 RETURN DM_RANKED_ATTRIBUTES PIPELINED;

Parameters

Table 44-49 GET_MODEL_DETAILS_AI Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-50 GET_MODEL_DETAILS_AI Function Return Values

Return ValueDescription

DM_RANKED_ATTRIBUTES

Represents a set of rows of type DM_RANKED_ATTRIBUTE. The rows have the following columns:

(attribute_name          VARCHAR2(4000,
 attribute_subname       VARCHAR2(4000),
 importance_value        NUMBER,
 rank                    NUMBER(38))

Examples

The following example returns model details for the attribute importance model AI_SH_sample, which was created by the sample program dmaidemo.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

SELECT attribute_name, importance_value, rank
    FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('AI_SH_sample'))
    ORDER BY RANK;
 
ATTRIBUTE_NAME                           IMPORTANCE_VALUE       RANK
---------------------------------------- ---------------- ----------
HOUSEHOLD_SIZE                                 .151685183          1
CUST_MARITAL_STATUS                            .145294546          2
YRS_RESIDENCE                                   .07838928          3
AGE                                            .075027496          4
Y_BOX_GAMES                                    .063039952          5
EDUCATION                                      .059605314          6
HOME_THEATER_PACKAGE                           .056458722          7
OCCUPATION                                     .054652937          8
CUST_GENDER                                    .035264741          9
BOOKKEEPING_APPLICATION                        .019204751         10
PRINTER_SUPPLIES                                        0         11
OS_DOC_SET_KANJI                               -.00050013         12
FLAT_PANEL_MONITOR                             -.00509564         13
BULK_PACK_DISKETTES                            -.00540822         14
COUNTRY_NAME                                   -.01201116         15
CUST_INCOME_LEVEL                              -.03951311         16

GET_MODEL_DETAILS_GLM Function

This table function returns the coefficient statistics for a Generalized Linear Model.

The same set of statistics is returned for both linear and logistic regression, but statistics that do not apply to the mining function are returned as NULL. For more details, see the Usage Notes.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_GLM (
             model_name             VARCHAR2)
RETURN DM_GLM_COEFF_SET PIPELINED;

Parameters

Table 44-51 GET_MODEL_DETAILS_GLM Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-52 GET_MODEL_DETAILS_GLM Return Values

Return ValueDescription

DM_GLM_COEFF_SET

Represents a set of rows of type DM_GLM_COEFF. The rows have the following columns:

(class                   VARCHAR2(4000),
 attribute_name          VARCHAR2(4000),
 attribute_subname       VARCHAR2(4000),
 attribute_value         VARCHAR2(4000),
 coefficient             NUMBER,
 std_error               NUMBER,
 test_statistic          NUMBER,
 p_value                 NUMBER,
 VIF                     NUMBER,
 std_coefficient         NUMBER,
 lower_coeff_limit       NUMBER,
 upper_coeff_limit       NUMBER,
 exp_coefficient         BINARY_DOUBLE,
 exp_lower_coeff_limit   BINARY_DOUBLE,
 exp_upper_coeff_limit   BINARY_DOUBLE)

GET_MODEL_DETAILS_GLM returns a row of statistics for each attribute and one extra row for the intercept, which is identified by a null value in the attribute name. Each row has the DM_GLM_COEFF data type. The statistics are described in Table 44-53.

Table 44-53 DM_GLM_COEFF Data Type Description

ColumnDescription

class

The non-reference target class for logistic regression. The model is built to predict the probability of this class.

The other class (the reference class) is specified in the model setting GLMS_REFERENCE_CLASS_NAME. See Table 44-11, "GLM Settings".

For linear regression, class is null.

attribute_name

The attribute name when there is no subname, or first part of the attribute name when there is a subname. The value of attribute_name is also the name of the column in the case table that is the source for this attribute.

For the intercept, attribute_name is null. Intercepts are equivalent to the bias term in SVM models.

attribute_subname

The name of an attribute in a nested table. The full name of a nested attribute has the form:

attribute_name.attribute_subname

where attribute_name is the name of the nested column in the case table that is the source for this attribute.

If the attribute is not nested, attribute_subname is null. If the attribute is an intercept, both the attribute_name and the attribute_subname are null.

attribute_value

The value of the attribute (categorical attribute only).

For numerical attributes, attribute_value is null.

coefficient

The linear coefficient estimate.

std_error

Standard error of the coefficient estimate.

test_statistic

For linear regression, the t-value of the coefficient estimate.

For logistic regression, the Wald chi-square value of the coefficient estimate.

p-value

Probability of the test_statistic. Used to analyze the significance of specific attributes in the model.

VIF

Variance Inflation Factor. The value is zero for the intercept. For logistic regression, VIF is null.

std_coefficient

Standardized estimate of the coefficient.

lower_coeff_limit

Lower confidence bound of the coefficient.

upper_coeff_limit

Upper confidence bound of the coefficient.

exp_coefficient

Exponentiated coefficient for logistic regression. For linear regression, exp_coefficient is null.

exp_lower_coeff_limit

Exponentiated coefficient for lower confidence bound of the coefficient for logistic regression. For linear regression, exp_lower_coeff_limit is null.

exp_upper_coeff_limit

Exponentiated coefficient for upper confidence bound of the coefficient for logistic regression. For linear regression, exp_lower_coeff_limit is null.


Usage Notes

Not all statistics are necessarily returned for each coefficient. Statistics will be null if:

  • They do not apply to the mining function. For example, exp_coefficient does not apply to linear regression.

  • They cannot be computed from a theoretical standpoint. For example, when ridge regression is enabled, the coefficient values are returned with no statistics except VIF if it is enabled. (For information on ridge regression, see Table 44-11, "GLM Settings".)

  • They cannot be computed because of limitations in system resources.

  • Their values would be infinity.

Examples

The following example returns some of the model details for the GLM regression model GLMR_SH_Regr_sample, which was created by the sample program dmglrdem.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

SQL> SELECT *
   FROM (SELECT class, attribute_name, attribute_value, coefficient, std_error
              FROM TABLE(dbms_data_mining.get_model_details_glm(
                           'GLMR_SH_Regr_sample'))
            ORDER BY class, attribute_name, attribute_value)
     WHERE ROWNUM < 11;

CLASS     ATTRIBUTE_NAME      ATTRIBUTE_VALUE  COEFFICIENT  STD_ERROR
--------- ------------------  ---------------- -----------  ----------
          AFFINITY_CARD                         -.60686139   .531250033
          BULK_PACK_DISKETTES                   -1.9729645   .924531227
          COUNTRY_NAME        Argentina         -1.3340963  1.1942193
          COUNTRY_NAME        Australia           -.340504  5.13688361
          COUNTRY_NAME        Brazil             5.3855269  1.93197882
          COUNTRY_NAME        Canada            4.13393291  2.41283125
          COUNTRY_NAME        China              .74409259  3.59923638
          COUNTRY_NAME        Denmark           -2.5287879  3.18569293
          COUNTRY_NAME        France            -1.0908689  7.18471003
          COUNTRY_NAME        Germany           -1.7472166  2.53689456

GET_MODEL_DETAILS_GLOBAL Function

This table function returns statistics about the model as a whole. Global details are available for GLM and for association rules.

Separate global details are returned for linear and logistic regression. When ridge regression is enabled, fewer global details are returned. For information about ridge, see Table 44-11, "GLM Settings".

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_GLOBAL (
      model_name     IN  VARCHAR2)
RETURN DM_MODEL_GLOBAL_DETAILS PIPELINED;

Parameters

Table 44-54 GET_MODEL_DETAILS_GLOBAL Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-55 GET_MODEL_DETAILS_GLOBAL Function Return Values

Return ValueDescription

DM_MODEL_GLOBAL_DETAILS

A collection of rows of type DM_MODEL_GLOBAL_DETAIL. The rows have the following columns:

(global_detail_name   VARCHAR2(30),
 global_detail_value   NUMBER)

Global Details for GLM: Linear Regression

Table 44-56 Global Details for Linear Regression

GLOBAL_DETAIL_NAMEDescription

MODEL_DF

Model degrees of freedom

MODEL_SUM_SQUARES

Model sum of squares

MODEL_MEAN_SQUARE

Model mean square

F_VALUE

Model F value statistic

MODEL_F_P_VALUE

Model F value probability

ERROR_DF

Error degrees of freedom

ERROR_SUM_SQUARES

Error sum of squares

ERROR_MEAN_SQUARE

Error mean square

CORRECTED_TOTAL_DF

Corrected total degrees of freedom

CORRECTED_TOT_SS

Corrected total sum of squares

ROOT_MEAN_SQ

Root mean square error

DEPENDENT_MEAN

Dependent mean

COEFF_VAR

Coefficient of variation

R_SQ

R-Square

ADJUSTED_R_SQUARE

Adjusted R-Square

AIC

Akaike's information criterion

SBIC

Schwarz's Bayesian information criterion

GMSEP

Estimated mean square error of the prediction, assuming multivariate normality

HOCKING_SP

Hocking Sp statistic

J_P

JP statistic (the final prediction error)

NUM_PARAMS

Number of parameters (the number of coefficients, including the intercept)

NUM_ROWS

Number of rows

MODEL_CONVERGED

Whether or not the model converged. Value is 1 if it converged, or 0 if it did not converge

VALID_COVARIANCE_MATRIX

Valid covariance matrix. Value is 1 if the covariance matrix was computed, or 0 if it was not computed


Global Details for GLM: Logistic Regression

Table 44-57 Global Details for Logistic Regression

GLOBAL_DETAIL_NAMEDescription

AIC_INTERCEPT

Akaike's criterion for the fit of the intercept only model

AIC_MODEL

Akaike's criterion for the fit of the intercept and the covariates (predictors) mode

SC_INTERCEPT

Schwarz's Criterion for the fit of the intercept only model

SC_MODEL

Schwarz's Criterion for the fit of the intercept and the covariates (predictors) model

NEG2_LL_INTERCEPT

-2 log likelihood of the intercept only model

NEG2_LL_MODEL

-2 log likelihood of the model

LR_DF

Likelihood ratio degrees of freedom

LR_CHI_SQ

Likelihood ratio chi-square value

LR_CHI_SQ_P_VALUE

Likelihood ratio chi-square probability value

PSEUDO_R_SQ_CS

Pseudo R-square Cox and Snell

PSEUDO_R_SQ_N

Pseudo R-square Nagelkerke

DEPENDENT_MEAN

Dependent mean

PCT_CORRECT

Percent of correct predictions

PCT_INCORRECT

Percent of incorrectly predicted rows

PCT_TIED

Percent of cases where probability for both cases is the same

NUM_PARAMS

Number of parameters (the number of coefficients, including the intercept)

NUM_ROWS

Number of rows

MODEL_CONVERGED

Whether or not the model converged. Value is 1if it converged, or 0 if it did not converge.

VALID_COVARIANCE_MATRIX

Valid covariance matrix. Value is 1 if the covariance matrix was computed, or 0 if the covariance matrix not computed


Global Detail for Association Rules

A single global detail is produced by an Association model.

Table 44-58 Global Detail for Association Rules

GLOBAL_DETAIL_NAMEDescription

RULE_COUNT

The number of association rules in the model.


Examples

The following example returns the global model details for the GLM regression model GLMR_SH_Regr_sample, which was created by the sample program dmglrdem.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

SELECT *
  FROM TABLE(dbms_data_mining.get_model_details_global(
              'GLMR_SH_Regr_sample'))
ORDER BY global_detail_name;
GLOBAL_DETAIL_NAME             GLOBAL_DETAIL_VALUE
------------------------------ -------------------
ADJUSTED_R_SQUARE                       .731412557
AIC                                       5931.814
COEFF_VAR                               18.1711243
CORRECTED_TOTAL_DF                            1499
CORRECTED_TOT_SS                        278740.504
DEPENDENT_MEAN  €˙                            38.892
ERROR_DF                                      1433
ERROR_MEAN_SQUARE                       49.9440956
ERROR_SUM_SQUARES                       71569.8891
F_VALUE                                 62.8492452
GMSEP                                    52.280819
HOCKING_SP                              .034877162
J_P                                     52.1749319
MODEL_CONVERGED                                  1
MODEL_DF                                        66
MODEL_F_P_VALUE                                  0
MODEL_MEAN_SQUARE                       3138.94871
MODEL_SUM_SQUARES                       207170.615
NUM_PARAMS                                      67
NUM_ROWS                                      1500
ROOT_MEAN_SQ                            7.06711367
R_SQ                                    .743238288
SBIC                                    6287.79977
VALID_COVARIANCE_MATRIX                          1

GET_MODEL_DETAILS_KM Function

This table function returns a set of rows that provide the details of a k-Means clustering model.

You can provide input to GET_MODEL_DETAILS_KM to request specific information about the model, thus improving the performance of the query. If you do not specify filtering parameters, GET_MODEL_DETAILS_KM returns all the information about the model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_KM (
          model_name          VARCHAR2,
          cluster_id          NUMBER    DEFAULT NULL,
          attribute           VARCHAR2  DEFAULT NULL,
          centroid            NUMBER    DEFAULT 1, 
          histogram           NUMBER    DEFAULT 1, 
          rules               NUMBER    DEFAULT 2,
          attribute_subname   VARCHAR2  DEFAULT NULL)
RETURN DM_CLUSTERS PIPELINED;

Parameters

Table 44-59 GET_MODEL_DETAILS_KM Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

cluster_id

The ID of a cluster in the model. When a valid cluster ID is specified, only the details of this cluster are returned. Otherwise the details for all clusters are returned.

attribute

The name of an attribute. When a valid attribute name is specified, only the details of this attribute are returned. Otherwise the details for all attributes are returned

centroid

This parameter accepts the following values:

  • 1 — Details about centroids are returned (default)

  • 0 — Details about centroids are not returned

histogram

This parameter accepts the following values:

  • 1 — Details about histograms are returned (default)

  • 0 — Details about histograms are not returned

rules

This parameter accepts the following values:

  • 2 — Details about rules are returned (default)

  • 1 — Rule summaries are returned

  • 0 — No information about rules is returned


Return Values

Table 44-60 GET_MODEL_DETAILS_KM Function Return Values

Return ValueDescription

DM_CLUSTERS

Represents a set of rows of type DM_CLUSTER. The rows have the following columns:

(id                   NUMBER,
 cluster_id           VARCHAR2(4000),
 record_count         NUMBER,
 parent               NUMBER,
 tree_level           NUMBER,
 dispersion           NUMBER,
 split_predicate      DM_PREDICATES,
 child                DM_CHILDREN,
 centroid             DM_CENTROIDS,
 histogram            DM_HISTOGRAMS,
 rule                 DM_RULE)

The split_predicate column of DM_CLUSTER returns a nested table of type DM_PREDICATES. Each row, of type DM_PREDICATE, has the following columns:

     (attribute_name           VARCHAR2(4000),
      attribute_subname        VARCHAR2(4000),
      conditional_operator     CHAR(2) /*=,<>,<,>,<=,>=*/,
      attribute_num_value      NUMBER,
      attribute_str_value      VARCHAR2(4000),
      attribute_support        NUMBER,
      attribute_confidence     NUMBER)

The child column of DM_CLUSTER returns a nested table of type DM_CHILDREN. The rows, of type DM_CHILD, have a single column of type NUMBER, which contains the identifiers of each child.


The centroid column of DM_CLUSTER returns a nested table of type DM_CENTROIDS. The rows, of type DM_CENTROID, have the following columns:

     (attribute_name    VARCHAR2(4000),
      attribute_subname  VARCHAR2(4000),
      mean               NUMBER,
      mode_value         VARCHAR2(4000),
      variance           NUMBER)

The histogram column of DM_CLUSTER returns a nested table of type DM_HISTOGRAMS. The rows, of type DM_HISTOGRAM_BIN, have the following columns:

     (attribute_name    VARCHAR2(4000),
      attribute_subname  VARCHAR2(4000),
      bin_id             NUMBER,
      lower_bound        NUMBER,
      upper_bound        NUMBER,
      label              VARCHAR2(4000),
      count              NUMBER)

The rule column of DM_CLUSTER returns a single row of type DM_RULE. The columns are:

     (rule_id            INTEGER,
      antecedent         DM_PREDICATES,
      consequent         DM_PREDICATES,
      rule_support       NUMBER,
      rule_confidence    NUMBER,
      rule_lift          NUMBER,
      antecedent_support NUMBER,
      consequent_support NUMBER,
      number_of_items    INTEGER)

The antecedent and consequent columns of DM_RULE each return nested tables of type DM_PREDICATES. The rows, of type DM_PREDICATE, have the following columns:

          (attribute_name           VARCHAR2(4000),
           attribute_subname  VARCHAR2(4000),
           conditional_operator     CHAR(2)/*=,<>,<,>,<=,>=*/,
           attribute_num_value      NUMBER,
           attribute_str_value      VARCHAR2(4000),
           attribute_support        NUMBER,
           attribute_confidence     NUMBER)

Usage Notes

The table function pipes out rows of type DM_CLUSTERS. For information on Data Mining data types and piped output from table functions, see "Data Types".

Examples

The following example returns model details for the k-Means clustering model KM_SH_Clus_sample, which was created by the sample program dmkmdemo.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

SELECT T.id           clu_id,
       T.record_count rec_cnt,
       T.parent       parent,
       T.tree_level   tree_level,
       T.dispersion   dispersion
  FROM (SELECT *
          FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM(
                     'KM_SH_Clus_sample'))
        ORDER BY id) T
 WHERE ROWNUM < 6;  
 
    CLU_ID    REC_CNT     PARENT TREE_LEVEL DISPERSION
---------- ---------- ---------- ---------- ----------
         1       1500                     1  5.9152211
         2        638          1          2 3.98458982
         3        862          1          2 5.83732097
         4        376          3          3 5.05192137
         5        486          3          3 5.42901522

GET_MODEL_DETAILS_NB Function

This table function returns a set of rows that provide the details of a Naive Bayes model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_NB (
   model_name      IN       VARCHAR2)
 RETURN DM_NB_DETAILS PIPELINED;

Parameters

Table 44-61 GET_MODEL_DETAILS_NB Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-62 GET_MODEL_DETAILS_NB Function Return Values

Return ValueDescription

DM_NB_DETAILS

Represents a set of rows of type DM_NB_DETAIL. The rows have the following columns:

(target_attribute_name          VARCHAR2(30),
 target_attribute_str_value     VARCHAR2(4000),
 target_attribute_num_value     NUMBER,
 prior_probability              NUMBER,
 conditionals                   DM_CONDITIONALS)

The conditionals column of DM_NB_DETAIL returns a nested table of type DM_CONDITIONALS. The rows, of type DM_CONDITIONAL, have the following columns:

     (attribute_name             VARCHAR2(4000),
      attribute_subname          VARCHAR2(4000),
      attribute_str_value        VARCHAR2(4000),
      attribute_num_value        NUMBER,
      conditional_probability    NUMBER)

Usage Notes

The table function pipes out rows of type DM_NB_DETAILS. For information on Data Mining data types and piped output from table functions, see "Data Types".

Examples

The following query is from the sample program dmnbdemo.sql. It returns model details about the model NB_SH_Clas_sample. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

The query creates labels from the bin boundary tables that were used to bin the training data. It replaces the attribute values with the labels. For numeric bins, the labels are (lower_boundary,upper_boundary]; for categorical bins, the label matches the value it represents. (This method of categorical label representation will only work for cases where one value corresponds to one bin.) The target was not binned.

WITH
    bin_label_view AS (
    SELECT col, bin, (DECODE(bin,'1','[','(') || lv || ',' || val || ']') label
      FROM (SELECT col,
                   bin,
                   LAST_VALUE(val) OVER (
                   PARTITION BY col ORDER BY val
                   ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) lv,
                   val
             FROM nb_sh_sample_num)
   UNION ALL
   SELECT col, bin, val label
     FROM nb_sh_sample_cat
   ),
   model_details AS (
   SELECT T.target_attribute_name                                        tname,
          TO_CHAR(
          NVL(T.target_attribute_num_value,T.target_attribute_str_value)) tval,
          C.attribute_name                                               pname,
          NVL(L.label, NVL(C.attribute_str_value, C.attribute_num_value)) pval,
          T.prior_probability                                           priorp,
          C.conditional_probability                                      condp
     FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NB('NB_SH_Clas_sample')) T,
          TABLE(T.conditionals) C,
          bin_label_view L
    WHERE C.attribute_name = L.col (+) AND
          (NVL(C.attribute_str_value,C.attribute_num_value) = L.bin(+))
   ORDER BY 1,2,3,4,5,6
   )
   SELECT tname, tval, pname, pval, priorp, condp
     FROM model_details
    WHERE ROWNUM < 11;

TNAME          TVAL PNAME                     PVAL           PRIORP   CONDP
-------------- ---- ------------------------- ------------- ------- -------
AFFINITY_CARD  0    AGE                       (24,30]         .6500   .1714
AFFINITY_CARD  0    AGE                       (30,35]         .6500   .1509
AFFINITY_CARD  0    AGE                       (35,40]         .6500   .1125
AFFINITY_CARD  0    AGE                       (40,46]         .6500   .1134
AFFINITY_CARD  0    AGE                       (46,53]         .6500   .1071
AFFINITY_CARD  0    AGE                       (53,90]         .6500   .1312
AFFINITY_CARD  0    AGE                       [17,24]         .6500   .2134
AFFINITY_CARD  0    BOOKKEEPING_APPLICATION   0               .6500   .1500
AFFINITY_CARD  0    BOOKKEEPING_APPLICATION   1               .6500   .8500
AFFINITY_CARD  0    BULK_PACK_DISKETTES       0               .6500   .3670

GET_MODEL_DETAILS_NMF Function

This table function returns a set of rows that provide the details of a Non-Negative Matrix Factorization model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF (
   model_name        IN        VARCHAR2)
 RETURN DM_NMF_FEATURE_SET PIPELINED;

Parameters

Table 44-63 GET_MODEL_DETAILS_NMF Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-64 GET_MODEL_DETAILS_NMF Function Return Values

Return ValueDescription

DM_NMF_FEATURE_SET

Represents a set of rows of DM_NMF_FEATURE. The rows have the following columns:

(feature_id          NUMBER,
 mapped_feature_id   VARCHAR2(4000),
 attribute_set       DM_NMF_ATTRIBUTE_SET)

The attribute_set column of DM_NMF_FEATURE returns a nested table of type DM_NMF_ATTRIBUTE_SET. The rows, of type DM_NMF_ATTRIBUTE, have the following columns:

     (attribute_name    VARCHAR2(4000),
      attribute_subname  VARCHAR2(4000),
      attribute_value    VARCHAR2(4000),
      coefficient        NUMBER)

Usage Notes

The table function pipes out rows of type DM_NMF_FEATURE_SET. For information on Data Mining data types and piped output from table functions, see "Data Types".

Examples

The following example returns model details for the feature extraction model NMF_SH_Sample, which was created by the sample program dmnmdemo.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

SELECT * FROM (
SELECT F.feature_id,
       A.attribute_name,
       A.attribute_value,
       A.coefficient
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_NMF('NMF_SH_Sample')) F,
       TABLE(F.attribute_set) A
ORDER BY feature_id,attribute_name,attribute_value
) WHERE ROWNUM < 11; 
 
FEATURE_ID ATTRIBUTE_NAME          ATTRIBUTE_VALUE          COEFFICIENT
--------- -----------------------  ---------------- -------------------
        1 AFFINITY_CARD                                 .051208078859308
        1 AGE                                          .0390513260041573
        1 BOOKKEEPING_APPLICATION                      .0512734004239326
        1 BULK_PACK_DISKETTES                           .232471260895683
        1 COUNTRY_NAME             Argentina          .00766817464479959
        1 COUNTRY_NAME             Australia         .000157637881096675
        1 COUNTRY_NAME             Brazil              .0031409632415604
        1 COUNTRY_NAME             Canada             .00144213099311427
        1 COUNTRY_NAME             China             .000102279310968754
        1 COUNTRY_NAME             Denmark           .000242424084307513

GET_MODEL_DETAILS_OC Function

This table function returns a set of rows that provide the details of an O-Cluster clustering model. The rows are an enumeration of the clustering patterns generated during the creation of the model.

You can provide input to GET_MODEL_DETAILS_OC to request specific information about the model, thus improving the performance of the query. If you do not specify filtering parameters, GET_MODEL_DETAILS_OC returns all the information about the model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_OC (
          model_name         VARCHAR2,
          cluster_id         NUMBER    DEFAULT NULL,
          attribute          VARCHAR2  DEFAULT NULL,
          centroid           NUMBER    DEFAULT 1, 
          histogram          NUMBER    DEFAULT 1, 
          rules              NUMBER    DEFAULT 2)
RETURN DM_CLUSTERS PIPELINED;

Parameters

Table 44-65 GET_MODEL_DETAILS_OC Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

cluster_id

The ID of a cluster in the model. When a valid cluster ID is specified, only the details of this cluster are returned. Otherwise the details for all clusters are returned.

attribute

The name of an attribute. When a valid attribute name is specified, only the details of this attribute are returned. Otherwise the details for all attributes are returned

centroid

This parameter accepts the following values:

  • 1 — Details about centroids are returned (default)

  • 0 — Details about centroids are not returned

histogram

This parameter accepts the following values:

  • 1 — Details about histograms are returned (default)

  • 0 — Details about histograms are not returned

rules

This parameter accepts the following values:

  • 2 — Details about rules are returned (default)

  • 1 — Rule summaries are returned

  • 0 — No information about rules is returned


Return Values

Table 44-66 GET_MODEL_DETAILS_OC Function Return Values

Return ValueDescription

DM_CLUSTERS

Represents a set of rows of type DM_CLUSTER. The rows have the following columns:

(id               NUMBER,
 cluster_id       VARCHAR2(4000),
 record_count     NUMBER,
 parent           NUMBER,
 tree_level       NUMBER,
 dispersion       NUMBER,
 split_predicate  DM_PREDICATES,
 child            DM_CHILDREN,
 centroid         DM_CENTROIDS,
 histogram        DM_HISTOGRAMS,
 rule             DM_RULE)

The split_predicate column of DM_CLUSTER returns a nested table of type DM_PREDICATES. Each row, of type DM_PREDICATE, has the following columns:

     (attribute_name           VARCHAR2(4000),
      attrib€˙ute_subname        VARCHAR2(4000),
      conditional_operator     CHAR(2) /*=,<>,<,>,<=,>=*/,
      attribute_num_value      NUMBER,
      attribute_str_value      VARCHAR2(4000),
      attribute_support        NUMBER,
      attribute_confidence     NUMBER)

The child column of DM_CLUSTER returns a nested table of type DM_CHILDREN. The rows, of type DM_CHILD, have a single column of type NUMBER, which contains the identifiers of each child.


The centroid column of DM_CLUSTER returns a nested table of type DM_CENTROIDS. The rows, of type DM_CENTROID, have the following columns:

      (attribute_name    VARCHAR2(4000),
       attribute_subname  VARCHAR2(4000),
       mean               NUMBER,
       mode_value         VARCHAR2(4000),
       variance           NUMBER)

The histogram column of DM_CLUSTER returns a nested table of type DM_HISTOGRAMS. The rows, of type DM_HISTOGRAM_BIN, have the following columns:

    (attribute_name    VARCHAR2(4000),
     attribute_subname  VARCHAR2(4000),
     bin_id             NUMBER,
     lower_bound        NUMBER,
     upper_bound        NUMBER,
     label              VARCHAR2(4000),
     count              NUMBER)

The rule column of DM_CLUSTER returns a single row of type DM_RULE. The columns are:

     (rule_id            INTEGER,
      antecedent         DM_PREDICATES,
      consequent         DM_PREDICATES,
      rule_support       NUMBER,
      rule_confidence    NUMBER,
      rule_lift          NUMBER,
      antecedent_support NUMBER,
      consequent_support NUMBER,
      number_of_items    INTEGER)

The antecedent and consequent columns each return nested tables of type DM_PREDICATES.The rows, of type DM_PREDICATE, have the following columns:

          (attribute_name           VARCHAR2(4000),
           attribute_subname        VARCHAR2(4000),
           conditional_operator     CHAR(2)/*=,<>,<,>,<=,>=*/,
           attribute_num_value      NUMBER,
           attribute_str_value      VARCHAR2(4000),
           attribute_support        NUMBER,
           attribute_confidence     NUMBER)

Usage Notes

The table function pipes out rows of type DM_CLUSTER. For information about Data Mining data types and piped output from table functions, see "Data Types".

Examples

The following example returns model details for the clustering model OC_SH_Clus_sample, which was created by the sample program dmocdemo.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

For each cluster in this example, the split predicate indicates the attribute and the condition used to assign records to the cluster's children during model build. It provides an important piece of information on how the population within a cluster can be divided up into two smaller clusters.

SELECT clu_id, attribute_name, op, s_value
     FROM (SELECT a.id clu_id, sp.attribute_name, sp.conditional_operator op,
                  sp.attribute_str_value s_value
             FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_OC(
                    'OC_SH_Clus_sample')) a,
                  TABLE(a.split_predicate) sp
           ORDER BY a.id, op, s_value)
     WHERE ROWNUM < 11;
 
     CLU_ID ATTRIBUTE_NAME       OP S_VALUE
----------- -------------------- ---------------------------------
          1 OCCUPATION           IN ?
          1 OCCUPATION           IN Armed-F
          1 OCCUPATION           IN Cleric.
          1 OCCUPATION           IN Crafts
          2 OCCUPATION           IN ?
          2 OCCUPATION           IN Armed-F
          2 OCCUPATION           IN Cleric.
          3 OCCUPATION           IN Exec.
          3 OCCUPATION           IN Farming
          3 OCCUPATION           IN Handler

GET_MODEL_DETAILS_SVM Function

This table function returns a set of rows that provide the details of a linear Support Vector Machine (SVM) model. If invoked for nonlinear SVM, it returns ORA-40215.

In linear SVM models, only nonzero coefficients are stored. This reduces storage and speeds up model loading. As a result, if an attribute is missing in the coefficient list returned by GET_MODEL_DETAILS_SVM, then the coefficient of this attribute should be interpreted as zero.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM (
        model_name        VARCHAR2,
        reverse_coef      NUMBER DEFAULT 0)
 RETURN DM_SVM_LINEAR_COEFF_SET PIPELINED;

Parameters

Table 44-67 GET_MODEL_DETAILS_SVM Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.

reverse_coef

Whether or not GET_MODEL_DETAILS_SVM should transform the attribute coefficients using the original attribute transformations.

When reverse_coef is set to 0 (default), GET_MODEL_DETAILS_SVM returns the coefficients directly from the model without applying transformations.

When reverse_coef is set to 1, GET_MODEL_DETAILS_SVM transforms the coefficients and bias by applying the normalization shifts and scales that were generated using automatic data preparation.

See Usage Note 4.


Return Values

Table 44-68 GET_MODEL_DETAILS_SVM Function Return Values

Return ValueDescription

DM_SVM_LINEAR_COEFF_SET

Represents a set of rows of type DM_SVM_LINEAR_COEFF. The rows have the following columns:

(class            VARCHAR2(4000),
 attribute_set    DM_SVM_ATTRIBUTE_SET)

The attribute_set column returns a nested table of type DM_SVM_ATTRIBUTE_SET. The rows, of type DM_SVM_ATTRIBUTE, have the following columns:

     (attribute_name      VARCHAR2(4000),
      attribute_subname   VARCHAR2(4000),
      attribute_value     VARCHAR2(4000),
      coefficient         NUMBER)

See Usage Notes.


Usage Notes

  1. This table function pipes out rows of type DM_SVM_LINEAR_COEFF. For information on Data Mining data types and piped output from table functions, see "Data Types".

  2. The class column of DM_SVM_LINEAR_COEFF contains classification target values. For SVM regression models, class is null. For each classification target value, a set of coefficients is returned. For binary classification, one-class classification, and regression models, only a single set of coefficients is returned.

  3. The attribute_value column in DM_SVM_ATTRIBUTE_SET is used for categorical attributes.

  4. GET_MODEL_DETAILS functions preserve model transparency by automatically reversing the transformations applied during the build process. Thus the attributes returned in the model details are the original attributes (or a close approximation of the original attributes) used to build the model.

    The coefficients are related to the transformed, not the original, attributes. When returned directly with the model details, the coefficients may not provide meaningful information. If you want GET_MODEL_DETAILS_SVM to transform the coefficients such that they relate to the original attributes, set the reverse_coef parameter to 1.

Examples

The following example returns model details for the SVM classification model SVMC_SH_Clas_sample, which was created by the sample program dmsvcdem.sql. For information about the sample programs, see Oracle Data Mining Administrator's Guide.

WITH
  mod_dtls AS (
  SELECT *
    FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_SVM('SVMC_SH_Clas_sample'))
  ),
  model_details AS (
  SELECT D.class, A.attribute_name, A.attribute_value, A.coefficient
    FROM mod_dtls D,
            TABLE(D.attribute_set) A
    ORDER BY D.class, ABS(A.coefficient) DESC
  )
  SELECT class, attribute_name aname, attribute_value aval, coefficient coeff
    FROM model_details
    WHERE ROWNUM < 11;
 
CLASS      ANAME                     AVAL                      COEFF
---------- ------------------------- ------------------------- -----
1                                                              -2.85
1          BOOKKEEPING_APPLICATION                              1.11
1          OCCUPATION                Other                      -.94
1          HOUSEHOLD_SIZE            4-5                         .88
1          CUST_MARITAL_STATUS       Married                     .82
1          YRS_RESIDENCE                                         .76
1          HOUSEHOLD_SIZE            6-8                        -.74
1          OCCUPATION                Exec.                       .71
1          EDUCATION                 11th                       -.71
1          EDUCATION                 Masters                     .63

GET_MODEL_DETAILS_XML Function

This function returns an XML object that provides the details of a Decision Tree model.

Syntax

DBMS_DATA_MINING.GET_MODEL_DETAILS_XML (
         model_name      IN       VARCHAR2)
   RETURN XMLTYPE;

Parameters

Table 44-69 GET_MODEL_DETAILS_XML Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-70 GET_MODEL_DETAILS_XML Function Return Value

Return ValueDescription

XMLTYPE

The XML definition for the decision tree model. See Chapter 260, "XMLTYPE" for details.

The XML conforms to the Data Mining Group Predictive Model Markup Language (PMML) version 2.1 specification. The specification is available at http://www.dmg.org.


Usage Notes

Special characters that cannot be displayed by Oracle XML are converted to '#'.

Examples

The following statements in SQL*Plus return the details of the decision tree model dt_sh_clas_sample. This model is created by the program dmdtdemo.sql, one of the sample data mining programs provided with Oracle Database Examples.

Note: The "&quot" characters you will see in the XML output are a result of SQL*Plus behavior. To display the XML in proper format, cut and past it into a file and open the file in a browser.

column dt_details format a320
SELECT 
 dbms_data_mining.get_model_details_xml('dt_sh_clas_sample') 
 AS DT_DETAILS
FROM dual;


DT_DETAILS
--------------------------------------------------------------------------------
<PMML version="2.1">
  <Header copyright="Copyright (c) 2004, Oracle Corporation. All rights
      reserved."/>
  <DataDictionary numberOfFields="9">
    <DataField name="AFFINITY_CARD" optype="categorical"/> 
    <DataField name="AGE" optype="continuous"/> 
    <DataField name="BOOKKEEPING_APPLICATION" optype="continuous"/>
    <DataField name="CUST_MARITAL_STATUS" optype="categorical"/>
    <DataField name="EDUCATION" optype="categorical"/> 
    <DataField name="HOUSEHOLD_SIZE" optype="categorical"/>
    <DataField name="OCCUPATION" optype="categorical"/>
    <DataField name="YRS_RESIDENCE" optype="continuous"/>
    <DataField name="Y_BOX_GAMES" optype="continuous"/>
  </DataDictionary>
  <TreeModel modelName="DT_SH_CLAS_SAMPLE" functionName="classification"
      splitCharacteristic="binarySplit">
    <Extension name="buildSettings">
      <Setting name="TREE_IMPURITY_METRIC" value="TREE_IMPURITY_GINI"/>
      <Setting name="TREE_TERM_MAX_DEPTH" value="7"/>
      <Setting name="TREE_TERM_MINPCT_NODE" value=".05"/>
      <Setting name="TREE_TERM_MINPCT_SPLIT" value=".1"/> 
      <Setting name="TREE_TERM_MINREC_NODE" value="10"/>
      <Setting name="TREE_TERM_MINREC_SPLIT" value="20"/>
      <costMatrix>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>0</predictedValue>
          <cost>0</cost>
        </costElement>
        <costElement>
          <actualValue>0</actualValue>
          <predictedValue>1</predictedValue>
          <cost>1</cost>
        </costElement>
        <costElement>
          <actualValue>1</actualValue>
          <predictedValue>0</predictedValue>
          <cost>8</cost> 
        </costElement>
        <costElement> 
          <actualValue>1</actualValue>
          <predictedValue>1</predictedValue> 
          <cost>0</cost> 
        </costElement>
      </costMatrix>
    </Extension>
    <MiningSchema>
      .
      .
      .
      .
      .
      . 
      </Node>
    </Node>
  </TreeModel>
</PMML> 

GET_MODEL_SETTINGS Function

The GET_MODEL_SETTINGS function was deprecated in Oracle Data Mining 11g Release 1 (11.1). It was replaced with the data dictionary view *_MINING_MODEL_SETTINGS. USER_, ALL_, and DBA_ versions of the view are available. See Oracle Data Mining Application Developer's Guide.

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

This table function returns the list of settings that were used to build the model.

Syntax

DBMS_DATA_MINING.GET_MODEL_SETTINGS(
   model_name           IN VARCHAR2)
 RETURN DM_MODEL_SETTINGS PIPELINED;

Parameters

Table 44-71 GET_MODEL_SETTINGS Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-72 GET_MODEL_SETTINGS Function Return Values

Return ValueDescription

DM_MODEL_SETTINGS

Represents a set of rows of type DM_MODEL_SETTING. The rows have the following columns:

(setting_name    VARCHAR2(30),
setting_value    VARCHAR2(128))

Usage Notes

The table function pipes out rows of type DM_MODEL_SETTING. For information about Data Mining data types and piped output from table functions, see "Data Types".

You can use this table function to determine the settings that were used to build the model. This is purely for informational purposes only — you cannot alter the model to adopt new settings.

Examples

Assume that you have built a classification model census_model using the Naive Bayes algorithm. You can retrieve the model settings using Oracle SQL as follows:

SELECT setting_name, setting_value
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SETTINGS('census_model'));

GET_MODEL_SIGNATURE Function

The GET_MODEL_SIGNATURE function was deprecated in Oracle Data Mining 11g Release 1 (11.1). It was replaced with the data dictionary view *_MINING_MODEL_ATTRIBUTES. USER_, ALL_, and DBA_ versions of the view are available. See Oracle Data Mining Application Developer's Guide.

Oracle recommends that you do not use deprecated procedures in new applications. Support for deprecated features is for backward compatibility only.

This table function returns the model signature, which lists the column attributes used to build the model and which should be present in the scoring data.

The case identifier is not considered a mining attribute. For classification and regression models, the target attribute is also not considered part of the model signature.


See Also:

Instead of using the GET_MODEL_SIGNATURE function, you can query the data dictionary view, ALL_MINING_MODEL_ATTRIBUTES. See the Oracle Data Mining Application Developer's Guide.

Syntax

DBMS_DATA_MINING.GET_MODEL_SIGNATURE(
  model_name           IN VARCHAR2)
RETURN DM_MODEL_SIGNATURE PIPELINED;

Parameters

Table 44-73 GET_MODEL_SIGNATURE Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-74 GET_MODEL_SIGNATURE Function Return Values

Return ValueDescription

DM_MODEL_SIGNATURE

Represents a set of rows of type DM_MODEL_SIGNATURE_ATTRIBUTE. The rows have the following columns:

(attribute_name      VARCHAR2(30),
 attribute_type      VARCHAR2(106))

Usage Notes

This table function pipes out rows of type DM_MODEL_SIGNATURE. For information on Data Mining data types and piped output from table functions, see "Data Types".

Examples

Assume that you have built a classification model census_model using the Naive Bayes algorithm. You can retrieve the model details using Oracle SQL as follows:

SELECT attribute_name, attribute_type
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_SIGNATURE('census_model');

GET_MODEL_TRANSFORMATIONS Function

This function returns the transformation expressions embedded in the specified model.

Syntax

DBMS_DATA_MINING.GET_MODEL_TRANSFORMATIONS (
      model_name     IN VARCHAR2)
RETURN DM_TRANSFORMS PIPELINED;

Parameters

Table 44-75 GET_MODEL_TRANSFORMATIONS Function Parameters

ParameterDescription

model_name

Name of the model in the form [schema_name.]model_name. If you do not specify a schema, your own schema is used.


Return Values

Table 44-76 GET_MODEL_TRANSFORMATIONS Function Return Value

Return ValueDescription

DM_TRANSFORMS

The transformation expressions embedded in model_name.

The DM_TRANSFORMS type is a table of DM_TRANSFORM objects. Each DM_TRANSFORM has these fields:

attribute_name       VARCHAR2(4000)
attribute_subname    VARCHAR2(4000)
expression           CLOB
reverse_expression   CLOB

Usage Notes

When Automatic Data Preparation (ADP) is enabled, both automatic and user-defined transformations may be associated with an attribute. In this case, the user-defined transformations are evaluated before the automatic transformations.

Examples

In this example, several columns in the SH.CUSTOMERS table are used to create a Naive Bayes model. A transformation expression is specified for one of the columns. The model does not use ADP.

CREATE OR REPLACE VIEW mining_data AS
   SELECT cust_id, cust_year_of_birth, cust_income_level,cust_credit_limit
   FROM sh.customers;

describe mining_data
 Name                                   Null?    Type
 -------------------------------------- -------- --------------------------
 CUST_ID                                NOT NULL NUMBER
 CUST_YEAR_OF_BIRTH                     NOT NULL NUMBER(4)
 CUST_INCOME_LEVEL                               VARCHAR2(30)
 CUST_CREDIT_LIMIT                               NUMBER
 
CREATE TABLE settings_nb(
      setting_name  VARCHAR2(30),
      setting_value VARCHAR2(30));
BEGIN
     INSERT INTO settings_nb (setting_name, setting_value) VALUES
           (dbms_data_mining.algo_name, dbms_data_mining.algo_naive_bayes);
     INSERT INTO settings_nb (setting_name, setting_value) VALUES
           (dbms_data_mining.prep_auto, dbms_data_mining.prep_auto_off);
     COMMIT;
END;
/
DECLARE
    mining_data_xforms   dbms_data_mining_transform.TRANSFORM_LIST;
  BEGIN
    dbms_data_mining_transform.SET_TRANSFORM (
         xform_list           =>  mining_data_xforms,
         attribute_name       => 'cust_year_of_birth',
         attribute_subname    =>  null,
         expression           => 'cust_year_of_birth + 10',
         reverse_expression   => 'cust_year_of_birth - 10');
    dbms_data_mining.CREATE_MODEL (
        model_name           =>  'new_model',
        mining_function      =>   dbms_data_mining.classification,
        data_table_name      =>  'mining_data',
        case_id_column_name  =>  'cust_id',
        target_column_name   =>  'cust_income_level',
        se