XSU's PL/SQL API -- XMLGEN


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.



Variable Index

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:

ALL_ROWS

    ALL_ROWS CONSTANT NUMBER
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.

SKIP_NONE

    SKIP_NONE CONSTANT NUMBER
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.

NONE

    NONE CONSTANT NUMBER
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.

DTD

    DTD CONSTANT NUMBER
The DTD constant indicates that the DTD should be included in the output. This can be passed to the getXML() function.

DEFAULT_ROWSETTAG

        DEFAULT_ROWSETTAG CONSTANT VARCHAR2(6) := 'ROWSET';
The DEFAULT_ROWSETTAG can be passed to the setRowsetTag() procedure and specifies the default tag name for the root of the document

DEFAULT_ERRORTAG

        DEFAULT_ERRORTAG CONSTANT VARCHAR2(5) := 'ERROR';
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

        DEFAULT_ROWIDATTR CONSTANT VARCHAR2(3) := 'NUM';

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.

 

Constants relevant to "XML to DB" functionality:

MATCH_CASE

        MATCH_CASE  CONSTANT NUMBER := 0;
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".

IGNORE_CASE

        IGNORE_CASE  CONSTANT NUMBER := 0;
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".

DEFAULT_DATE_FORMAT

        DEFAULT_DATE_FORMAT CONSTANT VARCHAR2(21) := 'YYYY-MM-DD HH24:MI:SS';

The DEFAULT_DATE_FORMAT defines the default format used for inserting date values. This constant can be passed to the setDateFormat procedure.

 

Constants relevant to "XML to DB" and "DB to XML" functionality:

DEFAULT_ROWTAG

        DEFAULT_ROWTAG CONSTANT VARCHAR2(3) := 'ROW';

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:

getXML

    FUNCTION getXML(query IN VARCHAR2, metaType IN number := NONE) RETURN CLOB
    FUNCTION getXML(query IN CLOB,             metaType IN number := NONE) RETURN CLOB
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.)

getDTD

    FUNCTION getDTD(query IN VARCHAR2, withVer IN BOOLEAN := NONE) RETURN CLOB
    FUNCTION getDTD(query IN CLOB,             withVer IN BOOLEAN := NONE) RETURN CLOB
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.)

setRowsetTag

    PROCEDURE setRowsetTag( tag IN VARCHAR2 );
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.

setRowIdAttrName

    PROCEDURE setRowIdAttrName(tag IN VARCHAR2);
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.

setRowIdColumn

    PROCEDURE setRowIdColumn(columnName IN VARCHAR2);
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.

setCollIdAttr

    PROCEDURE setCollIdAttr(attrname IN VARCHAR2);
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.

useNullAttributeIndicator

    PROCEDURE useNullAttributeIndicator(flag IN BOOLEAN);
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.

setErrorTag

    PROCEDURE setErrorTag( tag IN VARCHAR2 );
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.

useUpperCaseTagNames

    PROCEDURE useUpperCaseTagNames;
This forces all the tag names to be in upper case.

useLowerCaseTagNames

    PROCEDURE useLowerCaseTagNames;
This forces all the tag names to be in lower case.

useDefaultCaseTagNames

    PROCEDURE useDefaultCaseTagNames;
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.

setMaxRows

    PROCEDURE setMaxRows(rows IN NUMBER);
This procedure sets the maximum number of rows of the query result to be used in generating the XML document.

setSkipRows

    PROCEDURE setSkipRows(rows IN NUMBER);
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.

setStylesheetType

    PROCEDURE setStylesheetType( type IN VARCHAR2);
This procedure sets the stylesheet type in the generated XML documents PI (processing instructions) header.

setStylesheet

    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.

setBindValue

    PROCEDURE setBindValue(bName IN VARCHAR2, bVALUE IN VARCHAR2);
Sets the value for a particular name bind that was specified in the SQL query.

clearBindValues

    PROCEDURE clearBindValues;
Clears the bind values for all the name binds in the SQL query.

setMetaHeader

    PROCEDURE setMetaHeader(header IN CLOB := null );
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.

setDataHeader

    PROCEDURE setDataHeader(header IN CLOB := null, docTag IN VARCHAR2 :=null );
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.

setRaiseException

    PROCEDURE setRaiseException( flag IN BOOLEAN);
Tells the XSU to create NO error document when catching an exception; but rather, to propagates the exception.

propagateOriginalException

    PROCEDURE propagateOriginalException( flag in BOOLEAN);
Tell the XSU to throw the original exception rather than the OracleXMLSQLException wrapped around the original exception.

getExceptionContent

    PROCEDURE getExceptionContent(errNo OUT NUMBER, errMsg OUT VARCHAR2 );
If raiseOriginalException is true, this call returns the original exception's error code and error message (i.e. sql error code).

setLobDuration

    PROCEDURE setLobDuration(duration IN VARCHAR2);

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:

setDateFormat

    PROCEDURE setDateFormat(dateFormat IN varchar2);
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).

setIgnoreTagCase

    PROCEDURE setIgnoreTagCase(ignore IN number);
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.

setUpdateColumn

    PROCEDURE setUpdateColumn(colName IN VARCHAR2);
Adds a column to the list of columns to be updated.

clearUpdateColumnList

    PROCEDURE clearUpadateColumnList;
Removes all the columns from the list of columns which are to be updated.

setKeyColumn

    PROCEDURE setKeyColumn(colName IN VARCHAR2);
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.

clearKeyColumnList

    PROCEDURE clearKeyColumnList;
Clear the list of "key" columns.

setBatchSize

    PROCEDURE setBatchSize(size IN number)
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.

setCommitBatch

    PROCEDURE setInsertBatch(size IN number)
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.

insertXML

    FUNCTION insertXML(tableName IN varchar2, xmlDoc IN varchar2) RETURN NUMBER;
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:

setRowTag

    PROCEDURE setRowTag( tag IN VARCHAR2 );
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.

resetOptions

    PROCEDURE resetOptions;
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.