| General Information |
| Purpose |
Save XML to relational tables. The package body source code is in clear, unwrapped, text. |
| Source |
{ORACLE_HOME}/xdk/admin/dbmsxsu.sql |
| Constants |
| Name |
Data Type |
Value |
| DEFAULT_DATE_FORMAT |
VARCHAR2(21) |
'MM/dd/yyyy HH:mm:ss'; |
| DEFAULT_ROWTAG |
VARCHAR2(3) |
'ROW' |
| IGNORE_CASE |
NUMBER |
1 |
| MATCH_CASE |
NUMBER |
0 |
|
| Data Types |
SUBTYPE ctxType IS NUMBER; |
| Dependencies |
Only Standard |
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
| CLEARKEYCOLUMNLIST |
P_SETPRESERVEWHITESPACE |
SETKEYCOLUMN |
| CLEARUPDATECOLUMNLIST |
P_SETSQLTOXMLNAMEESC |
SETPRESERVEWHITESPACE |
| CLOSECONTEXT |
P_SETXSLT |
SETROWTAG |
| DELETEXML |
P_USEDBDATES |
SETSQLTOXMLNAMEESCAPING |
| GETEXCEPTIONCONTENT |
REMOVEXSLTPARAM |
SETUPDATECOLUMN |
| INSERTXML |
SETBATCHSIZE |
SETXSLT |
| NEWCONTEXT |
SETCOMMITBATCH |
SETXSLTPARAM |
| PROPAGATEORIGINALEXCEPTION |
SETDATEFORMAT |
UPDATEXML |
| P_PROPAGATEORIGINALEXCEPTION |
SETIGNORECASE |
USEDBDATES |
|
| |
| CLEARKEYCOLUMNLIST |
| Clears the key column list |
dbms_xmlsave.clearKeyColumnList(ctxHdl IN ctxType); |
CREATE TABLE orders (
order_id INTEGER,
order_revision INTEGER,
order_date DATE NOT NULL,
order_header_hash INTEGER NOT NULL,
order_currency VARCHAR2(3) NOT NULL,
order_language VARCHAR2(3) NOT NULL,
machine_name VARCHAR2(20) NOT NULL,
assembly_name VARCHAR2(255) NOT NULL,
user_name VARCHAR2(20) NOT NULL,
calling_meth_name VARCHAR2(255) NOT NULL);
ALTER TABLE orders
ADD CONSTRAINT pk_orders
PRIMARY KEY (order_id, order_revision);
CREATE INDEX ix_orders_order_date
ON orders(order_date);
CREATE INDEX ix_orders_header_hash
ON orders(order_header_hash);
DECLARE
xmlCtx dbms_xmlsave.ctxType;
BEGIN
xmlCtx := dbms_xmlsave.newContext('ORDERS');
dbms_xmlsave.clearKeyColumnList(xmlCtx);
dbms_xmlsave.clearUpdateColumnList(xmlCtx);
dbms_xmlsave.closeContext(xmlCtx);
END;
/ |
| |
| CLEARUPDATECOLUMNLIST |
| Clears the update column list |
dbms_xmlsave.clearUpdateColumnList(ctxHdl IN ctxType); |
| See ClearKeyColumnList procedure above |
| |
| CLOSECONTEXT |
| It closes/deallocates a particular save context |
dbms_xmlsave.closeContext(ctxHdl IN ctxType); |
| See ClearKeyColumnList procedure above |
| |
| DELETEXML |
Deletes records specified by data from the XML document, from specified table
Overload 1 |
dbms_xmlsave.deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
| TBD |
| Overload 2 |
dbms_xmlsave.deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| TBD |
| |
| GETEXCEPTIONCONTENT |
| Returns the thrown exception's error code and error message |
dbms_xmlsave.deleteXML(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2); |
| TBD |
| |
| INSERTXML |
Inserts the XML document into the table specified at the context creation time
Overload 1 |
dbms_xmlsave.insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
CREATE TABLE emp2 AS
SELECT * FROM scott.emp
WHERE 1=2;
DECLARE
l_clob clob := '<?xml version = "1.0"?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>12/17/1980 0:0:0</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="2">
<EMPNO>7499</EMPNO>
<ENAME>ALLEN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/20/1981 0:0:0</HIREDATE>
<SAL>1600</SAL>
<COMM>300</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="3">
<EMPNO>7521</EMPNO>
<ENAME>WARD</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>2/22/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>500</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="4">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 0:0:0</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="5">
<EMPNO>7654</EMPNO>
<ENAME>MARTIN</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/28/1981 0:0:0</HIREDATE>
<SAL>1250</SAL>
<COMM>1400</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="6">
<EMPNO>7698</EMPNO>
<ENAME>BLAKE</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>5/1/1981 0:0:0</HIREDATE>
<SAL>2850</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="7">
<EMPNO>7782</EMPNO>
<ENAME>CLARK</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>6/9/1981 0:0:0</HIREDATE>
<SAL>2450</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="8">
<EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>4/19/1987 0:0:0</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="9">
<EMPNO>7839</EMPNO>
<ENAME>KING</ENAME>
<JOB>PRESIDENT</JOB>
<HIREDATE>11/17/1981 0:0:0</HIREDATE>
<SAL>5000</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
<ROW num="10">
<EMPNO>7844</EMPNO>
<ENAME>TURNER</ENAME>
<JOB>SALESMAN</JOB>
<MGR>7698</MGR>
<HIREDATE>9/8/1981 0:0:0</HIREDATE>
<SAL>1500</SAL>
<COMM>0</COMM>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="11">
<EMPNO>7876</EMPNO>
<ENAME>ADAMS</ENAME>
<JOB>CLERK</JOB>
<MGR>7788</MGR>
<HIREDATE>5/23/1987 0:0:0</HIREDATE>
<SAL>1100</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="12">
<EMPNO>7900</EMPNO>
<ENAME>JAMES</ENAME>
<JOB>CLERK</JOB>
<MGR>7698</MGR>
<HIREDATE>12/3/1981 0:0:0</HIREDATE>
<SAL>950</SAL>
<DEPTNO>30</DEPTNO>
</ROW>
<ROW num="13">
<EMPNO>7902</EMPNO>
<ENAME>FORD</ENAME>
<JOB>ANALYST</JOB>
<MGR>7566</MGR>
<HIREDATE>12/3/1981 0:0:0</HIREDATE>
<SAL>3000</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW num="14">
<EMPNO>7934</EMPNO>
<ENAME>MILLER</ENAME>
<JOB>CLERK</JOB>
<MGR>7782</MGR>
<HIREDATE>1/23/1982 0:0:0</HIREDATE>
<SAL>1300</SAL>
<DEPTNO>10</DEPTNO>
</ROW>
</ROWSET>';
l_ctx dbms_xmlsave.ctxType;
l_rows number;
BEGIN
l_ctx := dbms_xmlsave.newContext('EMP2');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
dbms_output.put_line(l_rows || ' rows inserted...');
END insert_xml_emps;
/ |
| Overload 2 |
dbms_xmlsave.insertXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| TBD |
| |
| NEWCONTEXT |
| Creates a save context, and returns the context handle |
dbms_xmlsave.newContext(targetTable IN VARCHAR2) RETURN ctxType; |
| See ClearKeyColumnList procedure above |
| |
| PROPAGATEORIGINALEXCEPTION |
| Tells the XSU that if an exception is raised, the XSU should throw the exception rather then,
wrapping it with an OracleXMLSQLException |
dbms_xmlsave.propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN); |
| TBD |
| |
| P_PROPAGATEORIGINALEXCEPTION |
| Undocumented but workaround related to an ORA-00600 [kgmexchi11] |
dbms_xmlsave.p_propagateOriginalException(ctxHdl IN ctxType, flag IN NUMBER); |
| TBD |
| |
| P_SETPRESERVEWHITESPACE |
| Undocumented but workaround related to an ORA-00600 [kgmexchi11] |
dbms_xmlsave.p_setPreserveWhiteSpace(ctxHdl IN ctxType, flag IN NUMBER); |
| TBD |
| |
| P_SETSQLTOXMLNAMEESC |
| Undocumented but workaround related to an ORA-00600 [kgmexchi11] |
dbms_xmlsave.p_setSQLToXMLNameEsc(ctxHdl IN ctxType, flag IN NUMBER); |
| TBD |
| |
| P_SETXSLT |
Undocumented but workaround related to an ORA-00600 [kgmexchi11]
Overload 1 |
dbms_xmlsave.p_setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2); |
| TBD |
| Overload 2 |
dbms_xmlsave.p_setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2); |
| TBD |
| |
| P_USEDBDATES |
| Undocumented but workaround related to an ORA-00600 [kgmexchi11] |
dbms_xmlsave.p_useDBDates(ctxHdl IN ctxType, flag IN NUMBER); |
| TBD |
| |
| REMOVEXSLTPARAM |
| Removes the value of a top-level stylesheet parameter |
dbms_xmlsave.removeXSLTParam(ctxHdl IN ctxType, name IN VARCHAR2); |
| TBD |
| |
| SETBATCHSIZE |
| Changes the batch size used during DML operations |
dbms_xmlsave.setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER); |
| TBD |
| |
| SETCOMMITBATCH |
| Sets the commit batch size |
dbms_xmlsave.setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER); |
| TBD |
| |
| SETDATEFORMAT |
| Sets the format of the generated dates in the XML document |
dbms_xmlsave.setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2); |
| TBD |
| |
| SETIGNORECASE |
| The XSU does mapping of XML elements to database |
dbms_xmlsave.setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER); |
DECLARE
cnt INTEGER;
xmlCtx dbms_xmlsave.ctxType;
xmlTxt CLOB :='<Log><Item><OldValue>jones</OldValue><NewValue>JONES</NewValue></Item>'
||'<Item><OldValue>smith</OldValue><NewValue>SMITH</NewValue></Item></Log>';
BEGIN
xmlCtx := dbms_xmlsave.newContext('T');
dbms_xmlsave.setIgnoreCase(xmlCtx, 1);
dbms_xmlsave.closeContext(xmlCtx);
END;
/ |
| |
| SETKEYCOLUMN |
| This methods adds a column to the key column list |
dbms_xmlsave.setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2); |
| TBD |
| |
| SETPRESERVEWHITESPACE |
| Tells the XSU whether to preserve whitespace or not |
dbms_xmlsave.setPreserveWhitespace(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE); |
| TBD |
|
| SETROWTAG |
| Names the tag used in the XML document to enclose the XML elements corresponding to database |
dbms_xmlsave.setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2); |
| TBD |
| |
| SETSQLTOXMLNAMEESCAPING |
| 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 |
dbms_xmlsave.setSQLToXMLNameEscaping(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE); |
| TBD |
| |
| SETUPDATECOLUMN |
| Adds a column to the update column list |
dbms_xmlsave.setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2); |
| TBD |
| |
| SETXSLT |
Registers a XSL transform to be applied to the XML to be saved
Overload 1 |
dbms_xmlsave.setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2 := NULL); |
| TBD |
| Overload 2 |
dbms_xmlsave.setXSLT(ctxHdl IN ctxType, stylesheet IN CLOB, ref IN VARCHAR2 := NULL); |
| TBD |
| |
| SETXSLTPARAM |
| Sets the value of a top-level stylesheet parameter |
dbms_xmlsave.setXSLTParam(ctxHdl IN ctxType,name IN VARCHAR2,value IN VARCHAR2); |
| TBD |
| |
| UPDATEXML |
Updates the table given the XML document
Overload 1 |
dbms_xmlsave.updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER; |
| TBD |
| Overload 2 |
dbms_xmlsave.updateXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER; |
| TBD |
| |
| USEDBDATES |
| Undocumented |
dbms_xmlsave.useDBDates(ctxHdl IN ctxType, flag IN BOOLEAN := TRUE); |
DECLARE
xmlCtx dbms_xmlsave.ctxType;
BEGIN
xmlCtx := dbms_xmlsave.newContext('ORDERS');
dbms_xmlsave.useDBDates(xmlCtx, TRUE);
dbms_xmlsave.closeContext(xmlCtx);
END;
/ |
| |
