General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
Purpose
Save XML to relational tables. The package body source code is in clear, unwrapped, text.
This package was deprecated in 18.1 in favor of DBMS_XMLSTORE.
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
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@pdbdev
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;
/