DBMS_XMLSAVE
provides XML to database-type functionality.
See Also:
Oracle XML DB Developer's GuideThis chapter contains the following topics:
Constants
Types
Table 129-1 Constants of DBMS_XMLSAVE
Constant | Description |
---|---|
|
The default tag name for the element corresponding to database records -- ROW |
|
Default date mask:'MM/dd/yyyy HH:mm:ss' |
|
Used to specify that when mapping XML elements to database entities; the XSU should be case sensitive. |
|
Used to specify that when mapping XML elements to database. entities the XSU should be case insensitive. |
Table 129-2 Types of DBMS_XMLSAVE
Type | Description |
---|---|
|
The type of the query context handle. The type of the query context handle. This the return type of NEWCONTEXT. |
Table 129-3 DBMS_XMLSAVE Package Subprograms
Method | Description |
---|---|
Clears the key column list. |
|
Clears the update column list. |
|
It closes/deallocates a particular save context. |
|
Deletes records specified by data from the XML document, from the table specified at the context creation time. |
|
Via its arguments, this method returns the thrown exception's error code and error message. |
|
Inserts the XML document into the table specified at the context creation time. |
|
Creates a save context, and returns the context handle. |
|
Tells the XSU that if an exception is raised, and is being thrown, the XSU should throw the very exception raised; rather then, wrapping it with an |
|
Removes the value of a top-level stylesheet parameter |
|
Changes the batch size used during DML operations. |
|
Sets the commit batch size. |
|
Sets the format of the generated dates in the XML document. |
|
The XSU does mapping of XML elements to database. |
|
This methods adds a column to the key column list. |
|
Tells the XSU whether to preserve whitespace or not. |
|
Names the tag used in the XML document to enclose the XML elements corresponding to database. |
|
This turns on or off escaping of XML tags in the case that the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier. |
|
Adds a column to the update column list. |
|
Registers a XSL transform to be applied to the XML to be saved. |
|
Sets the value of a top-level stylesheet parameter. |
|
Updates the table given the XML document. |
Clears the key column list.
PROCEDURE clearKeyColumnList( ctxHdl IN ctxType);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
Clears the update column list.
PROCEDURE clearUpdateColumnList( ctxHdl IN ctxType);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
Closes/deallocates a particular save context.
PROCEDURE closeContext( ctxHdl IN ctxType);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
Deletes records specified by data from the XML document from the table specified at the context creation time, and returns the number of rows deleted. The options are described in the following table.
Syntax | Description |
---|---|
FUNCTION deleteXML(
|
Uses a VARCHAR2 type for the xDoc parameter. |
FUNCTION deleteXML(
|
Uses a CLOB type for the xDoc parameter. |
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
xDoc |
(IN) |
String containing the XML document. |
Through its arguments, this method returns the thrown exception's error code and error message, SQL error code. This is to get around the fact that the JVM throws an exception on top of whatever exception was raised; thus, rendering PL/SQL unable to access the original exception.
PROCEDURE getExceptionContent( ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
errNo |
(IN) |
Error number. |
errMsg |
(IN) |
Error message. |
Inserts the XML document into the table specified at the context creation time, and returns the number of rows inserted. The options are described in the following table.
Syntax | Description |
---|---|
FUNCTION insertXML(
|
Passes in the xDoc parameter as a VARCHAR2 . |
FUNCTION insertXML(
|
Passes in the xDoc parameter as a CLOB . |
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
xDoc |
(IN) |
String containing the XML document. |
Creates a save context, and returns the context handle.
FUNCTION newContext( targetTable IN VARCHAR2) RETURN ctxType;
Parameter | IN / OUT | Description |
---|---|---|
targetTable |
(IN) |
The target table into which to load the XML document. |
Tells the XSU that if an exception is raised, and is being thrown, the XSU should throw the very exception raised; rather then, wrapping it with an OracleXMLSQLException
.
PROCEDURE propagateOriginalException( ctxHdl IN ctxType, flag IN BOOLEAN);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
flag |
(IN) |
Propagate the original exception? 0=FALSE , 1=TRUE . |
Removes the value of a top-level stylesheet parameter.
PROCEDURE removeXSLTParam( ctxHdl IN ctxType, name IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
name |
(IN) |
Parameter name. |
Changes the batch size used during DML operations. When performing inserts, updates or deletes, it is better to batch the operations so that they get executed in one shot rather than as separate statements. The flip side is that more memory is needed to buffer all the bind values. Note that when batching is used, a commit occurs only after a batch is executed. So if one of the statement inside a batch fails, the whole batch is rolled back. This is a small price to pay considering the performance gain; nevertheless, if this behavior is unacceptable, then set the batch size to 1.
PROCEDURE setBatchSize( ctxHdl IN ctxType, batchSize IN NUMBER);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
batchSize |
(IN) |
Batch size. |
Sets the commit batch size. The commit batch size refers to the number or records inserted after which a commit should follow. If batchSize
is less than 1
or the session is in "auto-commit" mode, using the XSU does not make any explicit commits. By default, commitBatch
is 0
.
PROCEDURE setCommitBatch( ctxHdl IN ctxType, batchSize IN NUMBER);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
batchSize |
(IN) |
Commit batch size. |
Sets the format of the generated dates in the XML document. The syntax of the date format patern, the date mask, should conform to the requirements of the class java.text.SimpleDateFormat
. Setting the mask to <code>null</code>
or an empty string unsets the date mask.
PROCEDURE setDateFormat( ctxHdl IN ctxType, mask IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
mask |
(IN) |
Syntax of the date format pattern.. |
The XSU does mapping of XML elements to db columns/attributes based on the element names (XML tags). This function tells the XSU to do this match case insensitive.
PROCEDURE setIgnoreCase( ctxHdl IN ctxType, flag IN NUMBER);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
flag |
(IN) |
Ignore tag case in the XML doc? 0=FALSE , 1=TRUE . |
This method adds a column to the "key column list". The value for the column cannot be NULL
. In case of update or delete, the columns in the key column list make up the WHERE
clause of the statement. The key columns list must be specified before updates can complete; this is optional for delete operations.
PROCEDURE setKeyColumn( ctxHdl IN ctxType, colName IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
colName |
(IN) |
Column to be added to the key column list; cannot be NULL . |
Tells the XSU whether or not to preserve whitespace.
PROCEDURE setPreserveWhitespace( ctxHdl IN ctxType, flag IN BOOLEAN := true);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
flag |
(IN) |
Should XSU preserve whitespace? |
Names the tag used in the XML document to enclose the XML elements corresponding to db. records.
PROCEDURE setRowTag( ctxHdl IN ctxType, tag IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
tag |
(IN) |
Tag name. |
Turns on or off escaping of XML tags in the case that the SQL object name, which is mapped to a XML identifier, is not a valid XML identifier.
PROCEDURE setSQLToXMLNameEscaping( ctxHdl IN ctxType, flag IN BOOLEAN := true);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
flag |
(IN) |
Turn on escaping? |
Adds a column to the update column list. In case of insert, the default is to insert values to all the columns in the table; on the other hand, in case of updates, the default is to only update the columns corresponding to the tags present in the ROW element of the XML document. When the update column list is specified, the columns making up this list alone will get updated or inserted into.
PROCEDURE setUpdateColumn( ctxHdl IN ctxType, colName IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
colName |
(IN) |
Column to be added to the update column list. |
Registers an XSL transform to be applied to the XML to be saved. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet, pass in null for the URI. The options are described in the following table.
Syntax | Description |
---|---|
PROCEDURE setXSLT(
|
Passes in the stylesheet through a URI. |
PROCEDURE setXSLT(
|
Passes in the stylesheet through a CLOB . |
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
uri |
(IN) |
URI to the stylesheet to register. |
ref |
(IN) |
URL for include, import, and external entities. |
stylesheet |
(IN) |
CLOB containing the stylesheet to register. |
Sets the value of a top-level stylesheet parameter. The parameter is expected to be a valid XPath expression; literal values would therefore have to be explicitly quoted.
PROCEDURE setXSLTParam( ctxHdl IN ctxType, name IN VARCHAR2, value IN VARCHAR2);
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
name |
(IN) |
Parameter name. |
value |
(IN) |
Parameter value as an XPath expression |
Updates the table specified at the context creation time with data from the XML document, and returns the number of rows updated. The options are described in the following table.
Syntax | Description |
---|---|
FUNCTION updateXML(
|
Passes in the xDoc parameter as a VARCHAR2 . |
FUNCTION updateXML(
|
Passes in the xDoc parameter as a CLOB . |
Parameter | IN / OUT | Description |
---|---|---|
ctxHdl |
(IN) |
Context handle. |
xDoc |
(IN) |
String containing the XML document. |