Oracle DBMS_XMLSAVE
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Save XML to relational tables. The package body source code is in clear, unwrapped, text.
AUTHID CURRENT_USER
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
Documented No
First Available Not known
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/xdk/admin/dbmsxsu.sql
Subprograms
 
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 Demo Above
 
CLOSECONTEXT
Closes/deallocates a particular save context dbms_xmlsave.closeContext(ctxHdl IN ctxType);
See ClearKeyColumnList Demo 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;
conn scott/tiger@pdborcl

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 Demo 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 workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_propagateOriginalException(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETPRESERVEWHITESPACE
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_setPreserveWhiteSpace(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETSQLTOXMLNAMEESC
Undocumented workaround related to an ORA-00600 [kgmexchi11] dbms_xmlsave.p_setSQLToXMLNameEsc(ctxHdl IN ctxType, flag IN NUMBER);
TBD
 
P_SETXSLT
Undocumented 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 IN CLOB,
ref        IN VARCHAR2);
TBD
 
P_USEDBDATES
Undocumented 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);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setBatchSize(l_ctx, 250);
END;
/
 
SETCOMMITBATCH
Sets the commit batch size dbms_xmlsave.setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setCommitBatch(l_ctx, 100);
END;
/
 
SETDATEFORMAT
Sets the format of the generated dates in the XML document dbms_xmlsave.setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setDateFormat(l_ctx, dbms_xmlsave.default_date_format);
END;
/
 
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
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);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setPreserveWhitespace(l_ctx, TRUE);
END;
/
 
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);
DECLARE
 l_ctx dbms_xmlsave.ctxType;
BEGIN
  l_ctx := dbms_xmlsave.newContext('EMP2');
  dbms_xmlsave.setSQLToXMLNameEscaping(l_ctx, TRUE);
END;
/
 
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
Used Database Dates 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;
/

Related Topics
DBMS_XMLGEN
DBMS_XMLSTORE
Packages
XML Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved