| General Information |
| Note: Converts the results
of a SQL query to a canonical XML format. The package takes an arbitrary SQL query as
input, converts it to XML format, and returns the result as a CLOB. This package is
similar to the DBMS_XMLQUERY package, except that it is written in C and compiled into the
kernel. This package can only be run on the database. |
| Source |
$ORACLE_HOME/rdbms/admin/dbmsxml.sql |
| First Availability |
9.0.1 |
| Constants |
| Name |
Data Type |
Value |
|
Conversion or Schema Specs |
| DTD |
NUMBER |
1 |
| NONE |
NUMBER |
0 |
| SCHEMA |
NUMBER |
2 |
|
Conversion Type |
| ENTITY_DECODE |
conversionType |
1 |
| ENTITY_ENCODE |
conversionType |
0 |
|
Null Handling |
| DROP_NULLS |
NUMBER |
0 |
| EMPTY_TAG |
NUMBER |
2 |
| NULL_ATTR |
NUMBER |
1 |
|
| Defined Data Types |
SUBTYPE ctxHandle IS NUMBER;
SUBTYPE ctxType IS NUMBER;
SUBTYPE conversionType IS NUMBER;
TYPE PARAM_HASH IS TABLE OF VARCHAR2(100)
INDEX BY VARCHAR2(32);
/ |
| Dependencies |
| DBMS_LOB |
SDO_OLS |
URITYPE |
WWV_FLOW_DATLOAD_XML |
|
DBMS_XMLGEN_LIB |
URIFACTORY |
WRI$_ADV_OBJSPACE_TREND_T |
XMLTYPE |
|
| Security Model |
Execute is granted to PUBLIC |
Is XDK For PL/SQL Installed |
col comp_name format a45
SELECT comp_name, status, substr(version,1,10) as version
FROM dba_registry;
-- should return
COMP_NAME
STATUS VERSION
----------------------
-------- ---------
Oracle Database Catalog Views VALID
10.2.0.1.0
Oracle Database Packages and Types VALID 10.2.0.1.0
Oracle Workspace Manager
VALID 10.2.0.1.0
JServer JAVA Virtual Machine VALID
10.2.0.1.0
Oracle XDK
VALID 10.2.0.1.0
Oracle Database Java Packages VALID
10.2.0.1.0
Oracle Expression Filter
VALID 10.2.0.1.0
Oracle interMedia
VALID 10.2.0.1.0
Oracle Data Mining
VALID 10.2.0.1.0
Oracle Text
VALID 10.2.0.1.0
Oracle XML Database
VALID 10.2.0.1.0
OLAP Analytic Workspace
VALID 10.2.0.1.0
Oracle OLAP API
VALID 10.2.0.1.0
OLAP Catalog
VALID 10.2.0.1.0
Spatial
VALID 10.2.0.1.0
Oracle Enterprise Manager VALID
10.2.0.1.0
Oracle Ultra Search
NO SCRIPT 10.1.0.4.0
-- if not installed run
$ORACLE_HOME/rdbms/admin/initxml.sql
|
| Demo Tables |
-- emp table from SCOTT schema
CREATE TABLE test (testcol VARCHAR2(4000)); |
| |
| clearBindValues |
| Undocumented |
dbms_xmlgen.clearBindValues(ctx IN ctxHandle); |
| TBD |
| |
| closeContext |
Closes a given context and releases all
resources associated with it, including the SQL cursor and bind and define buffers |
dbms_xmlgen.closeContext(ctx IN ctxHandle); |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =
'|| emp_no);
dbms_xmlgen.closeContext(ctx);
dbms_output.put_line(ctx);
END;
/ |
| |
| Convert |
Converts the XML into the escaped or
unescaped XML equivalent
Overload 1 |
dbms_xmlgen.convert(
xmlData IN VARCHAR2,
flag IN NUMBER := ENTITY_ENCODE)
RETURN VARCHAR2;
-- Flags are the Conversion Type constants |
| TBD |
| Overload 2 |
dbms_xmlgen.convert(
xmlData IN CLOB,
flag IN NUMBER := ENTITY_ENCODE)
RETURN CLOB;
-- Flags are the Conversion Type constants |
| TBD |
| |
| getNumRowsProcessed |
Returns the number of SQL rows that were
processed in the last call to getXML |
dbms_xmlgen.getNumRowsProcessed(ctx IN ctxHandle)
RETURN NUMBER; |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
i PLS_INTEGER;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
xml := dbms_xmlgen.getXML(ctx);
i := dbms_xmlgen.getNumRowsProcessed(ctx);
dbms_output.put_line(TO_CHAR(i));
END;
/ |
| |
| getXML |
Gets the XML document
Overload 1 |
dbms_xmlgen.getXML(
ctx IN ctxHandle,
tmpclob IN OUT
NCOPY CLOB,
dtdOrSchema IN NUMBER := NONE); |
| TBD |
| Overload 2 |
dbms_xmlgen.getXML(
ctx IN ctxHandle,
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB; |
| TBD |
Overload 3 |
dbms_xmlgen.getXML(
sqlQuery IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN CLOB;
|
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- Required to convert
LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =
'|| emp_no);
dbms_output.put_line(ctx);
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- Display first part
dbms_output.put_line(xmlc);
END;
/ |
| |
| getXMLType |
Gets the XML document and returns it as XMLType
Overload 1 |
dbms_xmlgen.getXMLType(
ctx IN
ctxHandle,
tmpxmltype IN OUT NOCOPY xmltype,
dtdOrSchema IN NUMBER := NONE); |
| See
newContextFromHierarchy Demo |
| Overload 2 |
dbms_xmlgen.getXMLType(
ctx IN ctxHandle,
dtdOrSchema IN number := NONE)
RETURN sys.XMLType;
|
| TBD |
| Overload 3 |
dbms_xmlgen.getXMLType(
sqlQuery IN VARCHAR2,
dtdOrSchema IN NUMBER := NONE)
RETURN sys.XMLType;
|
| TBD |
| |
| newContext |
Creates a new context handle
Overload 1 |
dbms_xmlgen.newContext(query VARCHAR2) RETURN
ctxHandle; |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE
empno = '|| emp_no);
dbms_output.put_line(ctx);
dbms_xmlgen.closeContext(ctx);
END;
/ |
| Overload 2 |
dbms_xmlgen.newContext(queryString SYS_REFCURSOR)
RETURN ctxHandle; |
| TBD |
| |
| newContextFromHierarchy |
Undocumented. Used as part of new XML document creation |
dbms_xmlgen.newContextFromHierarchy(queryString IN
VARCHAR2)
RETURN ctxHandle; |
conn scott/tiger
desc emp
set serveroutput on
DECLARE
qryctx dbmx_xmlgen.ctxhandle;
result XMLTYPE;
PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
l_clob_length NUMBER;
l_iterations NUMBER;
l_chunk VARCHAR2(32767);
l_chunk_length NUMBER := 32767;
BEGIN
l_clob := p_clob;
l_clob_length := dbms_lob.getlength(l_clob);
l_iterations := CEIL(l_clob_length / l_chunk_length);
FOR i IN 0 .. l_iterations - 1 LOOP
l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
dbms_output.put_line(l_chunk);
END LOOP;
END;
BEGIN
qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
XMLElement("Position", XMLElement("Name", ename),
XMLElement("Title",
job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_xmlgen.closeContext(qryctx);
lob_output(RESULT.getClobVal());
END;
/ |
| |
| restartQUERY |
| Restarts the query to start fetching from
the beginning |
dbms_xmlgen.restartQUERY(ctx IN ctxHandle); |
| TBD |
| |
| removeXSLTParam |
| Undocumented XSLT
Support |
dbms_xmlgen.removeXSLTParam(ctx IN ctxType, name IN VARCHAR2); |
| TBD |
| |
| setBindValue |
| Undocumented |
dbms_xmlgen.setBindValue(
ctx IN ctxHandle,
bindName IN VARCHAR2,
bindValue IN VARCHAR2);
|
| TBD |
| |
| setCheckInvalidChars |
Sets whether checking for invalid characters
such as the NULL character |
dbms_xmlgen.setCheckInvalidChars(ctx IN
ctxHandle, chk IN BOOLEAN);
|
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
emp_no NUMBER := 7369;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE
empno = '|| emp_no);
dbms_xmlgen.setCheckInvalidChars(ctx, TRUE);
dbms_xmlgen.closeContext(ctx);
dbms_output.put_line(ctx);
END;
/ |
| |
| setConvertSpecialChars |
| Sets whether special characters such as $, which are non-XML characters, should be converted or not to their escaped representation |
dbms_xmlgen.setConvertSpecialChars(ctx IN
ctxHandle, conv IN BOOLEAN); |
| TBD |
| |
| setIndentationWidth |
Undocumented |
dbms_xmlgen.setIndentationWidth(ctx IN
ctxHandle, width IN NUMBER); |
conn scott/tiger
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- Required to convert LOB to
VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno =
'|| emp_no);
dbms_xmlgen.setIndentationWidth(ctx, 10);
dbms_output.put_line(ctx);
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- Display first part
dbms_output.put_line(xmlc);
END;
/ |
| |
| SetMaxRows |
Sets the maximum number of rows to be fetched each time |
dbms_xmlgen.setMaxRows(ctx IN ctxHandle, maxRows IN
NUMBER); |
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
i PLS_INTEGER;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp');
dbms_xmlgen.setMaxRows(ctx, 14);
xml := dbms_xmlgen.getXML(ctx);
i := dbms_xmlgen.getNumRowsProcessed(ctx);
dbms_output.put_line(TO_CHAR(i));
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- Display first part
INSERT INTO test (testcol) VALUES (xmlc);
COMMIT;
END;
/ |
| |
| SetNullHandling |
| Sets NULL handling options |
dbms_xmlgen.setNullHandling(ctx IN ctxHandle, flag IN NUMBER);
-- Flags are the NULL handling constants |
| TBD |
| |
| SetPrettyPrinting |
| Undocumented |
dbms_xmlgen.setPrettyPrinting(ctx IN ctxHandle, pp IN
BOOLEAN); |
| TBD |
| |
| setRowSetTag |
Sets the name of the element enclosing the entire result |
dbms_xmlgen.setRowSetTag(ctx IN
ctxHandle, rowSetTagName
IN VARCHAR2); |
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setRowSetTag(ctx, 'SRST');
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- Display first part
dbms_output.put_line(xmlc);
END;
/ |
| |
| setRowTag |
Sets the name of the element enclosing each row of the result |
dbms_xmlgen.setRowTag(ctx IN ctxHandle, rowTagName IN
VARCHAR2); |
set serveroutput on
DECLARE
ctx dbms_xmlgen.ctxHandle;
xml CLOB;
emp_no NUMBER := 7369;
xmlc VARCHAR2(4000); -- required to convert LOB to VARCHAR2
off PLS_INTEGER := 1;
len PLS_INTEGER := 4000;
BEGIN
ctx := dbms_xmlgen.newContext('SELECT * FROM emp WHERE empno = '|| emp_no);
dbms_xmlgen.setRowTag(ctx, 'SRT');
xml := dbms_xmlgen.getXML(ctx);
dbms_output.put_line(xml);
dbms_xmlgen.closeContext(ctx);
dbms_lob.read(xml, len, off, xmlc); -- Display first part
dbms_output.put_line(xmlc);
END;
/ |
| |
| setSkipRows |
| Sets the number of rows to skip every time before generating the XML |
dbms_xmlgen.setSkipRows(ctx IN ctxHandle,
skipRows IN
NUMBER); |
| TBD |
| |
| setXSLT |
XSLT Support
Overload 1 |
dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN CLOB); |
| TBD |
| Overload 2 |
dbms_xmlgen.setXSLT(ctx IN ctxType, stylesheet IN XMLType); |
| TBD |
| Overload 3 |
dbms_xmlgen.setXSLT(ctx IN ctxType, uri IN VARCHAR2); |
| TBD |
| |
| setXSLTParam |
| Undocumented |
dbms_xmlgen.setXSLT(ctx IN ctxType, name IN VARCHAR2,
value IN VARCHAR2); |
| TBD |
| |
| useItemTabsForColl |
| Forces the use of the collection column name appended with the tag _ITEM for collection elements |
dbms_xmlgen.useItemTagsForColl(ctx IN ctxHandle); |
| TBD |
| |
| useNullAttributeIndicator |
| Specified whether to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document. |
dbms_xmlgen.useNullAttributeIndicator(
ctx IN ctxHandle,
attrind IN BOOLEAN := TRUE); |
| TBD |
