Oracle | DBMS

Oracle DBMS_XMLSAVE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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

STANDARD (only)

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	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 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;

      /