Similar to java API of the XSU, the xmlgen provides calls for customizing the XSU's generation and loading of XML. These options are stored in static variables, and will manifest themselves for the duration of the session. Of course the user can call resetOptions() procedure to reset the options to their default values.
For a list of examples on using the xmlgen PL/SQL package, refer to
xmlgen
Example page.
Constants relevant to "DB to XML" functionality:
ALL_ROWS
The ALL_ROWS parameter is to indicate
that all rows are needed in the output.
SKIP_NONE
The SKIP_NONE parameter is to indicate
that no rows are to be skipped when generating the output.
NONE
The NONE specifies that the output
should not contain the DTD nor the XML Schema.
DTD
The DTD specifies that the DTD is
desired in the output.
NO_DTD
The NO_DTD specifies that the output
should not contain the DTD; legacy.
DEFAULT_ROWSETTAG
The DEFAULT_ROWSETTAG is the default
name for the document root tag. (the document name).
DEFAULT_ERRORTAG
The DEFAULT_ERRORTAG is the default
name for the ERROR tag, used when an error is raised.
DEFAULT_ROWIDATTR
The DEFAULT_ROWIDATTR is the default
name for the "num" attribute that is present in each
ROW element tag. The attribute, by
default, contains the count of the row.
Constants relevant to "XML to DB" functionality:
MATCH_CASE
The MATCH_CASE can be passed to the
setIgnoreTagCase call to force a match of the case of tag names in the document
with that of the column names during insert.
IGNORE_CASE
The IGNORE_CASE can be used in the
setIgnoreTagCase call to ignore the case of tag names during insert.
DEFAULT_DATE_FORMAT
The default date format used when
inserting date elements into database columns.
Constants relevant to "XML to DB" and "DB to XML" functionality:
DEFAULT_ROWTAG
The DEFAULT_ROWTAG is the
default tag name for the ROW element.
(ROW element separates each
row instance of the query).
Methods relevant to "DB to XML" functionality:
getXML
Creates the XML document given a
SQL query and an optional meta-type parameter.
getDTD
Creates the DTD given a SQL query
and optional with-version parameter.
setRowsetTag
Sets the name of the root tag in
the XML document.
setRowIdAttrName
Sets the name of the root tag in
the XML document.
setRowIdColumn
Sets the name of the attribute in
the row element that
setCollIdAttr
Sets the collection element's id-attribute
name.
useNullAttributeIndicator
Specifies that a null attribute should
be used to indicate if an element is null or not.
setErrorTag
Sets the error tag.
useUpperCaseTagNames
Forces the tag names to be in upper
case.
useLowerCaseTagNames
Forces the tag names to be in lower
case.
useDefaultCaseTagNames
Resets the case of the tag names
to be that specified in the query or database column definitions.
setMaxRows
Sets the maximum number of rows to
be used for generating the XML document.
setSkipRows
Sets the number of rows to skip in
the result before generating the XML document.
setStylesheetType
Sets the stylesheet type in the generated
XML document's PI (processing instructions).
setStylesheet
Sets the stylesheet header in the
generated XML document's PI (processing insturctions)..
setBindValue
Sets the bind value for a name bind
in the sql query.
clearBindValues
Clears all the bind values.
setMetaHeader
Sets the meta header.
setDataHeader
Sets the data header.
setRaiseException
Tells the XSU to raise an exception
in the case one is cought; rather then, generate an error doc.
propagateOriginalException
Tells the XSU to throw the original
raised exception; rather then, the XSU's wrapped version.
getExceptionContent
If raiseOriginalException is true,
this call returns the original exception's error code and error message.
setLobDuration
Sets the duration for the created
temp LOBs used to return the XML doc when the XSU is called from xmlgen.
Methods relevant to "XML to DB" functionality:
setIgnoreTagCase
Sets whether the case of the tag should
be ignored when comparing the element tags with column names during insert.
setDateFormat
Sets the date format to be used when
inserting date values. Default is DATE_FORMAT.
setUpdateColumn
Adds a column to the list of columns to
be updated.
clearUpdateColumnList
Removes all the columns from the list of
columns which are to be updated.
setKeyColumn
Adds a column to the list of key columns
(i.e. columns used to id. a row).
clearKeyColumnList
Clear the list of "key" columns.
setBatchSize
The number of inserts statements
that are pooled together (batched) and then executed together in a single trip.
setCommitBatch
The number of insert statement executions
committed at a time.
insertXML
Inserts a given XML document into
a specified table or view.
updateXML
Updates pecified table or view with
the data from an XML document.
deleteXML
Deletes records from a specified
table identified by data from an XML document..
Methods relevant to "XML to DB" and "DB to XML" functionality:
setRowTag
Sets the name of the tag for the
row element. Each row in the result is enclosed by a row element.
resetOptions
Resets all the options that were
set using the above functions.
Constants relevant to "DB to XML" functionality:
The ALL_ROWS constant can be passed to the setMaxRows() procedure. It specifies that all rows from the query should be included in the output. All rows of a query are included in the XML document by default.
The SKIP_NONE constant can be passed to the setSkipRows() procedure. This indicates that none of the rows must be skipped before creating the XML document from the result of the query. None of the rows in the result are skipped by default.
The NONE constant indicates that no meta data, like the DTD, should be included in the output. This can be passed to the getXML() function. This is the default value for the metaType parameter in the getXML() function.
The DTD constant indicates that the DTD should be included in the output. This can be passed to the getXML() function.
The DEFAULT_ROWSETTAG can be passed to the setRowsetTag() procedure and specifies the default tag name for the root of the document
The DEFAULT_ERRORTAG defines the default name for the error tag. The error tag is used when an error is raised during the XML creation. This can be passed to the setErrorTag() procedure.
DEFAULT_ROWIDATTR CONSTANT VARCHAR2(3) := 'NUM';
Constants relevant to "XML to DB" functionality:The DEFAULT_ROWIDATTR constant defines the default name for the rownum attribute in the ROW tag which indicates the row number in the output. This can be passed to the setRowIdAttrName() procedure.
The MATCH_CASE constant can be used in setIgnoreTagCase() procedure to force the matching of case of tag names with the column names during insert. By setting this a tag "EMPNO" would NOT match with a column defined as "empNo".
The IGNORE_CASE constant can be used in setIgnoreTagCase() procedure to ignore the matching of case of tag names with the column names during insert. By setting this a tag "EMPNO" would match with a column defined as "empNo".
Constants relevant to "XML to DB" and "DB to XML" functionality:The DEFAULT_DATE_FORMAT defines the default format used for inserting date values. This constant can be passed to the setDateFormat procedure.
The DEFAULT_ROWTAG defines the default name for the row separator tag. This can be passed to the setRowTag() procedure.
Methods relevant to "DB to XML" functionality:
Given a SQL query, this function generates the XML doc. If the optional parameter, metaType, is specified to be DTD, then the DTD is generated along with the XML doc. The result is returned in a temporary CLOB and it is up to the caller to close and free the temporary CLOB. (the temporary CLOB is by default allocated with session duration.)
Given a SQL query, this function generates the DTD. If the optional parameter, withVer, is specified to be true, then the version info is also generated along with the DTD. The result is returned in a temporary CLOB and it is up to the caller to close and free the temporary CLOB. (the temporary CLOB is by default allocated with session duration.)
This sets the name of the root tag which is the name of the document. The default name for the rowset tag is the same as DEFAULT_ROWSETTAG. A string of null value suppresses printing of this tag.
If the root tag is set to null, then the row tag name is used as the document root tag. In this case the document cannot contain more than one row or an error will be raised.
This sets the name of the rownum attribute included in the row element, to indicate the position of the row (this is the default behavior, see setRowIdColumn, next) in the result. A value of null or empty string suppresses the printing of this attribute.
This procedure sets the value of the rownum attribute to be that of the column (or alias) name specified in the parameter. The column (or alias) specified should be selected in the query and must be a scalar type. (such as number, date etc..). A null parameter value reverts it to the default setting which prints the position of the row in the result.
This procedure sets the collection element's id-attribute name. Passing in a null, or an empty string with inhibit the printing of the collection element's id-attribute, which is also the default behaviour.
This procedure specifies whether an attribute should be used in each element to indicate whether the element is null or not. The default is to omit the printing of the element that is null.
This sets the name of the error tag, used when an error document is generated. An error document is generated whenever an error occurs during the XML document creation. The error tag element contains the actual error message. No DTD is generated for an error document.
This forces all the tag names to be in upper case.
This forces all the tag names to be in lower case.
This resets the case setting for the tag names. The tag names use the default case, meaning they use the case as specified in the database definitions for columns and attributes.
This procedure sets the maximum number of rows of the query result to be used in generating the XML document.
The setSkipRows procedure sets the number of rows to be skipped in the query result before generating the XML document. If the number of rows skipped is greater than the number of rows returned by the query, then an empty document is generated.
The rownum attribute in the row element will include the actual row position which includes the rows skipped. Thus if you skip 10 rows and start the generation, the first row in the output will have the rownum attribute value set to 11.
This procedure sets the stylesheet type in the generated XML documents PI (processing instructions) header.
PROCEDURE setStylesheet( uri IN VARCHAR2);
This procedure sets the stylesheet URI in the generated XML documents PI.
PROCEDURE setStylesheet( uri IN VARCHAR2, type IN VARCHAR2);
This procedure sets the stylesheet URI and type in the generated XML documents PI.
Sets the value for a particular name bind that was specified in the SQL query.
Clears the bind values for all the name binds in the SQL query.
Sets the XML metadata header; which until unset is inserted at the begining of all XML metadata generated by this object (DTD or XMLSchema). To unset the header, call this function with null passed in for the "header" parameter.
Sets the XML data header, which until unset is inserted at the begining of all the XML data generated by this object. The docTag parameter specifies the tag name to be used to enclose the XML resulting from adding the "header" and the generated XML data. To unset the header, call this function with null passed in for the "header" parameter.
Tells the XSU to create NO error document when catching an exception; but rather, to propagates the exception.
Tell the XSU to throw the original exception rather than the OracleXMLSQLException wrapped around the original exception.
If raiseOriginalException is true, this call returns the original exception's error code and error message (i.e. sql error code).
Sets the duration for the created temp LOBs used to return the XML doc when the XSU is called from xmlgen; legal values are DBMS_LOB.CALL and DBMS_LOB.SESSION
Methods relevant to "XML to DB" functionality:
The setDateFormat takes in a format string for formatting date values before binding them for insert. The default format is DEFAULT_DATE_FORMAT. The format must match one of the valid date formats specified by Oracle.(See the Oracle SQL reference manual for a list of valid date formats).
The setIgnoreTagCase procedure sets whether the case of the tag names should be matched when comparing the element tags with the column names during insert. If this is set to MATCH_CASE, then an element with tag "EMPNO" would only match with a column of name "EMPNO" and not with a column of name "empno". Whereas, if this is set to IGNORE_CASE, then the tag "EMPNO" would match both "empno" and "EMPNO" columns.
Adds a column to the list of columns to be updated.
Removes all the columns from the list of columns which are to be updated.
Adds a column to the list of key columns (i.e. columns used to id. a row). It is these key columns that make up the "where ..." part of the update or delete SQL statement.
Clear the list of "key" columns.
The number of statements that are pooled together (batched) and then executed together in a single trip. For us, the values between 5 and 31 brought the best performance gains. The default is 17.
The number of statement executions committed at a time. By default this value is 51. Note that setting this value to 0 make it so that the XSU doesn't commit until the end of the session. Also note that if the commitBatch is very large, than our redo log can run out of space.
The insertXML function inserts the given xml document (xmlDoc) in to the table or view (tableName) specified. It returns the number of rows inserted.
Methods relevant to "XML to DB" and "DB to XML" functionality:
This sets the tag name which is used as the markup for each row in the result. The default value for the row tag is the same as DEFAULT_ROWTAG. A string of null value suppresses printing of this tag.(i.e. no row separators are to be used around each row).
If the row tag is set to null, then the result can either contain many single column rows or one multi-column row.
In the case of inserts, if the row tag is not set, then the input document must correspond to a single row.
The resetOptions procedure resets all the options back to their default values. If this procedure is not called then all the option settings (such as setMaxRows etc..) will be used in all of the subsequent getXML() and insertXML() calls for the remainder of the session.